This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 3038fb7e87c51740ef733fd4f9bde41b0c25e5b0 Author: Julian Hyde <[email protected]> AuthorDate: Mon Oct 19 22:55:40 2020 -0700 [CALCITE-4335] ARRAY_AGG, ARRAY_CONCAT_AGG, STRING_AGG aggregate functions for BigQuery --- core/src/main/codegen/default_config.fmpp | 3 + core/src/main/codegen/templates/Parser.jj | 199 ++++++++++++++------- .../calcite/adapter/enumerable/RexImpTable.java | 10 +- .../apache/calcite/sql/SqlFunctionalOperator.java | 2 +- .../main/java/org/apache/calcite/sql/SqlKind.java | 12 +- .../org/apache/calcite/sql/SqlLateralOperator.java | 2 +- .../calcite/sql/SqlNullTreatmentOperator.java | 7 + .../java/org/apache/calcite/sql/SqlSyntax.java | 29 ++- .../main/java/org/apache/calcite/sql/SqlUtil.java | 26 ++- .../calcite/sql/dialect/MssqlSqlDialect.java | 2 +- .../calcite/sql/dialect/OracleSqlDialect.java | 2 +- .../calcite/sql/dialect/SparkSqlDialect.java | 2 +- .../apache/calcite/sql/fun/SqlFloorFunction.java | 2 +- .../calcite/sql/fun/SqlJsonDepthFunction.java | 3 - .../calcite/sql/fun/SqlJsonPrettyFunction.java | 3 - .../calcite/sql/fun/SqlJsonTypeFunction.java | 3 - .../calcite/sql/fun/SqlLibraryOperators.java | 51 ++++++ .../calcite/sql/fun/SqlListaggAggFunction.java | 37 ++++ .../org/apache/calcite/sql/fun/SqlRowOperator.java | 2 +- .../calcite/sql/fun/SqlStdOperatorTable.java | 16 +- .../calcite/sql/fun/SqlStringAggAggFunction.java | 52 ++++++ .../org/apache/calcite/sql/type/ReturnTypes.java | 51 ++++++ .../apache/calcite/sql/type/SqlTypeTransforms.java | 10 ++ .../calcite/sql/util/ListSqlOperatorTable.java | 2 +- .../apache/calcite/sql2rel/SqlToRelConverter.java | 38 ++++ .../apache/calcite/sql/parser/SqlParserTest.java | 36 ++++ .../calcite/sql/test/SqlOperatorBaseTest.java | 191 ++++++++++++++------ .../java/org/apache/calcite/test/QuidemTest.java | 6 + core/src/test/resources/sql/agg.iq | 16 ++ site/_docs/reference.md | 6 + 30 files changed, 659 insertions(+), 162 deletions(-) diff --git a/core/src/main/codegen/default_config.fmpp b/core/src/main/codegen/default_config.fmpp index 380c475..ad3b724 100644 --- a/core/src/main/codegen/default_config.fmpp +++ b/core/src/main/codegen/default_config.fmpp @@ -39,6 +39,8 @@ parser: { "AFTER" "ALWAYS" "APPLY" + "ARRAY_AGG" + "ARRAY_CONCAT_AGG" "ASC" "ASSERTION" "ASSIGNMENT" @@ -299,6 +301,7 @@ parser: { "SQL_VARCHAR" "STATE" "STATEMENT" + "STRING_AGG" "STRUCTURE" "STYLE" "SUBCLASS_ORIGIN" diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 032dcfc..57b4917 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -870,23 +870,16 @@ List UnquantifiedFunctionParameterList( List FunctionParameterList( ExprContext exprContext) : { - SqlNode e = null; - List list = new ArrayList(); + final SqlLiteral qualifier; + final List list = new ArrayList(); } { <LPAREN> - [ - <DISTINCT> { - e = SqlSelectKeyword.DISTINCT.symbol(getPos()); - } + ( + qualifier = AllOrDistinct() { list.add(qualifier); } | - <ALL> { - e = SqlSelectKeyword.ALL.symbol(getPos()); - } - ] - { - list.add(e); - } + { list.add(null); } + ) Arg0(list, exprContext) ( <COMMA> { @@ -901,6 +894,15 @@ List FunctionParameterList( } } +SqlLiteral AllOrDistinct() : +{ +} +{ + <DISTINCT> { return SqlSelectKeyword.DISTINCT.symbol(getPos()); } +| + <ALL> { return SqlSelectKeyword.ALL.symbol(getPos()); } +} + void Arg0(List list, ExprContext exprContext) : { SqlIdentifier name = null; @@ -1269,6 +1271,7 @@ SqlNode TableRefWithHintsOpt() : SqlSelect SqlSelect() : { final List<SqlLiteral> keywords = new ArrayList<SqlLiteral>(); + final SqlLiteral keyword; final SqlNodeList keywordList; List<SqlNode> selectList; final SqlNode fromClause; @@ -1296,12 +1299,7 @@ SqlSelect SqlSelect() : } )? ( - <DISTINCT> { - keywords.add(SqlSelectKeyword.DISTINCT.symbol(getPos())); - } - | <ALL> { - keywords.add(SqlSelectKeyword.ALL.symbol(getPos())); - } + keyword = AllOrDistinct() { keywords.add(keyword); } )? { keywordList = new SqlNodeList(keywords, s.addAll(keywords).pos()); @@ -3838,6 +3836,59 @@ SqlNode RowExpressionExtension() : } /** + * Parses a call to the STRING_AGG aggregate function. + */ +SqlCall StringAggFunctionCall() : +{ + final Span s; + final SqlOperator op; + final List<SqlNode> args = new ArrayList(); + final SqlLiteral qualifier; + final SqlNodeList orderBy; + final Pair<SqlParserPos, SqlOperator> nullTreatment; +} +{ + ( + <ARRAY_AGG> { s = span(); op = SqlLibraryOperators.ARRAY_AGG; } + | <ARRAY_CONCAT_AGG> { s = span(); op = SqlLibraryOperators.ARRAY_CONCAT_AGG; } + | <STRING_AGG> { s = span(); op = SqlLibraryOperators.STRING_AGG; } + ) + <LPAREN> + ( + qualifier = AllOrDistinct() + | + { qualifier = null; } + ) + Arg(args, ExprContext.ACCEPT_SUB_QUERY) + ( + <COMMA> { + // a comma-list can't appear where only a query is expected + checkNonQueryExpression(ExprContext.ACCEPT_SUB_QUERY); + } + Arg(args, ExprContext.ACCEPT_SUB_QUERY) + )* + ( + nullTreatment = NullTreatment() + | + { nullTreatment = null; } + ) + [ + orderBy = OrderBy(true) { + args.add(orderBy); + } + ] + <RPAREN> + { + SqlCall call = op.createCall(qualifier, s.end(this), args); + if (nullTreatment != null) { + // Wrap in RESPECT_NULLS or IGNORE_NULLS. + call = nullTreatment.right.createCall(nullTreatment.left, call); + } + return call; + } +} + +/** * Parses an atomic row expression. */ SqlNode AtomicRowExpression() : @@ -6285,7 +6336,7 @@ SqlNodeList JsonArrayAggOrderByClause() : SqlCall JsonArrayAggFunctionCall() : { final SqlNode valueExpr; - SqlNodeList orderList = null; + final SqlNodeList orderList; List<SqlNode> list; final Span span; SqlJsonConstructorNullClause nullClause = @@ -6537,22 +6588,26 @@ SqlCall withinGroup(SqlNode arg) : } } -SqlCall nullTreatment(SqlCall arg) : +Pair<SqlParserPos, SqlOperator> NullTreatment() : { final Span span; } { - ( - <IGNORE> { span = span(); } <NULLS> { - return SqlStdOperatorTable.IGNORE_NULLS.createCall( - span.end(this), arg); - } - | - <RESPECT> { span = span(); } <NULLS> { - return SqlStdOperatorTable.RESPECT_NULLS.createCall( - span.end(this), arg); - } - ) + <IGNORE> { span = span(); } <NULLS> { + return Pair.of(span.end(this), SqlStdOperatorTable.IGNORE_NULLS); + } +| + <RESPECT> { span = span(); } <NULLS> { + return Pair.of(span.end(this), SqlStdOperatorTable.RESPECT_NULLS); + } +} + +SqlCall nullTreatment(SqlCall arg) : +{ + final Pair<SqlParserPos, SqlOperator> pair; +} +{ + pair = NullTreatment() { return pair.right.createCall(pair.left, arg); } } /** @@ -6572,47 +6627,20 @@ SqlCall nullTreatment(SqlCall arg) : */ SqlNode NamedFunctionCall() : { - final SqlFunctionCategory funcType; - final SqlIdentifier qualifiedName; - final Span s; - final List<SqlNode> args; SqlCall call; final Span filterSpan; final SqlNode filter; + final Span overSpan; final SqlNode over; - SqlLiteral quantifier = null; - SqlNodeList orderList = null; final Span withinGroupSpan; } { ( - <SPECIFIC> { - funcType = SqlFunctionCategory.USER_DEFINED_SPECIFIC_FUNCTION; - } - | - { funcType = SqlFunctionCategory.USER_DEFINED_FUNCTION; } - ) - qualifiedName = FunctionName() { - s = span(); - } - ( - LOOKAHEAD(2) <LPAREN> <STAR> { - args = startList(SqlIdentifier.star(getPos())); - } - <RPAREN> - | - LOOKAHEAD(2) <LPAREN> <RPAREN> { - args = Collections.emptyList(); - } + LOOKAHEAD(2) + call = StringAggFunctionCall() | - args = FunctionParameterList(ExprContext.ACCEPT_SUB_QUERY) { - quantifier = (SqlLiteral) args.get(0); - args.remove(0); - } + call = NamedCall() ) - { - call = createCall(qualifiedName, s.end(this), funcType, quantifier, args); - } [ LOOKAHEAD(2) call = nullTreatment(call) ] @@ -6630,14 +6658,14 @@ SqlNode NamedFunctionCall() : } ] [ - <OVER> + <OVER> { overSpan = span(); } ( over = SimpleIdentifier() | over = WindowSpecification() ) { - call = SqlStdOperatorTable.OVER.createCall(s.end(over), call, over); + call = SqlStdOperatorTable.OVER.createCall(overSpan.end(over), call, over); } ] { @@ -6645,6 +6673,44 @@ SqlNode NamedFunctionCall() : } } +SqlCall NamedCall() : +{ + final SqlFunctionCategory funcType; + final SqlIdentifier qualifiedName; + final Span s; + final List<SqlNode> args; + SqlLiteral quantifier = null; +} +{ + ( + <SPECIFIC> { + funcType = SqlFunctionCategory.USER_DEFINED_SPECIFIC_FUNCTION; + } + | + { funcType = SqlFunctionCategory.USER_DEFINED_FUNCTION; } + ) + qualifiedName = FunctionName() { + s = span(); + } + ( + LOOKAHEAD(2) <LPAREN> <STAR> { + args = startList(SqlIdentifier.star(getPos())); + } + <RPAREN> + | + LOOKAHEAD(2) <LPAREN> <RPAREN> { + args = Collections.emptyList(); + } + | + args = FunctionParameterList(ExprContext.ACCEPT_SUB_QUERY) { + quantifier = (SqlLiteral) args.get(0); + args.remove(0); + } + ) + { + return createCall(qualifiedName, s.end(this), funcType, quantifier, args); + } +} /* * Parse Floor/Ceil function parameters @@ -7101,6 +7167,8 @@ SqlPostfixOperator PostfixRowOperator() : | < APPLY: "APPLY" > | < ARE: "ARE" > | < ARRAY: "ARRAY" > +| < ARRAY_AGG: "ARRAY_AGG" > +| < ARRAY_CONCAT_AGG: "ARRAY_CONCAT_AGG" > | < ARRAY_MAX_CARDINALITY: "ARRAY_MAX_CARDINALITY" > | < AS: "AS" > | < ASC: "ASC" > @@ -7657,6 +7725,7 @@ SqlPostfixOperator PostfixRowOperator() : | < STDDEV_POP: "STDDEV_POP" > | < STDDEV_SAMP: "STDDEV_SAMP" > | < STREAM: "STREAM" > +| < STRING_AGG: "STRING_AGG" > | < STRUCTURE: "STRUCTURE" > | < STYLE: "STYLE" > | < SUBCLASS_ORIGIN: "SUBCLASS_ORIGIN" > diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java index cb21425..184c5df 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java @@ -101,6 +101,8 @@ import static org.apache.calcite.linq4j.tree.ExpressionType.Negate; import static org.apache.calcite.linq4j.tree.ExpressionType.NotEqual; import static org.apache.calcite.linq4j.tree.ExpressionType.Subtract; import static org.apache.calcite.linq4j.tree.ExpressionType.UnaryPlus; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_AGG; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_CONCAT_AGG; import static org.apache.calcite.sql.fun.SqlLibraryOperators.BOOL_AND; import static org.apache.calcite.sql.fun.SqlLibraryOperators.BOOL_OR; import static org.apache.calcite.sql.fun.SqlLibraryOperators.CHR; @@ -643,8 +645,10 @@ public class RexImpTable { aggMap.put(BIT_XOR, bitop); aggMap.put(SINGLE_VALUE, constructorSupplier(SingleValueImplementor.class)); aggMap.put(COLLECT, constructorSupplier(CollectImplementor.class)); + aggMap.put(ARRAY_AGG, constructorSupplier(CollectImplementor.class)); aggMap.put(LISTAGG, constructorSupplier(ListaggImplementor.class)); aggMap.put(FUSION, constructorSupplier(FusionImplementor.class)); + aggMap.put(ARRAY_CONCAT_AGG, constructorSupplier(FusionImplementor.class)); aggMap.put(INTERSECTION, constructorSupplier(IntersectionImplementor.class)); final Supplier<GroupingImplementor> grouping = constructorSupplier(GroupingImplementor.class); @@ -1136,7 +1140,8 @@ public class RexImpTable { } } - /** Implementor for the {@code COLLECT} aggregate function. */ + /** Implementor for the {@code COLLECT} and {@code ARRAY_AGG} + * aggregate functions. */ static class CollectImplementor extends StrictAggImplementor { @Override protected void implementNotNullReset(AggContext info, AggResetContext reset) { @@ -1218,7 +1223,8 @@ public class RexImpTable { } } - /** Implementor for the {@code FUSION} aggregate function. */ + /** Implementor for the {@code FUSION} and {@code ARRAY_CONCAT_AGG} + * aggregate functions. */ static class FusionImplementor extends StrictAggImplementor { @Override protected void implementNotNullReset(AggContext info, AggResetContext reset) { diff --git a/core/src/main/java/org/apache/calcite/sql/SqlFunctionalOperator.java b/core/src/main/java/org/apache/calcite/sql/SqlFunctionalOperator.java index 17ecc12..0d57ef1 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlFunctionalOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlFunctionalOperator.java @@ -52,6 +52,6 @@ public class SqlFunctionalOperator extends SqlSpecialOperator { SqlCall call, int leftPrec, int rightPrec) { - SqlUtil.unparseFunctionSyntax(this, writer, call); + SqlUtil.unparseFunctionSyntax(this, writer, call, false); } } diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java index 1011ee7..e76727b 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java @@ -802,6 +802,15 @@ public enum SqlKind { /** The {@code LISTAGG} aggregate function. */ LISTAGG, + /** The {@code STRING_AGG} aggregate function. */ + STRING_AGG, + + /** The {@code ARRAY_AGG} aggregate function. */ + ARRAY_AGG, + + /** The {@code ARRAY_CONCAT_AGG} aggregate function. */ + ARRAY_CONCAT_AGG, + /** The {@code COLLECT} aggregate function. */ COLLECT, @@ -1031,7 +1040,8 @@ public enum SqlKind { AVG, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, NTILE, COLLECT, FUSION, SINGLE_VALUE, ROW_NUMBER, RANK, PERCENT_RANK, DENSE_RANK, CUME_DIST, JSON_ARRAYAGG, JSON_OBJECTAGG, BIT_AND, BIT_OR, BIT_XOR, - LISTAGG, INTERSECTION, ANY_VALUE); + LISTAGG, STRING_AGG, ARRAY_AGG, ARRAY_CONCAT_AGG, + INTERSECTION, ANY_VALUE); /** * Category consisting of all DML operators. diff --git a/core/src/main/java/org/apache/calcite/sql/SqlLateralOperator.java b/core/src/main/java/org/apache/calcite/sql/SqlLateralOperator.java index 7c0d020..52e9325 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlLateralOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlLateralOperator.java @@ -48,7 +48,7 @@ public class SqlLateralOperator extends SqlSpecialOperator { writer.keyword(getName()); call.operand(0).unparse(writer, 0, 0); } else { - SqlUtil.unparseFunctionSyntax(this, writer, call); + SqlUtil.unparseFunctionSyntax(this, writer, call, false); } } } diff --git a/core/src/main/java/org/apache/calcite/sql/SqlNullTreatmentOperator.java b/core/src/main/java/org/apache/calcite/sql/SqlNullTreatmentOperator.java index 2e1afd1..b81d4c6 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlNullTreatmentOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlNullTreatmentOperator.java @@ -16,6 +16,7 @@ */ package org.apache.calcite.sql; +import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.validate.SqlValidator; @@ -41,6 +42,12 @@ public class SqlNullTreatmentOperator extends SqlSpecialOperator { || kind == SqlKind.IGNORE_NULLS); } + @Override public SqlCall createCall(SqlLiteral functionQualifier, + SqlParserPos pos, SqlNode... operands) { + // As super.createCall, but don't union the positions + return new SqlBasicCall(this, operands, pos, false, functionQualifier); + } + @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { assert call.operandCount() == 1; diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSyntax.java b/core/src/main/java/org/apache/calcite/sql/SqlSyntax.java index d43484d..356355e 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlSyntax.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlSyntax.java @@ -19,6 +19,8 @@ package org.apache.calcite.sql; import org.apache.calcite.sql.validate.SqlConformance; import org.apache.calcite.util.Util; +import java.util.Objects; + /** * Enumeration of possible syntactic types of {@link SqlOperator operators}. */ @@ -33,7 +35,7 @@ public enum SqlSyntax { SqlCall call, int leftPrec, int rightPrec) { - SqlUtil.unparseFunctionSyntax(operator, writer, call); + SqlUtil.unparseFunctionSyntax(operator, writer, call, false); } }, @@ -48,7 +50,17 @@ public enum SqlSyntax { SqlCall call, int leftPrec, int rightPrec) { - SqlUtil.unparseFunctionSyntax(operator, writer, call); + SqlUtil.unparseFunctionSyntax(operator, writer, call, false); + } + }, + + /** + * Function syntax with optional ORDER BY, as in "STRING_AGG(x, y ORDER BY z)". + */ + ORDERED_FUNCTION(FUNCTION) { + @Override public void unparse(SqlWriter writer, SqlOperator operator, + SqlCall call, int leftPrec, int rightPrec) { + SqlUtil.unparseFunctionSyntax(operator, writer, call, true); } }, @@ -130,7 +142,7 @@ public enum SqlSyntax { SqlCall call, int leftPrec, int rightPrec) { - SqlUtil.unparseFunctionSyntax(operator, writer, call); + SqlUtil.unparseFunctionSyntax(operator, writer, call, false); } }, @@ -149,6 +161,17 @@ public enum SqlSyntax { } }; + /** Syntax to treat this syntax as equivalent to when resolving operators. */ + public final SqlSyntax family; + + SqlSyntax() { + this(null); + } + + SqlSyntax(SqlSyntax family) { + this.family = Objects.requireNonNull(family == null ? this : family); + } + /** * Converts a call to an operator of this syntax into a string. */ diff --git a/core/src/main/java/org/apache/calcite/sql/SqlUtil.java b/core/src/main/java/org/apache/calcite/sql/SqlUtil.java index 76eae74..1176fbd 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlUtil.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlUtil.java @@ -266,17 +266,24 @@ public abstract class SqlUtil { } } + @Deprecated // to be removed before 2.0 + public static void unparseFunctionSyntax( + SqlOperator operator, + SqlWriter writer, + SqlCall call) { + unparseFunctionSyntax(operator, writer, call, false); + } + /** - * Unparses a call to an operator which has function syntax. + * Unparses a call to an operator that has function syntax. * * @param operator The operator * @param writer Writer - * @param call List of 0 or more operands + * @param call List of 0 or more operands + * @param ordered Whether argument list may end with ORDER BY */ - public static void unparseFunctionSyntax( - SqlOperator operator, - SqlWriter writer, - SqlCall call) { + public static void unparseFunctionSyntax(SqlOperator operator, + SqlWriter writer, SqlCall call, boolean ordered) { if (operator instanceof SqlFunction) { SqlFunction function = (SqlFunction) operator; @@ -300,6 +307,7 @@ public abstract class SqlUtil { return; case FUNCTION_STAR: // E.g. "COUNT(*)" case FUNCTION: // E.g. "RANK()" + case ORDERED_FUNCTION: // E.g. "STRING_AGG(x)" // fall through - dealt with below break; default: @@ -322,7 +330,11 @@ public abstract class SqlUtil { } } for (SqlNode operand : call.getOperandList()) { - writer.sep(","); + if (ordered && operand instanceof SqlNodeList) { + writer.sep("ORDER BY"); + } else { + writer.sep(","); + } operand.unparse(writer, 0, 0); } writer.endList(frame); diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java index f005175..7db41b8 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java @@ -145,7 +145,7 @@ public class MssqlSqlDialect extends SqlDialect { if (call.operandCount() != 3) { throw new IllegalArgumentException("MSSQL SUBSTRING requires FROM and FOR arguments"); } - SqlUtil.unparseFunctionSyntax(MSSQL_SUBSTRING, writer, call); + SqlUtil.unparseFunctionSyntax(MSSQL_SUBSTRING, writer, call, false); } else { switch (call.getKind()) { case FLOOR: diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java index ea2562a..deeb8a5 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java @@ -140,7 +140,7 @@ public class OracleSqlDialect extends SqlDialect { @Override public void unparseCall(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) { - SqlUtil.unparseFunctionSyntax(SqlLibraryOperators.SUBSTR, writer, call); + SqlUtil.unparseFunctionSyntax(SqlLibraryOperators.SUBSTR, writer, call, false); } else { switch (call.getKind()) { case FLOOR: diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java index 9423e9c..272e907 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java @@ -88,7 +88,7 @@ public class SparkSqlDialect extends SqlDialect { @Override public void unparseCall(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec) { if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) { - SqlUtil.unparseFunctionSyntax(SPARKSQL_SUBSTRING, writer, call); + SqlUtil.unparseFunctionSyntax(SPARKSQL_SUBSTRING, writer, call, false); } else { switch (call.getKind()) { case FLOOR: diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlFloorFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlFloorFunction.java index 7a06773..729bd14 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlFloorFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlFloorFunction.java @@ -114,6 +114,6 @@ public class SqlFloorFunction extends SqlMonotonicUnaryFunction { call1 = call.getOperator().createCall(call.getParserPosition(), op2, op1); } - SqlUtil.unparseFunctionSyntax(func, writer, call1); + SqlUtil.unparseFunctionSyntax(func, writer, call1, false); } } diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java index 2ade212..d221a31 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java @@ -20,10 +20,7 @@ import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlFunction; import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; -import org.apache.calcite.sql.SqlLiteral; -import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlOperandCountRange; -import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlOperandCountRanges; diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonPrettyFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonPrettyFunction.java index c282cf5..8a621db 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonPrettyFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonPrettyFunction.java @@ -20,10 +20,7 @@ import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlFunction; import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; -import org.apache.calcite.sql.SqlLiteral; -import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlOperandCountRange; -import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlOperandCountRanges; diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonTypeFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonTypeFunction.java index 4609381..39d50cd 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonTypeFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonTypeFunction.java @@ -20,10 +20,7 @@ import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlFunction; import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; -import org.apache.calcite.sql.SqlLiteral; -import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.SqlOperandCountRange; -import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SqlOperandCountRanges; diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java index e0b5a6f..cc9cb2b 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java @@ -19,12 +19,14 @@ package org.apache.calcite.sql.fun; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.sql.SqlAggFunction; +import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlFunction; import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.SqlOperatorBinding; import org.apache.calcite.sql.SqlOperatorTable; +import org.apache.calcite.sql.SqlSyntax; import org.apache.calcite.sql.type.OperandTypes; import org.apache.calcite.sql.type.ReturnTypes; import org.apache.calcite.sql.type.SameOperandTypeChecker; @@ -33,7 +35,10 @@ import org.apache.calcite.sql.type.SqlReturnTypeInference; import org.apache.calcite.sql.type.SqlTypeFamily; import org.apache.calcite.sql.type.SqlTypeName; import org.apache.calcite.sql.type.SqlTypeTransforms; +import org.apache.calcite.sql.validate.SqlValidator; +import org.apache.calcite.sql.validate.SqlValidatorScope; import org.apache.calcite.util.Litmus; +import org.apache.calcite.util.Optionality; import com.google.common.collect.ImmutableList; @@ -46,6 +51,7 @@ import static org.apache.calcite.sql.fun.SqlLibrary.MYSQL; import static org.apache.calcite.sql.fun.SqlLibrary.ORACLE; import static org.apache.calcite.sql.fun.SqlLibrary.POSTGRESQL; import static org.apache.calcite.sql.fun.SqlLibrary.SPARK; +import static org.apache.calcite.sql.type.ReturnTypes.stripOrderBy; /** * Defines functions and operators that are not part of standard SQL but @@ -268,6 +274,51 @@ public abstract class SqlLibraryOperators { public static final SqlAggFunction LOGICAL_OR = new SqlMinMaxAggFunction("LOGICAL_OR", SqlKind.MAX, OperandTypes.BOOLEAN); + /** The "ARRAY_AGG(value [ ORDER BY ...])" aggregate function, + * in BigQuery and PostgreSQL, gathers values into arrays. */ + @LibraryOperator(libraries = {POSTGRESQL, BIG_QUERY}) + public static final SqlAggFunction ARRAY_AGG = + new SqlAggFunction("ARRAY_AGG", null, SqlKind.ARRAY_AGG, + ReturnTypes.andThen(ReturnTypes::stripOrderBy, ReturnTypes.TO_ARRAY), + null, OperandTypes.ANY, SqlFunctionCategory.SYSTEM, false, false, + Optionality.OPTIONAL) { + @Override public SqlSyntax getSyntax() { + return SqlSyntax.ORDERED_FUNCTION; + } + + @Override public boolean allowsNullTreatment() { + return true; + } + + @Override public RelDataType deriveType(SqlValidator validator, + SqlValidatorScope scope, SqlCall call) { + return super.deriveType(validator, scope, stripOrderBy(call)); + } + }; + + /** The "ARRAY_CONCAT_AGG(value [ ORDER BY ...])" aggregate function, + * in BigQuery and PostgreSQL, concatenates array values into arrays. */ + @LibraryOperator(libraries = {POSTGRESQL, BIG_QUERY}) + public static final SqlAggFunction ARRAY_CONCAT_AGG = + new SqlAggFunction("ARRAY_CONCAT_AGG", null, SqlKind.ARRAY_CONCAT_AGG, + ReturnTypes.ARG0, null, OperandTypes.ARRAY, + SqlFunctionCategory.SYSTEM, false, false, Optionality.OPTIONAL) { + @Override public SqlSyntax getSyntax() { + return SqlSyntax.ORDERED_FUNCTION; + } + + @Override public RelDataType deriveType(SqlValidator validator, + SqlValidatorScope scope, SqlCall call) { + return super.deriveType(validator, scope, stripOrderBy(call)); + } + }; + + /** The "STRING_AGG(value [, separator ] [ ORDER BY ...])" aggregate function, + * BigQuery and PostgreSQL's equivalent of + * {@link SqlStdOperatorTable#LISTAGG}. */ + @LibraryOperator(libraries = {POSTGRESQL, BIG_QUERY}) + public static final SqlAggFunction STRING_AGG = new SqlStringAggAggFunction(); + /** The "DATE(string)" function, equivalent to "CAST(string AS DATE). */ @LibraryOperator(libraries = {BIG_QUERY}) public static final SqlFunction DATE = diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlListaggAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlListaggAggFunction.java new file mode 100644 index 0000000..9bda375 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlListaggAggFunction.java @@ -0,0 +1,37 @@ +/* + * 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.calcite.sql.fun; + +import org.apache.calcite.sql.SqlAggFunction; +import org.apache.calcite.sql.SqlFunctionCategory; +import org.apache.calcite.sql.SqlKind; +import org.apache.calcite.sql.type.OperandTypes; +import org.apache.calcite.sql.type.SqlReturnTypeInference; +import org.apache.calcite.util.Optionality; + +/** + * <code>LISTAGG</code> aggregate function + * returns the concatenation of its group rows. + */ +class SqlListaggAggFunction extends SqlAggFunction { + SqlListaggAggFunction(SqlKind kind, + SqlReturnTypeInference returnTypeInference) { + super(kind.name(), null, kind, returnTypeInference, + null, OperandTypes.or(OperandTypes.STRING, OperandTypes.STRING_STRING), + SqlFunctionCategory.SYSTEM, false, false, Optionality.OPTIONAL); + } +} diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlRowOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlRowOperator.java index e83728c..1a7259a 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlRowOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlRowOperator.java @@ -75,7 +75,7 @@ public class SqlRowOperator extends SqlSpecialOperator { SqlCall call, int leftPrec, int rightPrec) { - SqlUtil.unparseFunctionSyntax(this, writer, call); + SqlUtil.unparseFunctionSyntax(this, writer, call, false); } // override SqlOperator diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java index 4bf023e..7bc2459 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java @@ -2138,9 +2138,7 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { SqlCall call, int leftPrec, int rightPrec) { - SqlUtil.unparseFunctionSyntax( - this, - writer, call); + SqlUtil.unparseFunctionSyntax(this, writer, call, false); } }; @@ -2213,18 +2211,10 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable { }; /** - * The LISTAGG operator. Multiset aggregator function. + * The LISTAGG operator. String aggregator function. */ public static final SqlAggFunction LISTAGG = - new SqlAggFunction("LISTAGG", - null, - SqlKind.LISTAGG, - ReturnTypes.ARG0_NULLABLE, - null, - OperandTypes.or(OperandTypes.STRING, OperandTypes.STRING_STRING), - SqlFunctionCategory.SYSTEM, false, false, - Optionality.OPTIONAL) { - }; + new SqlListaggAggFunction(SqlKind.LISTAGG, ReturnTypes.ARG0_NULLABLE); /** * The FUSION operator. Multiset aggregator function. diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStringAggAggFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStringAggAggFunction.java new file mode 100644 index 0000000..7938e91 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStringAggAggFunction.java @@ -0,0 +1,52 @@ +/* + * 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.calcite.sql.fun; + +import org.apache.calcite.rel.type.RelDataType; +import org.apache.calcite.sql.SqlCall; +import org.apache.calcite.sql.SqlKind; +import org.apache.calcite.sql.SqlSyntax; +import org.apache.calcite.sql.type.ReturnTypes; +import org.apache.calcite.sql.validate.SqlValidator; +import org.apache.calcite.sql.validate.SqlValidatorScope; + +import static org.apache.calcite.sql.type.ReturnTypes.stripOrderBy; + +/** + * <code>STRING_AGG</code> aggregate function + * returns the concatenation of its group rows; + * it is the PostgreSQL and BigQuery equivalent of {@code LISTAGG}. + * + * <p>{@code STRING_AGG(v, sep ORDER BY x, y)} is implemented by + * rewriting to {@code LISTAGG(v, sep) WITHIN GROUP (ORDER BY x, y)}. + * + * @see SqlListaggAggFunction + */ +class SqlStringAggAggFunction extends SqlListaggAggFunction { + SqlStringAggAggFunction() { + super(SqlKind.STRING_AGG, ReturnTypes.ARG0_NULLABLE); + } + + @Override public SqlSyntax getSyntax() { + return SqlSyntax.ORDERED_FUNCTION; + } + + @Override public RelDataType deriveType(SqlValidator validator, + SqlValidatorScope scope, SqlCall call) { + return super.deriveType(validator, scope, stripOrderBy(call)); + } +} diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java index 44ede21..1204674 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java +++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java @@ -23,16 +23,20 @@ import org.apache.calcite.rel.type.RelDataTypeImpl; import org.apache.calcite.rel.type.RelDataTypeSystem; import org.apache.calcite.rel.type.RelProtoDataType; import org.apache.calcite.sql.ExplicitOperatorBinding; +import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlCallBinding; import org.apache.calcite.sql.SqlCollation; +import org.apache.calcite.sql.SqlNodeList; import org.apache.calcite.sql.SqlOperatorBinding; import org.apache.calcite.sql.SqlUtil; import org.apache.calcite.util.Glossary; +import org.apache.calcite.util.Util; import com.google.common.base.Preconditions; import java.util.AbstractList; import java.util.List; +import java.util.function.UnaryOperator; import static org.apache.calcite.util.Static.RESOURCE; @@ -90,6 +94,44 @@ public abstract class ReturnTypes { return explicit(RelDataTypeImpl.proto(typeName, precision, false)); } + /** Returns a return-type inference that first transforms a binding and + * then applies an inference. + * + * <p>{@link #stripOrderBy} is an example of {@code bindingTransform}. */ + public static SqlReturnTypeInference andThen( + UnaryOperator<SqlOperatorBinding> bindingTransform, + SqlReturnTypeInference typeInference) { + return opBinding -> + typeInference.inferReturnType(bindingTransform.apply(opBinding)); + } + + /** Converts a binding of {@code FOO(x, y ORDER BY z)} to a binding of + * {@code FOO(x, y)}. Used for {@code STRING_AGG}. */ + public static SqlOperatorBinding stripOrderBy( + SqlOperatorBinding operatorBinding) { + if (operatorBinding instanceof SqlCallBinding) { + final SqlCallBinding callBinding = (SqlCallBinding) operatorBinding; + final SqlCall call2 = stripOrderBy(callBinding.getCall()); + if (call2 != callBinding.getCall()) { + return new SqlCallBinding(callBinding.getValidator(), + callBinding.getScope(), call2); + } + } + return operatorBinding; + } + + public static SqlCall stripOrderBy(SqlCall call) { + if (!call.getOperandList().isEmpty() + && Util.last(call.getOperandList()) instanceof SqlNodeList) { + // Remove the last argument if it is "ORDER BY". The parser stashes the + // ORDER BY clause in the argument list but it does not take part in + // type derivation. + return call.getOperator().createCall(call.getFunctionQuantifier(), + call.getParserPosition(), Util.skipLast(call.getOperandList())); + } + return call; + } + /** * Type-inference strategy whereby the result type of a call is the type of * the operand #0 (0-based). @@ -435,6 +477,15 @@ public abstract class ReturnTypes { MULTISET.andThen(SqlTypeTransforms.ONLY_COLUMN); /** + * Returns an ARRAY type. + * + * <p>For example, given <code>INTEGER</code>, returns + * <code>INTEGER ARRAY</code>. + */ + public static final SqlReturnTypeInference TO_ARRAY = + ARG0.andThen(SqlTypeTransforms.TO_ARRAY); + + /** * Type-inference strategy whereby the result type of a call is * {@link #ARG0_INTERVAL_NULLABLE} and {@link #LEAST_RESTRICTIVE}. These rules * are used for integer division. diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java index 384ce91..33640b5 100644 --- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java +++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeTransforms.java @@ -167,6 +167,16 @@ public abstract class SqlTypeTransforms { opBinding.getTypeFactory().createMultisetType(typeToTransform, -1); /** + * Parameter type-inference transform strategy that wraps a given type + * in a array. + * + * @see org.apache.calcite.rel.type.RelDataTypeFactory#createArrayType(RelDataType, long) + */ + public static final SqlTypeTransform TO_ARRAY = + (opBinding, typeToTransform) -> + opBinding.getTypeFactory().createArrayType(typeToTransform, -1); + + /** * Parameter type-inference transform strategy where a derived type must be * a struct type with precisely one field and the returned type is the type * of that field. diff --git a/core/src/main/java/org/apache/calcite/sql/util/ListSqlOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/util/ListSqlOperatorTable.java index f752565..3fa96f8 100644 --- a/core/src/main/java/org/apache/calcite/sql/util/ListSqlOperatorTable.java +++ b/core/src/main/java/org/apache/calcite/sql/util/ListSqlOperatorTable.java @@ -58,7 +58,7 @@ public class ListSqlOperatorTable implements SqlOperatorTable { List<SqlOperator> operatorList, SqlNameMatcher nameMatcher) { for (SqlOperator operator : this.operatorList) { - if (operator.getSyntax() != syntax) { + if (operator.getSyntax().family != syntax) { continue; } if (!opName.isSimple() diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index 3e5b4c1..fab36f2 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -5374,6 +5374,8 @@ public class SqlToRelConverter { @Override public Void visit(SqlCall call) { switch (call.getKind()) { case FILTER: + case IGNORE_NULLS: + case RESPECT_NULLS: case WITHIN_GROUP: translateAgg(call); return null; @@ -5434,6 +5436,7 @@ public class SqlToRelConverter { SqlNodeList orderList, boolean ignoreNulls, SqlCall outerCall) { assert bb.agg == this; assert outerCall != null; + final List<SqlNode> operands = call.getOperandList(); switch (call.getKind()) { case FILTER: assert filter == null; @@ -5452,6 +5455,41 @@ public class SqlToRelConverter { translateAgg(call.operand(0), filter, orderList, ignoreNulls, outerCall); return; + case STRING_AGG: + // Translate "STRING_AGG(s, sep ORDER BY x, y)" + // as if it were "LISTAGG(s, sep) WITHIN GROUP (ORDER BY x, y)"; + // and "STRING_AGG(s, sep)" as "LISTAGG(s, sep)". + final List<SqlNode> operands2; + if (!operands.isEmpty() + && Util.last(operands) instanceof SqlNodeList) { + orderList = (SqlNodeList) Util.last(operands); + operands2 = Util.skipLast(operands); + } else { + operands2 = operands; + } + final SqlCall call2 = + SqlStdOperatorTable.LISTAGG.createCall( + call.getFunctionQuantifier(), call.getParserPosition(), + operands2); + translateAgg(call2, filter, orderList, ignoreNulls, outerCall); + return; + case ARRAY_AGG: + case ARRAY_CONCAT_AGG: + // Translate "ARRAY_AGG(s ORDER BY x, y)" + // as if it were "ARRAY_AGG(s) WITHIN GROUP (ORDER BY x, y)"; + // similarly "ARRAY_CONCAT_AGG". + if (!operands.isEmpty() + && Util.last(operands) instanceof SqlNodeList) { + orderList = (SqlNodeList) Util.last(operands); + final SqlCall call3 = + call.getOperator().createCall( + call.getFunctionQuantifier(), call.getParserPosition(), + Util.skipLast(operands)); + translateAgg(call3, filter, orderList, ignoreNulls, outerCall); + return; + } + // "ARRAY_AGG" and "ARRAY_CONCAT_AGG" without "ORDER BY" + // are handled normally; fall through. default: break; } diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java index 9fa572b..45c5641 100644 --- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java +++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java @@ -8830,6 +8830,42 @@ public class SqlParserTest { sql(sql).ok(expected); } + @Test void testStringAgg() { + final String sql = "select\n" + + " string_agg(ename order by deptno, ename) as c1,\n" + + " string_agg(ename, '; ' order by deptno, ename desc) as c2,\n" + + " string_agg(ename) as c3,\n" + + " string_agg(ename, ':') as c4,\n" + + " string_agg(ename, ':' ignore nulls) as c5\n" + + "from emp group by gender"; + final String expected = "SELECT" + + " STRING_AGG(`ENAME` ORDER BY `DEPTNO`, `ENAME`) AS `C1`," + + " STRING_AGG(`ENAME`, '; ' ORDER BY `DEPTNO`, `ENAME` DESC) AS `C2`," + + " STRING_AGG(`ENAME`) AS `C3`," + + " STRING_AGG(`ENAME`, ':') AS `C4`," + + " STRING_AGG(`ENAME`, ':') IGNORE NULLS AS `C5`\n" + + "FROM `EMP`\n" + + "GROUP BY `GENDER`"; + sql(sql).ok(expected); + } + + @Test void testArrayAgg() { + final String sql = "select\n" + + " array_agg(ename respect nulls order by deptno, ename) as c1,\n" + + " array_concat_agg(ename order by deptno, ename desc) as c2,\n" + + " array_agg(ename) as c3,\n" + + " array_concat_agg(ename) within group (order by ename) as c4\n" + + "from emp group by gender"; + final String expected = "SELECT" + + " ARRAY_AGG(`ENAME` ORDER BY `DEPTNO`, `ENAME`) RESPECT NULLS AS `C1`," + + " ARRAY_CONCAT_AGG(`ENAME` ORDER BY `DEPTNO`, `ENAME` DESC) AS `C2`," + + " ARRAY_AGG(`ENAME`) AS `C3`," + + " ARRAY_CONCAT_AGG(`ENAME`) WITHIN GROUP (ORDER BY `ENAME`) AS `C4`\n" + + "FROM `EMP`\n" + + "GROUP BY `GENDER`"; + sql(sql).ok(expected); + } + @Test void testJsonValueExpressionOperator() { expr("foo format json") .ok("`FOO` FORMAT JSON"); diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index 2733b32..b428de5 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -7053,19 +7053,19 @@ public abstract class SqlOperatorBaseTest { false); final String[] values = {"0", "CAST(null AS INTEGER)", "2", "2"}; tester.checkAgg("collect(x)", values, - Collections.singletonList("[0, 2, 2]"), (double) 0); + Collections.singletonList("[0, 2, 2]"), 0d); tester.checkAgg("collect(x) within group(order by x desc)", values, - Collections.singletonList("[2, 2, 0]"), (double) 0); + Collections.singletonList("[2, 2, 0]"), 0d); Object result1 = -3; if (!enable) { return; } tester.checkAgg("collect(CASE x WHEN 0 THEN NULL ELSE -1 END)", values, - result1, (double) 0); + result1, 0d); Object result = -1; tester.checkAgg("collect(DISTINCT CASE x WHEN 0 THEN NULL ELSE -1 END)", - values, result, (double) 0); - tester.checkAgg("collect(DISTINCT x)", values, 2, (double) 0); + values, result, 0d); + tester.checkAgg("collect(DISTINCT x)", values, 2, 0d); } @Test void testListAggFunc() { @@ -7086,9 +7086,121 @@ public abstract class SqlOperatorBaseTest { checkAggType(tester, "listagg('test')", "CHAR(4) NOT NULL"); checkAggType(tester, "listagg('test', ', ')", "CHAR(4) NOT NULL"); final String[] values1 = {"'hello'", "CAST(null AS CHAR)", "'world'", "'!'"}; - tester.checkAgg("listagg(x)", values1, "hello,world,!", (double) 0); + tester.checkAgg("listagg(x)", values1, "hello,world,!", 0d); final String[] values2 = {"0", "1", "2", "3"}; - tester.checkAgg("listagg(cast(x as CHAR))", values2, "0,1,2,3", (double) 0); + tester.checkAgg("listagg(cast(x as CHAR))", values2, "0,1,2,3", 0d); + } + + @Test void testStringAggFunc() { + checkStringAggFunc(libraryTester(SqlLibrary.POSTGRESQL)); + checkStringAggFunc(libraryTester(SqlLibrary.BIG_QUERY)); + checkStringAggFuncFails(libraryTester(SqlLibrary.MYSQL)); + } + + private void checkStringAggFunc(SqlTester t) { + final String[] values = {"'x'", "null", "'yz'"}; + t.checkAgg("string_agg(x)", values, "x,yz", 0); + t.checkAgg("string_agg(x,':')", values, "x:yz", 0); + t.checkAgg("string_agg(x,':' order by x)", values, "x:yz", 0); + t.checkAgg("string_agg(x order by char_length(x) desc)", values, + "yz,x", 0); + t.checkAggFails("^string_agg(x respect nulls order by x desc)^", values, + "Cannot specify IGNORE NULLS or RESPECT NULLS following 'STRING_AGG'", + false); + t.checkAggFails("^string_agg(x order by x desc)^ respect nulls", values, + "Cannot specify IGNORE NULLS or RESPECT NULLS following 'STRING_AGG'", + false); + } + + private void checkStringAggFuncFails(SqlTester t) { + final String[] values = {"'x'", "'y'"}; + t.checkAggFails("^string_agg(x)^", values, + "No match found for function signature STRING_AGG\\(<CHARACTER>\\)", + false); + t.checkAggFails("^string_agg(x, ',')^", values, + "No match found for function signature STRING_AGG\\(<CHARACTER>, " + + "<CHARACTER>\\)", + false); + t.checkAggFails("^string_agg(x, ',' order by x desc)^", values, + "No match found for function signature STRING_AGG\\(<CHARACTER>, " + + "<CHARACTER>\\)", + false); + } + + @Test void testArrayAggFunc() { + checkArrayAggFunc(libraryTester(SqlLibrary.POSTGRESQL)); + checkArrayAggFunc(libraryTester(SqlLibrary.BIG_QUERY)); + checkArrayAggFuncFails(libraryTester(SqlLibrary.MYSQL)); + } + + private void checkArrayAggFunc(SqlTester t) { + t.setFor(SqlLibraryOperators.ARRAY_CONCAT_AGG, VM_FENNEL, VM_JAVA); + final String[] values = {"'x'", "null", "'yz'"}; + t.checkAgg("array_agg(x)", values, "[x, yz]", 0); + t.checkAgg("array_agg(x ignore nulls)", values, "[x, yz]", 0); + t.checkAgg("array_agg(x respect nulls)", values, "[x, yz]", 0); + final String expectedError = "Invalid number of arguments " + + "to function 'ARRAY_AGG'. Was expecting 1 arguments"; + t.checkAggFails("^array_agg(x,':')^", values, expectedError, false); + t.checkAggFails("^array_agg(x,':' order by x)^", values, expectedError, + false); + t.checkAgg("array_agg(x order by char_length(x) desc)", values, + "[yz, x]", 0); + } + + private void checkArrayAggFuncFails(SqlTester t) { + t.setFor(SqlLibraryOperators.ARRAY_CONCAT_AGG, VM_FENNEL, VM_JAVA); + final String[] values = {"'x'", "'y'"}; + final String expectedError = "No match found for function signature " + + "ARRAY_AGG\\(<CHARACTER>\\)"; + final String expectedError2 = "No match found for function signature " + + "ARRAY_AGG\\(<CHARACTER>, <CHARACTER>\\)"; + t.checkAggFails("^array_agg(x)^", values, expectedError, false); + t.checkAggFails("^array_agg(x, ',')^", values, expectedError2, false); + t.checkAggFails("^array_agg(x, ',' order by x desc)^", values, + expectedError2, false); + } + + @Test void testArrayConcatAggFunc() { + checkArrayConcatAggFunc(libraryTester(SqlLibrary.POSTGRESQL)); + checkArrayConcatAggFunc(libraryTester(SqlLibrary.BIG_QUERY)); + checkArrayConcatAggFuncFails(libraryTester(SqlLibrary.MYSQL)); + } + + void checkArrayConcatAggFunc(SqlTester t) { + t.setFor(SqlLibraryOperators.ARRAY_CONCAT_AGG, VM_FENNEL, VM_JAVA); + t.checkFails("array_concat_agg(^*^)", "(?s)Encountered \"\\*\" at .*", false); + checkAggType(t, "array_concat_agg(ARRAY[1,2,3])", + "INTEGER NOT NULL ARRAY NOT NULL"); + + final String expectedError = "Cannot apply 'ARRAY_CONCAT_AGG' to arguments " + + "of type 'ARRAY_CONCAT_AGG\\(<INTEGER MULTISET>\\)'. Supported " + + "form\\(s\\): 'ARRAY_CONCAT_AGG\\(<ARRAY>\\)'"; + t.checkFails("^array_concat_agg(multiset[1,2])^", expectedError, false); + + final String expectedError1 = "Cannot apply 'ARRAY_CONCAT_AGG' to " + + "arguments of type 'ARRAY_CONCAT_AGG\\(<INTEGER>\\)'\\. Supported " + + "form\\(s\\): 'ARRAY_CONCAT_AGG\\(<ARRAY>\\)'"; + t.checkFails("^array_concat_agg(12)^", expectedError1, false); + + final String[] values1 = {"ARRAY[0]", "ARRAY[1]", "ARRAY[2]", "ARRAY[3]"}; + t.checkAgg("array_concat_agg(x)", values1, "[0, 1, 2, 3]", 0); + + final String[] values2 = {"ARRAY[0,1]", "ARRAY[1, 2]"}; + t.checkAgg("array_concat_agg(x)", values2, "[0, 1, 1, 2]", 0); + } + + void checkArrayConcatAggFuncFails(SqlTester t) { + t.setFor(SqlLibraryOperators.ARRAY_CONCAT_AGG, VM_FENNEL, VM_JAVA); + final String[] values = {"'x'", "'y'"}; + final String expectedError = "No match found for function signature " + + "ARRAY_CONCAT_AGG\\(<CHARACTER>\\)"; + final String expectedError2 = "No match found for function signature " + + "ARRAY_CONCAT_AGG\\(<CHARACTER>, <CHARACTER>\\)"; + t.checkAggFails("^array_concat_agg(x)^", values, expectedError, false); + t.checkAggFails("^array_concat_agg(x, ',')^", values, expectedError2, false); + t.checkAggFails("^array_concat_agg(x, ',' order by x desc)^", values, + expectedError2, false); } @Test void testFusionFunc() { @@ -8457,30 +8569,19 @@ public abstract class SqlOperatorBaseTest { tester.checkType("count(1, 2)", "BIGINT NOT NULL"); tester.checkType("count(1, 2, 'x', 'y')", "BIGINT NOT NULL"); final String[] values = {"0", "CAST(null AS INTEGER)", "1", "0"}; - tester.checkAgg( - "COUNT(x)", - values, - 3, - (double) 0); - tester.checkAgg( - "COUNT(CASE x WHEN 0 THEN NULL ELSE -1 END)", - values, - 2, - (double) 0); - tester.checkAgg( - "COUNT(DISTINCT x)", - values, - 2, - (double) 0); + tester.checkAgg("COUNT(x)", values, 3, 0d); + tester.checkAgg("COUNT(CASE x WHEN 0 THEN NULL ELSE -1 END)", values, 2, + 0d); + tester.checkAgg("COUNT(DISTINCT x)", values, 2, 0d); // string values -- note that empty string is not null final String[] stringValues = { "'a'", "CAST(NULL AS VARCHAR(1))", "''" }; - tester.checkAgg("COUNT(*)", stringValues, 3, (double) 0); - tester.checkAgg("COUNT(x)", stringValues, 2, (double) 0); - tester.checkAgg("COUNT(DISTINCT x)", stringValues, 2, (double) 0); - tester.checkAgg("COUNT(DISTINCT 123)", stringValues, 1, (double) 0); + tester.checkAgg("COUNT(*)", stringValues, 3, 0d); + tester.checkAgg("COUNT(x)", stringValues, 2, 0d); + tester.checkAgg("COUNT(DISTINCT x)", stringValues, 2, 0d); + tester.checkAgg("COUNT(DISTINCT 123)", stringValues, 1, 0d); } @Test void testApproxCountDistinctFunc() { @@ -8500,32 +8601,20 @@ public abstract class SqlOperatorBaseTest { "BIGINT NOT NULL"); final String[] values = {"0", "CAST(null AS INTEGER)", "1", "0"}; // currently APPROX_COUNT_DISTINCT(x) returns the same as COUNT(DISTINCT x) - tester.checkAgg( - "APPROX_COUNT_DISTINCT(x)", - values, - 2, - (double) 0); + tester.checkAgg("APPROX_COUNT_DISTINCT(x)", values, 2, 0d); tester.checkAgg( "APPROX_COUNT_DISTINCT(CASE x WHEN 0 THEN NULL ELSE -1 END)", - values, - 1, - (double) 0); + values, 1, 0d); // DISTINCT keyword is allowed but has no effect - tester.checkAgg( - "APPROX_COUNT_DISTINCT(DISTINCT x)", - values, - 2, - (double) 0); + tester.checkAgg("APPROX_COUNT_DISTINCT(DISTINCT x)", values, 2, 0d); // string values -- note that empty string is not null final String[] stringValues = { "'a'", "CAST(NULL AS VARCHAR(1))", "''" }; - tester.checkAgg("APPROX_COUNT_DISTINCT(x)", stringValues, 2, (double) 0); - tester.checkAgg("APPROX_COUNT_DISTINCT(DISTINCT x)", stringValues, 2, - (double) 0); - tester.checkAgg("APPROX_COUNT_DISTINCT(DISTINCT 123)", stringValues, 1, - (double) 0); + tester.checkAgg("APPROX_COUNT_DISTINCT(x)", stringValues, 2, 0d); + tester.checkAgg("APPROX_COUNT_DISTINCT(DISTINCT x)", stringValues, 2, 0d); + tester.checkAgg("APPROX_COUNT_DISTINCT(DISTINCT 123)", stringValues, 1, 0d); } @Test void testSumFunc() { @@ -8554,17 +8643,17 @@ public abstract class SqlOperatorBaseTest { false); tester.checkType("sum(cast(null as varchar(2)))", "DECIMAL(19, 9)"); final String[] values = {"0", "CAST(null AS INTEGER)", "2", "2"}; - tester.checkAgg("sum(x)", values, 4, (double) 0); + tester.checkAgg("sum(x)", values, 4, 0d); Object result1 = -3; if (!enable) { return; } tester.checkAgg("sum(CASE x WHEN 0 THEN NULL ELSE -1 END)", values, result1, - (double) 0); + 0d); Object result = -1; tester.checkAgg("sum(DISTINCT CASE x WHEN 0 THEN NULL ELSE -1 END)", values, - result, (double) 0); - tester.checkAgg("sum(DISTINCT x)", values, 2, (double) 0); + result, 0d); + tester.checkAgg("sum(DISTINCT x)", values, 2, 0d); } /** Very similar to {@code tester.checkType}, but generates inside a SELECT @@ -9071,9 +9160,6 @@ public abstract class SqlOperatorBaseTest { "Invalid number of arguments to function 'EVERY'. Was expecting 1 arguments", false); final String[] values = {"0", "CAST(null AS INTEGER)", "2", "2"}; - if (!enable) { - return; - } tester.checkAgg( "every(x = 2)", values, @@ -9099,9 +9185,6 @@ public abstract class SqlOperatorBaseTest { "Invalid number of arguments to function 'SOME'. Was expecting 1 arguments", false); final String[] values = {"0", "CAST(null AS INTEGER)", "2", "2"}; - if (!enable) { - return; - } tester.checkAgg( "some(x = 2)", values, diff --git a/core/src/test/java/org/apache/calcite/test/QuidemTest.java b/core/src/test/java/org/apache/calcite/test/QuidemTest.java index 55296ef..4bff995 100644 --- a/core/src/test/java/org/apache/calcite/test/QuidemTest.java +++ b/core/src/test/java/org/apache/calcite/test/QuidemTest.java @@ -252,6 +252,12 @@ public abstract class QuidemTest { .with(CalciteAssert.Config.REGULAR) .with(CalciteAssert.SchemaSpec.POST) .connect(); + case "post-big-query": + return CalciteAssert.that() + .with(CalciteConnectionProperty.FUN, "standard,bigquery") + .with(CalciteAssert.Config.REGULAR) + .with(CalciteAssert.SchemaSpec.POST) + .connect(); case "mysqlfunc": return CalciteAssert.that() .with(CalciteConnectionProperty.FUN, "mysql") diff --git a/core/src/test/resources/sql/agg.iq b/core/src/test/resources/sql/agg.iq index 3653b3a..25e627b 100644 --- a/core/src/test/resources/sql/agg.iq +++ b/core/src/test/resources/sql/agg.iq @@ -2874,4 +2874,20 @@ from emp group by gender; !ok +# STRING_AGG (BigQuery and PostgreSQL) is very similar to LISTAGG. +!use post-big-query +select + string_agg(ename order by deptno, ename) as default_string_agg_sep, + string_agg(ename, '; ' order by deptno, ename desc) as custom_string_agg_sep +from emp group by gender; ++----------------------------------+---------------------------------------+ +| DEFAULT_STRING_AGG_SEP | CUSTOM_STRING_AGG_SEP | ++----------------------------------+---------------------------------------+ +| Bob,Eric,Adam | Bob; Eric; Adam | +| Jane,Alice,Susan,Eve,Grace,Wilma | Jane; Susan; Alice; Eve; Grace; Wilma | ++----------------------------------+---------------------------------------+ +(2 rows) + +!ok + # End agg.iq diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 9c2740e..4b8a7fe 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -388,6 +388,8 @@ ALWAYS, APPLY, **ARE**, **ARRAY**, +ARRAY_AGG, +ARRAY_CONCAT_AGG, **ARRAY_MAX_CARDINALITY**, **AS**, ASC, @@ -944,6 +946,7 @@ STATEMENT, **STDDEV_POP**, **STDDEV_SAMP**, **STREAM**, +STRING_AGG, STRUCTURE, STYLE, SUBCLASS_ORIGIN, @@ -2513,10 +2516,13 @@ Dialect-specific aggregate functions. | C | Operator syntax | Description |:- |:-----------------------------------------------|:----------- +| b p | ARRAY_AGG( [ ALL | DISTINCT ] value [ RESPECT NULLS | IGNORE NULLS ] [ ORDER BY orderItem [, orderItem ]* ] ) | Gathers values into arrays +| b p | ARRAY_CONCAT_AGG( [ ALL | DISTINCT ] value [ ORDER BY orderItem [, orderItem ]* ] ) | Concatenates arrays into arrays | p | BOOL_AND(condition) | Synonym for `EVERY` | p | BOOL_OR(condition) | Synonym for `SOME` | b | LOGICAL_AND(condition) | Synonym for `EVERY` | b | LOGICAL_OR(condition) | Synonym for `SOME` +| b p | STRING_AGG( [ ALL | DISTINCT ] value [, separator] [ ORDER BY orderItem [, orderItem ]* ] ) | Synonym for `LISTAGG` Usage Examples:
