[ https://issues.apache.org/jira/browse/DERBY-6961?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas resolved DERBY-6961. ---------------------------------- Resolution: Fixed Fix Version/s: 10.14.0.0 > SET CYCLE fails to let an identity column cycle if the range is already > exhausted > --------------------------------------------------------------------------------- > > Key: DERBY-6961 > URL: https://issues.apache.org/jira/browse/DERBY-6961 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.14.0.0 > Reporter: Rick Hillegas > Assignee: Rick Hillegas > Fix For: 10.14.0.0 > > Attachments: derby-6961-01-aa-recyclingExhaustedIdentityColumns.diff > > > If a NO CYCLE identity column exhausts its range, then... > ALTER TABLE ALTER COLUMN $columnName SET CYCLE > ...will not revive the identity column. No more rows can be inserted into the > table. This violates the SQL Standard and is very surprising behavior after > the ALTER TABLE command completed successfully. > The problem is that the exhausted sequence generator has a next value of > NULL, signifying that it is done. After the ALTER TABLE command, the next > value of the sequence generator should be the minimum value (for an ascending > sequence generator) or the maximum value (for a descending sequence > generator) according to the 2016 SQL Standard, section 4.27.2 (Operations > involving sequence generators), quoted here in full: > "When a <next value expression> is applied to a sequence generator SG, SG > issues a value V taken from SG's current cycle such that V is expressible as > the current base value of SG plus N multiplied by the increment of SG, where > N is a non-negative number. > Thus a sequence generator will normally issue all of the values in its cycle > and these will normally be in increasing or decreasing order (depending on > the sign of the increment) but within that general ordering separate > subgroups of ordered values may occur. > If the sequence generator's cycle is exhausted (i.e., it cannot issue a value > that meets the criteria), then a new cycle is created with the current base > value set to the minimum value of SG (if SG is an ascending sequence > generator) or the maximum value of SG (if SG is a descending sequence > generator). > If a new cycle is created and the descriptor of SG includes NOCYCLE, then an > exception condition is raised. > If there are multiple instances of <next value expression>s specifying the > same sequence generator within a single SQL-statement, all those instances > return the same value for a given row processed by that SQL-statement." > The following script shows this problem: > {noformat} > connect 'jdbc:derby:memory:db;create=true'; > ------------------------------------------------ > -- > -- Exhaust a NO CYCLE identity column. > -- SET CYCLE does not allow the sequence generator > -- to continue processing. > -- > ------------------------------------------------ > create table t_noCycleExhaust(a int generated always as identity (start with > 2147483646 no cycle), b int); > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEEXHAUST'); > insert into t_noCycleExhaust(b) values (1); > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEEXHAUST'); > insert into t_noCycleExhaust(b) values (2); > -- the sequence generator has NULL as its next value > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEEXHAUST'); > -- should fail > insert into t_noCycleExhaust(b) values (3); > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEEXHAUST'); > select * from t_noCycleExhaust order by b; > alter table t_noCycleExhaust alter column a set cycle; > -- the sequence generator still has NULL as its next value. this is the bug. > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEEXHAUST'); > -- incorrectly fails > insert into t_noCycleExhaust(b) values (3); > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEEXHAUST'); > select * from t_noCycleExhaust order by b; > ------------------------------------------------ > -- > -- Do NOT exhaust a NO CYCLE identity column. > -- Then SET CYCLE. The sequence generator will > -- wrap around. > -- > ------------------------------------------------ > create table t_noCycleDoNotExhaust(a int generated always as identity (start > with 2147483646 no cycle), b int); > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEDONOTEXHAUST'); > insert into t_noCycleDoNotExhaust(b) values (1); > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEDONOTEXHAUST'); > alter table t_noCycleDoNotExhaust alter column a set cycle; > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEDONOTEXHAUST'); > insert into t_noCycleDoNotExhaust(b) values (2); > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEDONOTEXHAUST'); > insert into t_noCycleDoNotExhaust(b) values (3); > values syscs_util.syscs_peek_at_identity('APP', 'T_NOCYCLEDONOTEXHAUST'); > select * from t_noCycleDoNotExhaust order by b; > {noformat} -- This message was sent by Atlassian JIRA (v6.4.14#64029)