Major Description
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:
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;
Attachments
Attachments
Issue Links
- blocks
-
DERBY-6904 Enhance ALTER TABLE to allow CYCLE option to be changed.
-
- Resolved
-