[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: d3888-1a.diff

Thanks, Mamta. It looks like the patch for DERBY-6414 has been checked in, and it indeed helps with this issue.

The attached patch [^d3888-1a.diff] is a refreshed version of [^reenable-v3.diff], in which the conflicts with DERBY-6542 have been resolved.

DERBY-6542 removed the code that generated identity values for databases at version 10.10 and earlier. The patch instead uses the code added by DERBY-6414 to generate the identity values. Now ALTER TABLE will generate an UPDATE statement that looks like {{UPDATE <table> SET <col> = DEFAULT}} to populate the new column.

I don't want to revive the code that generates identity values for older database versions (it's not worth the maintenance cost to have two code paths for generating identity values), so adding an identity will only work if the database format is 10.11 or higher. If it's lower, you'll get an error such as this:

{noformat}
ij> alter table t1 add column id int generated always as identity;
ERROR XCL47: Use of 'ADD IDENTITY COLUMN' requires database to be upgraded from version 10.10 to version 10.11 or later.
{noformat}

All the regression tests passed with the patch. The patch is ready for review.

> 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: d3888-1a.diff, reenable-v2.diff, reenable-v3.diff, 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.3.4#6332)