db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r824966 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/execute/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Tue, 13 Oct 2009 22:58:20 GMT
Author: bpendleton
Date: Tue Oct 13 22:58:19 2009
New Revision: 824966

URL: http://svn.apache.org/viewvc?rev=824966&view=rev
Log:
DERBY-3002: Add partial support for ROLLUP variant of GROUP BY clause

This change adds an initial implementation of support for the ROLLUP
style of GROUP BY. Specifying ROLLUP causes Derby to perform multiple
nested levels of grouping in a single pass over the input data, returning
the results in a single result set.

Most of the changes were made to GroupedAggregateResultSet, which is
enhanced to be able to perform multiple levels of grouping in a single
pass over the (sorted) input data. There are also changes to the SQL grammar
to allow the new syntax to be specified, and changes to GroupByNode to
pass the rollup specification through to the execution engine.

Several tests were added to the OLAPTest test suite.



Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DistinctGroupedAggregateResultSet.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericAggregator.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/OrderableAggregator.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SumAggregator.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SystemAggregator.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java Tue Oct 13 22:58:19 2009
@@ -487,6 +487,7 @@
 		@param optimizerEstimatedRowCount	Estimated total # of rows by
 											optimizer
 		@param optimizerEstimatedCost		Estimated total cost by optimizer
+		@param isRollup true if this is a GROUP BY ROLLUP()
 		@return the scalar aggregation operation as a result set.
 		@exception StandardException thrown when unable to create the
 			result set
@@ -499,7 +500,8 @@
 		int rowSize,
 		int resultSetNumber, 
 		double optimizerEstimatedRowCount,
-		double optimizerEstimatedCost) 
+		double optimizerEstimatedCost,
+		boolean isRollup) 
 			throws StandardException;
 
 	/**
@@ -521,6 +523,7 @@
 		@param optimizerEstimatedRowCount	Estimated total # of rows by
 											optimizer
 		@param optimizerEstimatedCost		Estimated total cost by optimizer
+		@param isRollup true if this is a GROUP BY ROLLUP()
 		@return the scalar aggregation operation as a result set.
 		@exception StandardException thrown when unable to create the
 			result set
@@ -533,7 +536,8 @@
 		int rowSize,
 		int resultSetNumber, 
 		double optimizerEstimatedRowCount,
-		double optimizerEstimatedCost) 
+		double optimizerEstimatedCost,
+		boolean isRollup) 
 			throws StandardException;
 
 	/**

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=824966&r1=824965&r2=824966&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 Oct 13 22:58:19 2009
@@ -47,6 +47,7 @@
 public class GroupByList extends OrderedColumnList
 {
 	int		numGroupingColsAdded = 0;
+	boolean         rollup = false;
 
 	/**
 		Add a column to the list
@@ -75,6 +76,16 @@
 	}
 
 
+	public void setRollup()
+	{
+		rollup = true;
+	}
+	public boolean isRollup()
+	{
+		return rollup;
+	}
+                        
+
 	/**
 	 * Get the number of grouping columns that need to be added to the SELECT list.
 	 *

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java Tue Oct 13 22:58:19 2009
@@ -183,6 +183,11 @@
 		*/
 		addAggregates();
 
+		if (this.groupingList != null && this.groupingList.isRollup())
+                {
+			resultColumns.setNullability(true);
+			parent.getResultColumns().setNullability(true);
+                }
 		/* We say that the source is never in sorted order if there is a distinct aggregate.
 		 * (Not sure what happens if it is, so just skip it for now.)
 		 * Otherwise, we check to see if the source is in sorted order on any permutation
@@ -252,7 +257,7 @@
 		{
 			if (SanityManager.DEBUG)
 			{
-				SanityManager.ASSERT(numDistinct == 1,
+				SanityManager.ASSERT(groupingList != null || numDistinct == 1,
 					"Should not have more than 1 distinct aggregate per Group By node");
 			}
 			
@@ -1141,14 +1146,16 @@
 		 *			from the sort
 		 *  arg7: row size
 		 *  arg8: resultSetNumber
+		 *  arg9: isRollup
 		 */
 		String resultSet = (addDistinctAggregate) ? "getDistinctGroupedAggregateResultSet" : "getGroupedAggregateResultSet";
     
 		mb.push(costEstimate.rowCount());
 		mb.push(costEstimate.getEstimatedCost());
+		mb.push(groupingList.isRollup());
 
 		mb.callMethod(VMOpcode.INVOKEINTERFACE, (String) null, resultSet,
-                ClassName.NoPutResultSet, 9);
+                ClassName.NoPutResultSet, 10);
 
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Tue Oct 13 22:58:19 2009
@@ -564,7 +564,7 @@
 		** in the select list.
 		*/
 		numDistinctAggs = numDistinctAggregates(selectAggregates);
-		if (numDistinctAggs > 1)
+		if (groupByList == null && numDistinctAggs > 1)
 		{
 			throw StandardException.newException(SQLState.LANG_USER_AGGREGATE_MULTIPLE_DISTINCTS);
 		}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Tue Oct 13 22:58:19 2009
@@ -2225,6 +2225,7 @@
 |	<REPEATABLE: "repeatable">
 |	<RESTART: "restart">
 |	<RETURNS: "returns">
+|	<ROLLUP: "rollup">
 |	<ROW: "row">
 |	<SAVEPOINT: "savepoint">
 |	<SCALE: "scale">
@@ -9605,10 +9606,21 @@
 	GroupByList groupingCols;
 }
 {
-	<GROUP> <BY> groupingCols = groupingColumnReferenceList()
+	<GROUP> <BY> 
+        (
+	LOOKAHEAD ( { getToken(1).kind == ROLLUP &&
+			getToken(2).kind == LEFT_PAREN } )
+        <ROLLUP> <LEFT_PAREN> groupingCols = groupingColumnReferenceList() <RIGHT_PAREN>
+	{
+		groupingCols.setRollup();
+		return groupingCols;
+	}
+|
+	groupingCols = groupingColumnReferenceList()
 	{
 		return groupingCols;
 	}
+        )
 }
 
 GroupByList
@@ -13903,6 +13915,7 @@
 	|	tok = <RETURNING>
 	|	tok = <RETURNS>
 	|	tok = <ROLE>
+ 	|	tok = <ROLLUP>
 	|	tok = <ROW>
 //	|	tok = <ROW_COUNT>
 	|   tok = <RR>

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DistinctGroupedAggregateResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DistinctGroupedAggregateResultSet.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DistinctGroupedAggregateResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DistinctGroupedAggregateResultSet.java Tue Oct 13 22:58:19 2009
@@ -67,11 +67,12 @@
 					GeneratedMethod ra,
 					int maxRowSize,
 					int resultSetNumber,
-				    double optimizerEstimatedRowCount,
-				    double optimizerEstimatedCost) throws StandardException 
+					double optimizerEstimatedRowCount,
+					double optimizerEstimatedCost,
+					boolean isRollup) throws StandardException 
 	{
 		super(s, isInSortedOrder, aggregateItem, orderingItem,
-			  a, ra, maxRowSize, resultSetNumber, optimizerEstimatedRowCount, optimizerEstimatedCost);
+			  a, ra, maxRowSize, resultSetNumber, optimizerEstimatedRowCount, optimizerEstimatedCost, isRollup);
     }
 
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericAggregator.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericAggregator.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericAggregator.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericAggregator.java Tue Oct 13 22:58:19 2009
@@ -336,6 +336,11 @@
 		return aggregatorColumnId;
 	}
 
