db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jboy...@apache.org
Subject svn commit: r155715 - in incubator/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Mon, 28 Feb 2005 23:30:06 GMT
Author: jboynes
Date: Mon Feb 28 15:30:04 2005
New Revision: 155715

URL: http://svn.apache.org/viewcvs?view=rev&rev=155715
Log:
fix for DERBY-155 allow distinct in union/intersect/except

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

Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?view=diff&r1=155714&r2=155715
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
(original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
Mon Feb 28 15:30:04 2005
@@ -4151,14 +4151,14 @@
 	Token 			tok = null;
 }
 {
-	<UNION> [ tok = <ALL> ] expression =
+	<UNION> [ tok = <ALL> | <DISTINCT> ] expression =
 				queryExpression(term,
 								(tok != null) ? UNION_ALL_OP : UNION_OP)
 	{
 		return expression;
 	}
 |
-	<EXCEPT> [ tok = <ALL> ] expression =
+	<EXCEPT> [ tok = <ALL> | <DISTINCT> ] expression =
 				queryExpression(term,
 								(tok != null) ? EXCEPT_ALL_OP : EXCEPT_OP)
 	{
@@ -4272,7 +4272,7 @@
 	Token 			tok = null;
 }
 {
-	<INTERSECT> [ tok = <ALL> ] expression =
+	<INTERSECT> [ tok = <ALL> | <DISTINCT> ] expression =
 				nonJoinQueryTerm(term, (tok != null) ? INTERSECT_ALL_OP : INTERSECT_OP)
 	{
 		return expression;

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?view=diff&r1=155714&r2=155715
==============================================================================
--- 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
Mon Feb 28 15:30:04 2005
@@ -27,6 +27,12 @@
 5          |NULL       |NULL       
 2          |1          |2          
 1          |1          |1          
+ij> select id,i1,i2 from t1 intersect distinct select id,i1,i2 from t2 order by id DESC,i1,i2;
+ID         |I1         |I2         
+-----------------------------------
+5          |NULL       |NULL       
+2          |1          |2          
+1          |1          |1          
 ij> select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 1,2,3;
 ID         |I1         |I2         
 -----------------------------------
@@ -54,6 +60,13 @@
 1          |2          
 1          |3          
 NULL       |NULL       
+ij> select i1,i2 from t1 intersect distinct select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+1          |1          
+1          |2          
+1          |3          
+NULL       |NULL       
 ij> select i1,i2 from t1 intersect all select i1,i2 from t2 order by 1,2;
 I1         |I2         
 -----------------------
@@ -173,6 +186,12 @@
 3          |1          |3          
 4          |1          |3          
 6          |NULL       |NULL       
+ij> select id,i1,i2 from t1 except distinct select id,i1,i2 from t2 order by id,i1,i2;
+ID         |I1         |I2         
+-----------------------------------
+3          |1          |3          
+4          |1          |3          
+6          |NULL       |NULL       
 ij> select id,i1,i2 from t1 except all select id,i1,i2 from t2 order by 1 DESC,2,3;
 ID         |I1         |I2         
 -----------------------------------
@@ -196,6 +215,9 @@
 103        |1          |3          
 104        |1          |3          
 ij> select i1,i2 from t1 except select i1,i2 from t2 order by 1,2;
+I1         |I2         
+-----------------------
+ij> select i1,i2 from t1 except distinct select i1,i2 from t2 order by 1,2;
 I1         |I2         
 -----------------------
 ij> select i1,i2 from t1 except all select i1,i2 from t2 order by 1,2;

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out?view=diff&r1=155714&r2=155715
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out
(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out
Mon Feb 28 15:30:04 2005
@@ -40,6 +40,15 @@
 1          |2          |3          |4          
 -----------------------------------------------
 1          |2          |3          |4          
+ij> values (1, 2, 3, 4) union distinct values (5, 6, 7, 8);
+1          |2          |3          |4          
+-----------------------------------------------
+1          |2          |3          |4          
+5          |6          |7          |8          
+ij> values (1, 2, 3, 4) union distinct values (1, 2, 3, 4);
+1          |2          |3          |4          
+-----------------------------------------------
+1          |2          |3          |4          
 ij> values (1, 2, 3, 4) union values (5, 6, 7, 8) union values (9, 10, 11, 12);
 1          |2          |3          |4          
 -----------------------------------------------

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?view=diff&r1=155714&r2=155715
==============================================================================
--- 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
Mon Feb 28 15:30:04 2005
@@ -19,6 +19,7 @@
 
 -- no duplicates
 select id,i1,i2 from t1 intersect select id,i1,i2 from t2 order by id DESC,i1,i2;
+select id,i1,i2 from t1 intersect distinct select id,i1,i2 from t2 order by id DESC,i1,i2;
 select id,i1,i2 from t1 intersect all select id,i1,i2 from t2 order by 1,2,3;
 
 -- Only specify order by on some columns
@@ -27,6 +28,7 @@
 
 -- duplicates
 select i1,i2 from t1 intersect select i1,i2 from t2 order by 1,2;
+select i1,i2 from t1 intersect distinct select i1,i2 from t2 order by 1,2;
 select i1,i2 from t1 intersect all select i1,i2 from t2 order by 1,2;
 
 -- right side is empty
@@ -83,11 +85,13 @@
 
 -- except tests
 select id,i1,i2 from t1 except select id,i1,i2 from t2 order by id,i1,i2;
+select id,i1,i2 from t1 except distinct select id,i1,i2 from t2 order by id,i1,i2;
 select id,i1,i2 from t1 except all select id,i1,i2 from t2 order by 1 DESC,2,3;
 select id,i1,i2 from t2 except select id,i1,i2 from t1 order by 1,2,3;
 select id,i1,i2 from t2 except all select id,i1,i2 from t1 order by 1,2,3;
 
 select i1,i2 from t1 except select i1,i2 from t2 order by 1,2;
+select i1,i2 from t1 except distinct select i1,i2 from t2 order by 1,2;
 select i1,i2 from t1 except all select i1,i2 from t2 order by 1,2;
 select i1,i2 from t2 except select i1,i2 from t1 order by 1,2;
 select i1,i2 from t2 except all select i1,i2 from t1 order by 1,2;

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql?view=diff&r1=155714&r2=155715
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
Mon Feb 28 15:30:04 2005
@@ -27,6 +27,8 @@
 -- simple cases
 values (1, 2, 3, 4) union values (5, 6, 7, 8);
 values (1, 2, 3, 4) union values (1, 2, 3, 4);
+values (1, 2, 3, 4) union distinct values (5, 6, 7, 8);
+values (1, 2, 3, 4) union distinct values (1, 2, 3, 4);
 
 values (1, 2, 3, 4) union values (5, 6, 7, 8) union values (9, 10, 11, 12);
 values (1, 2, 3, 4) union values (1, 2, 3, 4) union values (1, 2, 3, 4);



Mime
View raw message