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 bp...@apache.org on 2008/04/25 16:10:51 UTC
svn commit: r651612 - in /db/derby/code/branches/10.3/java:
engine/org/apache/derby/impl/sql/compile/GroupByList.java
testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Author: bpendleton
Date: Fri Apr 25 07:10:44 2008
New Revision: 651612
URL: http://svn.apache.org/viewvc?rev=651612&view=rev
Log:
DERBY-3613: SELECT DISTINCT with GROUP BY produces wrong results
Merged revision 650728 from the trunk. There were merge conflicts
in GroupByTest.java, which was hand-edited to produce the desired diff.
Modified:
db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java
db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java?rev=651612&r1=651611&r2=651612&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java Fri Apr 25 07:10:44 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/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=651612&r1=651611&r2=651612&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java Fri Apr 25 07:10:44 2008
@@ -166,5 +166,70 @@
s.executeUpdate("drop table yy");
}
+
+
+ /**
+ * DERBY-3613 check combinations of DISTINCT and GROUP BY
+ */
+ public void testDistinctGroupBy() throws SQLException
+ {
+ Statement s = createStatement();
+ ResultSet rs;
+ s.executeUpdate("create table d3613 (a int, b int, c int, d int)");
+ s.executeUpdate("insert into d3613 values (1,2,1,2), (1,2,3,4), " +
+ "(1,3,5,6), (2,2,2,2)");
+
+ // 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");
+
+ }
+
}