db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r1300625 - in /db/derby/code/branches/10.8/java: engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Date Wed, 14 Mar 2012 16:33:25 GMT
Author: kmarsden
Date: Wed Mar 14 16:33:24 2012
New Revision: 1300625

URL: http://svn.apache.org/viewvc?rev=1300625&view=rev
Log:
DERBY-5584 Select statement with subqueries with group by and count distinct statements returns
wrong number of results

Contributed by Bryan Pendleton. Backport from trunk revision 1292134


Modified:
    db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java
    db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java

Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java?rev=1300625&r1=1300624&r2=1300625&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java
(original)
+++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java
Wed Mar 14 16:33:24 2012
@@ -88,6 +88,7 @@ class GroupedAggregateResultSet extends 
 	private ExecIndexRow sortTemplateRow;
 	public	boolean	hasDistinctAggregate;	// true if distinct aggregate
 	public	boolean isInSortedOrder;				// true if source results in sorted order
+	private	int numDistinctAggs = 0;
 	private int maxRowSize;
 
 	// set in open and not modified thereafter
@@ -237,7 +238,7 @@ class GroupedAggregateResultSet extends 
 		else if (!resultsComplete)
 		{
 			if (rollup)
-				resultRows = new ExecIndexRow[order.length+1];
+				resultRows = new ExecIndexRow[numGCols()+1];
 			else
 				resultRows = new ExecIndexRow[1];
 			if (aggInfoList.hasDistinct())
@@ -324,33 +325,26 @@ class GroupedAggregateResultSet extends 
 			** by that column, we just sorted it so that distinct
 			** aggregation would see the values in order.
 			*/
-			int numDistinctAggs = 0;
-			for (int i = 0; i < aggregates.length; i++)
-			{
-				AggregatorInfo aInfo = (AggregatorInfo)
-					aggInfoList.elementAt(i);
-				if (aInfo.isDistinct())
-					numDistinctAggs++;
-			}
 			// Although it seems like N aggs could have been
 			// added at the end, in fact only one has been
 			// FIXME -- need to get GroupByNode to handle this
 			// correctly, but that requires understanding
 			// scalar distinct aggregates.
 			numDistinctAggs = 1;
-			if (order.length > numDistinctAggs)
-			{
-				ColumnOrdering[] newOrder = new ColumnOrdering[
-					order.length - numDistinctAggs];
-				System.arraycopy(order, 0, newOrder, 0,
-					order.length-numDistinctAggs);
-				order = newOrder;
-			}
 		}
 		return tc.openSortScan(genericSortId,
 			activation.getResultSetHoldability());
 	}
 
