[DERBY-1554] IDENTITY_VAL_LOCAL() returned value is modified incorrectly by a multi-row INSERT statement.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.2.1.6
• Fix Version/s: 10.1.3.2, 10.2.1.6
• Component/s: SQL
• Urgency: Urgent
• Bug behavior facts: Regression
• Assignee: Yip Ng
• Reporter: Daniel John Debrunner
• Created: 20/Jul/06 20:59
• Updated: 30/Jun/09 15:55
• Resolved: 10/Aug/06 13:44

Description

Changes made in DERBY-353 break the documented behaviour of IDENTITY_VAL_LOCAL(). Now the returned value is modified when a multi-row insert is performed.
IDENTITY_VAL_LOCAL() should not be updated when this occurs.

For this script the last value returned should be 2 (as in 10.1) but in 10.2 is 4

create table ta (a int, b int generated always as identity);
insert into ta(a) values 100;
values IDENTITY_VAL_LOCAL();
insert into ta(a) values 200;
values IDENTITY_VAL_LOCAL();
insert into ta(a) values 300,400;
values IDENTITY_VAL_LOCAL();

Issue Links

• relates to: DERBY-353 It is desirable to have IDENTITY_VAL_LOCAL() function return last recent user specified value or system generated value for BY DEFAULT identity columns.