[
https://issues.apache.org/jira/browse/DERBY-6579?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14007239#comment-14007239
]
Rick Hillegas commented on DERBY-6579:
--------------------------------------
Thanks for that advice, Dag. I will add a release note to this issue and
resolve this issue as "won't fix".
> 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)
