db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r437070 [1/2] - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/compile/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/...
Date Sat, 26 Aug 2006 03:55:42 GMT
Author: djd
Date: Fri Aug 25 20:55:39 2006
New Revision: 437070

URL: http://svn.apache.org/viewvc?rev=437070&view=rev
Log:
DERBY-883 Enhance GROUP BY clause to support expressions instead of just column references.
Patch contributed by  Manish Khettry - manish_khettry@yahoo.com

Added:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubstituteExpressionVisitor.java   (with props)
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java   (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/Visitor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BaseColumnNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryListOperatorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryOperatorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CoalesceFunctionNode.java
    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/ConditionalNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConstantNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentDatetimeOperatorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentRowLocationNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DefaultNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.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/JavaToSQLValueNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ParameterNode.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/SelectNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SpecialFunctionNode.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/TernaryOperatorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VerifyAggregateExpressionsVisitor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VirtualColumnNode.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_en.properties
    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/groupBy.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/groupBy.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/Visitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/Visitor.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/Visitor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/Visitor.java Fri Aug 25 20:55:39 2006
@@ -91,5 +91,5 @@
 	 * 
 	 * @return true/false
 	 */
-	boolean skipChildren(Visitable node);
+	boolean skipChildren(Visitable node) throws StandardException;
 }	

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BaseColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BaseColumnNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BaseColumnNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BaseColumnNode.java Fri Aug 25 20:55:39 2006
@@ -171,4 +171,18 @@
 	{
 		return Qualifier.SCAN_INVARIANT;
 	}
+        
+        /**
+         * @inheritDoc
+         */
+	protected boolean isEquivalent(ValueNode o)
+	{
+		if (isSameNodeType(o)) 
+		{
+			BaseColumnNode other = (BaseColumnNode)o;
+			return other.tableName.equals(other.tableName) 
+			&& other.columnName.equals(columnName);
+		} 
+		return false;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryListOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryListOperatorNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryListOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryListOperatorNode.java Fri Aug 25 20:55:39 2006
@@ -423,4 +423,36 @@
 			
 		return returnNode;
 	}
+        
+        /**
+         * @inheritDoc
+         */
+	protected boolean isEquivalent(ValueNode o) throws StandardException
+	{
+		if (!isSameNodeType(o))
+		{
+			return false;
+		}
+		BinaryListOperatorNode other = (BinaryListOperatorNode)o;
+		if (!operator.equals(other.operator)
+				|| !leftOperand.isEquivalent(other.getLeftOperand())) 
+		{
+			return false;
+		}
+		
+		int sz = getRightOperandList().size();
+		if (sz != other.rightOperandList.size())
+		{
+			return false;
+		}
+		for (int i = 0; i < sz; i++)
+		{
+			ValueNode e = (ValueNode)rightOperandList.elementAt(i);
+			if (!e.isEquivalent((ValueNode)other.rightOperandList.elementAt(i))) 
+			{
+				return false;
+			}
+		}
+		return true;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryOperatorNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/BinaryOperatorNode.java Fri Aug 25 20:55:39 2006
@@ -870,6 +870,21 @@
 		return returnNode;
 	}
 
+        /**
+         * @inheritDoc
+         */
+        protected boolean isEquivalent(ValueNode o) throws StandardException
+        {
+        	if (!isSameNodeType(o))
+        	{
+        		return false;
+        	}
+        	BinaryOperatorNode other = (BinaryOperatorNode)o;
+        	return methodName.equals(other.methodName)
+        	       && leftOperand.isEquivalent(other.leftOperand)
+        	       && rightOperand.isEquivalent(other.rightOperand);
+        }
+
 	/**
 	 * Push the fields necessary to generate an instance of
 	 * SqlXmlExecutor, which will then be used at execution

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java Fri Aug 25 20:55:39 2006
@@ -1031,6 +1031,21 @@
 	{
 		return forDataTypeFunction;
 	}
+        
+	/**
+	 * {@inheritDoc}
+	 * @throws StandardException 
+	 */
+	protected boolean isEquivalent(ValueNode o) throws StandardException
+	{
+		if (isSameNodeType(o)) 
+		{
+			CastNode other = (CastNode)o;
+			return castTarget.equals(other.castTarget)
+				&& castOperand.isEquivalent(other.castOperand);
+		}
+		return false;
+	}
 }
 
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CoalesceFunctionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CoalesceFunctionNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CoalesceFunctionNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CoalesceFunctionNode.java Fri Aug 25 20:55:39 2006
@@ -34,9 +34,12 @@
 
 import org.apache.derby.iapi.services.compiler.LocalField;
 import org.apache.derby.iapi.services.compiler.MethodBuilder;
+import org.apache.derby.iapi.sql.compile.Visitable;
+import org.apache.derby.iapi.sql.compile.Visitor;
 
 import java.lang.reflect.Modifier;
 
+import java.util.Iterator;
 import java.util.Vector;
 
 /**
@@ -306,7 +309,8 @@
 	/*
 		print the non-node subfields
 	 */
-	public String toString() {
+	public String toString() 
+	{
 		if (SanityManager.DEBUG)
 		{
 			return super.toString()+functionName+"("+argumentsList+")\n";
@@ -316,7 +320,53 @@
 			return "";
 		}
 	}
-
+        
+	/**
+	 * {@inheritDoc}
+	 */
+	protected boolean isEquivalent(ValueNode o) throws StandardException
+	{
+		if (!isSameNodeType(o))
+		{
+			return false;
+		}
+		
+		CoalesceFunctionNode other = (CoalesceFunctionNode)o;
+		if (other.argumentsList.size() != argumentsList.size())
+		{
+			return false;
+			
+		}
+		
+		int size = argumentsList.size();
+		for (int index = 0; index < size; index++)
+		{
+			ValueNode v1 = (ValueNode)argumentsList.elementAt(index);
+			ValueNode v2 = (ValueNode)other.argumentsList.elementAt(index);
+			if (!v1.isEquivalent(v2)) 
+			{
+				return false;
+			}
+		}
+		return true;
+	}
+	public Visitable accept(Visitor v) throws StandardException 
+	{
+		Visitable returnNode = v.visit(this);
+		
+		if (v.skipChildren(this) || v.stopTraversal())
+		{
+			return returnNode;
+		}
+		
+		int size = argumentsList.size();
+		for (int index = 0; index < size; index++)
+		{
+			argumentsList.setElementAt(
+					(QueryTreeNode)(argumentsList.elementAt(index)).accept(v), index);
+		}
+		return returnNode;
+	}
 	/**
 	 * Preprocess an expression tree.  We do a number of transformations
 	 * here (including subqueries, IN lists, LIKE and BETWEEN) plus

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?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- 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 Aug 25 20:55:39 2006
@@ -1120,6 +1120,16 @@
 		colNum[0] = -1;
 		return null;
 	}
+	
+	protected boolean isEquivalent(ValueNode o) throws StandardException
+	{
+		if (!isSameNodeType(o)) {
+			return false;
+		}
+		ColumnReference other = (ColumnReference)o;
+		return (tableNumber == other.tableNumber 
+				&& columnName.equals(other.getColumnName()));
+	}
 
 	/**
 	 * Mark this column reference as "scoped", which means that it

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java Fri Aug 25 20:55:39 2006
@@ -488,4 +488,32 @@
 		
 		return returnNode;
 	}
+        
+	/**
+	 * @{inheritDoc}
+	 */
+	protected boolean isEquivalent(ValueNode o) throws StandardException
+	{
+		if (isSameNodeType(o)) 
+		{
+			ConditionalNode other = (ConditionalNode)o;
+			if (thenElseList.size() == other.thenElseList.size()
+					&& (testCondition.isEquivalent(other.testCondition))) 
+			{
+				int sz = thenElseList.size();
+				for (int i = 0; i < sz; i++)
+				{
+					ValueNode v1 = (ValueNode)thenElseList.elementAt(i);
+					ValueNode v2 = (ValueNode)other.thenElseList.elementAt(i);
+					if (!v1.isEquivalent(v2)) 
+					{
+						return false;
+					}
+					
+				}
+				return true;
+			}
+		}
+		return false;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConstantNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConstantNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConstantNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConstantNode.java Fri Aug 25 20:55:39 2006
@@ -277,4 +277,13 @@
 		// Constants are constant for the life of the query
 		return Qualifier.CONSTANT;
 	}
+        
+	protected boolean isEquivalent(ValueNode o) throws StandardException
+	{
+		if (isSameNodeType(o)) {
+			ConstantNode other = (ConstantNode)o;
+			return other.getValue().compare(getValue()) == 0;
+		}
+		return false;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentDatetimeOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentDatetimeOperatorNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentDatetimeOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentDatetimeOperatorNode.java Fri Aug 25 20:55:39 2006
@@ -180,4 +180,17 @@
 			return "";
 		}
 	}
