[
https://issues.apache.org/jira/browse/DERBY-6414?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-6414:
-----------------------------------
Attachment: DERBY6414_patch1_stat.txt
DERBY6414_patch1_diff.txt
Attaching patch DERBY6414_patch1_diff.txt for review. This patch supports
update of identity columns using DEFAULT keyword.
This support is for Derby 10.11 and higher. Starting 10.11, we have started
using sequence generator to create unique ids. If we fix this jira for
pre-10.11 releases, we will need to maintain the code for old way of generating
unique ids.
I have added some new junit tests for this feature in
GeneratedColumnsTest.java.
The support of this feature requires changes in MERGE statement code. MERGE has
required code for insert putting DEFAULT in generated columns. Implementation
needs to be added for update putting DEFAULT in generated columns in case of
MERGE. I will open a separate jira for that work. In the mean time, I have
renamed existing junit test test_015_bug_6414 to atest_015_bug_6414 in
MergeStatementTest.java so we do not run into failures.
I need to add upgrade tests to show that a soft upgrade from pre-10.11 will not
support update of generated columns using DEFAULT but 10.11 to trunk soft
upgrade will allow it.
I will also create a new jira for JDBC part of this feature so we can generate
auto generated resultset for an update statement updating generated columns.
This implementation is supported through Statement.RETURN_GENERATED_KEYS flag.
Not sure yet what is the work involved but I will look into it.
I will greatly appreciate the review of the patch and especially appreciuate
any suggestions on tips on more junit tests.
Some detail about the part of the implementation that was tricky.
The existing code was originally written to handle inserting values in identity
columns using DEFAULT. In case of insert, InsertResultSet uses
ColumnDescriptors in resultDescription to find the type of the generated
columns. This data structure holds the column descriptors of all the columns in
the table. All the columns are in this data structure because even though
INSERT statement may not explicitly assign a value to each and every column in
the table, all the columns end up getting some value in them through an INSERT
statement. The code in InsertResultSet.initializeAIcache method relies on
availability of all the columns type information. But in case of Update,
resultDescription does not include all the columns in the table. It only has
the columns being touched by the Update statement(the rest of the columns in
the table will retain their original values), and for each of those touched
columns, it has a duplicate entry in resultDescription in order to have before
and after values for the changed column values. This difference in array
content of resultDescription requires us to have separate implementation of
initializeAIcache method for insert and update.
> 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.2.2.1, 10.10.1.1
> Reporter: Rick Hillegas
> Assignee: Mamta A. Satoor
> Attachments: DERBY6414_patch1_diff.txt,
> DERBY6414_patch1_notReadyForCommit_diff.txt, DERBY6414_patch1_stat.txt
>
>
> 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.3.4#6332)
