This is an automated email from the ASF dual-hosted git repository.
chunwei pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 52c9b7836b [CALCITE-5163] MysqlSqlDialect support to unparse LISTAGG
aggregate function
52c9b7836b is described below
commit 52c9b7836bbed90038f28a933c0cb56d2d69cd28
Author: xurenhe <[email protected]>
AuthorDate: Fri May 20 16:37:04 2022 +0800
[CALCITE-5163] MysqlSqlDialect support to unparse LISTAGG aggregate function
---
.../calcite/sql/dialect/MysqlSqlDialect.java | 52 ++++++++++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 24 ++++++++++
2 files changed, 76 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
index be1c8ef501..195332db69 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
@@ -40,6 +40,8 @@ import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlWriter;
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.parser.SqlParserPos;
import org.apache.calcite.sql.type.InferTypes;
@@ -47,8 +49,12 @@ import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlTypeName;
+import com.google.common.collect.ImmutableList;
+
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.util.List;
+
/**
* A <code>SqlDialect</code> implementation for the MySQL database.
*/
@@ -223,11 +229,57 @@ public class MysqlSqlDialect extends SqlDialect {
unparseFloor(writer, call);
break;
+ case WITHIN_GROUP:
+ final List<SqlNode> operands = call.getOperandList();
+ if (operands.size() <= 0 || operands.get(0).getKind() !=
SqlKind.LISTAGG) {
+ super.unparseCall(writer, call, leftPrec, rightPrec);
+ return;
+ }
+ unparseListAggCall(writer, (SqlCall) operands.get(0),
+ operands.size() == 2 ? operands.get(1) : null, leftPrec, rightPrec);
+ break;
+
+ case LISTAGG:
+ unparseListAggCall(writer, call, null, leftPrec, rightPrec);
+ break;
+
default:
super.unparseCall(writer, call, leftPrec, rightPrec);
}
}
+ /**
+ * Unparses LISTAGG for MySQL. This call is translated to GROUP_CONCAT.<br/>
+ * For example:
+ * source: <code>LISTAGG(DISTINCT c1, ',') WITHIN GROUP (ORDER BY c2,
c3)</code>
+ * target: <code>GROUP_CONCAT(DISTINCT c1 ORDER BY c2, c3 SEPARATOR
',')</code>
+ *
+ * @param writer Writer
+ * @param listAggCall Call of LISTAGG
+ * @param orderItemNode Elems of WITHIN_GROUP, NULL means none elem in the
WITHIN_GROUP
+ * @param leftPrec leftPrec
+ * @param rightPrec rightPrec
+ */
+ private void unparseListAggCall(SqlWriter writer, SqlCall listAggCall,
+ @Nullable SqlNode orderItemNode, int leftPrec, int rightPrec) {
+ final List<SqlNode> listAggCallOperands = listAggCall.getOperandList();
+ final boolean separatorExist = listAggCallOperands.size() == 2;
+
+ final ImmutableList.Builder<SqlNode> newOperandListBuilder =
ImmutableList.<SqlNode>builder()
+ .add(listAggCallOperands.get(0));
+ if (orderItemNode != null) {
+ newOperandListBuilder.add(orderItemNode);
+ }
+ if (separatorExist) {
+ newOperandListBuilder.add(
+ SqlInternalOperators.SEPARATOR.createCall(
+ SqlParserPos.ZERO, listAggCallOperands.get(1)));
+ }
+
SqlLibraryOperators.GROUP_CONCAT.createCall(listAggCall.getFunctionQuantifier(),
+ listAggCall.getParserPosition(), newOperandListBuilder.build())
+ .unparse(writer, leftPrec, rightPrec);
+ }
+
/**
* Unparses datetime floor for MySQL. There is no TRUNC function, so simulate
* this using calls to DATE_FORMAT.
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index c2d235c132..4f6ee5d838 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -2457,6 +2457,30 @@ class RelToSqlConverterTest {
sql(query).withMysql().ok(expected);
}
+ @Test void testMySqlUnparseListAggCall() {
+ final String query = "select\n"
+ + "listagg(distinct \"product_name\", ',') within group(order by
\"cases_per_pallet\"),\n"
+ + "listagg(\"product_name\", ',') within group(order by
\"cases_per_pallet\"),\n"
+ + "listagg(distinct \"product_name\") within group(order by
\"cases_per_pallet\" desc),\n"
+ + "listagg(distinct \"product_name\", ',') within group(order by
\"cases_per_pallet\"),\n"
+ + "listagg(\"product_name\"),\n"
+ + "listagg(\"product_name\", ',')\n"
+ + "from \"product\"\n"
+ + "group by \"product_id\"\n";
+ final String expected = "SELECT GROUP_CONCAT(DISTINCT `product_name` "
+ + "ORDER BY `cases_per_pallet` IS NULL, `cases_per_pallet` SEPARATOR
','), "
+ + "GROUP_CONCAT(`product_name` "
+ + "ORDER BY `cases_per_pallet` IS NULL, `cases_per_pallet` SEPARATOR
','), "
+ + "GROUP_CONCAT(DISTINCT `product_name` "
+ + "ORDER BY `cases_per_pallet` IS NULL DESC, `cases_per_pallet` DESC),
"
+ + "GROUP_CONCAT(DISTINCT `product_name` "
+ + "ORDER BY `cases_per_pallet` IS NULL, `cases_per_pallet` SEPARATOR
','), "
+ + "GROUP_CONCAT(`product_name`), GROUP_CONCAT(`product_name` SEPARATOR
',')\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY `product_id`";
+ sql(query).withMysql().ok(expected);
+ }
+
@Test void
testMySqlWithHighNullsSelectWithOrderByAscNullsLastAndNoEmulation() {
final String query = "select \"product_id\" from \"product\"\n"
+ "order by \"product_id\" nulls last";
