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

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

     [ https://issues.apache.org/jira/browse/DERBY-6579?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas resolved DERBY-6579.
----------------------------------

    Resolution: Won't Fix

This behavior is fixed for new-style identity columns which are implemented using sequence generators. However this behavior is not fixed for old-style identity columns created in databases which have not been hard-upgraded to 10.11. Resolving 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
>         Attachments: releaseNote.html
>
>
> 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)