Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Wed, 21 May 2014 10:29:38 +0000 (UTC)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12405445.1222761704652.18660.1400668178147@arcas>
In-Reply-To: <JIRA.12405445.1222761704652@arcas>
References: <JIRA.12405445.1222761704652@arcas>
Subject: [jira] [Updated] (DERBY-3888) ALTER TABLE ... ADD COLUMN cannot add
 identity columns
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ 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-v3.diff

Attaching an updated patch, [^reenable-v3.diff], which makes it possible to add an identity column that is generated by default, to a non-empty table.

Originally, {{AlterTableConstantAction.addNewColumnToTable()}} populated the new column using this code:

{code}
        if (columnDescriptor.isAutoincrement())
        {
            updateNewAutoincrementColumn(columnInfo[ix].name,
                                         columnInfo[ix].autoincStart,
                                         columnInfo[ix].autoincInc);
        }

        // Update the new column to its default, if it has a non-null default
        if (columnDescriptor.hasNonNullDefault())
        {
            updateNewColumnToDefault(columnDescriptor);
        }
{code}

For a column that is GENERATED BY DEFAULT AS IDENTITY, both isAutoincrement() and hasNonNullDefault() return true. Because of this, after it has run updateNewAutoincrementColumn() and populated the new column with values based on the backing sequence, it will go on and call updateNewColumnToDefault() in order to set the column to the default value again. In addition to being redundant, the call to updateNewColumnToDefault() runs into DERBY-6414 and fails because it tries to set a non-nullable column to NULL.

The fix was simple enough, just change the last "if" to "else if" to make sure updateNewColumnToDefault() isn't called on identity columns.


> 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
>            Assignee: Knut Anders Hatlen
>            Priority: Minor
>              Labels: derby_triage10_10
>         Attachments: 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.2#6252)