db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r650728 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/GroupByList.java testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Date Wed, 23 Apr 2008 02:20:44 GMT
Author: bpendleton
Date: Tue Apr 22 19:20:43 2008
New Revision: 650728

URL: http://svn.apache.org/viewvc?rev=650728&view=rev
Log:
DERBY-3613: SELECT DISTINCT with GROUP BY produces wrong results

Certain combinations of DISTINCT and GROUP BY in the same query were
producing incorrect results. Duplicate rows were appearing in the
results because the query was including all of the GROUP BY columns
in the evaluation of the DISTINCT clause, not just the columns that
were explicitly specified to be DISTINCT.

For example, in the query:

  select distinct a, b from t group by a, b, c

Derby was including two separate rows in the result which had the same
value for columns a and b, but had different values for column c.

Internally, GroupByList.bindGroupByColumns() was generating the
extra column(s) from the group by list into the select's result
column list, but this processing should not be performed when the
query specifies distinct, because adding extra columns to the
set of distinct columns changes the results.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java?rev=650728&r1=650727&r2=650728&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java Tue
Apr 22 19:20:43 2008
@@ -172,8 +172,13 @@
 			}
 			/* If no match found in the SELECT list, then add a matching
 			 * ResultColumn/ColumnReference pair to the SelectNode's RCL.
+			 * However, don't add additional result columns if the query
+			 * specified DISTINCT, because distinct processing considers
+			 * the entire RCL and including extra columns could change the
+			 * results: e.g. select distinct a,b from t group by a,b,c
+			 * should not consider column c in distinct processing (DERBY-3613)
 			 */
-			if (! matchFound && 
+			if (! matchFound && !select.hasDistinct() &&
 			    groupingCol.getColumnExpression() instanceof ColumnReference) 
 			{
 			    	// only add matching columns for column references not 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=650728&r1=650727&r2=650728&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Tue Apr 22 19:20:43 2008
@@ -87,6 +87,10 @@
         st.executeUpdate("create table t5653 (c1 float)");
         st.executeUpdate("insert into t5653 values 0.0, 90.0");
 
+        st.executeUpdate("create table d3613 (a int, b int, c int, d int)");
+        st.executeUpdate("insert into d3613 values (1,2,1,2), (1,2,3,4), " +
+                "(1,3,5,6), (2,2,2,2)");
+
         // create an all types tables
         
         st.executeUpdate(
@@ -1485,6 +1489,85 @@
         String [][] expectedRows = {{"A","2"},{"B","2"}};
         JDBC.assertFullResultSet(rs, expectedRows);
         s.executeUpdate("DROP TABLE TAB");
+    }
+
+    /**
+      * DERBY-3613 check combinations of DISTINCT and GROUP BY
+      */
+    public void testDistinctGroupBy() throws SQLException
+    {
+        Statement s = createStatement();
+        ResultSet rs;
+        // First, a number of queries without aggregates:
+        rs = s.executeQuery("select distinct a from d3613 group by a");
+        JDBC.assertUnorderedResultSet(rs, new String[][] {{"2"},{"1"}});
+        rs = s.executeQuery("select distinct a from d3613 group by a,b");
+        JDBC.assertUnorderedResultSet(rs, new String[][] {{"2"},{"1"}});
+        rs = s.executeQuery("select a,b from d3613");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","2"},{"1","2"},{"1","3"},{"2","2"}});
+        rs = s.executeQuery("select distinct a,b from d3613 group by a,b,c");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","2"},{"1","3"},{"2","2"}});
+        rs = s.executeQuery("select distinct a,b from d3613 group by a,b");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","2"},{"1","3"},{"2","2"}});
+        rs = s.executeQuery("select distinct a,b from d3613 group by a,c,b");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","2"},{"1","3"},{"2","2"}});
+        // Second, a number of similar queries, with aggregates:
+        rs = s.executeQuery("select a,sum(b) from d3613 group by a,b");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","4"},{"1","3"},{"2","2"}});
+        rs = s.executeQuery("select distinct a,sum(b) from d3613 group by a,b");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","4"},{"1","3"},{"2","2"}});
+        rs = s.executeQuery("select a,sum(b) from d3613 group by a,c");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","2"},{"1","2"},{"1","3"},{"2","2"}});
+        rs = s.executeQuery("select distinct a,sum(b) from d3613 group by a,c");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","2"},{"1","3"},{"2","2"}});
+        rs = s.executeQuery(
+                "select a,sum(b) from d3613 group by a,b,c");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","2"},{"1","2"},{"1","3"},{"2","2"}});
+        rs = s.executeQuery(
+                "select distinct a,sum(b) from d3613 group by a,b,c");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","2"},{"1","3"},{"2","2"}});
+        rs = s.executeQuery(
+                "select distinct a,sum(b) from d3613 group by a");
+        JDBC.assertUnorderedResultSet(rs,
+                new String[][] {{"1","7"},{"2","2"}});
+        // A few error cases:
+        assertStatementError("42Y30", s,
+            "select distinct a,b from d3613 group by a");
+        assertStatementError("42Y30", s,
+            "select distinct a,b from d3613 group by a,c");
+        assertStatementError("42Y30", s,
+            "select distinct a,b,sum(b) from d3613 group by a");
+        
+        // A few queries from other parts of this suite, with DISTINCT added:
+        JDBC.assertFullResultSet(
+            s.executeQuery("select distinct t.t_i, m.t_i from " +
+                           "(select a, b from bug280 group by a, b) " +
+                           "t (t_i, t_dt), " +
+                           "(select a, b from bug280 group by a, b) " +
+                           "m (t_i, t_dt) " +
+                           "where t.t_i = m.t_i and t.t_dt = m.t_dt " +
+                           "group by t.t_i, t.t_dt, m.t_i, m.t_dt " +
+                           "order by t.t_i,m.t_i"),
+            new String[][] {  {"1","1"}, {"2","2"} } );
+
+        JDBC.assertFullResultSet(
+            s.executeQuery(
+                " select distinct t.i from t group by i, dt, b order by i"),
+            new String [][] { {"0"}, {"1"}, {null} });
+        JDBC.assertFullResultSet(
+            s.executeQuery(
+                " select distinct t.dt from t group by i, dt, b order by dt"),
+            new String [][] { {"1992-01-01"}, {"1992-09-09"}, {null} });
     }
 }
 



Mime
View raw message