Rick Hillegas created DERBY-6961:
------------------------------------

             Summary: 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)

Reply via email to