db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [PATCH] Re: [jira] Updated: (DERBY-219) EXCEPT/INTERSECT fails in views
Date Thu, 21 Apr 2005 00:25:21 GMT
<!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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\IntersectOrExceptNode.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\master\intersect.out<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
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&gt; 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&gt; -- 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&gt; select * from view_intr_uniq order by 1 DESC,2,3;
+ID         |I1         |I2         
+-----------------------------------
+5          |NULL       |NULL       
+2          |1          |2          
+1          |1          |1          
+ij&gt; 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&gt; select * from  view_intr_all order by 1,2,3;
+ID         |I1         |I2         
+-----------------------------------
+1          |1          |1          
+2          |1          |2          
+5          |NULL       |NULL       
+ij&gt; 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&gt; select * from view_ex_uniq order by 1,2,3;
+ID         |I1         |I2         
+-----------------------------------
+3          |1          |3          
+4          |1          |3          
+6          |NULL       |NULL       
+ij&gt; 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&gt; select * from view_ex_all order by 1 DESC,2,3;
+ID         |I1         |I2         
+-----------------------------------
+6          |NULL       |NULL       
+4          |1          |3          
+3          |1          |3          
+ij&gt; -- 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&gt; 
  </pre>
</blockquote>
</body>
</html>


Mime
View raw message