[jira] [Updated] (DERBY-3888) ALTER TABLE ... ADD COLUMN cannot add identity columns

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

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

Knut Anders Hatlen updated DERBY-3888:
--------------------------------------

    Attachment: reenable.diff

It doesn't look too hard to re-enable this code, and the code still appears to be mostly working. The attached patch [^reenable.diff] removes the code that disables the functionality in the parser, and makes two small changes to AlterTableConstantAction:

# The column descriptor for the new column needs to be flagged as a new auto-increment column
# The internal statement that populates the new column, needs to be compiled with a flag that allows it to use internal syntax

Now, the following seems to work as expected:

{noformat}
ij> create table t(x int);
0 rows inserted/updated/deleted
ij> insert into t values 1,2,3;
3 rows inserted/updated/deleted
ij> alter table t add column y int generated always as identity (start with 100, increment by 5);
0 rows inserted/updated/deleted
ij> select * from t;
X          |Y          
-----------------------
1          |100        
2          |105        
3          |110        

3 rows selected
ij> insert into t(x) values 4;
1 row inserted/updated/deleted
ij> select * from t;
X          |Y          
-----------------------
1          |100        
2          |105        
3          |110        
4          |115        

4 rows selected
{noformat}

I've noticed some anomalies:

- Adding an identity column that is GENERATED BY DEFAULT only works if the table is empty. Otherwise, it will fail like this:

{noformat}
ij> alter table t2 add column y int generated by default as identity;
ERROR 23502: Column 'Y'  cannot accept a NULL value.
{noformat}

- ALTER TABLE allows you to add multiple identity columns. This is not necessarily a bad thing, but CREATE TABLE doesn't allow it, so it's not symmetric.

- Dropping an identity column that has been added this way, or dropping a table that contains such a column, fails. Possibly because the old code doesn't account for the changes made in DERBY-6542.

{noformat}
ij> drop table t;
ERROR X0X81: SEQUENCE 'SYS.U0ddd00a9X0146X0418X3aa4X00000d4d4fe5' does not exist.
{noformat}

> ALTER TABLE ... ADD COLUMN cannot add identity columns
> ------------------------------------------------------
>
>                 Key: DERBY-3888
>                 URL: https://issues.apache.org/jira/browse/DERBY-3888
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.4.2.0
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>              Labels: derby_triage10_10
>         Attachments: reenable.diff
>
>
> ALTER TABLE .. ADD COLUMN cannot be used to add an identity column. There is code to handle identity columns, but it is disabled in the parser. See this thread on derby-user:
> http://mail-archives.apache.org/mod_mbox/db-derby-user/200708.mbox/%3C46C5DAA9.8080507@sbcglobal.net%3E
> The code was disabled for DB2 compatibility. Since DB2 compatibility is not a goal for Derby, we should see if we could re-enable it.



--
This message was sent by Atlassian JIRA
(v6.2#6252)