You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2020/10/22 21:30:55 UTC
[calcite] 02/02: [CALCITE-4335] ARRAY_AGG, ARRAY_CONCAT_AGG,
STRING_AGG aggregate functions for BigQuery
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 <jh...@apache.org>
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: