Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Mon, 18 Sep 2017 01:32:00 +0000 (UTC)
From: "ASF subversion and git services (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.13101837.1505268913000.132906.1505698320299@Atlassian.JIRA>
In-Reply-To: <JIRA.13101837.1505268913000@Atlassian.JIRA>
References: <JIRA.13101837.1505268913000@Atlassian.JIRA> <JIRA.13101837.1505268913405@jira-lw-us.apache.org>
Subject: [jira] [Commented] (DERBY-6961) SET CYCLE fails to let an identity
 column cycle if the range is already exhausted
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Mon, 18 Sep 2017 01:32:04 -0000


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