+	DataValueDescriptor getInputColumnValue(ExecRow row)
+	    throws StandardException
+	{
+	    return row.getColumn(inputColumnId + 1);
+	}
 
 	/**
 	 * Merge two partial aggregations.  This is how the

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java Tue Oct 13 22:58:19 2009
@@ -346,13 +346,14 @@
 		int maxRowSize,
 		int resultSetNumber, 
 		double optimizerEstimatedRowCount,
-		double optimizerEstimatedCost) 
+		double optimizerEstimatedCost,
+		boolean isRollup) 
 			throws StandardException
 	{
 		return new GroupedAggregateResultSet(
 						source, isInSortedOrder, aggregateItem, orderItem, source.getActivation(),
 						rowAllocator, maxRowSize, resultSetNumber, optimizerEstimatedRowCount,
-						optimizerEstimatedCost);
+						optimizerEstimatedCost, isRollup);
 	}
 
 	/**
@@ -367,13 +368,14 @@
 		int maxRowSize,
 		int resultSetNumber, 
 		double optimizerEstimatedRowCount,
-		double optimizerEstimatedCost) 
+		double optimizerEstimatedCost,
+		boolean isRollup) 
 			throws StandardException
 	{
 		return new DistinctGroupedAggregateResultSet(
 						source, isInSortedOrder, aggregateItem, orderItem, source.getActivation(),
 						rowAllocator, maxRowSize, resultSetNumber, optimizerEstimatedRowCount,
-						optimizerEstimatedCost);
+						optimizerEstimatedCost, isRollup);
 	}
 											
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GroupedAggregateResultSet.java Tue Oct 13 22:58:19 2009
@@ -21,7 +21,10 @@
 
 package org.apache.derby.impl.sql.execute;
 
+import java.util.ArrayList;
+import java.util.List;
 import java.util.Properties;
+import java.util.HashSet;
 
 import org.apache.derby.iapi.error.StandardException;
 import org.apache.derby.iapi.services.io.FormatableArrayHolder;
@@ -29,6 +32,7 @@
 import org.apache.derby.iapi.services.sanity.SanityManager;
 import org.apache.derby.iapi.sql.Activation;
 import org.apache.derby.iapi.sql.execute.CursorResultSet;
+import org.apache.derby.iapi.sql.execute.ExecAggregator;
 import org.apache.derby.iapi.sql.execute.ExecIndexRow;
 import org.apache.derby.iapi.sql.execute.ExecRow;
 import org.apache.derby.iapi.sql.execute.NoPutResultSet;
@@ -46,6 +50,30 @@
  * the grouped aggregates when scanning the source during the 
  * first call to next().
  *
+ * The implementation is capable of computing multiple levels of grouping
+ * in a single result set (this is requested using GROUP BY ROLLUP).
+ *
+ * This implementation has 3 variations, which it chooses according to
+ * the following rules:
+ * - If the data are guaranteed to arrive already in sorted order, we make
+ *   a single pass over the data, computing the aggregates in-line as the
+ *   data are read.
+ * - If the statement requests either multiple ROLLUP levels, or a DISTINCT
+ *   grouping, then the data are first sorted, then we make a single
+ *   pass over the data as above.
+ * - Otherwise, the data are sorted, and a SortObserver is used to compute
+ *   the aggregations inside the sort, and the results are read back directly
+ *   from the sorter.
+ *
+ * Note that, as of the introduction of the ROLLUP support, we no longer
+ * ALWAYS compute the aggregates using a SortObserver, which is an
+ * arrangement by which the sorter calls back into the aggregates during
+ * the sort process each time it consolidates two rows with the same
+ * sort key. Using aggregate sort observers is an efficient technique, but
+ * it was complex to extend it to the ROLLUP case, so to simplify the code
+ * we just have one path for both already-sorted and un-sorted data sources
+ * in the ROLLUP case.
+ *
  */
 class GroupedAggregateResultSet extends GenericAggregateResultSet
 	implements CursorResultSet {
@@ -70,15 +98,35 @@
 
 	private ExecIndexRow sortResultRow;
 
-	// In order group bys
-	private ExecIndexRow currSortedRow;
-	private boolean nextCalled;
-
-	// used to track and close sorts
-	private long distinctAggSortId;
-	private boolean dropDistinctAggSort;
+	// - resultRows: This is the current accumulating grouped result that
+	//   we are computing, at each level of aggregation. If we are not
+	//   doing a ROLLUP, then there is only one entry in resultRows, and
+	//   it contains the currently-accumulating aggregated result. If we
+	//   are doing a ROLLUP, then there are N+1 entries in resultRows,
+	//   as follows (imagine we're doing ROLLUP(a,b,c,d):
+	//   [0]: GROUP BY ()
+	//   [1]: GROUP BY (A)
+	//   [2]: GROUP BY (A,B)
+	//   [3]: GROUP BY (A,B,C)
+	//   [4]: GROUP BY (A,B,C,D)
+	// - finishedResults: this list is used only when a ROLLUP is computing
+	//   multiple levels of aggregation at once, and the results for
+	//   several groupings have been completed, but not yet returned to
+	//   our caller.
+	// - distinctValues: used only if DISTINCT aggregates are present,
+	//   this is a HashSet for each aggregate for each level of grouping,
+	//   and the HashSet instances contain the values this aggregate
+	//   has seen during this group instance, to eliminate duplicates.
+	//
+	private boolean resultsComplete;
+	private List finishedResults;
+	private ExecIndexRow[]			resultRows;
+	private HashSet [][]			distinctValues;
+
+	private boolean rollup;
+	private boolean usingAggregateObserver = false;
+
 	private long genericSortId;
-	private boolean dropGenericSort;
 	private TransactionController tc;
 
 	// RTS
@@ -112,10 +160,13 @@
 					int maxRowSize,
 					int resultSetNumber,
 				    double optimizerEstimatedRowCount,
-				    double optimizerEstimatedCost) throws StandardException 
+					double optimizerEstimatedCost,
+					boolean isRollup) throws StandardException 
 	{
 		super(s, aggregateItem, a, ra, resultSetNumber, optimizerEstimatedRowCount, optimizerEstimatedCost);
 		this.isInSortedOrder = isInSortedOrder;
+		rollup = isRollup;
+		finishedResults = new ArrayList();
 		sortTemplateRow = getExecutionFactory().getIndexableRow((ExecRow) rowAllocator.invoke(activation));
 		order = (ColumnOrdering[])
 					((FormatableArrayHolder)
@@ -127,6 +178,14 @@
 			SanityManager.DEBUG("AggregateTrace","execution time: "+ 
 					a.getPreparedStatement().getSavedObject(aggregateItem));
 		}
+		hasDistinctAggregate = aggInfoList.hasDistinct();
+		// If there is no ROLLUP, and no DISTINCT, and the data are
+		// not in sorted order, then we can use AggregateSortObserver
+		// to compute the aggregation in the sorter:
+		usingAggregateObserver =
+			!isInSortedOrder &&
+			!rollup &&
+			!hasDistinctAggregate;
 
 		recordConstructorTime();
     }
