db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6961) SET CYCLE fails to let an identity column cycle if the range is already exhausted
Date Mon, 18 Sep 2017 01:32:00 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6961?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16169500#comment-16169500
] 

ASF subversion and git services commented on DERBY-6961:
--------------------------------------------------------

Commit 1808668 from [~rhillegas] in branch 'code/trunk'
[ https://svn.apache.org/r1808668 ]

DERBY-6961: Correctly handle ALTER TABLE...SET [NO] CYCLE on exhausted identity columns; commit
derby-6961-01-aa-recyclingExhaustedIdentityColumns.diff.

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

Mime
View raw message