+	/**
+	 * Return the number of grouping columns.
+	 *
+	 * Since some additional sort columns may have been included
+	 * in the sort for DISTINCT aggregates, this function is
+	 * used to ignore those columns when computing the grouped
+	 * results.
+	 */
+	private int numGCols() { return order.length - numDistinctAggs; }
 
 	/**
 	 * Return the next row.  
@@ -409,7 +403,7 @@ class GroupedAggregateResultSet extends 
 			{
 				boolean sameGroup = (rollup ?
 				    r <= distinguisherCol :
-				    distinguisherCol == order.length);
+				    distinguisherCol == numGCols());
 				if (sameGroup)
 				{
 					/* Same group - initialize the new
@@ -486,7 +480,7 @@ class GroupedAggregateResultSet extends 
 	private int sameGroupingValues(ExecRow currRow, ExecRow newRow)
 		throws StandardException
 	{
-		for (int index = 0; index < order.length; index++)
+		for (int index = 0; index < numGCols(); index++)
 		{
 			DataValueDescriptor currOrderable = currRow.getColumn(order[index].getColumnId() + 1);
 			DataValueDescriptor newOrderable = newRow.getColumn(order[index].getColumnId() + 1);
@@ -495,7 +489,7 @@ class GroupedAggregateResultSet extends 
 				return index;
 			}
 		}
-		return order.length;
+		return numGCols();
 	}
 
 	/**
@@ -650,7 +644,7 @@ class GroupedAggregateResultSet extends 
 		int numRolledUpCols = resultRows.length - resultNum - 1;
 		for (int i = 0; i < numRolledUpCols; i++)
 		{
-			int rolledUpColIdx = order.length - 1 - i;
+			int rolledUpColIdx = numGCols() - 1 - i;
 			DataValueDescriptor rolledUpColumn =
 				row.getColumn(order[rolledUpColIdx].getColumnId() + 1);
 			rolledUpColumn.setToNull();

Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=1300625&r1=1300624&r2=1300625&view=diff
==============================================================================
--- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
(original)
+++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Wed Mar 14 16:33:24 2012
@@ -2351,4 +2351,262 @@ public class GroupByTest extends BaseJDB
 
             rollback();
     }
+
+
+    /**
+     * DISTINCT aggregates in result sets which are opened multiple times.
+     * DERBY-5584.
+     * @throws SQLException
+     */
+    public void testDerby5584()
+	throws SQLException
+    {
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.executeUpdate(
+		"CREATE TABLE TEST_5 (" +
+		"       profile_id INTEGER NOT NULL," +
+		"       group_ref INTEGER NOT NULL," +
+		"       matched_count INTEGER NOT NULL )"); 
+
+        s.executeUpdate(
+		"CREATE TABLE TEST_6 ( " +
+		"       profile_id INTEGER NOT NULL, " +
+		"       group_ref INTEGER NOT NULL, " +
+		"       matched_count INTEGER NOT NULL )"); 
+
+        s.executeUpdate( "insert into test_5 values (1, 10000, 1)" ); 
+        s.executeUpdate( "insert into test_5 values (2, 10000, 2)" ); 
+
+        s.executeUpdate( "insert into test_6 values (1, 10000, 1)" ); 
+        s.executeUpdate( "insert into test_6 values (2, 10000, 2)" );
+
+        rs = s.executeQuery( "SELECT ps1.group_ref," +
+		"COUNT(DISTINCT ps1.matched_count) AS matched_count" +
+		" FROM test_5 ps1 " +
+		" GROUP BY ps1.group_ref, ps1.profile_id" );
+        JDBC.assertFullResultSet(rs, new String[][] {
+                {"10000", "1"},
+                {"10000", "1"}
+	});
+
+        rs = s.executeQuery( "SELECT ps1.group_ref," +
+		"COUNT(ps1.matched_count) AS matched_count" +
+		" FROM test_5 ps1 " +
+		" GROUP BY ps1.group_ref, ps1.profile_id" );
+        JDBC.assertFullResultSet(rs, new String[][] {
+                {"10000", "1"},
+                {"10000", "1"}
+	});
+
+	String cartProdWithDISTINCTsubqueries = " SELECT *" +
+		" FROM " +
+		" (SELECT ps1.group_ref, ps1.profile_id, " +
+		"         COUNT(DISTINCT ps1.matched_count) AS matched_count " +
+		"  FROM test_5 ps1" +
+		"  GROUP BY ps1.group_ref, ps1.profile_id " +
+		" ) a, " +
+		" (SELECT ps2.group_ref, ps2.profile_id, " +
+		"         COUNT( DISTINCT ps2.matched_count) AS matched_count" +
+		"  FROM test_6 ps2" +
+		"  GROUP BY ps2.group_ref, ps2.profile_id " +
+		") b ";
+
+	String cartProdWithSubqueries = " SELECT * " +
+		" FROM " +
+		" (SELECT ps1.group_ref, ps1.profile_id, " +
+		"         COUNT(ps1.matched_count) AS matched_count " +
+		"  FROM test_5 ps1 " +
+		"  GROUP BY ps1.group_ref, ps1.profile_id " +
+		") a, " +
+		" (SELECT ps2.group_ref, ps2.profile_id, " +
+		"         COUNT( ps2.matched_count) AS matched_count " +
+		"  FROM test_6 ps2 " +
+		"  GROUP BY ps2.group_ref, ps2.profile_id " +
+		") b ";
+
+	String cartProdWithOrderBySubqueries = "SELECT * " +
+		" FROM " +
+		" (SELECT ps1.group_ref, ps1.profile_id " +
+		"  FROM test_5 ps1 ORDER BY profile_id fetch first 3 rows only) a, " +
+		" (SELECT ps2.group_ref, ps2.profile_id " +
+		"  FROM test_6 ps2 ORDER BY PROFILE_ID fetch first 2 rows only) b "; 
+
+
+	rs = s.executeQuery( cartProdWithDISTINCTsubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "1", "10000", "1", "1"},
+		{"10000", "1", "1", "10000", "2", "1"},
+		{"10000", "2", "1", "10000", "1", "1"},
+		{"10000", "2", "1", "10000", "2", "1"}
+	});
+
+	rs = s.executeQuery( cartProdWithSubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "1", "10000", "1", "1"},
+		{"10000", "1", "1", "10000", "2", "1"},
+		{"10000", "2", "1", "10000", "1", "1"},
+		{"10000", "2", "1", "10000", "2", "1"}
+	});
+
+        s.executeUpdate( "insert into test_5 values (3, 10000, 3)" ); 
+
+	rs = s.executeQuery( cartProdWithDISTINCTsubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "1", "10000", "1", "1"},
+		{"10000", "1", "1", "10000", "2", "1"},
+		{"10000", "2", "1", "10000", "1", "1"},
+		{"10000", "2", "1", "10000", "2", "1"},
+		{"10000", "3", "1", "10000", "1", "1"},
+		{"10000", "3", "1", "10000", "2", "1"}
+	});
+
+	rs = s.executeQuery( cartProdWithSubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "1", "10000", "1", "1"},
+		{"10000", "1", "1", "10000", "2", "1"},
+		{"10000", "2", "1", "10000", "1", "1"},
+		{"10000", "2", "1", "10000", "2", "1"},
+		{"10000", "3", "1", "10000", "1", "1"},
+		{"10000", "3", "1", "10000", "2", "1"}
+	});
+
+        s.executeUpdate( "insert into test_5 values (4, 10000, 4) "); 
+        s.executeUpdate( "insert into test_6 values (3, 10000, 3) "); 
+
+	// NOTE: At this point,
+	//   test_5 contains:		test_6 contains:
+	//	1, 10000, 1			1, 10000, 1
+	//	2, 10000, 2			2, 10000, 2
+	//	3, 10000, 3			3, 10000, 3
+	//	4, 10000, 4
+
+	rs = s.executeQuery( cartProdWithDISTINCTsubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "1", "10000", "1", "1"},
+		{"10000", "1", "1", "10000", "2", "1"},
+		{"10000", "1", "1", "10000", "3", "1"},
+		{"10000", "2", "1", "10000", "1", "1"},
+		{"10000", "2", "1", "10000", "2", "1"},
+		{"10000", "2", "1", "10000", "3", "1"},
+		{"10000", "3", "1", "10000", "1", "1"},
+		{"10000", "3", "1", "10000", "2", "1"},
+		{"10000", "3", "1", "10000", "3", "1"},
+		{"10000", "4", "1", "10000", "1", "1"},
+		{"10000", "4", "1", "10000", "2", "1"},
+		{"10000", "4", "1", "10000", "3", "1"}
+	});
+
+	rs = s.executeQuery( cartProdWithSubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "1", "10000", "1", "1"},
+		{"10000", "1", "1", "10000", "2", "1"},
+		{"10000", "1", "1", "10000", "3", "1"},
+		{"10000", "2", "1", "10000", "1", "1"},
+		{"10000", "2", "1", "10000", "2", "1"},
+		{"10000", "2", "1", "10000", "3", "1"},
+		{"10000", "3", "1", "10000", "1", "1"},
+		{"10000", "3", "1", "10000", "2", "1"},
+		{"10000", "3", "1", "10000", "3", "1"},
+		{"10000", "4", "1", "10000", "1", "1"},
+		{"10000", "4", "1", "10000", "2", "1"},
+		{"10000", "4", "1", "10000", "3", "1"}
+	});
+
+        s.executeUpdate( "insert into test_6 values (2, 10000, 1) "); 
+
+	rs = s.executeQuery( cartProdWithDISTINCTsubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "1", "10000", "1", "1"},
+		{"10000", "1", "1", "10000", "2", "2"},
+		{"10000", "1", "1", "10000", "3", "1"},
+		{"10000", "2", "1", "10000", "1", "1"},
+		{"10000", "2", "1", "10000", "2", "2"},
+		{"10000", "2", "1", "10000", "3", "1"},
+		{"10000", "3", "1", "10000", "1", "1"},
+		{"10000", "3", "1", "10000", "2", "2"},
+		{"10000", "3", "1", "10000", "3", "1"},
+		{"10000", "4", "1", "10000", "1", "1"},
+		{"10000", "4", "1", "10000", "2", "2"},
+		{"10000", "4", "1", "10000", "3", "1"}
+	});
+
+	rs = s.executeQuery( cartProdWithSubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "1", "10000", "1", "1"},
+		{"10000", "1", "1", "10000", "2", "2"},
+		{"10000", "1", "1", "10000", "3", "1"},
+		{"10000", "2", "1", "10000", "1", "1"},
+		{"10000", "2", "1", "10000", "2", "2"},
+		{"10000", "2", "1", "10000", "3", "1"},
+		{"10000", "3", "1", "10000", "1", "1"},
+		{"10000", "3", "1", "10000", "2", "2"},
+		{"10000", "3", "1", "10000", "3", "1"},
+		{"10000", "4", "1", "10000", "1", "1"},
+		{"10000", "4", "1", "10000", "2", "2"},
+		{"10000", "4", "1", "10000", "3", "1"}
+	});
+
+	// Now introduce some duplicate values so that the DISTINCT
+	// aggregates have some work to do
+
+
+        s.executeUpdate( "insert into test_6 values (1, 10000, 1) "); 
+        s.executeUpdate( "insert into test_6 values (2, 10000, 2) "); 
+
+	// NOTE: At this point,
+	//   test_5 contains:		test_6 contains:
+	//	1, 10000, 1			1, 10000, 1 (2 vals, 1 distinct)
+	//	2, 10000, 2			1, 10000, 1 
+	//	3, 10000, 3			2, 10000, 1 (3 vals, 2 distinct)
+	//	4, 10000, 4			2, 10000, 2
+	//	 				2, 10000, 2
+	//	 				3, 10000, 2 (1 val, 1 distinct)
+
+	rs = s.executeQuery( cartProdWithDISTINCTsubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "1", "10000", "1", "1"},
+		{"10000", "1", "1", "10000", "2", "2"},
+		{"10000", "1", "1", "10000", "3", "1"},
+		{"10000", "2", "1", "10000", "1", "1"},
+		{"10000", "2", "1", "10000", "2", "2"},
+		{"10000", "2", "1", "10000", "3", "1"},
+		{"10000", "3", "1", "10000", "1", "1"},
+		{"10000", "3", "1", "10000", "2", "2"},
+		{"10000", "3", "1", "10000", "3", "1"},
+		{"10000", "4", "1", "10000", "1", "1"},
+		{"10000", "4", "1", "10000", "2", "2"},
+		{"10000", "4", "1", "10000", "3", "1"}
+	});
+
+	rs = s.executeQuery( cartProdWithSubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "1", "10000", "1", "2"},
+		{"10000", "1", "1", "10000", "2", "3"},
+		{"10000", "1", "1", "10000", "3", "1"},
+		{"10000", "2", "1", "10000", "1", "2"},
+		{"10000", "2", "1", "10000", "2", "3"},
+		{"10000", "2", "1", "10000", "3", "1"},
+		{"10000", "3", "1", "10000", "1", "2"},
+		{"10000", "3", "1", "10000", "2", "3"},
+		{"10000", "3", "1", "10000", "3", "1"},
+		{"10000", "4", "1", "10000", "1", "2"},
+		{"10000", "4", "1", "10000", "2", "3"},
+		{"10000", "4", "1", "10000", "3", "1"}
+	});
+
+	rs = s.executeQuery( cartProdWithOrderBySubqueries );
+        JDBC.assertFullResultSet(rs, new String[][] {
+		{"10000", "1", "10000", "1"},
+		{"10000", "1", "10000", "1"},
+		{"10000", "2", "10000", "1"},
+		{"10000", "2", "10000", "1"},
+		{"10000", "3", "10000", "1"},
+		{"10000", "3", "10000", "1"}
+	});
+
+            rollback();
+    }
 }



Mime
View raw message