[jira] [Updated] (DERBY-6962) Forbid ALTER TABLE ... SET CYCLE on identity columns in pre-10.11 databases

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6962?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-6962:
---------------------------------
    Summary: Forbid ALTER TABLE ... SET CYCLE on identity columns in pre-10.11 databases  (was: Forbid ALTER TABLE ... SET CYCLE on identity columns in pre-10.12 databases)

Thanks for catching that,  Bryan. I am updating the title on this issue as you recommended.

> Forbid ALTER TABLE ... SET CYCLE on identity columns in pre-10.11 databases
> ---------------------------------------------------------------------------
>
>                 Key: DERBY-6962
>                 URL: https://issues.apache.org/jira/browse/DERBY-6962
>             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-6962-01-aa-forbidSetCycleOn10_10.diff, derby-6962-01-ab-forbidSetCycleOn10_10.diff
>
>
> The
>   ALTER TABLE ALTER COLUMN $columnName SET CYCLE
> command does not raise an error even on identity columns in pre-10.12 databases. Those identity columns are not backed by sequence generators. Afterwards, the columns do not cycle, even though the ALTER TABLE command appeared to run successfully. This should be easy to fix. The parser just needs to forbid the ALTER TABLE...SET CYCLE command in pre-10.12 databases. The following scripts show this behavior:
> {noformat}
> -- run the following script with version 10.10.1.1:
> connect 'jdbc:derby:db;create=true';
> create table t1(a int generated always as identity (start with 2147483646), b int);
> insert into t1(b) values (1);
> 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), b int);
> -- now run the following script with 10.14
> connect 'jdbc:derby:db';
> -- succeeds but should not
> alter table t2 alter column a set cycle;
> insert into t2(b) values (1);
> insert into t2(b) values (2);
> -- fails because the cycling behavior did not change
> insert into t2(b) values (3);
> select * from t2 order by b;
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)