+        
+        /**
+         * {@inheritDoc}
+         */
+	protected boolean isEquivalent(ValueNode o)
+	{
+		if (isSameNodeType(o)) 
+		{
+			CurrentDatetimeOperatorNode other = (CurrentDatetimeOperatorNode)o;
+			return other.whichType == whichType;
+		}
+		return false;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentRowLocationNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentRowLocationNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentRowLocationNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentRowLocationNode.java Fri Aug 25 20:55:39 2006
@@ -162,4 +162,9 @@
 		mbex.pushThis();
 		mbex.callMethod(VMOpcode.INVOKEVIRTUAL, (String) null, mb.getName(), ClassName.DataValueDescriptor, 0);
 	}
+	
+	protected boolean isEquivalent(ValueNode o)
+	{
+		return false;
+	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DefaultNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DefaultNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DefaultNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DefaultNode.java Fri Aug 25 20:55:39 2006
@@ -276,4 +276,12 @@
 				"generateExpression not expected to be called");
 		}
 	}
+
+    /**
+     * @inheritDoc
+     */
+	protected boolean isEquivalent(ValueNode other)
+    {
+		return false;
+    }
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/GroupByColumn.java Fri Aug 25 20:55:39 2006
@@ -40,8 +40,8 @@
  */
 public class GroupByColumn extends OrderedColumn 
 {
-	private ColumnReference	colRef;
-
+	private ValueNode columnExpression;
+	
 	/**
 	 * Initializer.
 	 *
@@ -49,7 +49,7 @@
 	 */
 	public void init(Object colRef) 
 	{
-		this.colRef = (ColumnReference) colRef;
+		this.columnExpression = (ValueNode)colRef;
 	}
 
 	/**
@@ -62,7 +62,7 @@
 	{
 		if (SanityManager.DEBUG)
 		{
-			return "Column Reference: "+colRef+super.toString();
+			return "Column Expression: "+columnExpression+super.toString();
 		}
 		else
 		{
@@ -83,10 +83,10 @@
 		{
 			super.printSubNodes(depth);
 
-			if (colRef != null)
+			if (columnExpression != null)
 			{
 				printLabel(depth, "colRef: ");
-				colRef.treePrint(depth + 1);
+				columnExpression.treePrint(depth + 1);
 			}
 		}
 	}
@@ -98,49 +98,7 @@
 	 */
 	public String getColumnName() 
 	{
-		return colRef.getColumnName();
-	}
-
-	/**
-	 * Get the ColumnReference from this GroupByColumn.
-	 *
-	 * @return ColumnReference	The ColumnReference from this node.
-	 */
-	public ColumnReference getColumnReference()
-	{
-		return colRef;
-	}
-
-	/**
-	 * Set the ColumnReference for this GroupByColumn.
-	 *
-	 * @param colRef	The new ColumnReference for this node.
-	 */
-	public void setColumnReference(ColumnReference colRef)
-	{
-		this.colRef = colRef;
-	}
-
-	/**
-	 * Get the table number for this GroupByColumn.
-	 *
-	 * @return	int The table number for this GroupByColumn
-	 */
-
-	public int getTableNumber()
-	{
-		return colRef.getTableNumber();
-	}
-
-	/**
-	 * Get the source this GroupByColumn
-	 *
-	 * @return	The source of this GroupByColumn
-	 */
-
-	public ResultColumn getSource()
-	{
-		return colRef.getSource();
+		return columnExpression.getColumnName();
 	}
 
 	/**
@@ -162,12 +120,16 @@
 				throws StandardException
 	{
 		/* Bind the ColumnReference to the FromList */
-		colRef = (ColumnReference) colRef.bindExpression(fromList,
+		columnExpression = (ValueNode) columnExpression.bindExpression(fromList,
 							  subqueryList,
 							  aggregateVector);
 
 		// Verify that we can group on the column
-
+		if (columnExpression.isParameterNode()) 
+		{
+			throw StandardException.newException(SQLState.LANG_INVALID_COL_REF_GROUPED_SELECT_LIST,
+					columnExpression);
+		}
 		/*
 		 * Do not check to see if we can map user types
 		 * to built-in types.  The ability to do so does
@@ -175,11 +137,22 @@
 		 * as of version 2.0, ordering does not work on
 		 * user types.
 		 */
-		TypeId ctid = colRef.getTypeId();
+		TypeId ctid = columnExpression.getTypeId();
 		if (! ctid.orderable(getClassFactory()))
 		{
 			throw StandardException.newException(SQLState.LANG_COLUMN_NOT_ORDERABLE_DURING_EXECUTION, 
 							ctid.getSQLTypeName());
 		}
+	}
+
+	public ValueNode getColumnExpression() 
+	{
+		return columnExpression;
+	}
+
+	public void setColumnExpression(ValueNode cexpr) 
+	{
+		this.columnExpression = cexpr;
+		
 	}
 }

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=437070&r1=437069&r2=437070&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 Fri Aug 25 20:55:39 2006
@@ -142,37 +142,26 @@
 									  dummySubqueryList, aggregateVector);
 		}
 
