svn commit: r831304 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/

[bp...@apache.org]

Author: bpendleton
Date: Fri Oct 30 14:11:29 2009
New Revision: 831304

URL: http://svn.apache.org/viewvc?rev=831304&view=rev
Log:
DERBY-4419: NPE with INSERT .. SELECT .. UNION and identity columns

The following SQL was causing problems:

    create table t3(x int, y int generated always as identity);
    insert into t3(x) select * from t1 union select * from t2;

During the insert, the value of column Y is not specified by the INSERT
statement, and therefore Derby notices that Y's value is to be generated,
and so Derby generates code to compute a value for Y when inserting each
row into T3.

When Derby is compiling the INSERT statement, Derby checks that the
column information for the column(s) to be inserted matches the correpsonding
column information for the column(s) that are selected by the SELECT
clause in the INSERT statement.

In the above example, Derby expands the "*" expression for each of tables
T1 and T2, and verifies that the result is a single integer column, thus
matching column X in T3.

However, at that point in processing, the ResultColumnList also contains
information about column Y, since its value is to be automatically generated.

Therefore, Derby needs to skip over such columns during datatype checking;
these columns are marked isGeneratedForUnmatchedColumnInInsert, so this
change causes Derby to check that value.

A similar behavior (DERBY-4425) occurs for columns which are generated by
expressions, which is a new feature in recent versions of Derby.

This change also fixes DERBY-4425. A separate commit for DERBY-4425 will
add an additional regression test for that situation.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=831304&r1=831303&r2=831304&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Fri Oct 30 14:11:29 2009
@@ -1688,7 +1688,7 @@
 			ResultColumn resultColumn = (ResultColumn) elementAt(index);
 
 			/* Skip over generated columns */
-			if (resultColumn.isGenerated())
+			if (resultColumn.isGenerated() || resultColumn.isGeneratedForUnmatchedColumnInInsert())
 			{
 				continue;
 			}

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out?rev=831304&r1=831303&r2=831304&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out Fri Oct 30 14:11:29 2009
@@ -2112,4 +2112,17 @@
 ij(CONN2)> -- should fail DERBY-4011
 drop table d4006_a;
 0 rows inserted/updated/deleted
+ij(CONN2)> -- DERBY-4419 is a variant on DERBY-1644:
+create table d4419_t1(x int);
+0 rows inserted/updated/deleted
+ij(CONN2)> insert into d4419_t1 values 1,2;
+2 rows inserted/updated/deleted
+ij(CONN2)> create table d4419_t2(x int);
+0 rows inserted/updated/deleted
+ij(CONN2)> insert into d4419_t2 values 2,3;
+2 rows inserted/updated/deleted
+ij(CONN2)> create table d4419_t3(x int, y int generated always as identity);
+0 rows inserted/updated/deleted
+ij(CONN2)> insert into d4419_t3(x) select * from d4419_t1 union select * from d4419_t2;
+3 rows inserted/updated/deleted
 ij(CONN2)> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql?rev=831304&r1=831303&r2=831304&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql Fri Oct 30 14:11:29 2009
@@ -1119,3 +1119,11 @@
 alter table d4006_a alter column z default null; -- should fail DERBY-4011
 drop table d4006_a;
 
+-- DERBY-4419 is a variant on DERBY-1644:
+create table d4419_t1(x int);
+insert into d4419_t1 values 1,2;
+create table d4419_t2(x int);
+insert into d4419_t2 values 2,3;
+create table d4419_t3(x int, y int generated always as identity);
+insert into d4419_t3(x) select * from d4419_t1 union select * from d4419_t2;
+