Author: bandaram
Date: Wed Apr 20 17:25:03 2005
New Revision: 162098

URL: http://svn.apache.org/viewcvs?rev=162098&view=rev
Log:
Derby-219: Address NullPointerException when INTERSECT is used in a view or subquerries.

Submitted by Jack Klabanoff(klebanoff-derby@sbcglobal.net)

Modified:
    incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
    incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
    incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql

Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java?rev=162098&r1=162097&r2=162098&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java Wed Apr 20 17:25:03 2005
@@ -224,6 +224,18 @@
                                       RowOrdering rowOrdering)
                           throws StandardException
     {
+		leftResultSet = optimizeSource(
+							optimizer,
+							leftResultSet,
+							(PredicateList) null,
+							outerCost);
+
+		rightResultSet = optimizeSource(
+							optimizer,
+							rightResultSet,
+							(PredicateList) null,
+							outerCost);
+
 		CostEstimate costEstimate = getCostEstimate(optimizer);
         CostEstimate leftCostEstimate = leftResultSet.getCostEstimate();
         CostEstimate rightCostEstimate = rightResultSet.getCostEstimate();

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out?rev=162098&r1=162097&r2=162098&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out Wed Apr 20 17:25:03 2005
@@ -350,4 +350,44 @@
 ERROR 42877: A qualified column name 'T1.I1' is not allowed in the ORDER BY clause.
 ij> select id,i1,i2 from t1 except select id,i1,i2 from t2 order by t1.i1;
 ERROR 42877: A qualified column name 'T1.I1' is not allowed in the ORDER BY clause.
+ij> -- views using intersect and except
+create view view_intr_uniq as select id,i1,i2 from t1 intersect select id,i1,i2 from t2;
+0 rows inserted/updated/deleted
+ij> select * from view_intr_uniq order by 1 DESC,2,3;
+ID         |I1         |I2         
+-----------------------------------
+5          |NULL       |NULL       
+2          |1          |2          
+1          |1          |1          
+ij> create view view_intr_all as select id,i1,i2 from t1 intersect all select id,i1,i2 from t2;
+0 rows inserted/updated/deleted
+ij> select * from  view_intr_all order by 1,2,3;
+ID         |I1         |I2         
+-----------------------------------
+1          |1          |1          
+2          |1          |2          
+5          |NULL       |NULL       
+ij> create view view_ex_uniq as select id,i1,i2 from t1 except select id,i1,i2 from t2;
+0 rows inserted/updated/deleted
+ij> select * from view_ex_uniq order by 1,2,3;
+ID         |I1         |I2         
+-----------------------------------
+3          |1          |3          
+4          |1          |3          
+6          |NULL       |NULL       
+ij> create view view_ex_all as select id,i1,i2 from t1 except all select id,i1,i2 from t2;
+0 rows inserted/updated/deleted
+ij> select * from view_ex_all order by 1 DESC,2,3;
+ID         |I1         |I2         
+-----------------------------------
+6          |NULL       |NULL       
+4          |1          |3          
+3          |1          |3          
+ij> -- intersect joins
+select t1.id,t1.i1,t2.i1 from t1 join t2 on t1.id = t2.id
+intersect select t1.id,t1.i2,t2.i2 from t1 join t2 on t1.id = t2.id;
+ID         |2          |3          
+-----------------------------------
+1          |1          |1          
+5          |NULL       |NULL       
 ij> 

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql?rev=162098&r1=162097&r2=162098&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql Wed Apr 20 17:25:03 2005
@@ -143,3 +143,20 @@
 -- Invalid order by
 select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by t1.i1;
 select id,i1,i2 from t1 except select id,i1,i2 from t2 order by t1.i1;
+
+-- views using intersect and except
+create view view_intr_uniq as select id,i1,i2 from t1 intersect select id,i1,i2 from t2;
+select * from view_intr_uniq order by 1 DESC,2,3;
+
+create view view_intr_all as select id,i1,i2 from t1 intersect all select id,i1,i2 from t2;
+select * from  view_intr_all order by 1,2,3;
+
+create view view_ex_uniq as select id,i1,i2 from t1 except select id,i1,i2 from t2;
+select * from view_ex_uniq order by 1,2,3;
+
+create view view_ex_all as select id,i1,i2 from t1 except all select id,i1,i2 from t2;
+select * from view_ex_all order by 1 DESC,2,3;
+
+-- intersect joins
+select t1.id,t1.i1,t2.i1 from t1 join t2 on t1.id = t2.id
+intersect select t1.id,t1.i2,t2.i2 from t1 join t2 on t1.id = t2.id;