Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 14 Sep 2017 01:22:00 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.13101837.1505268913000.107912.1505352120315@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: Thu, 14 Sep 2017 01:22:08 -0000


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

Rick Hillegas commented on DERBY-6961:
--------------------------------------

Here's another, similar problem: If a CYCLE identity column is on the verge of wrapping around and then the column is ALTERed to NO CYCLE, then the next insert into the table should fail. But that insert succeeds, incorrectly. The following script shows this:

{noformat}
connect 'jdbc:derby:memory:db;create=true';

create table t1(a int generated always as identity (start with 2147483646 cycle), b int);
insert into t1(b) values (1);
alter table t1 alter column a set no cycle;
insert into t1(b) values (2);
-- fails as expected
insert into t1(b) values (3);

select * from t1 order by b;

------

create table t2(a int generated always as identity (start with 2147483646 cycle), b int);
insert into t2(b) values (1);
insert into t2(b) values (2);
alter table t2 alter column a set no cycle;

-- should fail but does not
insert into t2(b) values (3);

select * from t2 order by b;
{noformat}

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