[
https://issues.apache.org/jira/browse/DERBY-6961?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16164078#comment-16164078
]
Bryan Pendleton commented on DERBY-6961:
----------------------------------------
Thanks for catching this, Rick.
I definitely don't think this was intentional behavior. I think we just missed
it during the implementation and testing.
Does it seem hard to fix?
> 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
>
> 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)
