[
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/%[email protected]%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)
