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
The following commit(s) were added to refs/heads/master by this push:
new f277a24 [CALCITE-4349] GROUP_CONCAT aggregate function (MySQL's
equivalent of LISTAGG)
f277a24 is described below
commit f277a2468805999a446e5bcd0ef70aa1e9550562
Author: zhen wang <[email protected]>
AuthorDate: Fri Jan 1 19:27:09 2021 +0800
[CALCITE-4349] GROUP_CONCAT aggregate function (MySQL's equivalent of
LISTAGG)
Close apache/calcite#2311
---
core/src/main/codegen/default_config.fmpp | 2 +
core/src/main/codegen/templates/Parser.jj | 15 ++++++-
.../main/java/org/apache/calcite/sql/SqlKind.java | 10 ++++-
.../main/java/org/apache/calcite/sql/SqlUtil.java | 5 +++
.../calcite/sql/fun/SqlBasicAggFunction.java | 48 ++++++++++++++++++----
.../calcite/sql/fun/SqlInternalOperators.java | 8 ++++
.../calcite/sql/fun/SqlLibraryOperators.java | 18 ++++++++
.../org/apache/calcite/sql/type/ReturnTypes.java | 24 ++++++++---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 45 +++++++++++++++++---
.../apache/calcite/sql/parser/SqlParserTest.java | 15 +++++++
.../calcite/sql/test/SqlOperatorBaseTest.java | 37 +++++++++++++++++
.../java/org/apache/calcite/test/QuidemTest.java | 2 +
.../org/apache/calcite/test/SqlValidatorTest.java | 2 +-
core/src/test/resources/sql/agg.iq | 43 ++++++++++++++++++-
site/_docs/reference.md | 3 ++
15 files changed, 252 insertions(+), 25 deletions(-)
diff --git a/core/src/main/codegen/default_config.fmpp
b/core/src/main/codegen/default_config.fmpp
index d683329..d39bf7b 100644
--- a/core/src/main/codegen/default_config.fmpp
+++ b/core/src/main/codegen/default_config.fmpp
@@ -126,6 +126,7 @@ parser: {
"GO"
"GOTO"
"GRANTED"
+ "GROUP_CONCAT"
"HIERARCHY"
"HOP"
"HOURS"
@@ -241,6 +242,7 @@ parser: {
"SECTION"
"SECURITY"
"SELF"
+ "SEPARATOR"
"SEQUENCE"
"SERIALIZABLE"
"SERVER"
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 9ea92ea..253e023 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -106,6 +106,7 @@ import org.apache.calcite.sql.SqlWindow;
import org.apache.calcite.sql.SqlWith;
import org.apache.calcite.sql.SqlWithItem;
import org.apache.calcite.sql.fun.SqlCase;
+import org.apache.calcite.sql.fun.SqlInternalOperators;
import org.apache.calcite.sql.fun.SqlLibraryOperators;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.fun.SqlTrimFunction;
@@ -3896,21 +3897,24 @@ SqlNode RowExpressionExtension() :
}
/**
- * Parses a call to the STRING_AGG aggregate function.
+ * Parses a call to the STRING_AGG aggregate function (or to an aggregate
+ * function with similar syntax: ARRAY_AGG, ARRAY_CONCAT_AGG, GROUP_CONCAT).
*/
SqlCall StringAggFunctionCall() :
{
- final Span s;
+ final Span s, s2;
final SqlOperator op;
final List<SqlNode> args = new ArrayList();
final SqlLiteral qualifier;
final SqlNodeList orderBy;
final Pair<SqlParserPos, SqlOperator> nullTreatment;
+ final SqlNode separator;
}
{
(
<ARRAY_AGG> { s = span(); op = SqlLibraryOperators.ARRAY_AGG; }
| <ARRAY_CONCAT_AGG> { s = span(); op =
SqlLibraryOperators.ARRAY_CONCAT_AGG; }
+ | <GROUP_CONCAT> { s = span(); op = SqlLibraryOperators.GROUP_CONCAT; }
| <STRING_AGG> { s = span(); op = SqlLibraryOperators.STRING_AGG; }
)
<LPAREN>
@@ -3937,6 +3941,11 @@ SqlCall StringAggFunctionCall() :
args.add(orderBy);
}
]
+ [
+ <SEPARATOR> { s2 = span(); } separator = StringLiteral() {
+ args.add(SqlInternalOperators.SEPARATOR.createCall(s2.end(this),
separator));
+ }
+ ]
<RPAREN>
{
SqlCall call = op.createCall(qualifier, s.end(this), args);
@@ -7441,6 +7450,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < GRANT: "GRANT" >
| < GRANTED: "GRANTED" >
| < GROUP: "GROUP" >
+| < GROUP_CONCAT: "GROUP_CONCAT" >
| < GROUPING: "GROUPING" >
| < GROUPS: "GROUPS" >
| < HAVING: "HAVING" >
@@ -7707,6 +7717,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < SELECT: "SELECT" > { afterTableName(); }
| < SELF: "SELF" >
| < SENSITIVE: "SENSITIVE" >
+| < SEPARATOR: "SEPARATOR" >
| < SEQUENCE: "SEQUENCE" >
| < SERIALIZABLE: "SERIALIZABLE" >
| < SERVER: "SERVER" >
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 d7a6493..d8858a3 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -374,6 +374,9 @@ public enum SqlKind {
/** {@code INTERVAL} expression. */
INTERVAL,
+ /** {@code SEPARATOR} expression. */
+ SEPARATOR,
+
/** {@code NULLIF} operator. */
NULLIF,
@@ -832,6 +835,9 @@ public enum SqlKind {
/** The {@code ARRAY_CONCAT_AGG} aggregate function. */
ARRAY_CONCAT_AGG,
+ /** The {@code GROUP_CONCAT} aggregate function. */
+ GROUP_CONCAT,
+
/** The {@code COLLECT} aggregate function. */
COLLECT,
@@ -1061,7 +1067,7 @@ 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, STRING_AGG, ARRAY_AGG, ARRAY_CONCAT_AGG, COUNTIF,
+ LISTAGG, STRING_AGG, ARRAY_AGG, ARRAY_CONCAT_AGG, GROUP_CONCAT,
COUNTIF,
INTERSECTION, ANY_VALUE);
/**
@@ -1143,7 +1149,7 @@ public enum SqlKind {
concat(
EnumSet.of(AS, ARGUMENT_ASSIGNMENT, DEFAULT,
RUNNING, FINAL, LAST, FIRST, PREV, NEXT,
- FILTER, WITHIN_GROUP, IGNORE_NULLS, RESPECT_NULLS,
+ FILTER, WITHIN_GROUP, IGNORE_NULLS, RESPECT_NULLS, SEPARATOR,
DESCENDING, CUBE, ROLLUP, GROUPING_SETS, EXTEND, LATERAL,
SELECT, JOIN, OTHER_FUNCTION, POSITION, CAST, TRIM, FLOOR,
CEIL,
TIMESTAMP_ADD, TIMESTAMP_DIFF, EXTRACT, INTERVAL,
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 2fd99e5..e87695b 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
@@ -342,11 +342,16 @@ public abstract class SqlUtil {
for (SqlNode operand : call.getOperandList()) {
if (ordered && operand instanceof SqlNodeList) {
writer.sep("ORDER BY");
+ } else if (ordered && operand.getKind() == SqlKind.SEPARATOR) {
+ writer.sep("SEPARATOR");
+ ((SqlCall) operand).operand(0).unparse(writer, 0, 0);
+ continue;
} else {
writer.sep(",");
}
operand.unparse(writer, 0, 0);
}
+
writer.endList(frame);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlBasicAggFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlBasicAggFunction.java
index bc52b9b..31e3080 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlBasicAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlBasicAggFunction.java
@@ -50,6 +50,7 @@ public final class SqlBasicAggFunction extends SqlAggFunction
{
private final Optionality distinctOptionality;
private final SqlSyntax syntax;
private final boolean allowsNullTreatment;
+ private final boolean allowsSeparator;
//~ Constructors -----------------------------------------------------------
@@ -59,7 +60,7 @@ public final class SqlBasicAggFunction extends SqlAggFunction
{
SqlOperandTypeChecker operandTypeChecker, SqlFunctionCategory funcType,
boolean requiresOrder, boolean requiresOver,
Optionality requiresGroupOrder, Optionality distinctOptionality,
- SqlSyntax syntax, boolean allowsNullTreatment) {
+ SqlSyntax syntax, boolean allowsNullTreatment, boolean allowsSeparator) {
super(name, sqlIdentifier, kind,
requireNonNull(returnTypeInference), operandTypeInference,
requireNonNull(operandTypeChecker),
@@ -68,6 +69,7 @@ public final class SqlBasicAggFunction extends SqlAggFunction
{
this.distinctOptionality = requireNonNull(distinctOptionality);
this.syntax = requireNonNull(syntax);
this.allowsNullTreatment = allowsNullTreatment;
+ this.allowsSeparator = allowsSeparator;
}
/** Creates a SqlBasicAggFunction whose name is the same as its kind. */
@@ -83,17 +85,30 @@ public final class SqlBasicAggFunction extends
SqlAggFunction {
SqlOperandTypeChecker operandTypeChecker) {
return new SqlBasicAggFunction(name, null, kind, returnTypeInference, null,
operandTypeChecker, SqlFunctionCategory.NUMERIC, false, false,
- Optionality.FORBIDDEN, Optionality.OPTIONAL, SqlSyntax.FUNCTION,
false);
+ Optionality.FORBIDDEN, Optionality.OPTIONAL, SqlSyntax.FUNCTION, false,
+ false);
}
//~ Methods ----------------------------------------------------------------
@Override public RelDataType deriveType(SqlValidator validator,
SqlValidatorScope scope, SqlCall call) {
+ SqlCall strippedCall = call;
if (syntax == SqlSyntax.ORDERED_FUNCTION) {
- call = ReturnTypes.stripOrderBy(call);
+ if (allowsSeparator) {
+ strippedCall = ReturnTypes.stripSeparator(strippedCall);
+ }
+ strippedCall = ReturnTypes.stripOrderBy(strippedCall);
}
- return super.deriveType(validator, scope, call);
+
+ RelDataType derivedType = super.deriveType(validator, scope, strippedCall);
+
+ // Assigning back the operands that might have been casted by validator
+ for (int i = 0; i < strippedCall.getOperandList().size(); i++) {
+ call.setOperand(i, strippedCall.getOperandList().get(i));
+ }
+
+ return derivedType;
}
@Override public Optionality getDistinctOptionality() {
@@ -116,7 +131,7 @@ public final class SqlBasicAggFunction extends
SqlAggFunction {
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), getFunctionType(), requiresOrder(),
requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
- allowsNullTreatment);
+ allowsNullTreatment, allowsSeparator);
}
/** Sets {@link #getFunctionType()}. */
@@ -125,7 +140,7 @@ public final class SqlBasicAggFunction extends
SqlAggFunction {
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), category, requiresOrder(),
requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
- allowsNullTreatment);
+ allowsNullTreatment, allowsSeparator);
}
@Override public SqlSyntax getSyntax() {
@@ -138,7 +153,7 @@ public final class SqlBasicAggFunction extends
SqlAggFunction {
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), getFunctionType(), requiresOrder(),
requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
- allowsNullTreatment);
+ allowsNullTreatment, allowsSeparator);
}
@Override public boolean allowsNullTreatment() {
@@ -151,7 +166,22 @@ public final class SqlBasicAggFunction extends
SqlAggFunction {
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), getFunctionType(), requiresOrder(),
requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
- allowsNullTreatment);
+ allowsNullTreatment, allowsSeparator);
+ }
+
+ /** Returns whether this aggregate function allows '{@code SEPARATOR string}'
+ * among its arguments. */
+ public boolean allowsSeparator() {
+ return allowsSeparator;
+ }
+
+ /** Sets {@link #allowsSeparator()}. */
+ public SqlBasicAggFunction withAllowsSeparator(boolean allowsSeparator) {
+ return new SqlBasicAggFunction(getName(), getSqlIdentifier(), kind,
+ getReturnTypeInference(), getOperandTypeInference(),
+ getOperandTypeChecker(), getFunctionType(), requiresOrder(),
+ requiresOver(), requiresGroupOrder(), distinctOptionality, syntax,
+ allowsNullTreatment, allowsSeparator);
}
/** Sets {@link #requiresGroupOrder()}. */
@@ -160,6 +190,6 @@ public final class SqlBasicAggFunction extends
SqlAggFunction {
getReturnTypeInference(), getOperandTypeInference(),
getOperandTypeChecker(), getFunctionType(), requiresOrder(),
requiresOver(), groupOrder, distinctOptionality, syntax,
- allowsNullTreatment);
+ allowsNullTreatment, allowsSeparator);
}
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
index e22b179..ccda212 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
@@ -20,11 +20,14 @@ import org.apache.calcite.rex.RexCall;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlInternalOperator;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.type.InferTypes;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.util.Litmus;
@@ -104,6 +107,11 @@ public abstract class SqlInternalOperators {
}
};
+ /** Separator expression inside GROUP_CONCAT, e.g. '{@code SEPARATOR ','}'.
*/
+ public static final SqlOperator SEPARATOR =
+ new SqlInternalOperator("SEPARATOR", SqlKind.SEPARATOR, 20, false,
+ ReturnTypes.ARG0, InferTypes.RETURN_TYPE, OperandTypes.ANY);
+
/** All implementations of {@code SUBSTRING} and {@code SUBSTR} map onto
* this. */
// TODO:
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 911fc21..98e4b22 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
@@ -356,6 +356,24 @@ public abstract class SqlLibraryOperators {
.withFunctionType(SqlFunctionCategory.SYSTEM)
.withSyntax(SqlSyntax.ORDERED_FUNCTION);
+ /** The "GROUP_CONCAT([DISTINCT] expr [, ...] [ORDER BY ...] [SEPARATOR
sep])"
+ * aggregate function, MySQL's equivalent of
+ * {@link SqlStdOperatorTable#LISTAGG}.
+ *
+ * <p>{@code GROUP_CONCAT(v ORDER BY x, y SEPARATOR s)} is implemented by
+ * rewriting to {@code LISTAGG(v, s) WITHIN GROUP (ORDER BY x, y)}. */
+ @LibraryOperator(libraries = {MYSQL})
+ public static final SqlAggFunction GROUP_CONCAT =
+ SqlBasicAggFunction
+ .create(SqlKind.GROUP_CONCAT,
+ ReturnTypes.andThen(ReturnTypes::stripOrderBy,
+ ReturnTypes.ARG0_NULLABLE),
+ OperandTypes.or(OperandTypes.STRING, OperandTypes.STRING_STRING))
+ .withFunctionType(SqlFunctionCategory.SYSTEM)
+ .withAllowsNullTreatment(false)
+ .withAllowsSeparator(true)
+ .withSyntax(SqlSyntax.ORDERED_FUNCTION);
+
/** 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/type/ReturnTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
index 881d810..5594878 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
@@ -26,6 +26,7 @@ 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.SqlKind;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.SqlUtil;
@@ -111,16 +112,19 @@ public abstract class ReturnTypes {
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}. */
+ /** Converts a binding of {@code FOO(x, y ORDER BY z)}
+ * or {@code FOO(x, y ORDER BY z SEPARATOR s)}
+ * to a binding of {@code FOO(x, y)}.
+ * Used for {@code STRING_AGG} and {@code GROUP_CONCAT}. */
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()) {
+ final SqlCall call2 = stripSeparator(callBinding.getCall());
+ final SqlCall call3 = stripOrderBy(call2);
+ if (call3 != callBinding.getCall()) {
return new SqlCallBinding(callBinding.getValidator(),
- callBinding.getScope(), call2);
+ callBinding.getScope(), call3);
}
}
return operatorBinding;
@@ -138,6 +142,16 @@ public abstract class ReturnTypes {
return call;
}
+ public static SqlCall stripSeparator(SqlCall call) {
+ if (!call.getOperandList().isEmpty()
+ && Util.last(call.getOperandList()).getKind() == SqlKind.SEPARATOR) {
+ // Remove the last argument if it is "SEPARATOR literal".
+ 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).
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 c099f45..ef1b058 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -5550,6 +5550,7 @@ public class SqlToRelConverter {
assert outerCall != null;
final List<SqlNode> operands = call.getOperandList();
final SqlParserPos pos = call.getParserPosition();
+ final SqlCall call2;
switch (call.getKind()) {
case FILTER:
assert filter == null;
@@ -5568,14 +5569,16 @@ public class SqlToRelConverter {
translateAgg(call.operand(0), filter, orderList, ignoreNulls,
outerCall);
return;
+
case COUNTIF:
// COUNTIF(b) ==> COUNT(*) FILTER (WHERE b)
// COUNTIF(b) FILTER (WHERE b2) ==> COUNT(*) FILTER (WHERE b2 AND b)
- final SqlCall call4 =
+ call2 =
SqlStdOperatorTable.COUNT.createCall(pos, SqlIdentifier.star(pos));
final SqlNode filter2 = SqlUtil.andExpressions(filter,
call.operand(0));
- translateAgg(call4, filter2, orderList, ignoreNulls, outerCall);
+ translateAgg(call2, filter2, 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)";
@@ -5588,11 +5591,42 @@ public class SqlToRelConverter {
} else {
operands2 = operands;
}
- final SqlCall call2 =
+ call2 =
+ SqlStdOperatorTable.LISTAGG.createCall(
+ call.getFunctionQuantifier(), pos, operands2);
+ translateAgg(call2, filter, orderList, ignoreNulls, outerCall);
+ return;
+
+ case GROUP_CONCAT:
+ // Translate "GROUP_CONCAT(s ORDER BY x, y SEPARATOR ',')"
+ // as if it were "LISTAGG(s, ',') WITHIN GROUP (ORDER BY x, y)".
+ // To do this, build a list of operands without ORDER BY with with sep.
+ operands2 = new ArrayList<>(operands);
+ final SqlNode separator;
+ if (!operands2.isEmpty()
+ && Util.last(operands2).getKind() == SqlKind.SEPARATOR) {
+ final SqlCall sepCall =
+ (SqlCall) operands2.remove(operands.size() - 1);
+ separator = sepCall.operand(0);
+ } else {
+ separator = null;
+ }
+
+ if (!operands2.isEmpty()
+ && Util.last(operands2) instanceof SqlNodeList) {
+ orderList = (SqlNodeList) operands2.remove(operands2.size() - 1);
+ }
+
+ if (separator != null) {
+ operands2.add(separator);
+ }
+
+ call2 =
SqlStdOperatorTable.LISTAGG.createCall(
call.getFunctionQuantifier(), pos, operands2);
translateAgg(call2, filter, orderList, ignoreNulls, outerCall);
return;
+
case ARRAY_AGG:
case ARRAY_CONCAT_AGG:
// Translate "ARRAY_AGG(s ORDER BY x, y)"
@@ -5601,14 +5635,15 @@ public class SqlToRelConverter {
if (!operands.isEmpty()
&& Util.last(operands) instanceof SqlNodeList) {
orderList = (SqlNodeList) Util.last(operands);
- final SqlCall call3 =
+ call2 =
call.getOperator().createCall(
call.getFunctionQuantifier(), pos, Util.skipLast(operands));
- translateAgg(call3, filter, orderList, ignoreNulls, outerCall);
+ translateAgg(call2, 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 3430afe..628bb4e 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
@@ -8974,6 +8974,21 @@ public class SqlParserTest {
sql(sql).ok(expected);
}
+ @Test void testGroupConcat() {
+ final String sql = "select\n"
+ + " group_concat(ename order by deptno, ename desc) as c2,\n"
+ + " group_concat(ename) as c3,\n"
+ + " group_concat(ename order by deptno, ename desc separator ',') as
c4\n"
+ + "from emp group by gender";
+ final String expected = "SELECT"
+ + " GROUP_CONCAT(`ENAME` ORDER BY `DEPTNO`, `ENAME` DESC) AS `C2`,"
+ + " GROUP_CONCAT(`ENAME`) AS `C3`,"
+ + " GROUP_CONCAT(`ENAME` ORDER BY `DEPTNO`, `ENAME` DESC SEPARATOR
',') 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 0b29fcc..d504449 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
@@ -7426,6 +7426,43 @@ public abstract class SqlOperatorBaseTest {
false);
}
+ @Test void testGroupConcatFunc() {
+ checkGroupConcatFunc(libraryTester(SqlLibrary.MYSQL));
+ checkGroupConcatFuncFails(libraryTester(SqlLibrary.BIG_QUERY));
+ checkGroupConcatFuncFails(libraryTester(SqlLibrary.POSTGRESQL));
+ }
+
+ private void checkGroupConcatFunc(SqlTester t) {
+ final String[] values = {"'x'", "null", "'yz'"};
+ t.checkAgg("group_concat(x)", values, "x,yz", 0);
+ t.checkAgg("group_concat(x,':')", values, "x:yz", 0);
+ t.checkAgg("group_concat(x,':' order by x)", values, "x:yz", 0);
+ t.checkAgg("group_concat(x order by x separator '|')", values, "x|yz", 0);
+ t.checkAgg("group_concat(x order by char_length(x) desc)", values,
+ "yz,x", 0);
+ t.checkAggFails("^group_concat(x respect nulls order by x desc)^", values,
+ "Cannot specify IGNORE NULLS or RESPECT NULLS following
'GROUP_CONCAT'",
+ false);
+ t.checkAggFails("^group_concat(x order by x desc)^ respect nulls", values,
+ "Cannot specify IGNORE NULLS or RESPECT NULLS following
'GROUP_CONCAT'",
+ false);
+ }
+
+ private void checkGroupConcatFuncFails(SqlTester t) {
+ final String[] values = {"'x'", "'y'"};
+ t.checkAggFails("^group_concat(x)^", values,
+ "No match found for function signature GROUP_CONCAT\\(<CHARACTER>\\)",
+ false);
+ t.checkAggFails("^group_concat(x, ',')^", values,
+ "No match found for function signature GROUP_CONCAT\\(<CHARACTER>, "
+ + "<CHARACTER>\\)",
+ false);
+ t.checkAggFails("^group_concat(x, ',' order by x desc)^", values,
+ "No match found for function signature GROUP_CONCAT\\(<CHARACTER>, "
+ + "<CHARACTER>\\)",
+ false);
+ }
+
@Test void testArrayAggFunc() {
checkArrayAggFunc(libraryTester(SqlLibrary.POSTGRESQL));
checkArrayAggFunc(libraryTester(SqlLibrary.BIG_QUERY));
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 e0eb974..cb44d57 100644
--- a/core/src/test/java/org/apache/calcite/test/QuidemTest.java
+++ b/core/src/test/java/org/apache/calcite/test/QuidemTest.java
@@ -261,6 +261,8 @@ public abstract class QuidemTest {
case "mysqlfunc":
return CalciteAssert.that()
.with(CalciteConnectionProperty.FUN, "mysql")
+ .with(CalciteAssert.Config.REGULAR)
+ .with(CalciteAssert.SchemaSpec.POST)
.connect();
case "oraclefunc":
return CalciteAssert.that()
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index e2ca616..a969c16 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7259,7 +7259,7 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
}
}
- @Test void testGroupAgg() {
+ @Test void testGroupByAliasedColumn() {
// alias in GROUP BY query has been known to cause problems
sql("select deptno as d, count(*) as c from emp group by deptno").ok();
}
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 807deb6..aee5c4a 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -2852,8 +2852,49 @@ from emp group by gender;
!ok
-# STRING_AGG (BigQuery and PostgreSQL) is very similar to LISTAGG.
+!use mysqlfunc
+
+# GROUP_CONCAT (MySQL) is very similar to LISTAGG.
+
+# GROUP_CONCAT with DISTINCT, SEPARATOR
+select
+ group_concat(distinct ename order by ename) as combined_name,
+ group_concat(ename order by ename separator ';') as separated_name
+from emp;
++------------------------------------------------+------------------------------------------------+
+| COMBINED_NAME | SEPARATED_NAME
|
++------------------------------------------------+------------------------------------------------+
+| Adam,Alice,Bob,Eric,Eve,Grace,Jane,Susan,Wilma |
Adam;Alice;Bob;Eric;Eve;Grace;Jane;Susan;Wilma |
++------------------------------------------------+------------------------------------------------+
+(1 row)
+
+!ok
+
+# GROUP_CONCAT with multiple columns
+select
+ group_concat(deptno, ename order by ename) as combined_name
+from emp;
++-----------------------------------------------+
+| COMBINED_NAME |
++-----------------------------------------------+
+| 50Alice30Bob10Eric20Eve50Grace60Jane10Susan30 |
++-----------------------------------------------+
+(1 row)
+
+!ok
+
+# We currently do not support GROUP_CONCAT with composite columns and
+# SEPARATOR because LIST_AGG does not support it.
+!if (false) {
+select
+ group_concat(deptno, ename order by ename separator ';') as separated_name
+from emp;
+!ok
+!}
+
!use post-big-query
+
+# STRING_AGG (BigQuery and PostgreSQL) is very similar to LISTAGG.
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
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 96d9a50..0b84bd5 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -624,6 +624,7 @@ GRANTED,
**GROUP**,
**GROUPING**,
**GROUPS**,
+GROUP_CONCAT,
**HAVING**,
HIERARCHY,
**HOLD**,
@@ -889,6 +890,7 @@ SECURITY,
**SELECT**,
SELF,
**SENSITIVE**,
+SEPARATOR,
SEQUENCE,
SERIALIZABLE,
SERVER,
@@ -2579,6 +2581,7 @@ Dialect-specific aggregate functions.
| p | BOOL_AND(condition) | Synonym for `EVERY`
| p | BOOL_OR(condition) | Synonym for `SOME`
| b | COUNTIF(condition) | Returns the number of
rows for which *condition* is TRUE; equivalent to `COUNT(*) FILTER (WHERE
condition)`
+| m | GROUP_CONCAT( [ ALL | DISTINCT ] value [, value ]* [ ORDER BY
orderItem [, orderItem ]* ] [ SEPARATOR separator ] ) | MySQL-specific variant
of `LISTAGG`
| 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`