-		/* Verify that the columns in the GROUP BY list are unique.
-		 * (Unique on table number and source ResultColumn.)
-		 */
-		verifyUniqueGroupingColumns();
-
+		
+		int				rclSize = selectRCL.size();
 		for (int index = 0; index < size; index++)
 		{
 			boolean				matchFound = false;
 			GroupByColumn		groupingCol = (GroupByColumn) elementAt(index);
-			String				curName = groupingCol.getColumnName();
 
 			/* Verify that this entry in the GROUP BY list matches a
 			 * grouping column in the select list.
 			 */
-			int				groupingColTableNum = groupingCol.getTableNumber();
-			ResultColumn	groupingSource = groupingCol.getSource();
-			int				rclSize = selectRCL.size();
 			for (int inner = 0; inner < rclSize; inner++)
 			{
-				ColumnReference selectListCR;
 				ResultColumn selectListRC = (ResultColumn) selectRCL.elementAt(inner);
-
-				if (! (selectListRC.getExpression() instanceof ColumnReference))
-				{
+				if (!(selectListRC.getExpression() instanceof ColumnReference)) {
 					continue;
 				}
-				selectListCR = (ColumnReference) selectListRC.getExpression();
+				
+				ColumnReference selectListCR = (ColumnReference) selectListRC.getExpression();
 
-				if (groupingColTableNum == selectListCR.getTableNumber() &&
-					groupingSource == selectListCR.getSource())
-				{
+				if (selectListCR.isEquivalent(groupingCol.getColumnExpression())) { 
 					/* Column positions for grouping columns are 0-based */
 					groupingCol.setColumnPosition(inner + 1);
 
@@ -182,19 +171,21 @@
 					break;
 				}
 			}
-
 			/* If no match found in the SELECT list, then add a matching
 			 * ResultColumn/ColumnReference pair to the SelectNode's RCL.
 			 */
-			if (! matchFound)
+			if (! matchFound && 
+			    groupingCol.getColumnExpression() instanceof ColumnReference) 
 			{
+			    	// only add matching columns for column references not 
+			    	// expressions yet. See DERBY-883 for details. 
 				ResultColumn newRC;
 
 				/* Get a new ResultColumn */
 				newRC = (ResultColumn) getNodeFactory().getNode(
 								C_NodeTypes.RESULT_COLUMN,
 								groupingCol.getColumnName(),
-								groupingCol.getColumnReference().getClone(),
+								groupingCol.getColumnExpression().getClone(),
 								getContextManager());
 				newRC.setVirtualColumnId(selectRCL.size() + 1);
 				newRC.markGenerated();
@@ -231,125 +222,33 @@
 		numGroupingColsAdded+= numColsAddedHere;
 	}
 
+	
 
 	/**
-	 * Check the uniqueness of the column names within a GROUP BY list.
-	 *
-	 * @exception StandardException		Thrown on error
+	 * Find the matching grouping column if any for the given expression
+	 * 
+	 * @param node an expression for which we are trying to find a match
+	 * in the group by list.
+	 * 
+	 * @return the matching GroupByColumn if one exists, null otherwise.
+	 * 
+	 * @throws StandardException
 	 */
-	public void verifyUniqueGroupingColumns() throws StandardException
+	public GroupByColumn findGroupingColumn(ValueNode node)
+	        throws StandardException
 	{
-		int				size = size();
-		String			colName;
-
-		for (int outer = 0; outer < size; outer++)
+		int sz = size();
+		for (int i = 0; i < sz; i++) 
 		{
-			GroupByColumn		groupingCol = (GroupByColumn) elementAt(outer);
-			int					outerTabNum = groupingCol.getTableNumber();
-			ResultColumn		outerRC = groupingCol.getSource();
-			String				curName = groupingCol.getColumnName();
-			/* Verify that this column's name is unique within the list */
-			colName = groupingCol.getColumnName();
-
-			for (int inner = outer + 1; inner < size; inner++)
+			GroupByColumn gbc = (GroupByColumn)elementAt(i);
+			if (gbc.getColumnExpression().isEquivalent(node))
 			{
-				GroupByColumn		innerGBC = (GroupByColumn) elementAt(inner);
-				int					innerTabNum = innerGBC.getTableNumber();
-				ResultColumn		innerRC = innerGBC.getSource();
-				if (outerTabNum == innerTabNum &&
-					outerRC == innerRC)
-				{
-					throw StandardException.newException(SQLState.LANG_AMBIGUOUS_GROUPING_COLUMN, colName);
-				}
+				return gbc;
 			}
 		}
-
-		/* No duplicate column names */
-	}
-
-	/**
-	 * Add any grouping columns which are not already in the appropriate RCL
-	 * to the RCL.
-	 * NOTE: The RC/VCNs in the SELECT list will point to the same pool of
-	 *		 ResultColumns as the GroupByColumns in this list.
-	 *
-	 * @param selectNode	The SelectNode whose RCL we add to.
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public void addNewGroupingColumnsToRCL(SelectNode selectNode)
-		throws StandardException
-	{
-		FromList			fromList = selectNode.getFromList();
-		int					size = size();
-		ResultColumnList	rcl = selectNode.getResultColumns();
-
-		if (SanityManager.DEBUG)
-		{
-			SanityManager.ASSERT(selectNode.getGroupByList() == this,
-				"selectNode.getGroupByList() expected to equal this");
-		}
-
-		for (int index = 0; index < size; index++)
-		{
-			GroupByColumn		gbc = (GroupByColumn) elementAt(index);
-			ResultColumn		newRC;
-			VirtualColumnNode	newVCN;
-
-			/* Skip over GBCs which have a match */
-			if (gbc.getColumnPosition() != GroupByColumn.UNMATCHEDPOSITION)
-			{
-				continue;
-			}
-
-			/* Get and bind a new VCN */
-			newVCN = (VirtualColumnNode) getNodeFactory().getNode(
-							C_NodeTypes.VIRTUAL_COLUMN_NODE,
-							fromList.getFromTableByResultColumn(gbc.getSource()),
-							gbc.getSource(),
-							ReuseFactory.getInteger(rcl.size() + 2),
-							getContextManager());
-			newVCN.setType(gbc.getColumnReference().getTypeServices());
-
-			/* Get and bind a new ResultColumn */
-			newRC = (ResultColumn) getNodeFactory().getNode(
-							C_NodeTypes.RESULT_COLUMN,
-							gbc.getColumnName(),
-							newVCN,
-							getContextManager());
-			newRC.setType(newVCN.getTypeServices());
-			newRC.setVirtualColumnId(rcl.size() + 2);
-
-			/* Add the new RC/VCN to the RCL */
-			rcl.addElement(newRC);
-
-			/* Set the columnPosition in the GroupByColumn, now that it
-			 * has a matching entry in the SELECT list.
-			 */
-			gbc.setColumnPosition(rcl.size());
-		}
-	}
-
-	/**
-	 *
-	 */
-	public GroupByColumn containsColumnReference(ColumnReference cr)
-	{
-		int size = size();
-		for (int index = 0; index < size; index++)
-		{
-			GroupByColumn		groupingCol = (GroupByColumn) elementAt(index);
-
-			if (groupingCol.getSource() == cr.getSource() &&
-				groupingCol.getTableNumber() == cr.getTableNumber())
-			{
-				return groupingCol;
-			}
-		}
-
 		return null;
 	}
-
+	
 	/**
 	 * Remap all ColumnReferences in this tree to be clones of the
 	 * underlying expression.
@@ -371,7 +270,7 @@
 			ValueNode	retVN;
 			gbc = (GroupByColumn) elementAt(index);
 
-			retVN = gbc.getColumnReference().remapColumnReferencesToExpressions();
+			retVN = gbc.getColumnExpression().remapColumnReferencesToExpressions();
 
 			if (SanityManager.DEBUG)
 			{
@@ -380,7 +279,7 @@
 					retVN.getClass().getName());
 			}
 
-			gbc.setColumnReference((ColumnReference) retVN);
+			gbc.setColumnExpression(retVN);
 		}
 	}
 
@@ -405,5 +304,18 @@
 		{
 			return "";
 		}
+	}
+
+	public void preprocess(
+			int numTables, FromList fromList, SubqueryList whereSubquerys, 
+			PredicateList wherePredicates) throws StandardException 
+	{
+		for (int index = 0; index < size(); index++)
+		{
+			GroupByColumn	groupingCol = (GroupByColumn) elementAt(index);
+			groupingCol.setColumnExpression(
+					groupingCol.getColumnExpression().preprocess(
+							numTables, fromList, whereSubquerys, wherePredicates));
+		}		
 	}
 }

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=437070&r1=437069&r2=437070&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 Fri Aug 25 20:55:39 2006
@@ -202,14 +202,25 @@
 
 			// Now populate the CR array and see if ordered
 			int glSize = this.groupingList.size();
-			for (int index = 0; index < glSize; index++)
+			int index;
+			for (index = 0; index < glSize; index++)
 			{
 				GroupByColumn gc =
 						(GroupByColumn) this.groupingList.elementAt(index);
-				crs[index] = gc.getColumnReference();
+				if (gc.getColumnExpression() instanceof ColumnReference) 
+				{
+					crs[index] = (ColumnReference)gc.getColumnExpression();
+				} 
+				else 
+				{
+					isInSortedOrder = false;
+					break;
+				}
+				
+			}
+			if (index == glSize) {
+				isInSortedOrder = childResult.isOrderedOn(crs, true, (Vector)null);
 			}
-
-			isInSortedOrder = childResult.isOrderedOn(crs, true, (Vector)null);
 		}
 	}
 
@@ -314,6 +325,70 @@
 
 	}
 
+	/**
+	 * In the query rewrite for group by, add the columns on which
+	 * we are doing the group by.
+
+	 * @see #addNewColumnsForAggregation
+	 */
+	private void addUnAggColumns() throws StandardException
+	{
+		ResultColumnList bottomRCL  = childResult.getResultColumns();
+		ResultColumnList groupByRCL = resultColumns;
+
+		int sz = groupingList.size();
+		for (int i = 0; i < sz; i++) 
+		{
+			GroupByColumn gbc = (GroupByColumn) groupingList.elementAt(i);
+			ResultColumn newRC = (ResultColumn) getNodeFactory().getNode(
+					C_NodeTypes.RESULT_COLUMN,
+					"##UnaggColumn",
+					gbc.getColumnExpression(),
+					getContextManager());
+
+			// add this result column to the bottom rcl
+			bottomRCL.addElement(newRC);
+			newRC.markGenerated();
+			newRC.bindResultColumnToExpression();
+			newRC.setVirtualColumnId(bottomRCL.size());
+			
+			// now add this column to the groupbylist
+			ResultColumn gbRC = (ResultColumn) getNodeFactory().getNode(
+					C_NodeTypes.RESULT_COLUMN,
+					"##UnaggColumn",
+					gbc.getColumnExpression(),
+					getContextManager());
+			groupByRCL.addElement(gbRC);
+			gbRC.markGenerated();
+			gbRC.bindResultColumnToExpression();
+			gbRC.setVirtualColumnId(groupByRCL.size());
+
+			/*
+			 ** Reset the original node to point to the
+			 ** Group By result set.
+			 */
+			VirtualColumnNode vc = (VirtualColumnNode) getNodeFactory().getNode(
+					C_NodeTypes.VIRTUAL_COLUMN_NODE,
+					this, // source result set.
+					gbRC,
+					new Integer(groupByRCL.size()),
+					getContextManager());
+
+			// we replace each group by expression 
+			// in the projection list with a virtual column node
+			// that effectively points to a result column 
+			// in the result set doing the group by
+			SubstituteExpressionVisitor se = 
+				new SubstituteExpressionVisitor(
+						gbc.getColumnExpression(),
+						vc,
+						AggregateNode.class);
+			parent.getResultColumns().accept(se);
+
+			// finally reset gbc to its new position.
+			gbc.setColumnPosition(bottomRCL.size());
+		}
+	}
 
 	/**
 	 * Add a whole slew of columns needed for 
@@ -357,19 +432,26 @@
 	private void addNewColumnsForAggregation()
 		throws StandardException
 	{
-		/*
-		** Now we have two new nodes, the sort and a new PR above
-		** it.  They all map to the child result set.  Now we must
-		** find every aggregate and massage the tree.  For now we
-		** will examine every result column of the original select
-		** list.
-		*/
-		DataDictionary			dd;
+		aggInfo = new AggregatorInfoList();
+		if (groupingList != null)
+		{
+			addUnAggColumns();
+		}
+		addAggregateColumns();
+	}
+	
+	/**
+	 * In the query rewrite involving aggregates, add the columns for
+	 * aggregation.
+	 *
+	 * @see #addNewColumnsForAggregation
+	 */
+	private void addAggregateColumns() throws StandardException
+	{
+		DataDictionary			dd = getDataDictionary();
 		AggregateNode	aggregate = null;
 		ColumnReference	newColumnRef;
-		ResultColumn	rcBottom;
 		ResultColumn	newRC;
-		ResultColumn	gbRC;
 		ResultColumn	tmpRC;
 		ResultColumn	aggInputRC;
 		ResultColumnList bottomRCL  = childResult.getResultColumns();
@@ -378,84 +460,21 @@
 		int				aggregatorVColId;
 		int				aggInputVColId;
 		int				aggResultVColId;
-
-		LanguageFactory lf = getLanguageConnectionContext().getLanguageFactory();
-		dd = getDataDictionary();
-		aggInfo = new AggregatorInfoList();
-
-		/*
-		** Get a list of all column references in the
-		** parent RCL, skipping (not going below) AggregateNodes
-		*/
-		CollectNodesVisitor getUnaggVisitor = new CollectNodesVisitor(ColumnReference.class, AggregateNode.class);
-		parent.getResultColumns().accept(getUnaggVisitor);
-		Vector colRefVector = getUnaggVisitor.getList();
-
-		/*
-		** Walk the list of unaggregated column references
-		** and push them down.
-		*/
-		int crvSize = colRefVector.size();
-		for (int index = 0; index < crvSize; index++)
-		{
-			ColumnReference origColumnRef = (ColumnReference) colRefVector.elementAt(index);
-			newColumnRef = (ColumnReference)origColumnRef.getClone();
-
-			/*
-			** Put the column reference in the bottom PR.
-			*/
-			newRC = (ResultColumn) getNodeFactory().getNode(
-									C_NodeTypes.RESULT_COLUMN,
-									"##UnaggColumn",
-									newColumnRef,
-									getContextManager());
-			newRC.setExpression(newColumnRef);
-			bottomRCL.addElement(newRC);
-			newRC.markGenerated();
-			newRC.bindResultColumnToExpression();
-			newRC.setVirtualColumnId(bottomRCL.size());
-
-			/*
-			** Reset the group by column position
-			*/
-			if (groupingList != null) 
-			{
-				GroupByColumn	gbColumn;
-				if ((gbColumn = 
-						groupingList.containsColumnReference(newColumnRef)) 
-					!= null)
-				{
-					gbColumn.setColumnPosition(bottomRCL.size());
-				}
-			}
 		
-			/*
-			** Add the column to the group by list
-			*/
-			gbRC = getColumnReference(newRC, dd);
-			groupByRCL.addElement(gbRC);
-			gbRC.markGenerated();
-			gbRC.bindResultColumnToExpression();
-			gbRC.setVirtualColumnId(groupByRCL.size());
-	
-			/*
-			** Reset the original node to point to the
-			** Group By result set.
-			*/
-			origColumnRef.setSource(gbRC);
-		}
 		/*
-		** Now process all of the aggregates.  Replace
-		** every aggregate with an RC.  We toss out
-		** the list of RCs, we need to get each RC
-		** as we process its corresponding aggregate.
-		*/
+		 ** Now process all of the aggregates.  Replace
+		 ** every aggregate with an RC.  We toss out
+		 ** the list of RCs, we need to get each RC
+		 ** as we process its corresponding aggregate.
+		 */
+		LanguageFactory lf = getLanguageConnectionContext().getLanguageFactory();
+		
 		ReplaceAggregatesWithCRVisitor replaceAggsVisitor = 
 			new ReplaceAggregatesWithCRVisitor(
 					(ResultColumnList) getNodeFactory().getNode(
-										C_NodeTypes.RESULT_COLUMN_LIST,
-										getContextManager()),
-					((FromTable) childResult).getTableNumber());
+							C_NodeTypes.RESULT_COLUMN_LIST,
+							getContextManager()),
+				((FromTable) childResult).getTableNumber());
 		parent.getResultColumns().accept(replaceAggsVisitor);
 
 		/*
@@ -471,10 +490,10 @@
 			** bottom project restrict.
 			*/
 			newRC = (ResultColumn) getNodeFactory().getNode(
-										C_NodeTypes.RESULT_COLUMN,
-										"##aggregate result",
-										aggregate.getNewNullResultExpression(),
-										getContextManager());
+					C_NodeTypes.RESULT_COLUMN,
+					"##aggregate result",
+					aggregate.getNewNullResultExpression(),
+					getContextManager());
 			newRC.markGenerated();
 			newRC.bindResultColumnToExpression();
 			bottomRCL.addElement(newRC);
