db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r516454 [1/2] - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/d...
Date Fri, 09 Mar 2007 16:37:22 GMT
Author: abrown
Date: Fri Mar  9 08:37:20 2007
New Revision: 516454

URL: http://svn.apache.org/viewvc?view=rev&rev=516454
Log:
DERBY-681: Remove the "wrap group by's in a subselect" rewrite in the parser.
This patch preserves the having clause through bind and optimize phases and
then, during the final rewrite for aggregates in the GroupByNode, it transforms
the having clause to a valid restriction. See text file attached to the Jira
for more information.

Contributed by Manish Khettry (manish_khettry@yahoo.com)

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.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/InsertNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceAggregatesWithCRVisitor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.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/SetOperatorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregateOptimization.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/views.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java Fri Mar  9 08:37:20 2007
@@ -24,7 +24,6 @@
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
 import org.apache.derby.iapi.sql.compile.NodeFactory;
 
-import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.types.DataTypeDescriptor;
 
 import org.apache.derby.iapi.error.StandardException;

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java Fri Mar  9 08:37:20 2007
@@ -280,7 +280,7 @@
 			
 			if (resultColumns != null)
 			{
-				if (resultColumns.size() != qeRCL.size())
+				if (resultColumns.size() != qeRCL.visibleSize())
 				{
 					throw StandardException.newException(
 							SQLState.LANG_TABLE_DEFINITION_R_C_L_MISMATCH,
@@ -295,6 +295,10 @@
 			for (int index = 0; index < qeRCL.size(); index++)
 			{
 				ResultColumn rc = (ResultColumn) qeRCL.elementAt(index);
+				if (rc.isGenerated()) 
+			        {
+				    continue;
+				}
 				/* Raise error if column name is system generated. */
 				if (rc.isNameGenerated())
 				{

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java Fri Mar  9 08:37:20 2007
@@ -193,7 +193,7 @@
 		 */
 		if (resultColumns != null)
 		{
-			if (resultColumns.size() != qeRCL.size())
+			if (resultColumns.size() != qeRCL.visibleSize())
 			{
 				throw StandardException.newException(SQLState.LANG_VIEW_DEFINITION_R_C_L_MISMATCH,
 								getFullName());

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java Fri Mar  9 08:37:20 2007
@@ -783,6 +783,7 @@
                                                      fromList, /* FROM list */
                                                      whereClause, /* WHERE clause */
                                                      null, /* GROUP BY list */
+                                                     null, /* having clause */
                                                      getContextManager());
 
         return (StatementNode) nodeFactory.getNode(
@@ -832,6 +833,7 @@
                                                      fromList, /* FROM list */
                                                      whereClause, /* WHERE clause */
                                                      null, /* GROUP BY list */
+						     null, /* having clause */
                                                      getContextManager());
 
         return (StatementNode) nodeFactory.getNode(

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Fri Mar  9 08:37:20 2007
@@ -52,6 +52,8 @@
 import org.apache.derby.iapi.sql.compile.JoinStrategy;
 import org.apache.derby.iapi.sql.compile.RowOrdering;
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+import org.apache.derby.iapi.sql.compile.Visitable;
+import org.apache.derby.iapi.sql.compile.Visitor;
 
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor;
@@ -4545,5 +4547,45 @@
 		this.raDependentScan = dependentScan;
 	}
 
+	/**
+	 * Accept a visitor, and call v.visit()
+	 * on child nodes as necessary.  
+	 * 
+	 * @param v the visitor
+	 *
+	 * @exception StandardException on error
+	 */
+	public Visitable accept(Visitor v) 
+	
+		throws StandardException
+	{
+
+	        Visitable returnNode = super.accept(v);
+
+		if (v.skipChildren(this))
+		{
+			return returnNode;
+		}
+
+
+
+		if (nonStoreRestrictionList != null && !v.stopTraversal()) {
+			nonStoreRestrictionList.accept(v);
+		}
+		
+		if (restrictionList != null & !v.stopTraversal()) {
+			restrictionList.accept(v);
+		}
+
+		if (nonBaseTableRestrictionList != null && !v.stopTraversal()) {
+			nonBaseTableRestrictionList.accept(v);
+		}
+
+		if (requalificationRestrictionList != null && !v.stopTraversal()) {
+			requalificationRestrictionList.accept(v);
+		}
+		
+		return returnNode;
+	}
 
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java Fri Mar  9 08:37:20 2007
@@ -404,7 +404,7 @@
 		}
 		else if (columnsTableName == null || columnsTableName.equals(correlationName)) // 5?
 		{
-		    resultColumn = resultColumns.getAtMostOneResultColumn(columnReference, correlationName);
+		    resultColumn = resultColumns.getAtMostOneResultColumn(columnReference, correlationName, false);
 		}
 		    
 

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?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- 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 Fri Mar  9 08:37:20 2007
@@ -197,6 +197,12 @@
 				* has a matching entry in the SELECT list.
 				*/
 				groupingCol.setColumnPosition(selectRCL.size());
+				
+				// a new hidden or generated column is added to this RCL
+				// i.e. that the size() of the RCL != visibleSize(). 
+				// Error checking done later should be aware of this 
+				// special case.
+				selectRCL.setCountMismatchAllowed(true);
 
 				/*
 				** Track the number of columns that we have added

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?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- 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 Fri Mar  9 08:37:20 2007
@@ -41,6 +41,15 @@
 import org.apache.derby.iapi.sql.compile.Optimizer;
 import org.apache.derby.iapi.sql.compile.RequiredRowOrdering;
 import org.apache.derby.iapi.sql.compile.RowOrdering;
+
+import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+
+import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
+import org.apache.derby.iapi.reference.ClassName;
+import org.apache.derby.iapi.reference.SQLState;
+
+import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+
 import org.apache.derby.iapi.sql.dictionary.ConglomerateDescriptor;
 import org.apache.derby.iapi.sql.dictionary.DataDictionary;
 import org.apache.derby.iapi.sql.execute.ExecutionContext;
@@ -49,6 +58,12 @@
 import org.apache.derby.impl.sql.execute.AggregatorInfoList;
 
 
+
+import org.apache.derby.impl.sql.compile.MaxMinAggregateDefinition;
+import java.util.Iterator;
+import java.util.Vector;
+
+
 /**
  * A GroupByNode represents a result set for a grouping operation
  * on a select.  Note that this includes a SELECT with aggregates
@@ -103,6 +118,10 @@
 	// Is the source in sorted order
 	private boolean isInSortedOrder;
 
+	private ValueNode havingClause;
+	
+	private SubqueryList havingSubquerys;
+	
 	/**
 	 * Intializer for a GroupByNode.
 	 *
@@ -112,24 +131,34 @@
 	 *		the query block.  Since aggregation is done
 	 *		at the same time as grouping, we need them
 	 *		here.
+	 * @param havingClause The having clause.
+	 * @param havingSubquerys subqueries in the having clause.
 	 * @param tableProperties	Properties list associated with the table
-	 *
+	 * @param nestingLevel nestingLevel of this group by node. This is used for 
+	 *     error checking of group by queries with having clause.
 	 * @exception StandardException		Thrown on error
 	 */
 	public void init(
-						Object  bottomPR,
+						Object bottomPR,
 						Object groupingList,
-						Object	aggregateVector,
-						Object tableProperties)
+						Object aggregateVector,
+						Object havingClause,
+						Object havingSubquerys,
+						Object tableProperties,
+						Object nestingLevel)
 			throws StandardException
 	{
 		super.init(bottomPR, tableProperties);
-
+		setLevel(((Integer)nestingLevel).intValue());
+		this.havingClause = (ValueNode)havingClause;
+		this.havingSubquerys = (SubqueryList)havingSubquerys;
 		/* Group by without aggregates gets xformed into distinct */
 		if (SanityManager.DEBUG)
 		{
-			SanityManager.ASSERT(((Vector) aggregateVector).size() > 0,
-				"aggregateVector expected to be non-empty");
+//			Aggregage vector can be null if we have a having clause.
+//          select c1 from t1 group by c1 having c1 > 1;			
+//			SanityManager.ASSERT(((Vector) aggregateVector).size() > 0,
+//			"aggregateVector expected to be non-empty");
 			if (!(childResult instanceof Optimizable))
 			{
 				SanityManager.THROWASSERT("childResult, " + childResult.getClass().getName() +
@@ -271,14 +300,31 @@
 		/*
 		** Get the new PR, put above the GroupBy.  
 		*/
+		ResultColumnList rclNew = (ResultColumnList)getNodeFactory().getNode(
+				                                                 C_NodeTypes.RESULT_COLUMN_LIST,
+				                                                 getContextManager());
+		int sz = resultColumns.size();
+		for (int i = 0; i < sz; i++) 
+		{
+			ResultColumn rc = (ResultColumn) resultColumns.elementAt(i);
+			if (!rc.isGenerated()) {
+				rclNew.addElement(rc);
+			}
+		}
+
+		// if any columns in the source RCL were generated for an order by
+		// remember it in the new RCL as well. After the sort is done it will
+		// have to be projected out upstream.
+		rclNew.copyOrderBySelect(resultColumns);
+		
 		parent = (FromTable) getNodeFactory().getNode(
 										C_NodeTypes.PROJECT_RESTRICT_NODE,
 										this, 	// child
-										resultColumns,		// result column list
-										null,				// restriction
+										rclNew,
+										null, //havingClause,
 										null,				// restriction list
 										null,				// project subqueries
-										null,				// restrict subqueries
+										havingSubquerys,
 										tableProperties,
 										getContextManager());
 
@@ -359,8 +405,36 @@
 						vc,
 						AggregateNode.class);
 			parent.getResultColumns().accept(se);
-
-			// finally reset gbc to its new position.
+			
+			// Since we always need a PR node on top of the GB 
+			// node to perform projection we can use it to perform 
+			// the having clause restriction as well. 
+			// To evaluate the having clause correctly, we need to 
+			// convert each aggregate and expression to point 
+			// to the appropriate result column in the group by node. 
+			// This is no different from the transformations we do to 
+			// correctly evaluate aggregates and expressions in the 
+			// projection list. 
+			// 
+			//
+			// For this query:
+			// SELECT c1, SUM(c2), MAX(c3)
+			//    FROM t1 
+			//    HAVING c1+max(c3) > 0;
+
+			// PRSN RCL -> (ptr(gbn:rcl[0]), ptr(gbn:rcl[1]), ptr(gbn:rcl[4]))
+			// Restriction: (> (+ ptr(gbn:rcl[0]) ptr(gbn:rcl[4])) 0)
+			//              |
+			// GBN (RCL) -> (C1, SUM(C2), <input>, <aggregator>, MAX(C3), <input>, <aggregator>
+			//              |
+			//       FBT (C1, C2)
+			if (havingClause != null) {
+				SubstituteExpressionVisitor havingSE =
+					new SubstituteExpressionVisitor(
+							gbc.getColumnExpression(),
+							vc, null);
+				havingClause.accept(havingSE);
+			}
 			gbc.setColumnPosition(bottomRCL.size());
 		}
 	}
@@ -368,8 +442,9 @@
 	/**
 	 * Add a whole slew of columns needed for 
 	 * aggregation. Basically, for each aggregate we add
-	 * 2 columns: the aggregate input expression
-	 * and the aggregator column.  The input expression is
+	 * 3 columns: the aggregate input expression
+	 * and the aggregator column and a column where the aggregate 
+	 * result is stored.  The input expression is
 	 * taken directly from the aggregator node.  The aggregator
 	 * is the run time aggregator.  We add it to the RC list
 	 * as a new object coming into the sort node.
@@ -389,7 +464,7 @@
 	 *	<LI> create a new RC in the GROUPBY RCL and set it to 
 	 *		 point to the bottom RC </LI>
 	 *	<LI> reset the top PR ref to point to the new GROUPBY
-	 *		 RC	
+	 *		 RC</LI></UL>	
 	 *
 	 * For each aggregate in aggregateVector <UL>
 	 *	<LI> create RC in FROM TABLE.  Fill it with 
@@ -400,8 +475,41 @@
 	 *		to point to FROM TABLE RC </LI>
 	 *	<LI> create RC in GROUPBY for agg result</LI>
 	 *	<LI> create RC in GROUPBY for aggregator</LI>
-	 *	<LI> replace Agg with reference to RC for agg result </LI>
-	 *
+	 *	<LI> replace Agg with reference to RC for agg result </LI></UL>.
+	 * <P>
+	 * For a query like,
+	 * <pre>
+	  select c1, sum(c2), max(c3)
+	  from t1 
+	  group by c1;
+	  </pre>
+	 * the query tree ends up looking like this:
+	   <pre>
+	    ProjectRestrictNode RCL -> (ptr to GBN(column[0]), ptr to GBN(column[1]), ptr to GBN(column[4]))
+	              |
+	    GroupByNode RCL->(C1, SUM(C2), <agg-input>, <aggregator>, MAX(C3), <agg-input>, <aggregator>)
+	              |
+	    ProjectRestrict RCL->(C1, C2, C3)
+	              |
+	    FromBaseTable
+	    </pre>
+	 * 
+	 * The RCL of the GroupByNode contains all the unagg (or grouping columns)
+	 * followed by 3 RC's for each aggregate in this order: the final computed
+	 * aggregate value, the aggregate input and the aggregator function.
+	 * <p>
+	 * The Aggregator function puts the results in the first of the 3 RC's 
+	 * and the PR resultset in turn picks up the value from there.
+	 * <p>
+	 * The notation (ptr to GBN(column[0])) basically means that it is
+	 * a pointer to the 0th RC in the RCL of the GroupByNode. 
+	 * <p>
+	 * The addition of these unagg and agg columns to the GroupByNode and 
+	 * to the PRN is performed in addUnAggColumns and addAggregateColumns. 
+	 * <p>
+	 * Note that that addition of the GroupByNode is done after the
+	 * query is optimized (in SelectNode#modifyAccessPaths) which means a 
+	 * fair amount of patching up is needed to account for generated group by columns.
 	 * @exception standard exception
 	 */
 	private void addNewColumnsForAggregation()
@@ -412,6 +520,32 @@
 		{
 			addUnAggColumns();
 		}
+		if (havingClause != null) {
+			// we have replaced group by expressions in the having clause.
+			// there should be no column references in the having clause 
+			// referencing this table. Skip over aggregate nodes.
+			//   select a, sum(b) from t group by a having a+c > 1 
+			//  is not valid because of column c.
+			// 
+			// it is allright to have columns from parent or child subqueries;
+			//   select * from p where p.p1 in 
+			//      (select c.c1 from c group by c.c1 having count(*) = p.p2
+			CollectNodesVisitor collectNodesVisitor = 
+				new CollectNodesVisitor(ColumnReference.class, AggregateNode.class);
+			havingClause.accept(collectNodesVisitor);
+			for (Iterator it = collectNodesVisitor.getList().iterator();
+			     it.hasNext(); ) 
+			{
+				ColumnReference cr = (ColumnReference)it.next();
+				
+				if (!cr.getGeneratedToReplaceAggregate() && 
+						cr.getSourceLevel() == level) {
+					throw StandardException.newException(
+							SQLState.LANG_INVALID_COL_HAVING_CLAUSE, 
+							cr.getSQLColumnName());						
+				}
+			}
+		}
 		addAggregateColumns();
 	}
 	
@@ -449,9 +583,27 @@
 					(ResultColumnList) getNodeFactory().getNode(
 							C_NodeTypes.RESULT_COLUMN_LIST,
 							getContextManager()),
-				((FromTable) childResult).getTableNumber());
+				((FromTable) childResult).getTableNumber(),
+				ResultSetNode.class);
 		parent.getResultColumns().accept(replaceAggsVisitor);
 
+		
+		if (havingClause != null) 
+		{
+			// replace aggregates in the having clause with column references.
+			replaceAggsVisitor = new ReplaceAggregatesWithCRVisitor(
+					(ResultColumnList) getNodeFactory().getNode(
+							C_NodeTypes.RESULT_COLUMN_LIST,
+							getContextManager()),					
+					((FromTable)childResult).getTableNumber());
+			havingClause.accept(replaceAggsVisitor);
+			// make having clause a restriction list in the parent 
+			// project restrict node.
+			ProjectRestrictNode parentPRSN = (ProjectRestrictNode)parent;
+			parentPRSN.setRestriction(havingClause);
+		}
+
+		
 		/*
 		** For each aggregate
 		*/

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java Fri Mar  9 08:37:20 2007
@@ -320,14 +320,14 @@
 		*/
 		if (targetColumnList != null)
 		{
-			if (resultSet.getResultColumns().size() > targetColumnList.size())
+			if (resultSet.getResultColumns().visibleSize() > targetColumnList.size())
 				throw StandardException.newException(SQLState.LANG_DB2_INVALID_COLS_SPECIFIED); 
 			resultSet.bindUntypedNullsToResultColumns(targetColumnList);
 			resultSet.setTableConstructorTypes(targetColumnList);
 		}
 		else
 		{
-			if (resultSet.getResultColumns().size() > resultColumnList.size())
+			if (resultSet.getResultColumns().visibleSize() > resultColumnList.size())
 				throw StandardException.newException(SQLState.LANG_DB2_INVALID_COLS_SPECIFIED); 
 			resultSet.bindUntypedNullsToResultColumns(resultColumnList);
 			resultSet.setTableConstructorTypes(resultColumnList);
@@ -336,7 +336,7 @@
 		/* Bind the columns of the result set to their expressions */
 		resultSet.bindResultColumns(fromList);
 
-		int resCols = resultSet.getResultColumns().size();
+		int resCols = resultSet.getResultColumns().visibleSize();
 		DataDictionary dd = getDataDictionary();
 		if (targetColumnList != null)
 		{
@@ -404,7 +404,7 @@
 			** table.
 			*/
 			for (int position = 0;
-				position < resultSet.getResultColumns().size();
+				position < resultSet.getResultColumns().visibleSize();
 				position++)
 			{
 				colMap[position] = position;

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java Fri Mar  9 08:37:20 2007
@@ -266,6 +266,7 @@
 		/* And ends with the non-ordering columns */
 		newRCL.destructiveAppend(resultColumns);
 		newRCL.resetVirtualColumnIds();
+		newRCL.copyOrderBySelect(resultColumns);
 		return newRCL;
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java Fri Mar  9 08:37:20 2007
@@ -1838,4 +1838,8 @@
 								   dependentScan);
 	}
 
+	public void setRestriction(ValueNode restriction) {
+		this.restriction = restriction;
+	}
+
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceAggregatesWithCRVisitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceAggregatesWithCRVisitor.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceAggregatesWithCRVisitor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ReplaceAggregatesWithCRVisitor.java Fri Mar  9 08:37:20 2007
@@ -47,8 +47,14 @@
 	 */
 	public ReplaceAggregatesWithCRVisitor(ResultColumnList rcl, int tableNumber)
 	{
+		this(rcl, tableNumber, null);
+	}
+
+	public ReplaceAggregatesWithCRVisitor(ResultColumnList rcl, int tableNumber, Class skipOverClass)
+	{
 		this.rcl = rcl;
 		this.tableNumber = tableNumber;
+		this.skipOverClass = skipOverClass;
 	}
 
 	/**

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java Fri Mar  9 08:37:20 2007
@@ -1434,6 +1434,10 @@
   			newResultColumn.setAutoincrement();
   		if (isGroupingColumn()) 
   			newResultColumn.markAsGroupingColumn();
+  		
+  		if (isGenerated()) {
+  			newResultColumn.markGenerated();
+  		}
   		return newResultColumn;
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java Fri Mar  9 08:37:20 2007
@@ -340,9 +340,12 @@
 	 *		  Only FromSubquery needs to call this flavor since
 	 *		  it can have ambiguous references in its own list.
 	 *
-	 * @param cr					The ColumnReference to resolve
-	 * @param exposedTableName		Exposed table name for FromTable
-	 *
+	 * @param cr					   The ColumnReference to resolve
+	 * @param exposedTableName		   Exposed table name for FromTable
+	 * @param considerGeneratedColumns Also consider columns that are generated.
+	 *   One example of this is group by where columns are added to the select list
+	 *   if they are referenced in the group by but are not present in the select
+	 *   list.
 	 * @return	the column that matches that name.
 	 *
 	 * @exception StandardException	Thrown on error
@@ -350,7 +353,8 @@
 
 	public ResultColumn getAtMostOneResultColumn(
 								ColumnReference cr,
-								String exposedTableName)
+								String exposedTableName,
+								boolean considerGeneratedColumns)
 		throws StandardException
 	{
 		int				size = size();
@@ -361,8 +365,11 @@
 		{
 			ResultColumn resultColumn = (ResultColumn) elementAt(index);
 
-			if (columnName.equals( resultColumn.getName()) )
+			if (columnName.equals( resultColumn.getName()))
 			{
+				if (resultColumn.isGenerated() && !considerGeneratedColumns) {
+					continue;
+				}
 				/* We should get at most 1 match */
 				if (retRC != null)
 				{
@@ -1570,7 +1577,7 @@
 	public void copyTypesAndLengthsToSource(ResultColumnList sourceRCL) throws StandardException
 	{
 		/* Source and target can have different lengths. */
-		int size = (size() > sourceRCL.size()) ? size() : sourceRCL.size();
+		int size = Math.min(size(), sourceRCL.size());
 		for (int index = 0; index < size; index++)
 		{
 			ResultColumn sourceRC = (ResultColumn) sourceRCL.elementAt(index);
@@ -1747,11 +1754,28 @@
 
 			newList.addResultColumn(newResultColumn);
 		}
-
+        newList.copyOrderBySelect(this);
 		return newList;
 	}
 
 	/**
+	 * Remove any columns that may have been added for an order by clause.
+	 * In a query like:
+	 * <pre>select a from t order by b</pre> b is added to the select list
+	 * However in the final projection, after the sort is complete, b will have
+	 * to be removed. 
+	 *
+	 */
+	public void removeOrderByColumns() 
+	{
+		int idx = size() - 1;
+		for (int i = 0; i < orderBySelect; i++, idx--) {
+			removeElementAt(idx);
+		}
+		orderBySelect = 0;
+	}
+	
+	/**
 	 * Walk the list and replace ResultColumn.expression with a new 
 	 * VirtualColumnNode.  This is useful when propagating a ResultColumnList
 	 * up the query tree.
@@ -1949,7 +1973,9 @@
 		if (derivedRCL.size() != size() &&
 		    ! derivedRCL.getCountMismatchAllowed())
 		{
-			throw StandardException.newException(SQLState.LANG_DERIVED_COLUMN_LIST_MISMATCH, tableName);
+			if (visibleSize() != derivedRCL.size()) {
+				throw StandardException.newException(SQLState.LANG_DERIVED_COLUMN_LIST_MISMATCH, tableName);
+			}
 		}
 
 		/* Check the uniqueness of the column names within the derived list */
@@ -2131,7 +2157,7 @@
 
 		if (SanityManager.DEBUG)
 		{
-			if (size() != otherRCL.size())
+			if (visibleSize() != otherRCL.visibleSize())
 			{
 				SanityManager.THROWASSERT(
 							"size() = (" +
@@ -3971,5 +3997,48 @@
 	 */
 	protected void markInitialSize() {
 		initialListSize = size();
+	}
+
+	/**
+	 * @return the number of generated columns in this RCL.
+	 */
+	int numGeneratedColumnsForGroupBy()
+	{
+		int numGenerated = 0;
+		int sz = size();
+		for (int i = sz - 1; i >= 0; i--) {
+			ResultColumn rc = (ResultColumn) elementAt(i);
+			if (rc.isGenerated() && rc.isGroupingColumn())
+			{
+				numGenerated++;
+			}
+		}
+		return numGenerated;
+	}
+	
+	/**
+	 * Remove any generated columns from this RCL.
+	 */
+	void removeGeneratedGroupingColumns()
+	{
+		int sz = size();
+		for (int i = sz - 1; i >= 0; i--) 
+		{
+			ResultColumn rc = (ResultColumn) elementAt(i);
+			if (rc.isGenerated() && rc.isGroupingColumn()) 
+			{
+				removeElementAt(i);
+			}
+		}
+	}
+	
+	/**
+	 * @return the number of columns that will be visible during execution. 
+	 * During compilation we can add columns for a group by/order by but these
+	 * to an RCL but these are projected out during query execution. 
+	 */
+	public int visibleSize() 
+	{
+		return size() - orderBySelect - numGeneratedColumnsForGroupBy(); 
 	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java Fri Mar  9 08:37:20 2007
@@ -314,7 +314,7 @@
 			throws StandardException
 	{
 		if (SanityManager.DEBUG)
-			SanityManager.ASSERT(resultColumns.size() <= typeColumns.size(),
+			SanityManager.ASSERT(resultColumns.visibleSize() <= typeColumns.size(),
 				"More columns in ResultColumnList than in base table");
 
 		/* Look for ? parameters in the result column list */
@@ -832,7 +832,6 @@
 		 * we won't be able to project out any of them.
 		 */
 		prRCList.genVirtualColumnNodes(this, resultColumns, false);
-
 		/* Finally, we create the new ProjectRestrictNode */
 		return (ResultSetNode) getNodeFactory().getNode(
 								C_NodeTypes.PROJECT_RESTRICT_NODE,
@@ -1444,12 +1443,16 @@
 		prRCList = resultColumns;
 		resultColumns = resultColumns.copyListAndObjects();
 
+		// Remove any columns that were generated.
+		prRCList.removeGeneratedGroupingColumns();
+
 		/* Replace ResultColumn.expression with new VirtualColumnNodes
 		 * in the NormalizeResultSetNode's ResultColumnList.  (VirtualColumnNodes include
 		 * pointers to source ResultSetNode, this, and source ResultColumn.)
 		 */
 		prRCList.genVirtualColumnNodes(this, resultColumns);
 
+		
 		/* Finally, we create the new NormalizeResultSetNode */
 		nrsn = (NormalizeResultSetNode) getNodeFactory().getNode(
 								C_NodeTypes.NORMALIZE_RESULT_SET_NODE,

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?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- 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 Fri Mar  9 08:37:20 2007
@@ -73,7 +73,7 @@
 	/* Aggregate Vectors for select and where clauses */
 	Vector	selectAggregates ;
 	Vector	whereAggregates;
-
+	Vector  havingAggregates;
 	/**
 	 * The ValueNode for the WHERE clause must represent a boolean
 	 * expression.  The binding phase will enforce this - the parser
@@ -88,6 +88,11 @@
 	 */
 	GroupByList	groupByList;
 
+	/** User specified a group by without aggregates and we turned 
+	 * it into a select distinct 
+	 */
+	private boolean wasGroupBy;
+	
 	/* List of columns in ORDER BY list */
 	OrderByList orderByList;
 	boolean		orderByQuery ;
@@ -95,9 +100,10 @@
 	/* PredicateLists for where clause */
 	PredicateList wherePredicates;
 
-	/* SubqueryLists for select and where clauses */
+	/* SubqueryLists for select where and having clauses */
 	SubqueryList  selectSubquerys;
 	SubqueryList  whereSubquerys;
+	SubqueryList  havingSubquerys;
 
 	/* Whether or not we are only binding the target list */
 	private boolean bindTargetListOnly;
@@ -112,24 +118,15 @@
 	/* Copy of fromList prior to generating join tree */
 	private FromList preJoinFL;
 
-	/**
-	 * Initializer for a SelectNode.
-	 *
-	 * @param selectList	The result column list for the SELECT statement
-	 * @param aggregateVector	The aggregate vector for this SELECT 
-	 * @param fromList	The FROM list for the SELECT statement
-	 * @param whereClause	An expression representing the WHERE clause.
-	 *			It must be a boolean expression, but this is
-	 *			not checked until binding.
-	 * @param groupByList	The GROUP BY list, if any.
-	 * @exception StandardException		Thrown on error
-	 */
-
+	ValueNode havingClause;
+	
+	private int nestingLevel;
 	public void init(Object selectList,
 			  Object aggregateVector,
 			  Object fromList,
 			  Object whereClause,
-			  Object groupByList)
+			  Object groupByList,
+			  Object havingClause)
 			throws StandardException
 	{
 		/* RESOLVE - remove aggregateList from constructor.
@@ -142,6 +139,7 @@
 		this.whereClause = (ValueNode) whereClause;
 		this.originalWhereClause = (ValueNode) whereClause;
 		this.groupByList = (GroupByList) groupByList;
+		this.havingClause = (ValueNode)havingClause;
 		bindTargetListOnly = false;
 	}
 
@@ -423,13 +421,8 @@
 						   FromList fromListParam) 
 					throws StandardException
 	{
-		int fromListParamSize = fromListParam.size();
 		int fromListSize = fromList.size();
-		int nestingLevel;
-		FromList fromListClone = (FromList) getNodeFactory().getNode(
-									C_NodeTypes.FROM_LIST,
-									getNodeFactory().doJoinOrderOptimization(),
-									getContextManager());
+		
 
 		wherePredicates = (PredicateList) getNodeFactory().getNode(
 											C_NodeTypes.PREDICATE_LIST,
@@ -567,6 +560,16 @@
 			getCompilerContext().popCurrentPrivType();
 		}
 
+		if (havingClause != null) {
+			havingAggregates = new Vector();
+			havingSubquerys = (SubqueryList) getNodeFactory().getNode(
+					C_NodeTypes.SUBQUERY_LIST,
+					getContextManager());
+			havingClause.bindExpression(
+					fromListParam, havingSubquerys, havingAggregates);
+			havingClause = havingClause.checkIsBoolean();
+		}
+		
 		/* Restore fromList */
 		for (int index = 0; index < fromListSize; index++)
 		{
@@ -964,6 +967,11 @@
 			groupByList.preprocess(numTables, fromList, whereSubquerys, wherePredicates);
 		}
 		
+		if (havingClause != null) {
+			havingClause = havingClause.preprocess(
+					numTables, fromList, havingSubquerys, wherePredicates);
+		}
+		
 		/* Pull apart the expression trees */
 		if (whereClause != null)
 		{
@@ -1008,16 +1016,18 @@
 			}
 		}
 
-		/* A valid group by without any aggregates is equivalent to 
-		 * a distinct without the group by.  We do the transformation
+		/* A valid group by without any aggregates or a having clause
+		 * is equivalent to a distinct without the group by.  We do the transformation
 		 * in order to simplify the group by code.
 		 */
 		if (groupByList != null &&
+			havingClause == null &&
 			selectAggregates.size() == 0 &&
 			whereAggregates.size() == 0)
 		{
 			isDistinct = true;
 			groupByList = null;
+			wasGroupBy = true;
 		}
 
 		/* Consider distinct elimination based on a uniqueness condition.
@@ -1087,8 +1097,9 @@
 					 * preserve the expected order.
 					 */
 					newTop = genProjectRestrictForReordering();
-					orderByList.resetToSourceRCs();
+ 					orderByList.resetToSourceRCs();
 					resultColumns = orderByList.reorderRCL(resultColumns);
+					newTop.getResultColumns().removeOrderByColumns();
 					orderByList = null;
 				}
 				orderByAndDistinctMerged = true;
@@ -1308,12 +1319,20 @@
 		if (((selectAggregates != null) && (selectAggregates.size() > 0)) 
 			|| (groupByList != null))
 		{
+			Vector aggs = selectAggregates;
+			if (havingAggregates != null && !havingAggregates.isEmpty()) {
+				havingAggregates.addAll(selectAggregates);
+				aggs = havingAggregates;
+			}
 			GroupByNode gbn = (GroupByNode) getNodeFactory().getNode(
 												C_NodeTypes.GROUP_BY_NODE,
 												prnRSN,
 												groupByList,
-												selectAggregates,
+												aggs,
+												havingClause,
+												havingSubquerys,
 												null,
+												new Integer(nestingLevel),
 												getContextManager());
 			gbn.considerPostOptimizeOptimizations(originalWhereClause != null);
 			gbn.assignCostEstimate(optimizer.getOptimizedCost());
@@ -1408,15 +1427,18 @@
 												getContextManager());
 				prnRSN.costEstimate = costEstimate.cloneMe();
 			}
-
+			
+			// There may be columns added to the select projection list
+			// a query like:
+			// select a, b from t group by a,b order by a+b
+			// the expr a+b is added to the select list.  
 			int orderBySelect = this.getResultColumns().getOrderBySelect();
 			if (orderBySelect > 0)
 			{
 				ResultColumnList selectRCs = prnRSN.getResultColumns().copyListAndObjects();
-				int wholeSize = selectRCs.size();
-				for (int i = wholeSize - 1; orderBySelect > 0; i--, orderBySelect--)
-					selectRCs.removeElementAt(i);
-				selectRCs.genVirtualColumnNodes(prnRSN, prnRSN.getResultColumns());
+				
+				selectRCs.removeOrderByColumns();
+				selectRCs.genVirtualColumnNodes(prnRSN, prnRSN.getResultColumns());				
 				prnRSN = (ResultSetNode) getNodeFactory().getNode(
 								C_NodeTypes.PROJECT_RESTRICT_NODE,
 								prnRSN,
@@ -1430,6 +1452,32 @@
 			}
 		}
 
+		
+		if (wasGroupBy && resultColumns.numGeneratedColumnsForGroupBy() > 0) {
+			// This case takes care of columns generated for group by's which 
+			// will need to be removed from the final projection. Note that the
+			// GroupByNode does remove generated columns but in certain cases
+			// we dispense with a group by and replace it with a distinct instead.
+			// So in a query like:
+			// select c1 from t group by c1, c2
+			// we would have added c2 to the projection list which will have to be 
+			// projected out.
+			
+			ResultColumnList newSelectList = prnRSN.getResultColumns().copyListAndObjects(); 
+			newSelectList.removeGeneratedGroupingColumns();
+			newSelectList.genVirtualColumnNodes(prnRSN, prnRSN.getResultColumns());
+			prnRSN = (ResultSetNode) getNodeFactory().getNode(
+						C_NodeTypes.PROJECT_RESTRICT_NODE,
+						prnRSN,
+						newSelectList,
+						null,
+						null,
+						null,
+						null,
+						null,
+						getContextManager());
+		}
+
 		if (!(orderByList != null && orderByList.getSortNeeded()) && orderByQuery)
 		{
 			// Remember if the result is dependent on the ordering
@@ -1683,6 +1731,10 @@
 		{
 			whereSubquerys.optimize(dataDictionary, costEstimate.rowCount());
 		}
+		
+		if (havingSubquerys != null && havingSubquerys.size() > 0) {
+			havingSubquerys.optimize(dataDictionary, costEstimate.rowCount());
+		}
 
 		return this;
 	}
@@ -1776,6 +1828,10 @@
 		{
 			whereSubquerys.modifyAccessPaths();
 		}
+		
+		if (havingSubquerys != null && havingSubquerys.size() > 0) {
+			havingSubquerys.modifyAccessPaths();
+		}
 
 		/* Build a temp copy of the current FromList for sort elimination, etc. */
 		preJoinFL.removeAllElements();
@@ -2183,6 +2239,10 @@
 			wherePredicates = (PredicateList)wherePredicates.accept(v);
 		}		
 
+		if (havingClause != null && !v.stopTraversal()) {
+			havingClause = (ValueNode)havingClause.accept(v);
+		}
+		
 		return returnNode;
 	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java Fri Mar  9 08:37:20 2007
@@ -577,8 +577,8 @@
 		/* Verify that both sides of the union have the same # of columns in their
 		 * RCL.
 		 */
-		if (leftResultSet.getResultColumns().size() !=
-			rightResultSet.getResultColumns().size())
+		if (leftResultSet.getResultColumns().visibleSize() !=
+			rightResultSet.getResultColumns().visibleSize())
 		{
 			throw StandardException.newException(SQLState.LANG_UNION_UNMATCHED_COLUMNS,
                                                  getOperatorName());

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Fri Mar  9 08:37:20 2007
@@ -678,7 +678,7 @@
 		 *    an expression subquery on the right side
 		 *	  of a BinaryComparisonOperatorNode.
 		 *  o There are no aggregates in the select list
-		 *  o There is no group by clause
+		 *  o There is no group by clause or having clause.
 		 *  o There is a uniqueness condition that ensures
 		 *	  that the flattening of the subquery will not
 		 *	  introduce duplicates into the result set.
@@ -697,6 +697,7 @@
 		{
 			SelectNode	select = (SelectNode) resultSet;
 			if ((select.getAggregateVector(IN_SELECT_LIST).size() == 0) &&
+			    (select.havingClause == null) &&
 				(! select.getGeneratedForGroupbyClause()))
 			{
 				ValueNode origLeftOperand = leftOperand;

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?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- 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 Fri Mar  9 08:37:20 2007
@@ -335,88 +335,6 @@
 											"TRUE");
 	}
 
-	/*
-	** Make sure that a select list in a parser-rewritten tree does
-	** not contain two columns by the same name. This is the patch
-	** to bug 280. In this case, the bind phase cannot tell the
-	** difference between the two columns and so the query may
-	** return wrong results.
-	**
-	** This issue is caused by parser rewriting of group by/having clauses
-	** into table expressions. (See function tableExpression() in this file)
-	** There is an improvement request filed under DERBY-681 to eliminate this
-	** rewrite, after which it should be possible to allow multiple columns to
-	** have same name in the select list.
-	*/
-	private	void	vetSelectList280( ResultColumnList selectList )
-		throws StandardException
-	{
-		Hashtable	allNames = new Hashtable();
-		int			count = selectList.size();
-
-		for ( int i = 0; i < count; i++ )
-		{
-			ResultColumn	rc = (ResultColumn) selectList.elementAt( i );
-			String			duplicateName = ambiguousDuplicateName( allNames, rc );
-
-			if ( duplicateName != null )
-			{
-				throw StandardException.newException
-				( SQLState.LANG_UNBINDABLE_REWRITE, duplicateName );
-			}
-
-		}
-	}
-
-	// allow two references to the same column. disallow aliasing
-	// two different expressions to the same name. returns the
-	// ambiguous column name if two result columns cannot be distinguished.
-	private	String	ambiguousDuplicateName( Hashtable allNames, ResultColumn rc )
-		throws StandardException
-	{
-		String			columnName = rc.getName();
-		ValueNode		newExpression = rc.getExpression();
-
-		// no column name means that the compiler will generate a
-		// unique name. that's ok and avoids bug 280
-		if ( columnName == null ) { return null; }
-
-		ValueNode		oldExpression = (ValueNode) allNames.get( columnName );
-
-		// no problem yet if we haven't seen this column
-		if ( oldExpression == null )
-		{
-			allNames.put( columnName, newExpression );
-			return null;
-		}
-
-		// if the two identically named references aren't both the
-		// same table column, then the bug occurs
-		if (
-				!(newExpression instanceof ColumnReference) ||
-				!(oldExpression instanceof ColumnReference)
-		   )
-		{ return columnName; }
-
-		ColumnReference		 newCR = (ColumnReference) newExpression;
-		ColumnReference		 oldCR = (ColumnReference) oldExpression;
-		TableName			 newTableName = newCR.getTableNameNode();
-		TableName			 oldTableName = oldCR.getTableNameNode();
-
-		if (
-				( (newTableName == null) && (oldTableName == null) ) ||
-				( (newTableName != null) && (newTableName.equals( oldTableName )) )
-		   )
-		{
-			// same table but different column aliased to same column name
-			if ( !newCR.getColumnName().equals( oldCR.getColumnName() ) )
-			{ return columnName; }
-			else { return null; }
-		}
-
-		// different tables
-		return null;
-	}
 
 	/**
 		Check that the current mode supports internal extensions.
@@ -878,6 +796,7 @@
 										fromList, /* FROM list */
 					  					whereClause, /* WHERE clause */
 										null, /* GROUP BY list */
+										null, /* having clause */
 										getContextManager());
 
 		StatementNode retval =
@@ -917,6 +836,7 @@
 										fromList, /* FROM list */
 					  					whereClause, /* WHERE clause */
 										null, /* GROUP BY list */
+										null, /* having clause */
 										getContextManager());
 
 		StatementNode retval =
@@ -8447,6 +8367,21 @@
 	[ groupByList = groupByClause() ]
 	[ havingClause = havingClause() ]
 	{
+ 
+		// fix for HAVING without GROUP BY, makes sure we get one
+		// aggregate operator by adding a count(*), this fixes beetle 5853, 5890
+		if (havingClause != null && groupByList == null) {
+			ValueNode vn = (ValueNode) nodeFactory.getNode(
+								C_NodeTypes.AGGREGATE_NODE,
+								null,
+								org.apache.derby.impl.sql.compile.CountAggregateDefinition.class,
+								Boolean.FALSE, // distinct Boolean.TRUE?
+								"COUNT(*)",
+								getContextManager());
+			AggregateNode n = (AggregateNode) vn;
+			n.replaceAggregatesWithColumnReferences(selectList, 0);			
+		}
+		
 		selectNode = (SelectNode) nodeFactory.getNode(
 							C_NodeTypes.SELECT_NODE,
 							selectList,
@@ -8454,115 +8389,14 @@
 							fromList,
 							whereClause,
 							groupByList,
+							havingClause,
 							getContextManager());
 
-		/* A SELECT with a GROUP BY or HAVING clause is the one instance where the
-		 * parser will generate a tree that does not exactly match the query.
-		 * The resulting tree is an outer SelectNode with the HAVING clause
-		 * as its WHERE clause and a FromList containing a single FromSubquery -
-		 * the user SELECT, minus the HAVING clause.
-		 * The outer SELECT gets a SELECT *.
-		 * The FromSubquery will have a null name, something that the user cannot do,
-		 * to enable the binding of any correlated columns in the HAVING clause.
-		 */
-		if (groupByList != null || havingClause != null)
-		{
-			vetSelectList280( selectList );
-
-			FromSubquery		fromSubquery;
-			ResultColumnList	outerRCL =
-										(ResultColumnList) nodeFactory.getNode(
-												C_NodeTypes.RESULT_COLUMN_LIST,
-												getContextManager());
-
-			/* Wrap the user SELECT in a FromSubquery */
-			fromList = (FromList) nodeFactory.getNode(
-									C_NodeTypes.FROM_LIST,
-									getNodeFactory().doJoinOrderOptimization(),
-									getContextManager());
-			fromSubquery = (FromSubquery) nodeFactory.getNode(
-													C_NodeTypes.FROM_SUBQUERY,
-													selectNode, 
-													null,
-													null,
-													null,
-													getContextManager());
-			fromList.addElement(fromSubquery);
-
-			/* Pull any aggregates out of the HAVING clause and append them to 
-			 * SELECT list in the user's select, replacing the aggregates in
-			 * the HAVING clause with ColumnReferences to the aggregate.
-			 * Do NOT replace anything below a ResultSetNode.  This means that
-			 * we'll replace
-			 * 		FROM x HAVING max(x.x) > 2
-			 * but not
-			 *		FROM x HAVING x.x = (select max(y.y) from y)
-			 * Aggregates under a SELECT are all fixed up correctly
-			 * later, but here we need to get the aggregates that
-			 * aren't under result sets.
-			 *
-			 * Appended ResultColumns marked as generated so the wrapper
-			 * select won't see them when the * is expanded.
-			 *
-			 * RESOLVE - someday we should try to find matching aggregates
-			 * instead of just adding them.
-             *
-             * NOTE: This rewriting of the query tree makes the handling of an ORDER BY
-             * clause difficult. See OrderByColumn.pullUpOrderByColumn. It makes specific
-             * assumptions about the structure of the generated query tree. Do not make
-             * any changes to this transformation without carefully considering the
-             * OrderByColumn pullUpOrderByColumn and bindOrderByColumn methods.
-			 */
-			if (havingClause != null)
-			{
-				ReplaceAggregatesWithCRVisitor visitor = 
-					new ReplaceAggregatesWithCRVisitor(selectList, ResultSetNode.class);
-				havingClause = (ValueNode)havingClause.accept(visitor);
-				
-				// fix for HAVING without GROUP BY, makes sure we get one
-				// aggregate operator by adding a count(*), this fixes beetle 5853, 5890
-				if (groupByList == null) {
-					ValueNode vn = (ValueNode) nodeFactory.getNode(
-								C_NodeTypes.AGGREGATE_NODE,
-								null,
-								org.apache.derby.impl.sql.compile.CountAggregateDefinition.class,
-								Boolean.FALSE, // distinct Boolean.TRUE?
-								"COUNT(*)",
-								getContextManager());
-					AggregateNode n = (AggregateNode) vn;
-					n.replaceAggregatesWithColumnReferences(selectList, 0);
-				}
-			}
-
-			outerRCL.addResultColumn((ResultColumn) nodeFactory.getNode(
-												C_NodeTypes.ALL_RESULT_COLUMN,
-												null,
-												getContextManager()));
-			// wrap another selectNode on the outside of the subquery
-			selectNode = (SelectNode) nodeFactory.getNode(
-										C_NodeTypes.SELECT_NODE,
-										outerRCL,	/* SELECT * from user SELECT */
-										null, 		/* AGGREGATE list */
-										fromList,
-										havingClause,
-										null,		/* GROUP BY list */
-										getContextManager());
-
-			/* Mark the wrapping FromSubquery and SelectNode as appropriate */
-			if (groupByList != null)
-			{
-				fromSubquery.markAsForGroupByClause();
-				selectNode.markAsForGroupByClause();
-			}
-			if (havingClause != null)
-			{
-				fromSubquery.markAsForHavingClause();
-				selectNode.markAsForHavingClause();
-			}
-		}
 		return selectNode;
 	}
 }
+
+
 
 /*
  * <A NAME="fromClause">fromClause</A>

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Fri Mar  9 08:37:20 2007
@@ -1357,6 +1357,12 @@
                 <arg>cursorName</arg>
             </msg>
 
+			<msg>
+				<name>42X24</name>
+				<text>Column {0} is referenced in the HAVING clause but is not in the GROUP BY list.</text>
+				<arg>columnName</arg>
+			</msg>
+			
             <msg>
                 <name>42X25</name>
                 <text>The '{0}' function is not allowed on the '{1}' type.</text>

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Fri Mar  9 08:37:20 2007
@@ -774,7 +774,7 @@
 	String LANG_NOT_COMPARABLE                                         = "42818";
 	String LANG_NON_BOOLEAN_WHERE_CLAUSE                               = "42X19";
 	String LANG_CURSOR_NOT_UPDATABLE                                   = "42X23";
-	//	String LANG_UNARY_MINUS_BAD_TYPE                                   = "42X24";
+	String LANG_INVALID_COL_HAVING_CLAUSE                              = "42X24";
 	String LANG_UNARY_FUNCTION_BAD_TYPE                                = "42X25";
 	String LANG_TYPE_DOESNT_EXIST                                      = "42X26";
 	String LANG_CURSOR_DELETE_MISMATCH                                 = "42X28";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregate.out Fri Mar  9 08:37:20 2007
@@ -947,7 +947,10 @@
 select a, count( a ) as a
 from bug280
 group by a;
-ERROR X0A00: The select list mentions column 'A' twice. This is not allowed in queries with GROUP BY or HAVING clauses. Try aliasing one of the conflicting columns to a unique name.
+A          |A          
+-----------------------
+1          |3          
+2          |2          
 ij> -- should return same results as first query (but with extra column)
 select a, count( a ), a
 from bug280

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregateOptimization.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregateOptimization.out?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregateOptimization.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/aggregateOptimization.out Fri Mar  9 08:37:20 2007
@@ -136,7 +136,7 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
-Project-Restrict ResultSet (5):
+Project-Restrict ResultSet (4):
 Number of opens = 1
 Rows seen = 7
 Rows filtered = 0
@@ -149,65 +149,52 @@
 	restriction time (milliseconds) = 0
 	projection time (milliseconds) = 0
 Source result set:
-	Project-Restrict ResultSet (4):
+	Grouped Aggregate ResultSet:
 	Number of opens = 1
-	Rows seen = 7
-	Rows filtered = 0
-	restriction = false
-	projection = true
+	Rows input = 9
+	Has distinct aggregate = false
+	In sorted order = false
+	Sort information: 
+		Number of rows input=9
+		Number of rows output=7
 		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
-		restriction time (milliseconds) = 0
-		projection time (milliseconds) = 0
 	Source result set:
-		Grouped Aggregate ResultSet:
+		Project-Restrict ResultSet (3):
 		Number of opens = 1
-		Rows input = 9
-		Has distinct aggregate = false
-		In sorted order = false
-		Sort information: 
-			Number of rows input=9
-			Number of rows output=7
+		Rows seen = 9
+		Rows filtered = 0
+		restriction = false
+		projection = true
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
+			restriction time (milliseconds) = 0
+			projection time (milliseconds) = 0
 		Source result set:
-			Project-Restrict ResultSet (3):
+			Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
 			Number of opens = 1
 			Rows seen = 9
 			Rows filtered = 0
-			restriction = false
-			projection = true
+			Fetch Size = 16
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-				restriction time (milliseconds) = 0
-				projection time (milliseconds) = 0
-			Source result set:
-				Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer
-				Number of opens = 1
-				Rows seen = 9
-				Rows filtered = 0
-				Fetch Size = 1
-					constructor time (milliseconds) = 0
-					open time (milliseconds) = 0
-					next time (milliseconds) = 0
-					close time (milliseconds) = 0
-					next time in milliseconds/row = 0
-				scan information: 
-					Bit set of columns fetched={0}
-					Number of columns fetched=1
-					Number of pages visited=1
-					Number of rows qualified=9
-					Number of rows visited=9
-					Scan type=heap
-					start position: 
-null					stop position: 
-null					qualifiers:
+				next time in milliseconds/row = 0
+			scan information: 
+				Bit set of columns fetched={0}
+				Number of columns fetched=1
+				Number of pages visited=1
+				Number of rows qualified=9
+				Number of rows visited=9
+				Scan type=heap
+				start position: 
+null				stop position: 
+null				qualifiers:
 None
 ij> -- min optimization
 create index i1 on t1(c1);
@@ -667,7 +654,7 @@
 			Number of opens = 1
 			Rows seen = 0
 			Rows filtered = 0
-			Fetch Size = 1
+			Fetch Size = 16
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
@@ -752,7 +739,7 @@
 			Number of opens = 1
 			Rows seen = 1
 			Rows filtered = 0
-			Fetch Size = 1
+			Fetch Size = 16
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
@@ -838,7 +825,7 @@
 			Number of opens = 1
 			Rows seen = 3
 			Rows filtered = 0
-			Fetch Size = 1
+			Fetch Size = 16
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
@@ -925,7 +912,7 @@
 			Number of opens = 1
 			Rows seen = 5
 			Rows filtered = 0
-			Fetch Size = 1
+			Fetch Size = 16
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
@@ -1016,7 +1003,7 @@
 			Number of opens = 1
 			Rows seen = 5
 			Rows filtered = 0
-			Fetch Size = 1
+			Fetch Size = 16
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
@@ -1701,7 +1688,7 @@
 Begin Execution Timestamp : null
 End Execution Timestamp : null
 Statement Execution Plan Text: 
-Project-Restrict ResultSet (5):
+Project-Restrict ResultSet (4):
 Number of opens = 1
 Rows seen = 5
 Rows filtered = 0
@@ -1714,66 +1701,53 @@
 	restriction time (milliseconds) = 0
 	projection time (milliseconds) = 0
 Source result set:
-	Project-Restrict ResultSet (4):
+	Grouped Aggregate ResultSet:
 	Number of opens = 1
-	Rows seen = 5
-	Rows filtered = 0
-	restriction = false
-	projection = true
+	Rows input = 5
+	Has distinct aggregate = false
+	In sorted order = true
 		constructor time (milliseconds) = 0
 		open time (milliseconds) = 0
 		next time (milliseconds) = 0
 		close time (milliseconds) = 0
-		restriction time (milliseconds) = 0
-		projection time (milliseconds) = 0
 	Source result set:
-		Grouped Aggregate ResultSet:
+		Project-Restrict ResultSet (3):
 		Number of opens = 1
-		Rows input = 5
-		Has distinct aggregate = false
-		In sorted order = true
+		Rows seen = 5
+		Rows filtered = 0
+		restriction = false
+		projection = true
 			constructor time (milliseconds) = 0
 			open time (milliseconds) = 0
 			next time (milliseconds) = 0
 			close time (milliseconds) = 0
+			restriction time (milliseconds) = 0
+			projection time (milliseconds) = 0
 		Source result set:
-			Project-Restrict ResultSet (3):
+			Index Scan ResultSet for X using index IX at read committed isolation level using instantaneous share row locking chosen by the optimizer
 			Number of opens = 1
 			Rows seen = 5
 			Rows filtered = 0
-			restriction = false
-			projection = true
+			Fetch Size = 16
 				constructor time (milliseconds) = 0
 				open time (milliseconds) = 0
 				next time (milliseconds) = 0
 				close time (milliseconds) = 0
-				restriction time (milliseconds) = 0
-				projection time (milliseconds) = 0
-			Source result set:
-				Index Scan ResultSet for X using index IX at read committed isolation level using share row locking chosen by the optimizer
-				Number of opens = 1
-				Rows seen = 5
-				Rows filtered = 0
-				Fetch Size = 1
-					constructor time (milliseconds) = 0
-					open time (milliseconds) = 0
-					next time (milliseconds) = 0
-					close time (milliseconds) = 0
-					next time in milliseconds/row = 0
-				scan information: 
-					Bit set of columns fetched={0}
-					Number of columns fetched=1
-					Number of deleted rows visited=0
-					Number of pages visited=1
-					Number of rows qualified=5
-					Number of rows visited=5
-					Scan type=btree
-					Tree height=1
-					start position: 
+				next time in milliseconds/row = 0
+			scan information: 
+				Bit set of columns fetched={0}
+				Number of columns fetched=1
+				Number of deleted rows visited=0
+				Number of pages visited=1
+				Number of rows qualified=5
+				Number of rows visited=5
+				Scan type=btree
+				Tree height=1
+				start position: 
 	None
-					stop position: 
+				stop position: 
 	None
-					qualifiers:
+				qualifiers:
 None
 ij> -- could do max optimization on this, but we don't 
 -- really know much about qualifications

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/groupBy.out Fri Mar  9 08:37:20 2007
@@ -50,7 +50,7 @@
 
 -- cannot contain column references which are not grouping columns
 select a from t1 group by a having c = 1;
-ERROR 42X04: Column 'C' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C' is not a column in the target table.
+ERROR 42X24: Column C is referenced in the HAVING clause but is not in the GROUP BY list.
 ij> select a from t1 o group by a having a = (select a from t1 where b = b.o);
 ERROR 42X04: Column 'B.O' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B.O' is not a column in the target table.
 ij> -- ?s in group by
@@ -340,8 +340,8 @@
 select i, dt, b from t group by i, dt, b order by i, dt;
 I          |DT        |B   
 ---------------------------
-0          |1992-01-01|ffff
 0          |1992-01-01|12af
+0          |1992-01-01|ffff
 0          |1992-09-09|12af
 1          |1992-01-01|12af
 NULL       |NULL      |NULL
@@ -577,7 +577,7 @@
 select * from t1 having c1 = 1;
 ERROR 42Y35: Column reference 'T1.C1' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
 ij> select 1 from t1 having c1 = 1;
-ERROR 42X04: Column 'C1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C1' is not a column in the target table.
+ERROR 42X24: Column C1 is referenced in the HAVING clause but is not in the GROUP BY list.
 ij> -- correlated subquery in having clause
 select * from t1 t1_outer 
 having 1 = (select 1 from t1 where c1 = t1_outer.c1);

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out Fri Mar  9 08:37:20 2007
@@ -216,26 +216,6 @@
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
 1          |ASHOK     |NULL      |K51 
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
 10         |BOBBIE    |HAMID     |K55 
 13         |DAN       |ROGER     |K52 
 17         |DON       |GUY       |K55 
@@ -284,26 +264,6 @@
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
 1          |ASHOK     |NULL      |K51 
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
 10         |BOBBIE    |HAMID     |K55 
 13         |DAN       |ROGER     |K52 
 17         |DON       |GUY       |K55 
@@ -356,26 +316,6 @@
 VC0        |VNAME     |VMGRNAME  |VDNO
 --------------------------------------
 1          |ASHOK     |NULL      |K51 
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
 10         |BOBBIE    |HAMID     |K55 
 13         |DAN       |ROGER     |K52 
 17         |DON       |GUY       |K55 
@@ -678,9 +618,6 @@
 C0         |DNO|DNAME     
 --------------------------
 3          |K51|CS        
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
 2          |K52|OFC       
 1          |K55|DB        
 ij> delete from db2test.dept where exists ( select max(mgrname) from
@@ -3231,7 +3168,7 @@
                   where e4.name = e2.mgrname group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                    where e.mgrname = 'JOHN'))))) order by 2, 3, 4;
-ERROR 42X04: Column 'E4.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E4.NAME' is not a column in the target table.
+ERROR 42X24: Column E4.NAME is referenced in the HAVING clause but is not in the GROUP BY list.
 ij> -- select should get -119;
 delete from db2test.emp 
   where dno in (select dno from db2test.emp5 e5
@@ -3242,7 +3179,7 @@
                   where e4.name = e2.mgrname group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                   where db2test.emp.mgrname = 'JOHN')))));
-ERROR 42X04: Column 'E4.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E4.NAME' is not a column in the target table.
+ERROR 42X24: Column E4.NAME is referenced in the HAVING clause but is not in the GROUP BY list.
 ij> select * from db2test.emp order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -4429,26 +4366,6 @@
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
 1          |ASHOK     |NULL      |K51 
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
 10         |BOBBIE    |HAMID     |K55 
 13         |DAN       |ROGER     |K52 
 17         |DON       |GUY       |K55 
@@ -4498,26 +4415,6 @@
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
 1          |ASHOK     |NULL      |K51 
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
 10         |BOBBIE    |HAMID     |K55 
 13         |DAN       |ROGER     |K52 
 17         |DON       |GUY       |K55 
@@ -4571,26 +4468,6 @@
 VC0        |VNAME     |VMGRNAME  |VDNO
 --------------------------------------
 1          |ASHOK     |NULL      |K51 
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
 10         |BOBBIE    |HAMID     |K55 
 13         |DAN       |ROGER     |K52 
 17         |DON       |GUY       |K55 
@@ -4933,9 +4810,6 @@
 C0         |DNO|DNAME     
 --------------------------
 3          |K51|CS        
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
-WARNING 01003: Null values were eliminated from the argument of a column function.
 2          |K52|OFC       
 1          |K55|DB        
 ij> delete from db2test.dept where exists ( select max(mgrname) from

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out Fri Mar  9 08:37:20 2007
@@ -746,8 +746,6 @@
 -----------------------------------------------------------------------------------------------------
 0          |0     |0                             |0                             |0                   
 ij> select * from s where i = (select min(i) from s group by i);
-I          |S     |C                             |VC                            |B                   
------------------------------------------------------------------------------------------------------
 ERROR 21000: Scalar subquery is only allowed to return a single row.
 ij> -- tests for distinct expression subquery
 create table dist1 (c1 int);

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out Fri Mar  9 08:37:20 2007
@@ -66,8 +66,6 @@
 1          |2          |3          
 ij> -- group by in subquery
 select * from outer1 o where c1 <= (select c1 from idx1 i group by c1);
-C1         |C2         |C3         
------------------------------------
 ERROR 21000: Scalar subquery is only allowed to return a single row.
 ij> -- otherwise flattenable subquery under an or 
 -- subquery returns no rows

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out Fri Mar  9 08:37:20 2007
@@ -971,4 +971,23 @@
 0 rows inserted/updated/deleted
 ij> drop table b;
 0 rows inserted/updated/deleted
+ij> -- DERBY-681. Check union with group by/having
+create table o (name varchar(20), ord int);
+0 rows inserted/updated/deleted
+ij> create table a (ord int, amount int);
+0 rows inserted/updated/deleted
+ij> create view v1 (vx, vy) 
+as select name, sum(ord) from o where ord > 0 group by name, ord
+    having ord <= ANY (select ord from a);
+0 rows inserted/updated/deleted
+ij> select vx, vy from v1
+     union select vx, sum(vy) from v1 group by vx, vy having (vy / 2) > 15;
+VX                  |2          
+--------------------------------
+ij> drop view v1;
+0 rows inserted/updated/deleted
+ij> drop table o;
+0 rows inserted/updated/deleted
+ij> drop table a;
+0 rows inserted/updated/deleted
 ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out Fri Mar  9 08:37:20 2007
@@ -390,4 +390,24 @@
 -- expect error
 CREATE VIEW v1(c1) AS VALUES NULL;
 ERROR 42X07: Null is only allowed in a VALUES clause within an INSERT statement.
+ij> -- DERBY-681
+create table o (name varchar(20), ord int);
+0 rows inserted/updated/deleted
+ij> create table a (ord int, amount int);
+0 rows inserted/updated/deleted
+ij> create view v1 (vx, vy) 
+as select name, sum(ord) from o where ord > 0 group by name, ord;
+0 rows inserted/updated/deleted
+ij> create view v2 (vx, vy) as
+  select name, sum(ord) from o where ord > 0 group by name, ord
+    having ord <= ANY (select ord from a);
+0 rows inserted/updated/deleted
+ij> drop view v2;
+0 rows inserted/updated/deleted
+ij> drop view v1;
+0 rows inserted/updated/deleted
+ij> drop table a;
+0 rows inserted/updated/deleted
+ij> drop table o;
+0 rows inserted/updated/deleted
 ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java?view=diff&rev=516454&r1=516453&r2=516454
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java Fri Mar  9 08:37:20 2007
@@ -148,6 +148,18 @@
             new String [] {"NO", "NO"},
             new String [] {"INTEGER", "CHAR"});
     }
+    
+    public void testCreateTableWithGroupByInQuery() throws Exception 
+    {
+        positiveTest(
+            "create table t3 (x, y) as " +
+            " (select v, sum(i) from t1 where i > 0 " +
+            " group by i, v having i <= " +
+            " ANY (select a from t2)) with no data",
+            new String[] {"X", "Y"},
+            new String[] {"NO", "YES"},
+            new String[] {"VARCHAR", "INTEGER"});
+    }
 
     /**
      * Test error when base table does not exist.



Mime
View raw message