[jira] [Created] (DERBY-6579) Changing the step value for an identity column incorrectly changes its current value.

["Rick Hillegas (JIRA)" <ji...@apache.org>]

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)