Rick Hillegas created DERBY-6579:
------------------------------------
Summary: Changing the step value for an identity column
incorrectly changes its current value.
Key: DERBY-6579
URL: https://issues.apache.org/jira/browse/DERBY-6579
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.11.0.0
Reporter: Rick Hillegas
Priority: Minor
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:
{noformat}
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
{noformat}
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.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
