You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by dj...@apache.org on 2006/08/26 05:55:42 UTC
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/...
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;
+ }
+}