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 ba...@apache.org on 2005/04/21 02:25:05 UTC
svn commit: r162098 - in /incubator/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
testing/org/apache/derbyTesting/functionTests/master/intersect.out
testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
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;