Details
-
Bug
-
Status: Closed
-
Minor
-
Resolution: Won't Fix
-
10.11.1.1
-
None
-
None
-
Blocker
-
Release Note Needed, Repro attached
-
Deviation from standard
Description
According to the SQL Standard, identity columns are supposed to behave as though they are backed by internal sequence generators. This means that the current value of an identity column is not affected by whether you delete rows from the table. Deleting rows from a table does not change the internal state of the sequence generator.
Sequence generators have the following state:
minValue
maxValue
initValue
currentValue
step
cycleOrNot
The NEXT VALUE FOR expression causes the sequence generator to do two things:
1) Return currentValue
2) Set currentValue = currentValue + step
You can use ALTER TABLE to change the step value of an identity column's internal sequence generator:
ALTER TABLE t1 ALTER b SET INCREMENT BY 100;
This is not supposed to affect the current value of the sequence generator which backs the identity column. However, Derby resets currentValue as follows:
currentValue = ( SELECT MAX( b ) FROM t1 ) + newStep
This behavior is deliberate (see AlterTableConstantAction.getColumnMax()) and has been part of Derby since it was open-sourced.
The following script shows this behavior:
ij version 10.11 ij> connect 'jdbc:derby:memory:db;create=true'; ij> create table t1( a int, b int generated always as identity ); 0 rows inserted/updated/deleted ij> insert into t1( a ) values ( 100 ), ( 200 ); 2 rows inserted/updated/deleted ij> select * from t1 order by a; A |B ----------------------- 100 |1 200 |2 2 rows selected ij> alter table t1 alter b set increment by 10; 0 rows inserted/updated/deleted ij> -- the next values of the identity column should be 3 then 13. but they are 12 and 22 insert into t1( a ) values ( 300 ), ( 400 ); 2 rows inserted/updated/deleted ij> select * from t1 order by a; A |B ----------------------- 100 |1 200 |2 300 |12 400 |22 4 rows selected ij> -- -- Try the same experiment but delete the last row. -- drop table t1; 0 rows inserted/updated/deleted ij> create table t1( a int, b int generated always as identity ); 0 rows inserted/updated/deleted ij> insert into t1( a ) values ( 100 ), ( 200 ); 2 rows inserted/updated/deleted ij> select * from t1 order by a; A |B ----------------------- 100 |1 200 |2 2 rows selected ij> delete from t1 where a = 200; 1 row inserted/updated/deleted ij> alter table t1 alter b set increment by 10; 0 rows inserted/updated/deleted ij> -- deleting rows changes the behavior of the insert. -- now the next values are 11 and 21. insert into t1( a ) values ( 300 ), ( 400 ); 2 rows inserted/updated/deleted ij> select * from t1 order by a; A |B ----------------------- 100 |1 300 |11 400 |21 3 rows selected
I think that this divergence from the Standard is a minor edge-case. I am not inclined to fix it. However, I am also not inclined to preserve this divergence from the Standard when we start using real sequence generators to implement identity columns (see DERBY-6542).
Please speak up if you think that this bug should be fixed for identity columns in soft-upgraded databases or if you think that the new-style identity columns should preserve this divergence from the Standard.
Attachments
Attachments
Issue Links
- relates to
-
DERBY-6542 Improve the concurrency of identity columns by using SYS.SYSSEQUENCES
-
- Closed
-