[
https://issues.apache.org/jira/browse/DERBY-6545?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13974009#comment-13974009
]
Rick Hillegas commented on DERBY-6545:
--------------------------------------
Thanks for the quick review, Mike. Not backporting this patch sounds
appropriate to me. The upgrade behavior of this patch should be:
1) If you soft-upgrade to 10.11, you will not be able to add a default to an
identity column.
2) If you soft-downgrade back to 10.10 or earlier, you will again be able to
add a default to an identity column.
I don't think that this patch creates any new problems for columns which used
to be identity columns but were given defaults by 10.10 or earlier. The
metadata changes and some debug printouts suggest that the old, non-Standard
behavior was equivalent to the following sequence of transformations:
{noformat}
ALTER TABLE $tableName ALTER COLUMN $columnName DROP IDENTITY;
ALTER TABLE $tableName ALTER COLUMN $columnName DEFAULT $defaultValue;
{noformat}
It may be that there are problems with columns which have undergone these
transformations. If so, those problems would affect the columns today in 10.10.
This patch does not add any new problems. By itself, this patch does not change
any metadata and it doesn't change any DML behavior. So I can't think of a
problem case for these columns which I could add to the upgrade tests. But I'm
happy to add an upgrade test if someone can think of a problem case.
Thanks.
> Should not be able to add a default to an identity column
> ---------------------------------------------------------
>
> Key: DERBY-6545
> URL: https://issues.apache.org/jira/browse/DERBY-6545
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Fix For: 10.11.0.0
>
> Attachments: derby-6545-01-aa-forbidDefaultOnIdentityColumn.diff
>
>
> According to the 2011 edition of the SQL Standard, the only alterations
> allowed on identity columns are the following:
> 1) changing the identity properties of the column
> 2) causing the column to cease to be an identity column
> This is described in part 2, section 11.2 (alter column definition), syntax
> rule 6. However, Derby allows you to add a default to an identity column.
> It's clear from the CREATE TABLE syntax that a column can't both have a
> default and be an identity column. A side-effect of using ALTER TABLE to add
> a default to an identity column is this: the column ceases to be an identity
> column. The following script output shows this Derby behavior:
> {noformat}
> ij version 10.11
> ij> connect 'jdbc:derby:memory:db;create=true';
> ij> create table t1_01
> (
> a int generated always as identity ( start with 10, increment by 20 ),
> b int
> );
> 0 rows inserted/updated/deleted
> ij> -- should not succeed but does
> alter table t1_01 alter column a default 99;
> 0 rows inserted/updated/deleted
> ij> mainline (1.8) > runsql zzz
> ij version 10.11
> ij> connect 'jdbc:derby:memory:db;create=true';
> ij> create table t1_01
> (
> a int generated always as identity ( start with 10, increment by 20 )
> );
> 0 rows inserted/updated/deleted
> ij> select c.autoincrementvalue, c.autoincrementstart, c.autoincrementinc,
> c.columndefault
> from sys.syscolumns c, sys.systables t
> where c.referenceid = t.tableid
> and t.tablename = 'T1_01';
> AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |COLUMNDEFAULT
> ------------------------------------------------------------------------------
> 10 |10 |20 |NULL
> 1 row selected
> ij> -- should not succeed but does
> alter table t1_01 alter column a default 99;
> 0 rows inserted/updated/deleted
> ij> select c.autoincrementvalue, c.autoincrementstart, c.autoincrementinc,
> c.columndefault
> from sys.syscolumns c, sys.systables t
> where c.referenceid = t.tableid
> and t.tablename = 'T1_01';
> AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |COLUMNDEFAULT
> ------------------------------------------------------------------------------
> NULL |NULL |NULL |99
> 1 row selected
> {noformat}
> Fixing this deviation from the Standard will create a backward
> incompatibility. However, it is hard to imagine that any production
> application relies on this deviation. This is the sort of schema evolution
> which one tests out in the laboratory or resorts to during an application
> upgrade. A judicious use of RENAME COLUMN and ALTER TABLE should provide a
> usable workaround.
> For that reason, I think that we should correct this deviation.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