@@ -488,19 +507,19 @@
 			** ReplaceAggregatesWithColumnReferencesVisitor()
 			*/
 			newColumnRef = (ColumnReference) getNodeFactory().getNode(
-												C_NodeTypes.COLUMN_REFERENCE,
-												newRC.getName(),
-												null,
-												getContextManager());
+					C_NodeTypes.COLUMN_REFERENCE,
+					newRC.getName(),
+					null,
+					getContextManager());
 			newColumnRef.setSource(newRC);
 			newColumnRef.setType(newRC.getExpressionType());
 			newColumnRef.setNestingLevel(this.getLevel());
 			newColumnRef.setSourceLevel(this.getLevel());
 			tmpRC = (ResultColumn) getNodeFactory().getNode(
-								C_NodeTypes.RESULT_COLUMN,
-								newRC.getColumnName(),
-								newColumnRef,
-								getContextManager());
+					C_NodeTypes.RESULT_COLUMN,
+					newRC.getColumnName(),
+					newColumnRef,
+					getContextManager());
 			tmpRC.markGenerated();
 			tmpRC.bindResultColumnToExpression();
 			groupByRCL.addElement(tmpRC);
@@ -559,8 +578,8 @@
 			** to this agg if it is a user agg.
 			*/
 			aggRCL = (ResultColumnList) getNodeFactory().getNode(
-											C_NodeTypes.RESULT_COLUMN_LIST,
-											getContextManager());
+					C_NodeTypes.RESULT_COLUMN_LIST,
+					getContextManager());
 			aggRCL.addElement(aggInputRC);
 
 			/*
@@ -568,14 +587,14 @@
 			** so we have to subtract 1.
 			*/
 			aggInfo.addElement(new AggregatorInfo(
-							aggregate.getAggregateName(),
-							aggregate.getAggregatorClassName(),
-							aggInputVColId - 1,			// aggregate input column
-							aggResultVColId -1,			// the aggregate result column
-							aggregatorVColId - 1,		// the aggregator column	
-							aggregate.isDistinct(),
-							lf.getResultDescription(aggRCL.makeResultDescriptors(), "SELECT")
-						));
+					aggregate.getAggregateName(),
+					aggregate.getAggregatorClassName(),
+					aggInputVColId - 1,			// aggregate input column
+					aggResultVColId -1,			// the aggregate result column
+					aggregatorVColId - 1,		// the aggregator column	
+					aggregate.isDistinct(),
+					lf.getResultDescription(aggRCL.makeResultDescriptors(), "SELECT")
+			));
 		}
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java Fri Aug 25 20:55:39 2006
@@ -354,4 +354,13 @@
 		
 		return returnNode;
 	}
+        
+	/**
+	 * {@inheritDoc}
+	 */
+    protected boolean isEquivalent(ValueNode o)
+    {
+    	// anything in the java domain is not equiavlent.
+    	return false;
+    }
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ParameterNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ParameterNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ParameterNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ParameterNode.java Fri Aug 25 20:55:39 2006
@@ -485,4 +485,12 @@
 	{
 		return true;
 	}
+
+    /**
+     * @inheritDoc
+     */
+    protected boolean isEquivalent(ValueNode o)
+    {
+    	return false;
+    }
 }

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?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- 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 Aug 25 20:55:39 2006
@@ -1433,8 +1433,9 @@
 
   		if (isAutoincrement())
   			newResultColumn.setAutoincrement();
-		
-		return newResultColumn;
+  		if (isGroupingColumn()) 
+  			newResultColumn.markAsGroupingColumn();
+  		return newResultColumn;
 	}
 
 	/**
@@ -1592,6 +1593,12 @@
   	{
   		autoincrement = true;
   	}
+        
+        public boolean isGroupingColumn()
+        {
+        	return isGroupingColumn;
+        }
+        
 	/**
 	 * @exception StandardException		Thrown on error
 	 */
@@ -1815,6 +1822,18 @@
 		// reference nor a FromBaseTable beneath it--for example,
 		// if it is of type BaseColumnNode. 
 		return -1;
