[
https://issues.apache.org/jira/browse/DERBY-6414?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13820307#comment-13820307
]
Rick Hillegas commented on DERBY-6414:
--------------------------------------
This understanding of the SQL Standard behavior is supported by DB2's behavior
as described here:
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Frbafyupdateidentity.htm
> Incorrect handling when using an UPDATE to SET an identity column to DEFAULT
> ----------------------------------------------------------------------------
>
> Key: DERBY-6414
> URL: https://issues.apache.org/jira/browse/DERBY-6414
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.10.1.1
> Reporter: Rick Hillegas
>
> Derby violates the SQL Standard when an UPDATE statement SETs an identity
> column to the value DEFAULT. Derby does the right thing for INSERTs, however.
> For INSERTs, the 2011 Standard defines the meaning of DEFAULT in part 2,
> section 15.10 (Effect of inserting tables into base tables), general rule 2.
> For INSERTs, the DEFAULT value is the next value of the sequence generator
> which defines the identity column.
> For UPDATEs, the 2011 Standard defines the meaning of DEFAULT in part 2,
> section 15.13 (Effect of replacing rows in base tables), general rule 5. For
> UPDATEs, the DEFAULT value is also the next value of the sequence generator
> which defines the identity column.
> Note also that the 2011 Standard says that a GENERATED ALWAYS identity column
> can be SET to DEFAULT. Furthermore, that is the only explicit value accepted.
> See the 2011 Standard, part 2, section 14.15 (set clause list), syntax rule 5:
> {
> "If <set clause> SC specifies an <object column> that references a column of
> which some underlying column is either a generated column or an identity
> column whose descriptor indicates that values are always generated, then the
> <update source> specified in SC shall consist of a <default specification>."
> }
> What Derby actually does for UPDATEs is the following:
> 1) If the column was declared GENERATED ALWAYS, then Derby raises a
> compile-time error saying that the value of an identity column can't be
> overridden.
> 2) If the column was declared GENERATED BY DEFAULT, then Derby raises an
> execution time-error when trying to stuff a null into the column.
> Correcting this bug would result in backwardly incompatible behavior.
> However, I think that the incompatibility is minor: it would mean the
> successful run of statements which previously raised errors.
> I tripped across this problem while implementing the UPDATE action of the
> MERGE statement (DERBY-3155). If we decide to fix this bug, we will want to
> make sure that the UPDATE actions of MERGE statements also correctly handle
> DEFAULT values for identity columns.
> The following script shows this problem:
> connect 'jdbc:derby:memory:db;create=true';
> create table t1( a int generated always as identity, b int );
> create table t2( a int generated by default as identity, b int );
> insert into t1( a, b ) values ( default, 100 );
> insert into t2( a, b ) values ( default, 100 );
> update t1 set a = default;
> update t2 set a = default;
> select * from t1;
> select * from t2;
--
This message was sent by Atlassian JIRA
(v6.1#6144)
