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 jb...@apache.org on 2005/03/01 00:30:06 UTC
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/
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);