+	}
+	
+	public boolean isEquivalent(ValueNode o) throws StandardException 
+	{
+        if (o.getNodeType() == getNodeType()) 
+        {                
+        	ResultColumn other = (ResultColumn)o;
+        	if (expression != null) {
+        		return expression.isEquivalent(other.expression);
+        	}
+        }
+        return false;
 	}
 
 }

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=437070&r1=437069&r2=437070&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 Fri Aug 25 20:55:39 2006
@@ -282,16 +282,6 @@
 	}
 
 	/**
-	 * Return the groupByList for this SelectNode.
-	 *
-	 * @return GroupByList	The groupByList for this SelectNode.
-	 */
-	public GroupByList getGroupByList()
-	{
-		return groupByList;
-	}
-
-	/**
 	 * Find colName in the result columns and return underlying columnReference.
 	 * Note that this function returns null if there are more than one FromTable
 	 * for this SelectNode and the columnReference needs to be directly under
@@ -612,14 +602,14 @@
 						"Unexpected Aggregate vector generated by Group By clause");
 			}
 		}
-
 		/* If ungrouped query with aggregates in SELECT list, verify
 		 * that all result columns are valid aggregate expressions -
 		 * no column references outside of an aggregate.
 		 * If grouped query with aggregates in SELECT list, verify that all
-		 * result columns are either grouping columns or valid 
-		 * grouped aggregate expressions - the only column references allowed
-		 * outside of an aggregage are columns in the group by list.
+		 * result columns are either grouping expressions or valid
+		 * grouped aggregate expressions - the only column references
+		 * allowed outside of an aggregate are columns in expressions in 
+		 * the group by list.
 		 */
 		if (groupByList != null || selectAggregates.size() > 0)
 		{
@@ -627,7 +617,7 @@
   			VerifyAggregateExpressionsVisitor visitor = 
   				new VerifyAggregateExpressionsVisitor(groupByList);
 			resultColumns.accept(visitor);
-		}
+		}       
 
 		/*
 		** RESOLVE: for now, only one distinct aggregate is supported
@@ -966,6 +956,15 @@
 								   wherePredicates);
 		}
 
+		/* Preprocess the group by list too. We need to compare 
+		 * expressions in the group by list with the select list and we 
+		 * can't rewrite one and not the other.
+		 */
+		if (groupByList != null)
+		{
+			groupByList.preprocess(numTables, fromList, whereSubquerys, wherePredicates);
+		}
+		
 		/* Pull apart the expression trees */
 		if (whereClause != null)
 		{

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SpecialFunctionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SpecialFunctionNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SpecialFunctionNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SpecialFunctionNode.java Fri Aug 25 20:55:39 2006
@@ -222,4 +222,14 @@
 			return "";
 		}
 	}
+        
+	protected boolean isEquivalent(ValueNode o)
+	{
+		if (isSameNodeType(o))
+		{
+			SpecialFunctionNode other = (SpecialFunctionNode)o;
+			return methodName.equals(other.methodName);
+		}
+		return false;
+	}
 }

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?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- 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 Aug 25 20:55:39 2006
@@ -2292,4 +2292,12 @@
 		}
 		setType(dts);
 	}
+        
+    /**
+     * {@inheritDoc}
+     */    
+    protected boolean isEquivalent(ValueNode o)
+    {
+    	return false;
+    }
 }

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubstituteExpressionVisitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubstituteExpressionVisitor.java?rev=437070&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubstituteExpressionVisitor.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubstituteExpressionVisitor.java Fri Aug 25 20:55:39 2006
@@ -0,0 +1,76 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.SubstituteExpressionVisitor
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to you under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+package org.apache.derby.impl.sql.compile;
+
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.sql.compile.Visitable;
+import org.apache.derby.iapi.sql.compile.Visitor;
+
+/**
+ * Replaces a <em>source</em> expression with a <em>target</em>
+ * expression.  
+ * 
+ * @author manish
+ */
+class SubstituteExpressionVisitor implements Visitor 
+{
+	private ValueNode source;
+	private ValueNode target;
+	private Class     skipOverClass;
+	
+	SubstituteExpressionVisitor(
+			ValueNode s, ValueNode t, Class skipThisClass)  
+	{
+		source = s;
+		target = t;
+		skipOverClass = skipThisClass;
+	}
+
+	public Visitable visit(Visitable node) throws StandardException 
+	{
+		if (!(node instanceof ValueNode))
+		{
+			return node;
+		}
+		
+		ValueNode nd = (ValueNode)node;
+		if (nd.isEquivalent(source)) 
+		{
+			return target;
+		} 
+		else 
+		{
+			return node;
+		}
+	}
+
+	public boolean stopTraversal() 
+	{
+		return false;
+	}
+
+	public boolean skipChildren(Visitable node) 
+	{
+		return (skipOverClass == null) ?
+				false:
+				skipOverClass.isInstance(node);
+	}
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubstituteExpressionVisitor.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java Fri Aug 25 20:55:39 2006
@@ -915,4 +915,17 @@
 	private DataTypeDescriptor getVarcharDescriptor() {
 		return new DataTypeDescriptor(TypeId.getBuiltInTypeId(Types.VARCHAR), true);
 	}
+        
+    protected boolean isEquivalent(ValueNode o) throws StandardException
+    {
+    	if (isSameNodeType(o)) 
+	{
+		TernaryOperatorNode other = (TernaryOperatorNode)o;
+    		return (other.methodName.equals(methodName)
+				&& other.receiver.isEquivalent(receiver)
+    				&& other.leftOperand.isEquivalent(leftOperand)
+    				&& other.rightOperand.isEquivalent(rightOperand));
+        }
+    	return false;
+    }
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnaryOperatorNode.java Fri Aug 25 20:55:39 2006
@@ -857,4 +857,22 @@
         mb.push(((Boolean)additionalArgs[0]).booleanValue());
         return 3;
     }
+    
+    /**
+     * @throws StandardException 
+     * {@inheritDoc}
+     */
+    protected boolean isEquivalent(ValueNode o) throws StandardException
+    {
+    	if (isSameNodeType(o)) 
+    	{
+		// the first condition in the || covers the case when 
+	    	// both operands are null.
+    		UnaryOperatorNode other = (UnaryOperatorNode)o;
+    		return (operator.equals(other.operator) && 
+			((operand == other.operand)|| 
+			 ((operand != null) && operand.isEquivalent(other.operand))));
+    	}
+    	return false;
+    }
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java Fri Aug 25 20:55:39 2006
@@ -1329,5 +1329,35 @@
 	{
 		return false;
 	}
+	
+	/**
+	 * Tests if this node is equivalent to the specified ValueNode. Two 
+	 * ValueNodes are considered equivalent if they will evaluate to the same
+	 * value during query execution. 
+	 * 
+	 * @param other the node to compare this ValueNode against.
+	 * @return <code>true</code> if the two nodes are equivalent, 
+	 * <code>false</code> otherwise.
+	 * 
+	 * @throws StandardException 
+	 */
+	protected abstract boolean isEquivalent(ValueNode other)
+		throws StandardException;
 
+	/**
+	 * Tests if this node is of the same type as the specified node as
+	 * reported by {@link QueryTreeNode#getNodeType()}.
+	 * 
+	 * @param other the node to compare this value node against. 
+	 * 
+	 * @return <code>true</code> if the two nodes are of the same type.  
+	 */
+	protected final boolean isSameNodeType(ValueNode other)
+	{
+		if (other != null) {
+			return other.getNodeType() == getNodeType();
+		}
+		return false;
+	}
+	
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VerifyAggregateExpressionsVisitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VerifyAggregateExpressionsVisitor.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VerifyAggregateExpressionsVisitor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VerifyAggregateExpressionsVisitor.java Fri Aug 25 20:55:39 2006
@@ -64,8 +64,8 @@
 	 *
 	 * @return me
 	 *
-	 * @exception StandardException on ColumnReferernce not
-	 *	in group by list, ValueNode or 
+	 * @exception StandardException on ColumnReference not
+	 * 	in group by list, ValueNode or	
 	 * 	JavaValueNode that isn't under an
 	 * 	aggregate
 	 */
@@ -81,11 +81,12 @@
 				throw StandardException.newException(SQLState.LANG_INVALID_COL_REF_NON_GROUPED_SELECT_LIST, cr.getSQLColumnName());
 			}
 
-			if (groupByList.containsColumnReference(cr) == null)
+			if (groupByList.findGroupingColumn(cr) == null)
 			{
-				throw StandardException.newException(SQLState.LANG_INVALID_COL_REF_GROUPED_SELECT_LIST, cr.getSQLColumnName());
+				throw StandardException.newException(SQLState.LANG_INVALID_GROUPED_SELECT_LIST);
 			}
 		} 
+		
 		/*
 		** Subqueries are only valid if they do not have
 		** correlations and are expression subqueries.  RESOLVE:
@@ -119,22 +120,34 @@
 							SQLState.LANG_INVALID_NON_GROUPED_SELECT_LIST :
 							SQLState.LANG_INVALID_GROUPED_SELECT_LIST);
 			}
+		} else if (node instanceof JavaToSQLValueNode) 
+		{
+			// disallow any expression which involves native java computation. 
+		    	// Not possible to consider java expressions for equivalence.
+			throw StandardException.newException( (groupByList == null) ?
+					SQLState.LANG_INVALID_NON_GROUPED_SELECT_LIST :
+						SQLState.LANG_INVALID_GROUPED_SELECT_LIST);
 		}
 
 		return node;
 	}
 
 	/**
-	 * Don't visit children under an aggregate
+	 * Don't visit children under an aggregate, subquery or any node which
+	 * is equivalent to any of the group by expressions.
 	 *
 	 * @param node 	the node to process
 	 *
 	 * @return true/false
+	 * @throws StandardException 
 	 */
