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 rh...@apache.org on 2012/08/31 18:55:20 UTC
svn commit: r1379505 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: rhillegas
Date: Fri Aug 31 16:55:20 2012
New Revision: 1379505
URL: http://svn.apache.org/viewvc?rev=1379505&view=rev
Log:
DERBY-672: Add support for DISTINCT user defined aggregates in the SELECT list.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UserDefinedAggregator.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UserDefinedAggregatesTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java?rev=1379505&r1=1379504&r2=1379505&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java Fri Aug 31 16:55:20 2012
@@ -29,7 +29,9 @@ import org.apache.derby.iapi.services.lo
import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.sql.dictionary.AliasDescriptor;
import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
import org.apache.derby.iapi.sql.compile.CompilerContext;
import org.apache.derby.iapi.sql.compile.C_NodeTypes;
@@ -54,6 +56,7 @@ import org.apache.derby.impl.sql.compile
import org.apache.derby.impl.sql.compile.MaxMinAggregateDefinition;
import org.apache.derby.impl.sql.compile.SumAvgAggregateDefinition;
+import java.util.List;
import java.util.Vector;
/**
@@ -67,6 +70,7 @@ public class AggregateNode extends Unary
private boolean distinct;
private AggregateDefinition uad;
+ private TableName userAggregateName;
private StringBuffer aggregatorClassName;
private String aggregateDefinitionClassName;
private Class aggregateDefinitionClass;
@@ -108,10 +112,14 @@ public class AggregateNode extends Unary
super.init(operand);
this.aggregateName = (String) aggregateName;
- if (uadClass instanceof AggregateDefinition)
+ if ( uadClass instanceof UserAggregateDefinition )
{
- this.uad = (AggregateDefinition) uadClass;
- this.aggregateDefinitionClass = uad.getClass();
+ setUserDefinedAggregate( (UserAggregateDefinition) uadClass );
+ this.distinct = ((Boolean) distinct).booleanValue();
+ }
+ else if ( uadClass instanceof TableName )
+ {
+ this.userAggregateName = (TableName) uadClass;
this.distinct = ((Boolean) distinct).booleanValue();
}
else
@@ -123,10 +131,18 @@ public class AggregateNode extends Unary
{
this.distinct = ((Boolean) distinct).booleanValue();
}
+
+ this.aggregateDefinitionClassName = aggregateDefinitionClass.getName();
}
- this.aggregateDefinitionClassName = aggregateDefinitionClass.getName();
}
+ /** initialize fields for user defined aggregate */
+ private void setUserDefinedAggregate( UserAggregateDefinition userAgg )
+ {
+ this.uad = userAgg;
+ this.aggregateDefinitionClass = uad.getClass();
+ this.aggregateDefinitionClassName = aggregateDefinitionClass.getName();
+ }
/**
* Replace aggregates in the expression tree with a ColumnReference to
@@ -268,12 +284,38 @@ public class AggregateNode extends Unary
Vector aggregateVector)
throws StandardException
{
+ DataDictionary dd = getDataDictionary();
DataTypeDescriptor dts = null;
ClassFactory cf;
cf = getClassFactory();
classInspector = cf.getClassInspector();
+ if ( userAggregateName != null )
+ {
+ userAggregateName.bind( dd );
+
+ AliasDescriptor ad = resolveAggregate
+ (
+ dd,
+ getSchemaDescriptor( userAggregateName.getSchemaName(), true ),
+ userAggregateName.getTableName()
+ );
+
+ if ( ad == null )
+ {
+ throw StandardException.newException
+ (
+ SQLState.LANG_OBJECT_NOT_FOUND,
+ AliasDescriptor.getAliasType( AliasInfo.ALIAS_TYPE_AGGREGATE_AS_CHAR ),
+ userAggregateName.getTableName()
+ );
+ }
+
+ setUserDefinedAggregate( new UserAggregateDefinition( ad ) );
+ aggregateName = ad.getJavaClassName();
+ }
+
instantiateAggDef();
/* Add ourselves to the aggregateVector before we do anything else */
@@ -370,6 +412,21 @@ public class AggregateNode extends Unary
return this;
}
+ /**
+ * Resolve a user-defined aggregate.
+ */
+ public static AliasDescriptor resolveAggregate
+ ( DataDictionary dd, SchemaDescriptor sd, String rawName )
+ throws StandardException
+ {
+ java.util.List list = dd.getRoutineList
+ ( sd.getUUID().toString(), rawName, AliasInfo.ALIAS_NAME_SPACE_AGGREGATE_AS_CHAR );
+
+ if ( list.size() > 0 ) { return (AliasDescriptor) list.get( 0 ); }
+
+ return null;
+ }
+
/*
** Make sure the aggregator class is ok
*/
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java?rev=1379505&r1=1379504&r2=1379505&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java Fri Aug 31 16:55:20 2012
@@ -200,7 +200,7 @@ public class StaticMethodCallNode extend
// The field methodName is used by resolveRoutine and
// is set to the name of the routine (procedureName.getTableName()).
- resolveRoutine(fromList, subqueryList, aggregateVector, sd);
+ resolveRoutine( fromList, subqueryList, aggregateVector, sd );
if ( (ad != null) && (ad.getAliasType() == AliasInfo.ALIAS_TYPE_AGGREGATE_AS_CHAR) )
{
@@ -684,39 +684,13 @@ public class StaticMethodCallNode extend
}
}
- if ( ad == null )
+ if ( (ad == null) && (methodParms.length == 1) )
{
- resolveAggregate( fromList, subqueryList, aggregateVector, sd );
+ ad = AggregateNode.resolveAggregate( getDataDictionary(), sd, methodName );
}
}
/**
- * Resolve a user-defined aggregate.
- *
- * @param fromList
- * @param subqueryList
- * @param aggregateVector
- * @param sd
- * @throws StandardException
- */
- private void resolveAggregate
- (FromList fromList, SubqueryList subqueryList, Vector aggregateVector, SchemaDescriptor sd)
- throws StandardException
- {
- // aggregates have only 1 argument
- if ( methodParms.length != 1 ) { return; }
-
- java.util.List list = getDataDictionary().getRoutineList
- ( sd.getUUID().toString(), methodName, AliasInfo.ALIAS_NAME_SPACE_AGGREGATE_AS_CHAR );
-
- for ( int i = 0; i < list.size(); i++ )
- {
- ad = (AliasDescriptor) list.get( i );
- break;
- }
- }
-
- /**
* Add code to set up the SQL session context for a stored
* procedure or function which needs a nested SQL session
* context (only needed for those which can contain SQL).
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=1379505&r1=1379504&r2=1379505&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 31 16:55:20 2012
@@ -1155,6 +1155,57 @@ public class SQLParser
/**
+ * Return true if a builtin aggregate follows.
+ */
+ private boolean builtinAggregateFollows()
+ {
+ switch (getToken(1).kind)
+ {
+ case MAX:
+ case AVG:
+ case MIN:
+ case SUM:
+ case COUNT:
+ // This is a built-in aggregate
+ return true;
+
+ default:
+ return false;
+ }
+ }
+
+ /**
+ * Return true if the following tokens are the start of a
+ * distinct user defined aggregate invocation. These have
+ * the form
+ *
+ * aggName( distinct
+ *
+ * or
+ *
+ * schemaName.aggName( distinct
+ *
+ */
+ private boolean distinctUDAFollows()
+ {
+ if ( builtinAggregateFollows() ) { return false; }
+
+ if (
+ (getToken(2).kind == LEFT_PAREN) &&
+ (getToken(3).kind == DISTINCT)
+ )
+ { return true; }
+ else if (
+ (getToken(2).kind == PERIOD) &&
+ (getToken(4).kind == LEFT_PAREN) &&
+ (getToken(5).kind == DISTINCT)
+ )
+ { return true; }
+
+ return false;
+ }
+
+ /**
* Determine whether the next sequence of tokens can be the beginning of a
* window or aggregate function.
* @return TRUE iff the next set of tokens is the beginning of a
@@ -1162,6 +1213,8 @@ public class SQLParser
*/
private boolean windowOrAggregateFunctionFollows()
{
+ if ( distinctUDAFollows() ) { return false; }
+
boolean retval = false;
switch (getToken(1).kind) {
@@ -7871,6 +7924,28 @@ void methodCallParameterList(Vector para
ValueNode
routineInvocation() throws StandardException :
{
+ ValueNode valueNode;
+}
+{
+ LOOKAHEAD( { !distinctUDAFollows() } )
+ valueNode = routineExpression()
+ {
+ return valueNode;
+ }
+|
+ LOOKAHEAD( { distinctUDAFollows() } )
+ valueNode = distinctUDA()
+ {
+ return valueNode;
+ }
+}
+
+/*
+ * <A NAME="routineExpression">routineExpression</A>
+ */
+ValueNode
+routineExpression() throws StandardException :
+{
Vector parameterList = new Vector();
TableName routineName;
MethodCallNode methodNode;
@@ -7900,6 +7975,36 @@ routineInvocation() throws StandardExcep
}
/*
+ * <A NAME="distinctUDA">distinctUDA</A>
+ */
+ValueNode
+distinctUDA() throws StandardException :
+{
+ ValueNode value;
+ TableName aggName;
+}
+{
+ aggName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
+ <LEFT_PAREN>
+ <DISTINCT> value = additiveExpression(null, 0)
+ <RIGHT_PAREN>
+ {
+ //
+ // The parser can't distinguish one-arg function calls from
+ // non-distinct aggregates. That has to be figured out at
+ // bind() time.
+ //
+ return (ValueNode) nodeFactory.getNode(
+ C_NodeTypes.AGGREGATE_NODE,
+ value,
+ aggName,
+ Boolean.TRUE,
+ "",
+ getContextManager());
+ }
+}
+
+/*
* <A NAME="javaClass">javaClass</A>
*/
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UserDefinedAggregator.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UserDefinedAggregator.java?rev=1379505&r1=1379504&r2=1379505&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UserDefinedAggregator.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UserDefinedAggregator.java Fri Aug 31 16:55:20 2012
@@ -123,9 +123,9 @@ public final class UserDefinedAggregator
public void merge(ExecAggregator addend)
throws StandardException
{
- Aggregator other = (Aggregator) addend;
+ UserDefinedAggregator other = (UserDefinedAggregator) addend;
- _aggregator.merge( other );
+ _aggregator.merge( other._aggregator );
}
/**
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UserDefinedAggregatesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UserDefinedAggregatesTest.java?rev=1379505&r1=1379504&r2=1379505&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UserDefinedAggregatesTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UserDefinedAggregatesTest.java Fri Aug 31 16:55:20 2012
@@ -50,6 +50,10 @@ public class UserDefinedAggregatesTest
public static final String OBJECT_EXISTS = "X0Y68";
public static final String ILLEGAL_AGGREGATE = "42ZC3";
public static final String NAME_COLLISION = "X0Y87";
+ public static final String MISSING_FUNCTION = "42Y03";
+ public static final String MISSING_SCHEMA = "42Y07";
+ public static final String BAD_AGGREGATE_USAGE = "42903";
+ public static final String BAD_ORDER_BY = "42Y35";
///////////////////////////////////////////////////////////////////////////////////
//
@@ -295,16 +299,20 @@ public class UserDefinedAggregatesTest
/**
* <p>
- * Basic test for non-distinct aggregates.
+ * Basic test for aggregates in the select list.
* </p>
*/
- public void test_05_basicNonDistinct() throws Exception
+ public void test_05_basicSelectList() throws Exception
{
Connection conn = getConnection();
+ goodStatement( conn, "create schema agg_schema\n" );
goodStatement
( conn, "create derby aggregate mode for int\n" +
"external name 'org.apache.derbyTesting.functionTests.tests.lang.ModeAggregate'" );
+ goodStatement
+ ( conn, "create derby aggregate agg_schema.mode2 for int\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.ModeAggregate'" );
goodStatement( conn, "create table mode_inputs( a int, b int )" );
goodStatement( conn, "insert into mode_inputs( a, b ) values ( 1, 1 ), ( 1, 2 ), ( 1, 2 ), ( 1, 2 ), ( 2, 3 ), ( 2, 3 ), ( 2, 4 )" );
@@ -319,6 +327,26 @@ public class UserDefinedAggregatesTest
},
false
);
+ assertResults
+ (
+ conn,
+ "select app.mode( b ) from mode_inputs",
+ new String[][]
+ {
+ { "2" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select agg_schema.mode2( b ) from mode_inputs",
+ new String[][]
+ {
+ { "2" },
+ },
+ false
+ );
// grouped aggregate
assertResults
@@ -332,6 +360,291 @@ public class UserDefinedAggregatesTest
},
false
);
+ assertResults
+ (
+ conn,
+ "select a, app.mode( b ) from mode_inputs group by a",
+ new String[][]
+ {
+ { "1", "2" },
+ { "2", "3" },
+ },
+ false
+ );
+
+ // distinct scalar aggregate
+ assertResults
+ (
+ conn,
+ "select mode( distinct b ) from mode_inputs",
+ new String[][]
+ {
+ { "4" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select agg_schema.mode2( distinct b ) from mode_inputs",
+ new String[][]
+ {
+ { "4" },
+ },
+ false
+ );
+
+ // distinct grouped aggregate
+ assertResults
+ (
+ conn,
+ "select a, mode( distinct b ) from mode_inputs group by a",
+ new String[][]
+ {
+ { "1", "2" },
+ { "2", "4" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, agg_schema.mode2( distinct b ) from mode_inputs group by a",
+ new String[][]
+ {
+ { "1", "2" },
+ { "2", "4" },
+ },
+ false
+ );
+
+ // some negative tests for missing aggregates
+ expectCompilationError( MISSING_FUNCTION, "select agg_schema.mode( b ) from mode_inputs" );
+ expectCompilationError( OBJECT_DOES_NOT_EXIST, "select agg_schema.mode( distinct b ) from mode_inputs" );
+ expectCompilationError( MISSING_SCHEMA, "select missing_schema.mode( b ) from mode_inputs" );
+ expectCompilationError( MISSING_SCHEMA, "select missing_schema.mode( distinct b ) from mode_inputs" );
+
+ // some negative tests for aggregates in the WHERE clause
+ expectCompilationError( BAD_AGGREGATE_USAGE, "select * from mode_inputs where mode( b ) = 4" );
+ expectCompilationError( BAD_AGGREGATE_USAGE, "select * from mode_inputs where mode( distinct b ) = 4" );
+ expectCompilationError( BAD_AGGREGATE_USAGE, "select * from mode_inputs where app.mode( b ) = 4" );
+ expectCompilationError( BAD_AGGREGATE_USAGE, "select * from mode_inputs where app.mode( distinct b ) = 4" );
+
+ // negative test: can't put an aggregate in an ORDER BY list unless it's in the SELECT list too
+ expectCompilationError( BAD_ORDER_BY, "select * from mode_inputs order by mode( b )" );
+
+ // various other syntactically correct placements of user-defined aggregates
+ assertResults
+ (
+ conn,
+ "select mode( b ) from mode_inputs order by mode( b )",
+ new String[][]
+ {
+ { "2" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, mode( b ) from mode_inputs group by a order by mode( b )",
+ new String[][]
+ {
+ { "1", "2" },
+ { "2", "3" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, mode( b ) from mode_inputs group by a order by mode( b ) desc",
+ new String[][]
+ {
+ { "2", "3" },
+ { "1", "2" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, mode( b ) from mode_inputs group by a having mode( b ) = 3",
+ new String[][]
+ {
+ { "2", "3" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, count( b ) from mode_inputs group by a having mode( b ) = 3",
+ new String[][]
+ {
+ { "2", "3" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, sum( b ) from mode_inputs group by a having mode( b ) = 3",
+ new String[][]
+ {
+ { "2", "10" },
+ },
+ false
+ );
+
+ assertResults
+ (
+ conn,
+ "select mode( b ) from mode_inputs order by app.mode( b )",
+ new String[][]
+ {
+ { "2" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, mode( b ) from mode_inputs group by a order by app.mode( b )",
+ new String[][]
+ {
+ { "1", "2" },
+ { "2", "3" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, mode( b ) from mode_inputs group by a order by app.mode( b ) desc",
+ new String[][]
+ {
+ { "2", "3" },
+ { "1", "2" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, mode( b ) from mode_inputs group by a having app.mode( b ) = 3",
+ new String[][]
+ {
+ { "2", "3" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, count( b ) from mode_inputs group by a having app.mode( b ) = 3",
+ new String[][]
+ {
+ { "2", "3" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, sum( b ) from mode_inputs group by a having app.mode( b ) = 3",
+ new String[][]
+ {
+ { "2", "10" },
+ },
+ false
+ );
+
+ assertResults
+ (
+ conn,
+ "select app.mode( b ) from mode_inputs order by app.mode( b )",
+ new String[][]
+ {
+ { "2" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, app.mode( b ) from mode_inputs group by a order by app.mode( b )",
+ new String[][]
+ {
+ { "1", "2" },
+ { "2", "3" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, app.mode( b ) from mode_inputs group by a order by app.mode( b ) desc",
+ new String[][]
+ {
+ { "2", "3" },
+ { "1", "2" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, app.mode( b ) from mode_inputs group by a having app.mode( b ) = 3",
+ new String[][]
+ {
+ { "2", "3" },
+ },
+ false
+ );
+
+ assertResults
+ (
+ conn,
+ "select app.mode( b ) from mode_inputs order by mode( b )",
+ new String[][]
+ {
+ { "2" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, app.mode( b ) from mode_inputs group by a order by mode( b )",
+ new String[][]
+ {
+ { "1", "2" },
+ { "2", "3" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, app.mode( b ) from mode_inputs group by a order by mode( b ) desc",
+ new String[][]
+ {
+ { "2", "3" },
+ { "1", "2" },
+ },
+ false
+ );
+ assertResults
+ (
+ conn,
+ "select a, app.mode( b ) from mode_inputs group by a having mode( b ) = 3",
+ new String[][]
+ {
+ { "2", "3" },
+ },
+ false
+ );
}
/**
@@ -358,6 +671,15 @@ public class UserDefinedAggregatesTest
{
{ "1", "2" },
{ "2", "3" },
+ },
+ new String[][]
+ {
+ { "4" },
+ },
+ new String[][]
+ {
+ { "1", "2" },
+ { "2", "4" },
}
);
@@ -376,6 +698,15 @@ public class UserDefinedAggregatesTest
{
{ "1", "ab" },
{ "2", "abc" },
+ },
+ new String[][]
+ {
+ { "abcd" },
+ },
+ new String[][]
+ {
+ { "1", "ab" },
+ { "2", "abcd" },
}
);
@@ -388,7 +719,9 @@ public class UserDefinedAggregatesTest
String nestedClassName,
String values,
String[][] scalarResult,
- String[][] groupedResult
+ String[][] groupedResult,
+ String[][] distinctScalarResult,
+ String[][] distinctGroupedResult
)
throws Exception
{
@@ -415,6 +748,22 @@ public class UserDefinedAggregatesTest
groupedResult,
false
);
+
+ assertResults
+ (
+ conn,
+ "select " + aggName + "( distinct b ) from " + tableName,
+ distinctScalarResult,
+ false
+ );
+
+ assertResults
+ (
+ conn,
+ "select a, " + aggName + "( distinct b ) from " + tableName + " group by a",
+ distinctGroupedResult,
+ false
+ );
}
}