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/24 16:29:57 UTC
svn commit: r651275 - in /db/derby/code/branches/10.4/java:
engine/org/apache/derby/impl/sql/compile/GroupByList.java
testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Author: bpendleton
Date: Thu Apr 24 07:29:48 2008
New Revision: 651275
URL: http://svn.apache.org/viewvc?rev=651275&view=rev
Log:
DERBY-3613: SELECT DISTINCT with GROUP BY produces wrong results
Merged revision 650728 from the trunk.
Modified:
db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java
db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java?rev=651275&r1=651274&r2=651275&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java (original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java Thu Apr 24 07:29:48 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.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=651275&r1=651274&r2=651275&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java (original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java Thu Apr 24 07:29:48 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} });
}
}