You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2006/11/23 18:27:43 UTC
svn commit: r478622 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
testing/org/apache/derbyTesting/functionTests/master/joins.out
testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql
Author: bpendleton
Date: Thu Nov 23 09:27:39 2006
New Revision: 478622
URL: http://svn.apache.org/viewvc?view=rev&rev=478622
Log:
DERBY-1089: NPE inserting a join into a table with a generated column
DERBY-2015: NPE in INSERT .. SELECT with IDENTITY column
When an INSERT ... SELECT statement inserts a GENERATED ALWAYS identity
column, the identity column's column reference is NULL, since that
column does not have a corresponding column in the SELECT list. This
NULL expression needs to be skipped over when remapping column references
from the SELECT column list to the INSERT column list.
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/joins.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.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?view=diff&rev=478622&r1=478621&r2=478622
==============================================================================
--- 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 Thu Nov 23 09:27:39 2006
@@ -2868,7 +2868,14 @@
{
ResultColumn rc = (ResultColumn) elementAt(index);
- rc.setExpression(rc.getExpression().remapColumnReferencesToExpressions());
+ // The expression may be null if this column is an identity
+ // column generated always. If the expression is not null, it
+ // is a ColumnReference; we call through to the ColumnReference
+ // to give it a chance to remap itself from the outer query
+ // node to this one.
+ if (rc.getExpression() != null)
+ rc.setExpression(
+ rc.getExpression().remapColumnReferencesToExpressions());
}
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/joins.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/joins.out?view=diff&rev=478622&r1=478621&r2=478622
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/joins.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/joins.out Thu Nov 23 09:27:39 2006
@@ -386,6 +386,26 @@
-----------------------------------------------------------------------
1 |1 |1 |1 |1 |2
7 |7 |8 |9 |1 |3
+ij> -- JIRA 1089: demonstrate that a table with an identity column generated
+-- always can be used as the target of an insert-as-select join:
+create table j1089_source (source_id int);
+0 rows inserted/updated/deleted
+ij> insert into j1089_source values (0);
+1 row inserted/updated/deleted
+ij> create table j1089_dest (
+ dest_id int not null primary key generated always as identity,
+ source_id_1 int not null,
+ source_id_2 int not null);
+0 rows inserted/updated/deleted
+ij> insert into j1089_dest (source_id_1, source_id_2)
+ select s1.source_id, s2.source_id
+ from j1089_source as s1
+ join j1089_source as s2 on 1 = 1;
+1 row inserted/updated/deleted
+ij> select * from j1089_dest;
+DEST_ID |SOURCE_ID_1|SOURCE_ID_2
+-----------------------------------
+1 |0 |0
ij> -----------------------------------
-- clean up
----------------------------------
@@ -410,5 +430,9 @@
ij> drop table x;
0 rows inserted/updated/deleted
ij> drop table y;
+0 rows inserted/updated/deleted
+ij> drop table j1089_source;
+0 rows inserted/updated/deleted
+ij> drop table j1089_dest;
0 rows inserted/updated/deleted
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql?view=diff&rev=478622&r1=478621&r2=478622
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/joins.sql Thu Nov 23 09:27:39 2006
@@ -186,6 +186,21 @@
from D join ((B join C on b2=c2) right outer join A on a1=b1)
on d3=b3 and d1=a2;
+-- JIRA 1089: demonstrate that a table with an identity column generated
+-- always can be used as the target of an insert-as-select join:
+create table j1089_source (source_id int);
+insert into j1089_source values (0);
+create table j1089_dest (
+ dest_id int not null primary key generated always as identity,
+ source_id_1 int not null,
+ source_id_2 int not null);
+
+insert into j1089_dest (source_id_1, source_id_2)
+ select s1.source_id, s2.source_id
+ from j1089_source as s1
+ join j1089_source as s2 on 1 = 1;
+select * from j1089_dest;
+
-----------------------------------
-- clean up
----------------------------------
@@ -200,3 +215,5 @@
drop table instab;
drop table x;
drop table y;
+drop table j1089_source;
+drop table j1089_dest;