@@ -159,27 +218,40 @@
         source.openCore();
 
 		try {
-			/* If this is an in-order group by then we do not need the sorter.
-			 * (We can do the aggregation ourselves.)
-			 * We save a clone of the first row so that subsequent next()s
-			 * do not overwrite the saved row.
-			 */
-			if (isInSortedOrder)
-			{
-				currSortedRow = getNextRowFromRS();
-				if (currSortedRow != null)
-				{
-					currSortedRow = (ExecIndexRow) currSortedRow.getClone();
-					initializeVectorAggregation(currSortedRow);
-				}
-			}
+		/* If this is an in-order group by then we do not need the sorter.
+		 * (We can do the aggregation ourselves.)
+		 * We save a clone of the first row so that subsequent next()s
+		 * do not overwrite the saved row.
+		 */
+		if (!isInSortedOrder)
+			scanController = loadSorter();
+
+		ExecIndexRow currSortedRow = getNextRowFromRS();
+		resultsComplete = (currSortedRow == null);
+		if (usingAggregateObserver)
+		{
+			if (currSortedRow != null)
+				finishedResults.add(
+					finishAggregation(currSortedRow).getClone());
+		}
+		else if (!resultsComplete)
+		{
+			if (rollup)
+				resultRows = new ExecIndexRow[order.length+1];
 			else
-			{
-				/*
-				** Load up the sorter
-				*/
-				scanController = loadSorter();
+				resultRows = new ExecIndexRow[1];
+			if (aggInfoList.hasDistinct())
+			    distinctValues = new HashSet[resultRows.length][aggregates.length];
+			for (int r = 0; r < resultRows.length; r++)
+			{
+				resultRows[r] =
+					(ExecIndexRow) currSortedRow.getClone();
+				initializeVectorAggregation(resultRows[r]);
+				if (aggInfoList.hasDistinct())
+					distinctValues[r] = new HashSet[aggregates.length];
+				initializeDistinctMaps(r, true);
 			}
+		}
 		} catch (StandardException e) {
 			// DERBY-4330 Result set tree must be atomically open or
 			// closed for reuse to work (after DERBY-827).
@@ -197,8 +269,7 @@
 
 	/**
 	 * Load up the sorter.  Feed it every row from the
-	 * source scan.  If we have a vector aggregate, initialize
-	 * the aggregator for each source row.  When done, close
+	 * source scan.  When done, close
 	 * the source scan and open the sort.  Return the sort
 	 * scan controller.
 	 *
@@ -210,106 +281,29 @@
 		throws StandardException
 	{
 		SortController 			sorter;
-		long 					sortId;
 		ExecRow 				sourceRow;
 		ExecRow 				inputRow;
 		int						inputRowCountEstimate = (int) optimizerEstimatedRowCount;
-		boolean					inOrder = isInSortedOrder;
 
 		tc = getTransactionController();
 
-		ColumnOrdering[] currentOrdering = order;
-
-		/*
-		** Do we have any distinct aggregates?  If so, we'll need
-		** a separate sort.  We use all of the sorting columns and
-		** drop the aggregation on the distinct column.  Then
-		** we'll feed this into the sorter again w/o the distinct
-		** column in the ordering list.
-		*/
-		if (aggInfoList.hasDistinct())
-		{
-			hasDistinctAggregate = true;
-			
-			GenericAggregator[] aggsNoDistinct = getSortAggregators(aggInfoList, true,
-						activation.getLanguageConnectionContext(), source);
-			SortObserver sortObserver = new AggregateSortObserver(true, aggsNoDistinct, aggregates,
-																  sortTemplateRow);
-
-			sortId = tc.createSort((Properties)null, 
-					sortTemplateRow.getRowArray(),
-					order,
-					sortObserver,
-					false,			// not in order
-					inputRowCountEstimate,				// est rows, -1 means no idea	
-					maxRowSize		// est rowsize
-					);
-			sorter = tc.openSort(sortId);
-			distinctAggSortId = sortId;
-			dropDistinctAggSort = true;
-				
-			while ((sourceRow = source.getNextRowCore())!=null) 
-			{
-				sorter.insert(sourceRow.getRowArray());
-				rowsInput++;
-			}
-
-			/*
-			** End the sort and open up the result set
-			*/
-			source.close();
-			sortProperties = sorter.getSortInfo().getAllSortInfo(sortProperties);
-			sorter.completedInserts();
-
-			scanController = 
-                tc.openSortScan(sortId, activation.getResultSetHoldability());
-			
-			/*
-			** Aggs are initialized and input rows
-			** are in order.  All we have to do is
-			** another sort to remove (merge) the 
-			** duplicates in the distinct column
-			*/	
-			inOrder = true;
-			inputRowCountEstimate = rowsInput;
-	
-			/*
-			** Drop the last column from the ordering.  The
-			** last column is the distinct column.  Don't
-			** pay any attention to the fact that the ordering
-			** object's name happens to correspond to a techo
-			** band from the 80's.
-			**
-			** If there aren't any ordering columns other
-			** than the distinct (i.e. for scalar distincts)
-			** just skip the 2nd sort altogether -- we'll
-			** do the aggregate merge ourselves rather than
-			** force a 2nd sort.
-			*/
-			if (order.length == 1)
-			{
-				return scanController;
-			}
+		SortObserver observer;
+		if (usingAggregateObserver)
+			observer = new AggregateSortObserver(true, aggregates,
+				aggregates, sortTemplateRow);
+		else
+			observer = new BasicSortObserver(true, false,
+				sortTemplateRow, true);
 
-			ColumnOrdering[] newOrder = new ColumnOrdering[order.length - 1];
-			System.arraycopy(order, 0, newOrder, 0, order.length - 1);
-			currentOrdering = newOrder;
-		}
-
-		SortObserver sortObserver = new AggregateSortObserver(true, aggregates, aggregates,
-															  sortTemplateRow);
-
-		sortId = tc.createSort((Properties)null, 
-						sortTemplateRow.getRowArray(),
-						currentOrdering,
-						sortObserver,
-						inOrder,
-						inputRowCountEstimate, // est rows
-					 	maxRowSize			// est rowsize 
-						);
-		sorter = tc.openSort(sortId);
-		genericSortId = sortId;
-		dropGenericSort = true;
+		genericSortId = tc.createSort((Properties)null, 
+				sortTemplateRow.getRowArray(),
+				order,
+				observer,
+				false,
+				inputRowCountEstimate, // est rows
+				maxRowSize			// est rowsize 
+		);
+		sorter = tc.openSort(genericSortId);
 	
 		/* The sorter is responsible for doing the cloning */
 		while ((inputRow = getNextRowFromRS()) != null) 
@@ -317,10 +311,44 @@
 			sorter.insert(inputRow.getRowArray());
 		}
 		source.close();
-		sortProperties = sorter.getSortInfo().getAllSortInfo(sortProperties);
 		sorter.completedInserts();
-
-		return tc.openSortScan(sortId, activation.getResultSetHoldability());
+		sortProperties = sorter.getSortInfo().
+			getAllSortInfo(sortProperties);
+		if (aggInfoList.hasDistinct())
+		{
+			/*
+			** If there was a distinct aggregate, then that column
+			** was automatically included as the last column in
+			** the sort ordering. But we don't want it to be part
+			** of the ordering anymore, because we aren't grouping
+			** 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());
 	}
 
 
@@ -341,82 +369,105 @@
 
 		beginTime = getCurrentTimeMillis();
 
-		// In order group by
-		if (isInSortedOrder)
-		{
-			// No rows, no work to do
-			if (currSortedRow == null)
-			{
-				nextTime += getElapsedMillis(beginTime);
-				return null;
-			}
-
-		    ExecIndexRow nextRow = getNextRowFromRS();
+		if (finishedResults.size() > 0)
+			return makeCurrent(finishedResults.remove(0));
+		else if (resultsComplete)
+			return null;
 
-			/* Drain and merge rows until we find new distinct values for the grouping columns. */
-			while (nextRow != null)
-			{
-				/* We found a new set of values for the grouping columns.  
-				 * Update the current row and return this group. 
-				 */
-				if (! sameGroupingValues(currSortedRow, nextRow))
+		ExecIndexRow nextRow = getNextRowFromRS();
+		// No rows, no work to do
+		if (nextRow == null)
+			return finalizeResults();
+
+		// If the aggregation was performed using the SortObserver, the
+		// result row from the sorter is complete and ready to return:
+		if (usingAggregateObserver)
+			return finishAggregation(nextRow);
+
+		/* Drain and merge rows until we find new distinct values for the grouping columns. */
+		while (nextRow != null)
+		{
+			/* We found a new set of values for the grouping columns.  
+			 * Update the current row and return this group. 
+			 *
+			 * Note that in the case of GROUP BY ROLLUP,
+			 * there may be more than one level of grouped
+			 * aggregates which is now complete. We can
+			 * only return 1, and the other completed
+			 * groups are held in finishedResults until
+			 * our caller calls getNextRowCore() again to
+			 * get the next level of results.
+			 */
+			ExecIndexRow currSortedRow =
+				    resultRows[resultRows.length-1];
+                        ExecRow origRow = (ExecRow)nextRow.getClone();;
+                        initializeVectorAggregation(nextRow);
+			int distinguisherCol = 
+				    sameGroupingValues(currSortedRow, nextRow);
+
+			for (int r = 0; r < resultRows.length; r++)
+			{
+				boolean sameGroup = (rollup ?
+				    r <= distinguisherCol :
+				    distinguisherCol == order.length);
+				if (sameGroup)
 				{
-					ExecIndexRow result = currSortedRow;
-
-					/* Save a clone of the new row so that it doesn't get overwritten */
-					currSortedRow = (ExecIndexRow) nextRow.getClone();
-					initializeVectorAggregation(currSortedRow);
-
-					nextTime += getElapsedMillis(beginTime);
-					rowsReturned++;
-					return finishAggregation(result);
+					/* Same group - initialize the new
+					   row and then merge the aggregates */
+					//initializeVectorAggregation(nextRow);
+					mergeVectorAggregates(nextRow, resultRows[r], r);
 				}
 				else
 				{
-					/* Same group - initialize the new row and then merge the aggregates */
-					initializeVectorAggregation(nextRow);
-					mergeVectorAggregates(nextRow, currSortedRow);
+					setRollupColumnsToNull(resultRows[r],r);
+					finishedResults.add(finishAggregation(resultRows[r]));
+					/* Save a clone of the new row so
+					   that it doesn't get overwritten */
+					resultRows[r] = (ExecIndexRow)
+						    origRow.getClone();
+					initializeVectorAggregation(resultRows[r]);
+					initializeDistinctMaps(r, false);
 				}
-
-				// Get the next row
-				nextRow = getNextRowFromRS();
 			}
-
-			// We've drained the source, so no more rows to return
-			ExecIndexRow result = currSortedRow;
-			currSortedRow = null;
-			nextTime += getElapsedMillis(beginTime);
-			return finishAggregation(result);
-		}
-		else
-		{
-		    ExecIndexRow sortResult = null;
-
-	        if ((sortResult = getNextRowFromRS()) != null)
+			if (finishedResults.size() > 0)
 			{
-				setCurrentRow(sortResult);
+				nextTime += getElapsedMillis(beginTime);
+				rowsReturned++;
+                                return makeCurrent(finishedResults.remove(0));
 			}
 
-			/*
-			** Only finish the aggregation
-			** if we have a return row.  We don't generate
-			** a row on a vector aggregate unless there was
-			** a group.
-			*/
-			if (sortResult != null)
-			{
-				sortResult = finishAggregation(sortResult);
-				currentRow = sortResult;
-			}
+			// Get the next row
+			nextRow = getNextRowFromRS();
+		}
 
-			if (sortResult != null)
+		return finalizeResults();
+	}
+	// Return the passed row, after ensuring that we call setCurrentRow
+	private ExecRow makeCurrent(Object row)
+		throws StandardException
+	{
+		ExecRow resultRow = (ExecRow)row;
+		setCurrentRow(resultRow);
+		return resultRow;
+	}
+	private ExecRow finalizeResults()
+		throws StandardException
+	{
+		// We've drained the source, so no more rows to return
+		resultsComplete = true;
+		if (! usingAggregateObserver )
+		{
+			for (int r = 0; r < resultRows.length; r++)
 			{
-				rowsReturned++;
+				setRollupColumnsToNull(resultRows[r],r);
+				finishedResults.add(finishAggregation(resultRows[r]));
 			}
-
-			nextTime += getElapsedMillis(beginTime);
-		    return sortResult;
 		}
+		nextTime += getElapsedMillis(beginTime);
+		if (finishedResults.size() > 0)
+			return makeCurrent(finishedResults.remove(0));
+		else
+			return null;
 	}
 
 	/**
@@ -427,12 +478,12 @@
 	 * @param currRow	The current row.
 	 * @param newRow	The new row.
 	 *
-	 * @return	Whether or not to filter out the new row has the same values for the 
-	 *			grouping columns as the current row.
+	 * @return	The order index number which first distinguished
+	 *			these rows, or order.length if the rows match.
 	 *
 	 * @exception StandardException thrown on failure to get row location
 	 */
-	private boolean sameGroupingValues(ExecRow currRow, ExecRow newRow)
+	private int sameGroupingValues(ExecRow currRow, ExecRow newRow)
 		throws StandardException
 	{
 		for (int index = 0; index < order.length; index++)
@@ -441,10 +492,10 @@
 			DataValueDescriptor newOrderable = newRow.getColumn(order[index].getColumnId() + 1);
 			if (! (currOrderable.compare(DataValueDescriptor.ORDER_OP_EQUALS, newOrderable, true, true)))
 			{
-				return false;
+				return index;
 			}
 		}
-		return true;
+		return order.length;
 	}
 
 	/**
@@ -468,16 +519,9 @@
 			sourceExecIndexRow = null;
 			closeSource();
 
-			if (dropDistinctAggSort)
-			{
-				tc.dropSort(distinctAggSortId);
-				dropDistinctAggSort = false;
-			}
-
-			if (dropGenericSort)
+			if (!isInSortedOrder)
 			{
 				tc.dropSort(genericSortId);
-				dropGenericSort = false;
 			}
 			super.close();
 		}
@@ -597,6 +641,22 @@
 	}
 
 
+	// We are performing a ROLLUP aggregation and
+	// we need to set the N rolled-up columns in this
+	// row to NULL.
+	private void setRollupColumnsToNull(ExecRow row, int resultNum)
+		throws StandardException
+	{
+		int numRolledUpCols = resultRows.length - resultNum - 1;
+		for (int i = 0; i < numRolledUpCols; i++)
+		{
+			int rolledUpColIdx = order.length - 1 - i;
+			DataValueDescriptor rolledUpColumn =
+				row.getColumn(order[rolledUpColIdx].getColumnId() + 1);
+			rolledUpColumn.setToNull();
+		}
+	}
+
 	/**
 	 * Get a row from the sorter.  Side effects:
 	 * sets currentRow.
@@ -616,6 +676,7 @@
 			inputRow = getExecutionFactory().getIndexableRow(currentRow);
 
 			scanController.fetch(inputRow.getRowArray());
+
 		}
 		return inputRow;
 	}
@@ -691,16 +752,80 @@
 	 *
 	 * @exception	standard Derby exception
 	 */
-	private void mergeVectorAggregates(ExecRow newRow, ExecRow currRow)
+	private void mergeVectorAggregates(ExecRow newRow, ExecRow currRow,
+		int level)
 		throws StandardException
 	{
 		for (int i = 0; i < aggregates.length; i++)
 		{
 			GenericAggregator currAggregate = aggregates[i];
+			AggregatorInfo aInfo = (AggregatorInfo)
+					aggInfoList.elementAt(i);
+			if (aInfo.isDistinct())
+			{
+				DataValueDescriptor newValue = currAggregate.getInputColumnValue(newRow);
+				// A NULL value is always distinct, so we only
+				// have to check for duplicate values for
+				// non-NULL values.
+				if (newValue.getString() != null)
+				{
+					if (distinctValues[level][i].contains(
+						    newValue.getString()))
+						continue;
+					distinctValues[level][i].add(
+						newValue.getString());
+				}
+			}
 
 			// merge the aggregator
 			currAggregate.merge(newRow, currRow);
 		}
 	}
 
+	private void initializeDistinctMaps(int r, boolean allocate)
+	    throws StandardException
+	{
+		for (int a = 0; a < aggregates.length; a++)
+		{
+			AggregatorInfo aInfo = (AggregatorInfo)
+						aggInfoList.elementAt(a);
+			if (aInfo.isDistinct())
+			{
+				if (allocate)
+					distinctValues[r][a] = new HashSet();
+				else
+					distinctValues[r][a].clear();
+				DataValueDescriptor newValue =
+					aggregates[a].getInputColumnValue(resultRows[r]);
+				distinctValues[r][a].add(newValue.getString());
+			}
+		}
+	}
+
+        private void dumpAllRows(int cR)
+            throws StandardException
+        {
+            System.out.println("dumpAllRows("+cR+"/"+resultRows.length+"):");
+            for (int r = 0; r < resultRows.length; r++)
+                System.out.println(dumpRow(resultRows[r]));
+        }
+	private String dumpRow(ExecRow r)
+		throws StandardException
+	{
+            if (r == null)
+                return "<NULL ROW>";
+	    StringBuffer buf = new StringBuffer();
+	    int nCols = r.nColumns();
+	    for (int d = 0; d < nCols; d++)
+	    {
+		if (d > 0) buf.append(",");
+                DataValueDescriptor o = r.getColumn(d+1);
+                buf.append(o.getString());
+                if (o instanceof ExecAggregator)
+                    buf.append("[").
+                        append(((ExecAggregator)o).getResult().getString()).
+                        append("]");
+	    }
+	    return buf.toString();
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/OrderableAggregator.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/OrderableAggregator.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/OrderableAggregator.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/OrderableAggregator.java Tue Oct 13 22:58:19 2009
@@ -76,6 +76,16 @@
 	{
 		return value;
 	}
+        public String toString()
+        {
+            try {
+            return "OrderableAggregator: " + value.getString();
+            }
+            catch (StandardException e)
+            {
+                return super.toString() + ":" + e.getMessage();
+            }
+        }
 
 	/////////////////////////////////////////////////////////////
 	// 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SumAggregator.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SumAggregator.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SumAggregator.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SumAggregator.java Tue Oct 13 22:58:19 2009
@@ -92,4 +92,14 @@
 	 *	@return	the formatID of this class
 	 */
 	public	int	getTypeFormatId()	{ return StoredFormatIds.AGG_SUM_V01_ID; }
+        public String toString()
+        {
+            try {
+            return "SumAggregator: " + value.getString();
+            }
+            catch (StandardException e)
+            {
+                return super.toString() + ":" + e.getMessage();
+            }
+        }
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SystemAggregator.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SystemAggregator.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SystemAggregator.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SystemAggregator.java Tue Oct 13 22:58:19 2009
@@ -73,4 +73,15 @@
 	{
 		eliminatedNulls = in.readBoolean();
 	}
+        public String toString()
+        {
+            try
+            {
+            return super.toString() + "[" + getResult().getString() + "]";
+            }
+            catch (Exception e)
+            {
+                return e.getMessage();
+            }
+        }
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OLAPTest.java Tue Oct 13 22:58:19 2009
@@ -23,6 +23,7 @@
 
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.PreparedStatement;
 import java.sql.Statement;
 
 import junit.framework.Test;
@@ -331,6 +332,558 @@
 		s.close();
 	}
 
+    private String makeString(int len)
+    {
+        StringBuffer buf = new StringBuffer(len);
+        for (int i = 0; i < len; i++)
+            buf.append('a');
+        return buf.toString();
+    }
+        /**
+          * Basic test of GROUP BY ROLLUP capability.
+          *
+          * This test case has a few basic tests of GROUP BY ROLLUP, both
+          * positive and negative tests.
+          */
+    public void testGroupByRollup()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate("create table ru (a int, b int, c int, d int)");
+        s.executeUpdate("insert into ru values (1,1,1,1), (1,2,3,4),"+
+                "(1,1,2,2), (4,3,2,1), (4,4,4,4)");
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+                    "select a,b,c,sum(d) from ru group by rollup(a,b,c)"),
+                new String[][]{
+                    {"1","1","1","1"},
+                    {"1","1","2","2"},
+                    {"1","2","3","4"},
+                    {"4","3","2","1"},
+                    {"4","4","4","4"},
+                    {"1","1",null,"3"},
+                    {"1","2",null,"4"},
+                    {"4","3",null,"1"},
+                    {"4","4",null,"4"},
+                    {"1",null,null,"7"},
+                    {"4",null,null,"5"},
+                    {null,null,null,"12"}});
+        JDBC.assertFullResultSet( s.executeQuery(
+                "select count(*) from ru group by mod(a,b)"),
+                new String[][]{ {"3"},{"2"}});
+
+        // Try a few negative tests:
+        assertStatementError("42X04", s,
+                "select a,b,c,sum(d) from ru group by rollup");
+        assertStatementError("42X01", s,
+                "select a,b,c,sum(d) from ru group by rollup(");
+        assertStatementError("42X01", s,
+                "select a,b,c,sum(d) from ru group by rollup)");
+        assertStatementError("42X01", s,
+                "select a,b,c,sum(d) from ru group by rollup()");
+
+        s.executeUpdate("drop table ru");
+        s.close();
+    }
+    /**
+      * Verify that ROLLUP can still be used as the name of a column or table.
+      */
+    public void testRollupReservedWord()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate("create table t_roll(rollup int, x int)");
+        JDBC.assertEmpty( s.executeQuery(
+                    "select rollup, sum(x) from t_roll group by rollup"));
+        JDBC.assertEmpty( s.executeQuery(
+                    "select count(*) from t_roll group by mod(rollup,x)"));
+        JDBC.assertEmpty( s.executeQuery(
+                    "select count(*) from t_roll group by mod(x,rollup)"));
+        s.executeUpdate("create table rollup(a int, x int)");
+        JDBC.assertEmpty( s.executeQuery("select a, x from rollup"));
+        s.executeUpdate("insert into rollup(a,x) values(1,2)");
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+                    "select a,sum(x) from rollup group by rollup(a)"),
+                new String[][]{
+                    {"1","2"}, {null,"2"}});
+        s.executeUpdate("drop table rollup");
+        s.executeUpdate("drop table t_roll");
+        s.close();
+    }
+    /**
+      * Verify that non-aggregate columns are returned as NULLABLE if ROLLUP.
+      *
+      * If a GROUP BY ROLLUP is used, the un-aggregated columns may contain
+      * NULL values, so we need to verify that the DatabaseMetadata returns
+      * the right values for the nullability of the columns.
+      */
+    public void testRollupColumnNullability()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate(
+                "create table t_notnull(a int not null, b int," +
+                "                       c int not null, d int)");
+        ResultSet rs = s.executeQuery(
+                "select a,b,c,sum(d) from t_notnull group by rollup(a,b,c)");
+        JDBC.assertNullability(rs,
+                new boolean[]{true, true, true, true});
+        rs.close();
+
+        rs = s.executeQuery(
+                "select 1,2,3,sum(d) from t_notnull group by rollup(1,2,3)");
+        JDBC.assertNullability(rs,
+                new boolean[]{true, true, true, true});
+        rs.close();
+
+        s.executeUpdate("drop table t_notnull");
+        s.close();
+    }
+
+    /**
+      * Verify the behavior of GROUP BY ROLLUP for empty result sets.
+      */
+    public void testRollupEmptyTables()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate("create table ru (a int, b int, c int, d int)");
+
+        JDBC.assertEmpty( s.executeQuery("select sum(a) from ru group by b"));
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("select sum(a) from ru"), (String)null);
+        s.executeUpdate("insert into ru values (1,1,1,1), (1,2,3,4),"+
+                "(1,1,2,2), (4,3,2,1), (4,4,4,4)");
+        JDBC.assertEmpty( s.executeQuery(
+                    "select b, sum(a) from ru where 1<>1 group by rollup(b)"));
+
+        s.executeUpdate("drop table ru");
+        s.close();
+    }
+
+    /**
+      * A ROLLUP case suggested by Dag in 1-sep-2009 comment on DERBY-3002
+      */
+    public void testRollupNullabilityCasts()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate("create table t(c varchar(2) not null," +
+                "c2 varchar(2), i integer)");
+        s.executeUpdate("insert into t values('aa',null,null)");
+        s.executeUpdate("insert into t values('bb',null,null)");
+        String [][]rows = 
+                new String[][]{
+                    {"aa",null,null},
+                    {"aa",null,null},
+                    {null,null,null},
+                    {"bb",null,null},
+                    {"bb",null,null}};
+        JDBC.assertUnorderedResultSet(
+            s.executeQuery("select c,c2,sum(i) from t group by rollup(c,c2)"),
+            rows);
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                "select cast(c as varchar(2)),c2,sum(i) from t " +
+                "group by rollup(c,c2)"),
+            rows);
+        /* FIXME -- this test currently fails due to improper handling of
+           the nullability of the result columns.
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                "select cast(x as varchar(2)),y,z from " +
+                " (select c,c2,sum(i) from t " +
+                "      group by rollup (c,c2)) t(x,y,z)"),
+            rows);
+            */
+
+        s.executeUpdate("drop table t");
+        s.close();
+    }
+
+    /**
+      * Verify the behavior of GROUP BY ROLLUP when it can use a covering index.
+      */
+    public void testRollupOfCoveringIndex()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate("create table ru (a int,b int,c int,d varchar(1000))");
+        s.executeUpdate("create index ru_idx on ru(a,b,c)");
+        PreparedStatement ps = prepareStatement(
+                "insert into ru (a,b,c,d) values (?,?,?,?)");
+        for (int i = 0; i < 100; i++)
+        {
+            ps.setInt(1, (i%5));
+            ps.setInt(2, 2*i);
+            ps.setInt(3, 100+i);
+            ps.setString(4, makeString(900));
+            ps.executeUpdate();
+        }
+        ps.close();
+        // FIXME
+        //dumpIt(s, 2, "select a,sum(c) from ru group by a");
+        //dumpIt(s, 3, "select a,b,sum(c) from ru group by a,b");
+        //dumpIt(s, 3, "select a,b,sum(c) from ru group by rollup(a,b)");
+        s.executeUpdate("drop table ru");
+        s.close();
+    }
+    private void dumpIt(Statement s, int cols, String sql)
+        throws SQLException
+    {
+        System.out.println(sql);
+        ResultSet rs = s.executeQuery(sql);
+        while (rs.next())
+        {
+            StringBuffer buf = new StringBuffer();
+            for (int i = 1; i <= cols; i++)
+            {
+                if (i > 1)
+                    buf.append(",");
+                buf.append(rs.getString(i));
+            }
+            System.out.println(buf.toString());
+        }
+        rs.close();
+    }
+    /*
+     * Various GROUP BY tests, with and without ROLLUP.
+     */
+    public void testGroupByWithAndWithoutRollup()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        // A very simple set of master-detail ORDER and ORDER_ITEM tables,
+        // with some fake customer data:
+        s.executeUpdate(
+                "create table orders(order_id int primary key," +
+                "   customer varchar(10)," +
+                "   order_date date, " +
+                "   shipping int)");
+        s.executeUpdate(
+                "create table order_items(item_id int primary key," +
+                "   order_id int," +
+                "   order_item varchar(10), " +
+                "   cost int)");
+        s.executeUpdate(
+                "create table customers(customer varchar(10) primary key," +
+                "   name varchar(100), city varchar(100), state varchar(2))");
+        s.executeUpdate("insert into customers values " +
+                "('ABC','ABC Corporation','ABC City', 'AB')," +
+                "('DEF','DEF, Inc.', 'DEFburg', 'DE')");
+        s.executeUpdate("insert into orders values(1,'ABC','2009-01-01',40)");
+        s.executeUpdate("insert into orders values(2,'ABC','2009-01-02',30)");
+        s.executeUpdate("insert into orders values(3,'ABC','2009-01-03',25)");
+        s.executeUpdate("insert into orders values(4,'DEF','2009-01-02',10)");
+        s.executeUpdate("insert into order_items values(1,1,'Item A',100)");
+        s.executeUpdate("insert into order_items values(2,1,'Item B',150)");
+        s.executeUpdate("insert into order_items values(3,2,'Item C',125)");
+        s.executeUpdate("insert into order_items values(4,2,'Item B',50)");
+        s.executeUpdate("insert into order_items values(5,2,'Item H',200)");
+        s.executeUpdate("insert into order_items values(6,3,'Item X',100)");
+        s.executeUpdate("insert into order_items values(7,4,'Item Y',50)");
+        s.executeUpdate("insert into order_items values(8,4,'Item Z',300)");
+        // Joining the two tables produces one row per order item:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.order_id, o.customer, o.order_date, " +
+            "o.shipping, od.item_id, od.order_item, od.cost " +
+            " from orders o inner join order_items od " +
+            " on o.order_id = od.order_id"),
+            new String[][]{
+                    {"1","ABC","2009-01-01","40","1","Item A","100"},
+                    {"1","ABC","2009-01-01","40","2","Item B","150"},
+                    {"2","ABC","2009-01-02","30","3","Item C","125"},
+                    {"2","ABC","2009-01-02","30","4","Item B","50"},
+                    {"2","ABC","2009-01-02","30","5","Item H","200"},
+                    {"3","ABC","2009-01-03","25","6","Item X","100"},
+                    {"4","DEF","2009-01-02","10","7","Item Y","50"},
+                    {"4","DEF","2009-01-02","10","8","Item Z","300"},
+                });
+        // Grouping the items by customer to compute items/customer:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.Customer, count(*) as items_per_customer " +
+            " from orders o inner join order_items od " +
+            "      on o.order_id = od.order_id " +
+            " group by o.customer"),
+            new String[][]{
+                {"ABC","6"},
+                {"DEF","2"},
+            });
+        // Also include the total cost per customer:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.Customer, count(*) as items_per_customer, " +
+            "       sum(od.cost) as order_total " +
+            " from orders o inner join order_items od " +
+            "      on o.order_id = od.order_id " +
+            " group by o.customer"),
+            new String[][]{
+                {"ABC","6","725"},
+                {"DEF","2","350"},
+            });
+        // ROLLUP the items and costs to grand totals:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.Customer, count(*) as items_per_customer, " +
+            "       sum(od.cost) as order_total " +
+            " from orders o inner join order_items od " +
+            "      on o.order_id = od.order_id " +
+            " group by ROLLUP(o.customer)"),
+            new String[][]{
+                {"ABC","6","725"},
+                {"DEF","2","350"},
+                {null,"8","1075"},
+            });
+        // Show a usage of Count(distinct) to compute the orders/customer,
+        // which is not the same as the items/customer:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.Customer, count(*) as items_per_customer, " +
+            "       count(distinct o.order_id) as orders_per_customer " +
+            " from orders o inner join order_items od " +
+            "      on o.order_id = od.order_id " +
+            " group by o.customer"),
+            new String[][]{
+                {"ABC","6","3"},
+                {"DEF","2","1"},
+            });
+        // ROLLUP should work for the distinct count, too:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.Customer, count(*) as items_per_customer, " +
+            "       count(distinct o.order_id) as orders_per_customer " +
+            " from orders o inner join order_items od " +
+            "      on o.order_id = od.order_id " +
+            " group by ROLLUP(o.customer)"),
+            new String[][]{
+                {"ABC","6","3"},
+                {"DEF","2","1"},
+                {null,"8","4"},
+            });
+        // can we compute the total shipping per customer:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.Customer, count(*) as items_per_customer, " +
+            "       sum(od.cost) as order_total, " +
+            "       count(distinct o.order_id) as orders_per_customer, " +
+            "       sum(o.shipping) as shipping_per_customer " +
+            " from orders o inner join order_items od " +
+            "      on o.order_id = od.order_id " +
+            " group by o.customer"),
+            new String[][]{
+                {"ABC","6","725","3","195"},
+                {"DEF","2","350","1","20"},
+            });
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.Customer, count(*) as items_per_customer, " +
+            "       sum(od.cost) as order_total, " +
+            "       count(distinct o.order_id) as orders_per_customer, " +
+            "       sum(o.shipping) as shipping_per_customer " +
+            " from orders o inner join order_items od " +
+            "      on o.order_id = od.order_id " +
+            " group by ROLLUP(o.customer)"),
+            new String[][]{
+                {"ABC","6","725","3","195"},
+                {"DEF","2","350","1","20"},
+                {null,"8","1075","4","215"},
+            });
+        // Show a usage of disinct shipping aggregate, similar to the
+        // distinct count aggregate:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.Customer, count(*) as items_per_customer, " +
+            "       sum(od.cost) as order_total, " +
+            "       count(distinct o.order_id) as orders_per_customer, " +
+            "       sum(distinct o.shipping) as shipping_per_customer " +
+            " from orders o inner join order_items od " +
+            "      on o.order_id = od.order_id " +
+            " group by o.customer"),
+            new String[][]{
+                {"ABC","6","725","3","95"},
+                {"DEF","2","350","1","10"},
+            });
+        // Demonstrate some of the dangers of using distinct aggregates.
+        // Duplicate SUM values may be real duplicates from the data, not
+        // from duplicate-producing master-detail joins. The COUNT changes
+        // from 1 to 2 for customer DEF, but the shipping_per_customer is
+        // still 10, which is logically wrong (there are 2 DEF orders, each
+        // with value 10, so we "expected" 20 for shipping_per_customer).
+        s.executeUpdate("insert into orders values(5,'DEF','2009-01-04',10)");
+        s.executeUpdate("insert into order_items values(9,5,'Item J',125)");
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.Customer, count(*) as items_per_customer, " +
+            "       sum(od.cost) as order_total, " +
+            "       count(distinct o.order_id) as orders_per_customer, " +
+            "       sum(distinct o.shipping) as shipping_per_customer " +
+            " from orders o inner join order_items od " +
+            "      on o.order_id = od.order_id " +
+            " group by o.customer"),
+            new String[][]{
+                {"ABC","6","725","3","95"},
+                {"DEF","3","475","2","10"},
+            });
+        // Same as before, but with ROLLUP:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.Customer, count(*) as items_per_customer, " +
+            "       sum(od.cost) as order_total, " +
+            "       count(distinct o.order_id) as orders_per_customer, " +
+            "       sum(distinct o.shipping) as shipping_per_customer " +
+            " from orders o inner join order_items od " +
+            "      on o.order_id = od.order_id " +
+            " group by ROLLUP(o.customer)"),
+            new String[][]{
+                {"ABC","6","725","3","95"},
+                {"DEF","3","475","2","10"},
+                {null,"9","1200","5","105"},
+            });
+        // Produce the results we expected by constructing a sub-query:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select order_id, count(*) as Items_per_order, " +
+            "       sum(cost) as Order_total "+
+            " from order_items " +
+            " group by order_id"),
+            new String[][]{
+                {"1","2","250"},
+                {"2","3","375"},
+                {"3","1","100"},
+                {"4","2","350"},
+                {"5","1","125"},
+            });
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select order_id, count(*) as Items_per_order, " +
+            "       sum(cost) as Order_total "+
+            " from order_items " +
+            " group by ROLLUP(order_id)"),
+            new String[][]{
+                {"1","2","250"},
+                {"2","3","375"},
+                {"3","1","100"},
+                {"4","2","350"},
+                {"5","1","125"},
+                {null,"9","1200"},
+            });
+        // ... then encapsulate that sub-select with a join to the orders:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.order_id, o.Customer, o.Shipping, " +
+            "       d.items_per_order, d.order_total " +
+            " from orders o inner join (" +
+            "   select order_id, count(*) as Items_per_order, " +
+            "          sum(cost) as Order_total "+
+            "    from order_items " +
+            "    group by order_id " +
+            "   ) d on o.order_id = d.order_id"),
+            new String[][]{
+                {"1","ABC","40","2","250"},
+                {"2","ABC","30","3","375"},
+                {"3","ABC","25","1","100"},
+                {"4","DEF","10","2","350"},
+                {"5","DEF","10","1","125"},
+            });
+        // ... and group *THAT* join, in turn, by customer, to get the
+        // correct values of shipping_per_customer and items_per_customer.
+        // Note that total_per_customer is a SUM(SUM()), while 
+        // items_per_customer is a SUM(COUNT()). And no DISTINCT needed.
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.customer, count(*) as orders_per_customer, " +
+            "       sum(o.shipping) as shipping_per_customer, " +
+            "       sum(d.items_per_order) as items_per_customer, " +
+            "       sum(d.order_total) as total_per_customer " +
+            " from orders o inner join (" +
+            "   select order_id, count(*) as Items_per_order, " +
+            "          sum(cost) as Order_total "+
+            "    from order_items " +
+            "    group by order_id " +
+            "   ) d on o.order_id = d.order_id " +
+            " group by o.customer"),
+            new String[][]{
+                {"ABC","3","95","6","725"},
+                {"DEF","2","20","3","475"},
+            });
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.customer, count(*) as orders_per_customer, " +
+            "       sum(o.shipping) as shipping_per_customer, " +
+            "       sum(d.items_per_order) as items_per_customer, " +
+            "       sum(d.order_total) as total_per_customer " +
+            " from orders o inner join (" +
+            "   select order_id, count(*) as Items_per_order, " +
+            "          sum(cost) as Order_total "+
+            "    from order_items " +
+            "    group by order_id " +
+            "   ) d on o.order_id = d.order_id " +
+            " group by ROLLUP(o.customer)"),
+            new String[][]{
+                {"ABC","3","95","6","725"},
+                {"DEF","2","20","3","475"},
+                {null,"5","115","9","1200"},
+            });
+        // Include customer address information. First by joining and grouping:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select o.customer, c.name, c.city, c.state, " +
+            "       count(*) as orders_per_customer, " +
+            "       sum(o.shipping) as shipping_per_customer, " +
+            "       sum(d.items_per_order) as items_per_customer, " +
+            "       sum(d.order_total) as total_per_customer " +
+            " from orders o inner join (" +
+            "   select order_id, count(*) as Items_per_order, " +
+            "          sum(cost) as Order_total "+
+            "    from order_items " +
+            "    group by order_id " +
+            "   ) d on o.order_id = d.order_id " +
+            "   inner join customers c on o.customer = c.customer " +
+            " group by ROLLUP(o.customer,c.name, c.city,c.state)"),
+            new String[][]{
+                {"ABC","ABC Corporation","ABC City","AB","3","95","6","725"},
+                {"DEF","DEF, Inc.","DEFburg","DE","2","20","3","475"},
+                {"ABC","ABC Corporation","ABC City",null,"3","95","6","725"},
+                {"DEF","DEF, Inc.","DEFburg",null,"2","20","3","475"},
+                {"ABC","ABC Corporation",null,null,"3","95","6","725"},
+                {"DEF","DEF, Inc.",null,null,"2","20","3","475"},
+                {"ABC",null,null,null,"3","95","6","725"},
+                {"DEF",null,null,null,"2","20","3","475"},
+                {null,null,null,null,"5","115","9","1200"},
+            });
+        // Then, alternately, by sub-selecting and grouping:
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select c.customer, c.name, c.city, c.state, " +
+            "       o.orders_per_customer, o.shipping_per_customer, " +
+            "       o.items_per_customer, o.total_per_customer " +
+            " from ( " +
+            "   select o.customer, count(*) as orders_per_customer, " +
+            "          sum(o.shipping) as shipping_per_customer, " +
+            "          sum(d.items_per_order) as items_per_customer, " +
+            "          sum(d.order_total) as total_per_customer " +
+            "    from orders o inner join (" +
+            "      select order_id, count(*) as Items_per_order, " +
+            "             sum(cost) as Order_total "+
+            "       from order_items " +
+            "       group by order_id " +
+            "      ) d on o.order_id = d.order_id " +
+            "    group by o.customer) o " +
+            "  inner join customers c on o.customer = c.customer"),
+            new String[][]{
+                {"ABC","ABC Corporation","ABC City","AB","3","95","6","725"},
+                {"DEF","DEF, Inc.","DEFburg","DE","2","20","3","475"},
+            });
+        // Note that we can put the ROLLUP in the sub-query, but then we
+        // need to outer-join with the customers table since the rollup
+        // results will have NULL in the join key.
+        JDBC.assertUnorderedResultSet( s.executeQuery(
+            "select c.customer, c.name, c.city, c.state, " +
+            "       o.orders_per_customer, o.shipping_per_customer, " +
+            "       o.items_per_customer, o.total_per_customer " +
+            " from ( " +
+            "   select o.customer, count(*) as orders_per_customer, " +
+            "          sum(o.shipping) as shipping_per_customer, " +
+            "          sum(d.items_per_order) as items_per_customer, " +
+            "          sum(d.order_total) as total_per_customer " +
+            "    from orders o inner join (" +
+            "      select order_id, count(*) as Items_per_order, " +
+            "             sum(cost) as Order_total "+
+            "       from order_items " +
+            "       group by order_id " +
+            "      ) d on o.order_id = d.order_id " +
+            "    group by ROLLUP(o.customer)) o " +
+            "  left outer join customers c on o.customer = c.customer"),
+            new String[][]{
+                {"ABC","ABC Corporation","ABC City","AB","3","95","6","725"},
+                {"DEF","DEF, Inc.","DEFburg","DE","2","20","3","475"},
+                {null,null,null,null,"5","115","9","1200"},
+            });
+
+        s.close();
+    }
+
 	public static Test suite() {
 		return TestConfiguration.defaultSuite(OLAPTest.class);
 	}

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java?rev=824966&r1=824965&r2=824966&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java Tue Oct 13 22:58:19 2009
@@ -1045,7 +1045,7 @@
                     "on srt.sort_rs_id = rs.sort_rs_id " +
                     "where rs.op_identifier='GROUPBY'"),
                 new String[][] {
-                    {"IN","10","7",null, null, null,"N","Y"} } );
+                    {"IN","10","10",null, null, null,"N","Y"} } );
         verifySensibleStatementTimings(s);
         verifySensibleResultSetTimings(s);
     }



Mime
View raw message