db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6414) Incorrect handling when using an UPDATE to SET an identity column to DEFAULT
Date Wed, 19 Feb 2014 21:16:22 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6414?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13906087#comment-13906087
] 

Mike Matrigali commented on DERBY-6414:
---------------------------------------

here is the current 10.10 branch behavior for the included script, it matches the issue description
of the incorrect derby behavior:
ij version 10.10
CONNECTION0* -  jdbc:derby:wombat
* = current connection
ij> create table t1( a int generated always as identity, b int );
0 rows inserted/updated/deleted
ij> create table t2( a int generated by default as identity, b int );
0 rows inserted/updated/deleted
ij> insert into t1( a, b ) values ( default, 100 );
1 row inserted/updated/deleted
ij> insert into t2( a, b ) values ( default, 100 );
1 row inserted/updated/deleted
ij> update t1 set a = default;
ERROR 42Z23: Attempt to modify an identity column 'A'.
ij> update t2 set a = default;
ERROR 23502: Column 'A'  cannot accept a NULL value.
ij> select * from t1;
A          |B
-----------------------
1          |100

1 row selected
ij> select * from t2;
A          |B
-----------------------
1          |100

1 row selected


> 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
>          Components: SQL
>    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.5#6160)

Mime
View raw message