I have
made a fix for the problem. Actually the bug affects any subquery, not
just a view.
The fix includes an expanded lang/intersect.sql test. The fix passes
the derbylang test suite.
The patch is included here and in Jira.
Jack Klebanoff
Index: java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java (revision 161138)
+++ java/engine/org/apache/derby/impl/sql/compile/IntersectOrExceptNode.java (working copy)
@@ -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();
Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql (revision 161138)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/intersect.sql (working copy)
@@ -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;
Index: java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/intersect.out (revision 161138)
+++ java/testing/org/apache/derbyTesting/functionTests/master/intersect.out (working copy)
@@ -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>
