Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 24AA9D859 for ; Fri, 31 Aug 2012 16:56:10 +0000 (UTC) Received: (qmail 85926 invoked by uid 500); 31 Aug 2012 16:56:10 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 85867 invoked by uid 500); 31 Aug 2012 16:56:09 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 85857 invoked by uid 99); 31 Aug 2012 16:56:09 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 31 Aug 2012 16:56:09 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 31 Aug 2012 16:56:05 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id 4439723888FD; Fri, 31 Aug 2012 16:55:21 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: 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/ Date: Fri, 31 Aug 2012 16:55:20 -0000 To: derby-commits@db.apache.org From: rhillegas@apache.org X-Mailer: svnmailer-1.0.8-patched Message-Id: <20120831165521.4439723888FD@eris.apache.org> 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; + } +} + +/* + * routineExpression + */ +ValueNode +routineExpression() throws StandardException : +{ Vector parameterList = new Vector(); TableName routineName; MethodCallNode methodNode; @@ -7900,6 +7975,36 @@ routineInvocation() throws StandardExcep } /* + * distinctUDA + */ +ValueNode +distinctUDA() throws StandardException : +{ + ValueNode value; + TableName aggName; +} +{ + aggName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH) + + value = additiveExpression(null, 0) + + { + // + // 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()); + } +} + +/* * javaClass */ 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 /** *

- * Basic test for non-distinct aggregates. + * Basic test for aggregates in the select list. *

*/ - 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 + ); } }