<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Submitted this patch.<br>
<br>
Satheesh<br>
<br>
Sending
java\engine\org\apache\derby\impl\sql\compile\IntersectOrExceptNode.java<br>
Sending
java\testing\org\apache\derbyTesting\functionTests\master\intersect.out<br>
Sending
java\testing\org\apache\derbyTesting\functionTests\tests\lang\intersect.sql<br>
Transmitting file data ...<br>
Committed revision 162098.<br>
<br>
Jack Klebanoff wrote:
<blockquote cite="mid425D43C7.7010807@sbcglobal.net" type="cite">I have
made a fix for the problem. Actually the bug affects any subquery, not
just a view.
<br>
<br>
The fix includes an expanded lang/intersect.sql test. The fix passes
the derbylang test suite.
<br>
<br>
The patch is included here and in Jira.
<br>
<br>
Jack Klebanoff
<br>
<pre wrap="">
<hr size="4" width="90%">
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>
</pre>
</blockquote>
</body>
</html>
|