-	public boolean skipChildren(Visitable node)
+	public boolean skipChildren(Visitable node) throws StandardException 
 	{
-		return (node instanceof AggregateNode) ||
-				(node instanceof SubqueryNode);
+		return ((node instanceof AggregateNode) ||
+				(node instanceof SubqueryNode) ||
+				(node instanceof ValueNode &&
+						groupByList != null 
+						&& groupByList.findGroupingColumn((ValueNode)node) != null));
 	}
 	
 	public boolean stopTraversal()

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VirtualColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VirtualColumnNode.java?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VirtualColumnNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/VirtualColumnNode.java Fri Aug 25 20:55:39 2006
@@ -288,4 +288,13 @@
         }
         return dtd;
     } // end of getTypeServices
+    
+    protected boolean isEquivalent(ValueNode o) throws StandardException
+    {
+    	if (isSameNodeType(o)) {
+    		VirtualColumnNode other = (VirtualColumnNode)o;
+    		return sourceColumn.isEquivalent(other.sourceColumn);
+    	}
+    	return false;
+    }
 }

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=437070&r1=437069&r2=437070&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 Fri Aug 25 20:55:39 2006
@@ -9408,15 +9408,26 @@
 void
 groupingColumnReference(GroupByList groupingCols) throws StandardException :
 {
-	ColumnReference colRef;
+        ValueNode columnExpression;
 }
 {
-	colRef = columnReference() 
+	columnExpression = additiveExpression(null, 0, false)
 	{
+		if (columnExpression.isParameterNode()) 
+	    	{
+			throw StandardException.newException(SQLState.LANG_SYNTAX_ERROR, "?");
+	    	}
+		if (columnExpression instanceof AggregateNode)
+		{
+			AggregateNode agNode = (AggregateNode)columnExpression;
+			throw StandardException.newException(
+				SQLState.LANG_AGGREGATE_IN_GROUPBY_LIST, 
+				agNode.getAggregateName());
+		}        
 		groupingCols.addGroupByColumn(
 			(GroupByColumn) nodeFactory.getNode(
 							C_NodeTypes.GROUP_BY_COLUMN,
-							colRef,
+							columnExpression,
 							getContextManager()));
 	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties Fri Aug 25 20:55:39 2006
@@ -633,20 +633,20 @@
 42Z99=String or Hex literal cannot exceed 64K.
 42Y16=No public static method ''{0}'' was found in class ''{1}''. The method might exist, but it is not public, or it is not static. 
 42846=Cannot convert types ''{0}'' to ''{1}''.
-42Y19=''{0}'' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
+#42Y19=''{0}'' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
 42Y22=Aggregate {0} cannot operate on type {1}.
 42Y23=Incorrect JDBC type info returned for column {0}.
 42Y24=View ''{0}'' is not updatable. (Views are currently not updatable.) 
 42Y25=''{0}'' is a system table.  Users are not allowed to modify the contents of this table.
-#42Y26=Parameters are not allowed in the WHEN clause of a trigger.
+42Y26=Aggregates are not allowed in the GROUP BY list.
 42Y27=Parameters are not allowed in the trigger action.
 42Y29=The SELECT list of a non-grouped query contains at least one invalid expression. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.
-42Y30=The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.  
+42Y30=The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.  
 42Y32=Aggregator class ''{0}'' for aggregate ''{1}'' on type {2} does not implement com.ibm.db2j.aggregates.Aggregator. 
 42Y33=Aggregate {0} contains one or more aggregates.
 42Y34=Column name ''{0}'' matches more than one result column in table ''{1}''.
 42Y35=Column reference ''{0}'' is invalid. When the SELECT list contains at least one aggregate then all entries must be valid aggregate expressions.  
-42Y36=Column reference ''{0}'' is invalid.  For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.  
+42Y36=Column reference ''{0}'' is invalid.  For a SELECT list with a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.  
 42Y37=''{0}'' is a Java primitive and cannot be used with this operator.
 42Y38=insertMode = replace is not permitted on an insert where the target table, ''{0}'', is referenced in the SELECT.
 # NOTE: The parameter to this message is a keyword used as a noun phrase.

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?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- 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 Aug 25 20:55:39 2006
@@ -858,17 +858,18 @@
 	String LANG_NO_METHOD_MATCHING_ALIAS                               = "42Y16";
 	// String LANG_DROP_SYSTEM_TABLE_ATTEMPTED                         = "42Y17"; -- replaced by 42X62
 	String LANG_INVALID_CAST                                           = "42846";
-	String LANG_AMBIGUOUS_GROUPING_COLUMN                              = "42Y19";
+    //	String LANG_AMBIGUOUS_GROUPING_COLUMN                              = "42Y19"; -- unused post 883.
 	//	String LANG_UNMATCHED_GROUPING_COLUMN                              =	//	"42Y20"; -- not used
 	String LANG_USER_AGGREGATE_BAD_TYPE                                = "42Y22";
 	String LANG_BAD_J_D_B_C_TYPE_INFO                                  = "42Y23";
 	String LANG_VIEW_NOT_UPDATEABLE                                    = "42Y24";
 	String LANG_UPDATE_SYSTEM_TABLE_ATTEMPTED                          = "42Y25";
-	//	String LANG_NO_PARAMS_IN_TRIGGER_WHEN                              = "42Y26"; -- not used.
+    	String LANG_AGGREGATE_IN_GROUPBY_LIST                              = "42Y26";
 	String LANG_NO_PARAMS_IN_TRIGGER_ACTION                            = "42Y27";
 	// String LANG_NO_TRIGGER_ON_SYSTEM_TABLE                             = "42Y28"; -- replaced by 42X62
 	String LANG_INVALID_NON_GROUPED_SELECT_LIST                        = "42Y29";
 	String LANG_INVALID_GROUPED_SELECT_LIST                            = "42Y30";
+	
 	String LANG_TOO_MANY_ELEMENTS                            = "54004";
 	String LANG_BAD_AGGREGATOR_CLASS2                                  = "42Y32";
 	String LANG_USER_AGGREGATE_CONTAINS_AGGREGATE                      = "42Y33";

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?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- 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 Aug 25 20:55:39 2006
@@ -5,24 +5,20 @@
 0 rows inserted/updated/deleted
 ij> insert into t2 values (1,1,1), (2,2,2);
 2 rows inserted/updated/deleted
-ij> -- group by position
+ij> -- group by constant. should compile but fail because
+-- it is not a valid grouping expression.
 select * from t1 group by 1;
-ERROR 42X01: Syntax error: Encountered "1" at line 2, column 27.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.  
 ij> -- column in group by list not in from list
 select a as d from t1 group by d;
 ERROR 42X04: Column 'D' 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 'D' is not a column in the target table.
 ij> -- column in group by list not in select list
 select a as b from t1 group by b;
-ERROR 42Y36: Column reference 'A' is invalid.  For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.  
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.  
 ij> select a from t1 group by b;
-ERROR 42Y36: Column reference 'A' is invalid.  For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.  
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.  
 ij> select a, char(b) from t1 group by a;
-ERROR 42Y36: Column reference 'B' is invalid.  For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.  
-ij> -- columns in group by list must be unique
-select a, b from t1 group by a, a;
-ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
-ij> select a, b from t1 group by a, t1.a;
-ERROR 42Y19: 'A' appears multiple times in the GROUP BY list. Columns in the GROUP BY list must be unambiguous.
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.  
 ij> -- cursor with group by is not updatable
 get cursor c1 as 'select a from t1 group by a for update';
 ERROR 42Y90: FOR UPDATE is not permitted in this type of statement.  
@@ -31,7 +27,7 @@
 ERROR 21000: Scalar subquery is only allowed to return a single row.
 ij> -- correlation on outer table
 select t2.a, (select b from t1 where t1.b = t2.b) from t1 t2 group by t2.a;
-ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.  
+ERROR 42Y30: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.  
 ij> -- having clause
 -- cannot contain column references which are not grouping columns
 select a from t1 group by a having c = 1;
@@ -40,7 +36,7 @@
 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
 select a from t1 group by ?;
-ERROR 42X01: Syntax error: Encountered "?" at line 2, column 27.
+ERROR 42X01: Syntax error: ?.
 ij> -- group by on long varchar type
 create table unmapped(c1 long varchar);
 0 rows inserted/updated/deleted

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=437070&r1=437069&r2=437070&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Fri Aug 25 20:55:39 2006
@@ -1,6 +1,7 @@
 jdbcapi/metadata.java
 jdbcapi/metadataMultiConn.java
 jdbcapi/odbc_metadata.java
+lang/GroupByExpressionTest.junit
 lang/AIjdbc.java
 lang/AggregateClassLoading.java
 lang/CharUTF8.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java?rev=437070&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByExpressionTest.java Fri Aug 25 20:55:39 2006
@@ -0,0 +1,490 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.GroupByExpressionTest
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Statement;
+
+import junit.extensions.TestSetup;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.BaseJDBCTestSetup;
+
+public class GroupByExpressionTest extends BaseJDBCTestCase
+{
+
+    private static String[][] TABLES = { 
+        {"test", "create table test (c1 int, c2 int, c3 int, c4 int)"},
+        {"coal", "create table coal (vc1 varchar(2), vc2 varchar(2))"},
+        {"alltypes", 
+            "create table alltypes (i int, s smallint, l bigint, " +
+            "c char(10), v varchar(50), lvc long varchar, " +
+            " d double precision, r real, " + 
+            " dt date, t time, ts timestamp, " +
+            " b char(2) for bit data, bv varchar(8) for bit data, " +
+            " lbv long varchar for bit data, dc decimal(5,2))"}};
+    private static String[][] FUNCTIONS = {
+        {"r", "create function r() returns double external name " +
+            "'java.lang.Math.random' language java parameter style java"}};
+    
+    /** 
+     * Basic test case. Checks functionality with simple arithmetic expressions
+     */
+    public void testSimpleExpressions() throws Exception
+    {
+        verifyQueryResults(
+                "Q1",
+                "select c1,c2,sum(c3) from test group by c2,c1",
+                new int[][] {
+                        {1,10,100},
+                        {2,10,100},
+                        {1,11,100},
+                        {2,11,202}});
+
+        verifyQueryResults(
+                "Q2",
+                "select c1+c2, sum(c3) from test group by c1,c2",
+                new int[][] {
+                        {11, 100},
+                        {12, 100}, 
+                        {12, 100}, 
+                        {13, 202}});
+        verifyQueryResults(
+                "Q3",
+                "select c1+c2, sum(c3) from test group by c1+c2",
+                new int[][] {
+                        {11, 100}, 
+                        {12, 200}, 
+                        {13, 202}});
+        verifyQueryResults(
+                "Q4",
+                "select (c1+c2)+1, sum(c3) from test group by c1+c2",
+                new int[][] {
+                        {12, 100}, 
+                        {13, 200}, 
+                        {14, 202}});
+        verifyQueryResults(
+                "Q5",
+                "select (c1+c2), sum(c3)+(c1+c2) from test group by c1+c2",
+                new int[][] {
+                        {11,111},
+                        {12,212},
+                        {13,215}});
+        verifyQueryResults(
+                "Q6",
+                "select c2-c1, c1+c2, count(*) from test group by c1+c2, c2-c1",
+                new int[][] {
+                        {9,11,1},
+                        {8,12,1},
+                        {10,12,1},
+                        {9,13,2}});
+    }
+    
+    
+    public void testSubSelect() throws Exception
+    {
+        /* query with a group by on a subselect */
+        verifyQueryResults(
+                "Q1",
+                "select a+1, sum(b) from (select c1+1  a , c2+1 b from test) t group by a",
+                new int[][] {
+                        {3,23}, {4,35}});
+        
+        verifyQueryResults(
+                "Q2",
+                "select a+1, sum(b) from (select c1+1  a , c2+1 b from test) t group by a+1",
+                new int[][] {
+                        {3,23}, {4,35}});
+        
+        verifyQueryResults(
+                "Q3",
+                "select b/2,sum(a) from " +
+                "(select c1+1 a, max(c2) b from test group by c1+1) t " +
+                "group by b/2",
+                new int[][] {{5,5}});
+    }
+    
+
+    public void testMiscExpressions() throws Exception
+    {
+        // cast
+        verifyQueryResults(
+                "cast",
+                "select (cast (c1 as char(2))), count(*) from test " +
+                " group by (cast (c1 as char(2)))",
+                new Object[][] {
+                        {"1 ", new Integer(2)}, 
+                        {"2 ", new Integer(3)}});
+        
+        // coalesce
+        verifyQueryResults(
+                "coalesce",
+                "select (coalesce(vc1,vc2)), count(*) from coal " +
+                " group by (coalesce(vc1,vc2))",
+                new Object[][] {{"1", new Integer(2)}, {"2", new Integer(1)}});
+        // concat
+        verifyQueryResults(
+                "concat",
+                "select c||v, count(*) from alltypes group by c||v",
+                new Object[][] {
+                        {"duplicate noone is here", new Integer(1)},
+                        {"duplicate this is duplicated", new Integer(13)},
+                        {"goodbye   this is duplicated", new Integer(1)}});
+        // conditional.
+        verifyQueryResults(
+                "cond",
+                "select (case when c1 = 1 then 2 else 1 end), sum(c2) from test " +
+                " group by (case when c1 = 1 then 2 else 1 end)",
+                new int[][] {{1,32}, {2, 21}});
+        
+        // length
+        verifyQueryResults(
+                "length",
+                "select length(v), count(*) from alltypes group by length(v)",
+                new int[][] {{13,1},{18,14}});
+        
+        // current time. ignore the value of current time. 
+        // just make sure we can group by it and get the right results for
+        // the aggregate.
+        verifyQueryResults(
+                "current_time",
+                "select co from " +
+                "(select current_time ct, count(*) co from test t1, test t2, test t3 group by current_time) t",
+                new int[][] {{125}});
+        // concat + substr
+        verifyQueryResults(
+                "concat+substr",
+                "select substr(c||v, 1, 4), count(*) from alltypes group by substr(c||v, 1, 4)",
+                new Object[][] {
+                        {"dupl", new Integer(14)},
+                        {"good", new Integer(1)}});
+
+    }
+    
+    public void testExtractOperator() throws Exception
+    {
+        verifyQueryResults(
+                "year",
+                "select year(dt), count(*) from alltypes group by year(dt)",
+                new int[][] {{1992, 15}});
+        verifyQueryResults(
+                "month",
+                "select month(dt), count(*) from alltypes group by month(dt)",
+                new int[][] {{1,5},{2,6},{3,4}});
+        verifyQueryResults(
+                "day",
+                "select day(dt), count(*) from alltypes group by day(dt)",
+                new int[][] {{1,3},{2,3},{3,3},{4,3},{5,2},{6,1}});
+        verifyQueryResults(
+                "hour",
+                "select hour(t), count(*) from alltypes group by hour(t)",
+                new int[][] {{12, 15}});
+        verifyQueryResults(
+                "hour2",
+                "select hour(ts), count(*) from alltypes group by hour(ts)",
+                new int[][] {{12,15}});
+        verifyQueryResults(
+                "minute",
+                "select minute(ts), count(*) from alltypes group by minute(ts)",
+                new int[][] {{30,14},{55,1}});
+        verifyQueryResults(
+                "second",
+                "select second(t), count(*) from alltypes group by second(t)",
+                new int[][]{
+                        {30,2},{31,1},{32,1},{33,1},{34,1},{35,1},
+                        {36,1},{37,1},{38,1},{39,1},{40,1},{41,1},
+                        {42,1},{55,1}});
+    }
+    
+    /**
+     * Check that duplicate columns are now allowed in group by's. Earlier
+     * (pre 883), derby would flag an error.
+     *
+     */
+    public void testDuplicateColumns() throws Exception
+    {
+        verifyQueryResults(
+                "Q1",
+                "select c1, sum(c2) from test group by c1,c1",
+                new int[][]{ {1,21}, {2,32}});
+        
+        verifyQueryResults(
+                "Q2",
+                "select c1, c1, sum(c2) from test group by c1,c1",
+                new int[][]{ {1,1,21}, {2,2,32}});
+    }
+    /**
+     * Negative tests. These queries should not compile at all.
+     */
+    public void testNegative()
+    {
+        // disallow java function 
+        assertCompileError(
+                "42Y30", "select r(), count(*) from test group by r()");
+
+        // invalid grouping expression.
+        assertCompileError(
+                "42Y30", "select c1+1, count(*) from test group by c1+2");
+        
+        // again invalid grouping expression because cast type is different.
+        assertCompileError(
+                "42Y30", "select (cast (c as char(2))), count(*) " +
+                " from alltypes group by (cast (c as char(3)))");
+
+        // same column name, same table but different tablenumber in the query
+        assertCompileError(
+                "42Y30", 
+                "select t1.c1, count(*) from test t1, test t2 " + 
+                " group by t2.c1");
+        // ternary operator, not equivalent test.
+        assertCompileError(
+                "42Y30",
+                "select substr(c, 3, 4) from alltypes group by substr(v, 3, 4)");
+        // aggregates in group by list.
+        assertCompileError(
+                "42Y26",
+                "select 1, max(c1) from test group by max(c1)");
+    }
+    /* --------------------- begin helper methods -------------------- */
+    
+    private Object[] intRow(int[] expected)
+    {
+        Object[] arr = new Object[expected.length];
+        for (int i = 0; i < expected.length; i++)
+        {
+            arr[i] = new Integer(expected[i]);
+        }
+        return arr;
+    }
+    
+    private void verifyQueryResults(
+            String assertString, String query, Object[][] golden)
+        throws Exception
+    {
+
+        PreparedStatement ps = prepareStatement(query);
+        ResultSet rs = ps.executeQuery();
+        for (int i = 0; i < golden.length; i++)
+        {
+            assertTrue(
+                    "Not enough rows. Expected " + golden.length + 
+                    " but found " + i, 
+                    rs.next());
+
+            assertRow(assertString + ":Row:" + i, rs, golden[i]);
+        }
+        rs.close();
+        ps.close();
+    }
+    
+    private void verifyQueryResults(
+            String assertString, String query, int[][] golden) 
+        throws Exception
+    {
+        PreparedStatement ps = prepareStatement(query);
+        ResultSet rs = ps.executeQuery();
+        for (int i = 0; i < golden.length; i++)
+        {
+            assertTrue(
+                "Not enough rows. Expected " + golden.length + 
+                " but found " + i, 
+                rs.next());
+            assertRow(assertString + ":Row:" + i, rs, intRow(golden[i]));
+        }
+        assertFalse("more rows than expected", rs.next());
+        rs.close();
+        ps.close();
+    }
+    
+    public void    assertRow(
+            String assertString, ResultSet rs, Object[] expectedRow)
+        throws Exception
+    {
+        int        count = expectedRow.length;
+        
+        for ( int i = 0; i < count; i++ )
+        {
+            int    columnNumber = i + 1;
+            Object expected = expectedRow[i];
+            Object actual = rs.getObject(columnNumber);
+            assertEquals(assertString + ":Column number ", expected, actual);
+        }
+    }
+
+    /* ------------------- end helper methods  -------------------------- */ 
+    public GroupByExpressionTest(String name)
+    {
+        super(name);
+    }
+    
+    public static Test suite()
+    {
+        TestSuite suite = new TestSuite();
+        suite.addTestSuite(GroupByExpressionTest.class);
+        
+        TestSetup wrapper = new BaseJDBCTestSetup(suite) { 
+            public void setUp() throws Exception
+            { 
+                Connection c = getConnection();
+                c.setAutoCommit(false);
+                Statement s = c.createStatement();
+                for (int i = 0; i < TABLES.length; i++) {
+                    s.execute(TABLES[i][1]);
+                }
+                for (int i = 0; i < FUNCTIONS.length; i++) {
+                    s.execute(FUNCTIONS[i][1]);
+                }
+                
+                s.execute("insert into test values (1, 10, 100, 1000)");
+                s.execute("insert into test values (1, 11, 100, 1001)");
+                s.execute("insert into test values (2, 10, 100, 1000)");
+                s.execute("insert into test values (2, 11, 101, 1001)");
+                s.execute("insert into test values (2, 11, 101, 1000)");
+                
+                s.execute("insert into coal values ('1', '2')");
+                s.execute("insert into coal values (null, '2')");
+                s.execute("insert into coal values ('1', null)");
+                
+                s.execute(
+                    "insert into alltypes values (0, 100, 1000000, " +
+                    "'duplicate', 'this is duplicated', 'also duplicated', " +
+                    "200.0e0, 200.0e0, " + 
+                    " date('1992-01-01'), time('12:30:30'), " + 
+                    " timestamp('1992-01-01 12:30:30'), " +
+                    "X'12af', x'0000111100001111', X'1234', 111.11) ");
+                s.execute(
+                    "insert into alltypes values (0, 100, 1000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 200.0e0, "  +
+                    " date('1992-01-02'), time('12:30:31'), " + 
+                    "timestamp('1992-01-02 12:30:31'), " +
+                    " X'12af', X'0000111100001111', X'1234', 111.11)");
+                s.execute(
+                    "insert into alltypes values (1, 100, 1000000, " +
+                    "'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 200.0e0, " +  
+                    " date('1992-01-03'), time('12:30:32'), " + 
+                    " timestamp('1992-01-03 12:30:32'), " +
+                    " X'12af', X'0000111100001111', X'1234', 111.11)");
+                s.execute(
+                    "insert into alltypes values (0, 200, 1000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 200.0e0, " +
+                    " date('1992-01-04'), time('12:30:33'), " + 
+                    " timestamp('1992-01-04 12:30:33'), " +
+                    " X'12af', X'0000111100001111', X'1234', 222.22)");
+                s.execute(
+                    "insert into alltypes values (0, 100, 2000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 200.0e0,  " +
+                    " date('1992-01-05'), time('12:30:34'), " + 
+                    " timestamp('1992-01-05 12:30:34'), " +
+                    " X'12af', X'0000111100001111', X'1234', 222.22)");
+                s.execute("insert into alltypes values (0, 100, 1000000, " +
+                    " 'goodbye', 'this is duplicated', 'also duplicated', "  +
+                    " 200.0e0, 200.0e0, " +
+                    " date('1992-02-01'), time('12:30:35'), " + 
+                    " timestamp('1992-02-01 12:30:35'), " +
+                    " X'12af', X'0000111100001111', X'1234', 111.11)");
+                s.execute("insert into alltypes values (0, 100, 1000000, " +
+                    " 'duplicate', 'noone is here', 'jimmie noone was here', " +
+                    " 200.0e0, 200.0e0, " +
+                    " date('1992-02-02'), time('12:30:36'), " + 
+                    " timestamp('1992-02-02 12:30:36'), " +
+                    " X'12af', X'0000111100001111', X'1234', 111.11) ");
+                s.execute("insert into alltypes values (0, 100, 1000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 200.0e0, " +
+                    " date('1992-02-03'), time('12:30:37'), " + 
+                    " timestamp('1992-02-03 12:30:37'), " +
+                    " X'12af', X'0000111100001111', X'1234', 111.11)");
+                s.execute("insert into alltypes values (0, 100, 1000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 100.0e0, 200.0e0, " +
+                    " date('1992-02-04'), time('12:30:38'), " + 
+                    " timestamp('1992-02-04 12:30:38'), " +
+                    " X'12af', X'0000111100001111', X'1234', 111.11) ");
+                s.execute("insert into alltypes values (0, 100, 1000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 100.0e0, " +
+                    " date('1992-02-05'), time('12:30:39'), " + 
+                    " timestamp('1992-02-05 12:30:39'), " +
+                    " X'12af', X'0000111100001111', X'1234', 111.11) ");
+                s.execute("insert into alltypes values (0, 100, 1000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 200.0e0, " +
+                    " date('1992-02-06'), time('12:30:40'), " + 
+                    " timestamp('1992-02-06 12:30:40'), " +
+                    " X'12af', X'0000111100001111', X'1234', 111.11) ");
+                s.execute("insert into alltypes values (0, 100, 1000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 200.0e0, " +
+                    " date('1992-03-01'), time('12:55:55'), " + 
+                    "timestamp('1992-03-01 12:30:30'), " +
+                    " X'12af', X'0000111100001111', X'1234', 111.11) ");
+                s.execute("insert into alltypes values (0, 100, 1000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 200.0e0, " +
+                    " date('1992-03-02'), time('12:30:30'), " + 
+                    "timestamp('1992-03-02 12:55:55'), " +
+                    " X'12af', X'0000111100001111', X'1234', 111.11) ");
+                s.execute("insert into alltypes values (0, 100, 1000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 200.0e0, " +
+                    " date('1992-03-03'), time('12:30:41'), " + 
+                    " timestamp('1992-03-03 12:30:41'), " +
+                    " X'ffff', X'0000111100001111', X'1234', 111.11) ");
+                s.execute("insert into alltypes values (0, 100, 1000000, " +
+                    " 'duplicate', 'this is duplicated', 'also duplicated', " +
+                    " 200.0e0, 200.0e0, " +
+                    " date('1992-03-04'), time('12:30:42'), " + 
+                    " timestamp('1992-03-04 12:30:42'), " +
+                    " X'12af', X'1111111111111111', X'1234', 111.11) " );
+                
+                s.close();
+                c.commit();
+                c.close();
+              }
+            protected void tearDown() throws Exception 
+            { 
+                Connection c = getConnection();
+                c.setAutoCommit(false);
+                Statement s = c.createStatement();
+                
+                for (int i = 0; i < TABLES.length; i++) {
+                    s.execute("drop table " + TABLES[i][0]);
+                }
+                for (int i = 0; i < FUNCTIONS.length; i++) {
+                    s.execute("drop function " + FUNCTIONS[i][0]);
+                }
+                
+                c.commit();
+                super.tearDown();
+            }
+            }; 
+        return wrapper;
+    }
+}



Mime
View raw message