[CALCITE-1174] When generating SQL, translate SUM0(x) to COALESCE(SUM(x), 0)
Don't require that '(' and ')' are formally part of a list; it the
caller may not know the full context within the parse tree.
Add some tests for RelToSql applied to empty GROUP BY.
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/332ffb44
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/332ffb44
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/332ffb44
Branch: refs/heads/master
Commit: 332ffb44783142bd75ffd429e72bfba305aa3bf1
Parents: c33a4f4
Author: Julian Hyde <[email protected]>
Authored: Wed Oct 3 15:09:22 2018 -0700
Committer: Julian Hyde <[email protected]>
Committed: Wed Oct 31 12:01:27 2018 -0700
----------------------------------------------------------------------
.../calcite/rel/rel2sql/SqlImplementor.java | 39 +++++++----
.../calcite/sql/pretty/SqlPrettyWriter.java | 6 --
.../rel/rel2sql/RelToSqlConverterTest.java | 70 ++++++++++++++++++++
3 files changed, 97 insertions(+), 18 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/332ffb44/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
----------------------------------------------------------------------
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index 8660951..0323a52 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -675,11 +675,21 @@ public abstract class SqlImplementor {
orderList, isRows, lowerBound, upperBound, allowPartial, POS);
final List<SqlNode> nodeList = toSql(program, rexOver.getOperands());
- final SqlCall aggFunctionCall =
- rexOver.getAggOperator().createCall(POS, nodeList);
+ return createOverCall(rexOver.getAggOperator(), nodeList, sqlWindow);
+ }
+ private SqlCall createOverCall(SqlAggFunction op, List<SqlNode> operands,
+ SqlWindow window) {
+ if (op instanceof SqlSumEmptyIsZeroAggFunction) {
+ // Rewrite "SUM0(x) OVER w" to "COALESCE(SUM(x) OVER w, 0)"
+ final SqlCall node =
+ createOverCall(SqlStdOperatorTable.SUM, operands, window);
+ return SqlStdOperatorTable.COALESCE.createCall(POS, node,
+ SqlLiteral.createExactNumeric("0", POS));
+ }
+ final SqlCall aggFunctionCall = op.createCall(POS, operands);
return SqlStdOperatorTable.OVER.createCall(POS, aggFunctionCall,
- sqlWindow);
+ window);
}
private SqlNode toSql(RexProgram program, RexFieldCollation rfc) {
@@ -760,17 +770,22 @@ public abstract class SqlImplementor {
/** Converts a call to an aggregate function to an expression. */
public SqlNode toSql(AggregateCall aggCall) {
- SqlOperator op = aggCall.getAggregation();
- if (op instanceof SqlSumEmptyIsZeroAggFunction) {
- op = SqlStdOperatorTable.SUM;
- }
- final List<SqlNode> operands = Expressions.list();
+ final SqlOperator op = aggCall.getAggregation();
+ final List<SqlNode> operandList = Expressions.list();
for (int arg : aggCall.getArgList()) {
- operands.add(field(arg));
+ operandList.add(field(arg));
+ }
+ final SqlLiteral qualifier =
+ aggCall.isDistinct() ? SqlSelectKeyword.DISTINCT.symbol(POS) : null;
+ final SqlNode[] operands = operandList.toArray(new SqlNode[0]);
+ if (op instanceof SqlSumEmptyIsZeroAggFunction) {
+ final SqlNode node =
+ SqlStdOperatorTable.SUM.createCall(qualifier, POS, operands);
+ return SqlStdOperatorTable.COALESCE.createCall(POS, node,
+ SqlLiteral.createExactNumeric("0", POS));
+ } else {
+ return op.createCall(qualifier, POS, operands);
}
- return op.createCall(
- aggCall.isDistinct() ? SqlSelectKeyword.DISTINCT.symbol(POS) : null,
- POS, operands.toArray(new SqlNode[0]));
}
/** Converts a collation to an ORDER BY item. */
http://git-wip-us.apache.org/repos/asf/calcite/blob/332ffb44/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java
----------------------------------------------------------------------
diff --git
a/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java
b/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java
index 90ef611..06358a1 100644
--- a/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java
+++ b/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java
@@ -881,12 +881,6 @@ public class SqlPrettyWriter implements SqlWriter {
}
public void print(String s) {
- if (s.equals("(")) {
- throw new RuntimeException("Use 'startList'");
- }
- if (s.equals(")")) {
- throw new RuntimeException("Use 'endList'");
- }
maybeWhitespace(s);
pw.print(s);
charCount += s.length();
http://git-wip-us.apache.org/repos/asf/calcite/blob/332ffb44/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
----------------------------------------------------------------------
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 7e51158..70bf317 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
@@ -178,6 +178,37 @@ public class RelToSqlConverterTest {
sql(query).ok(expected);
}
+ @Test public void testSelectQueryWithGroupByEmpty() {
+ final String sql0 = "select count(*) from \"product\" group by ()";
+ final String sql1 = "select count(*) from \"product\"";
+ final String expected = "SELECT COUNT(*)\n"
+ + "FROM \"foodmart\".\"product\"";
+ final String expectedMySql = "SELECT COUNT(*)\n"
+ + "FROM `foodmart`.`product`";
+ sql(sql0)
+ .ok(expected)
+ .withMysql()
+ .ok(expectedMySql);
+ sql(sql1)
+ .ok(expected)
+ .withMysql()
+ .ok(expectedMySql);
+ }
+
+ @Test public void testSelectQueryWithGroupByEmpty2() {
+ final String query = "select 42 as c from \"product\" group by ()";
+ final String expected = "SELECT 42 AS \"C\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "GROUP BY ()";
+ final String expectedMySql = "SELECT 42 AS `C`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "GROUP BY ()";
+ sql(query)
+ .ok(expected)
+ .withMysql()
+ .ok(expectedMySql);
+ }
+
@Test public void testSelectQueryWithMinAggregateFunction() {
String query = "select min(\"net_weight\") from \"product\" group by
\"product_class_id\" ";
final String expected = "SELECT MIN(\"net_weight\")\n"
@@ -236,6 +267,45 @@ public class RelToSqlConverterTest {
}
/** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-1174";>[CALCITE-1174]
+ * When generating SQL, translate SUM0(x) to COALESCE(SUM(x), 0)</a>. */
+ @Test public void testSum0BecomesCoalesce() {
+ final RelBuilder builder = relBuilder();
+ final RelNode root = builder
+ .scan("EMP")
+ .aggregate(builder.groupKey(),
+ builder.aggregateCall(SqlStdOperatorTable.SUM0, false, false, null,
+ "s", builder.field(3)))
+ .build();
+ final String expectedMysql = "SELECT COALESCE(SUM(`MGR`), 0) AS `s`\n"
+ + "FROM `scott`.`EMP`";
+ assertThat(toSql(root, SqlDialect.DatabaseProduct.MYSQL.getDialect()),
+ isLinux(expectedMysql));
+ final String expectedPostgresql = "SELECT COALESCE(SUM(\"MGR\"), 0) AS
\"s\"\n"
+ + "FROM \"scott\".\"EMP\"";
+ assertThat(toSql(root, SqlDialect.DatabaseProduct.POSTGRESQL.getDialect()),
+ isLinux(expectedPostgresql));
+ }
+
+ /** As {@link #testSum0BecomesCoalesce()} but for windowed aggregates. */
+ @Test public void testWindowedSum0BecomesCoalesce() {
+ final String query = "select\n"
+ + " AVG(\"net_weight\") OVER (order by \"product_id\" rows 3
preceding)\n"
+ + "from \"foodmart\".\"product\"";
+ final String expectedPostgresql = "SELECT CASE WHEN (COUNT(\"net_weight\")"
+ + " OVER (ORDER BY \"product_id\" ROWS BETWEEN 3 PRECEDING AND CURRENT
ROW)) > 0 "
+ + "THEN CAST(COALESCE(SUM(\"net_weight\")"
+ + " OVER (ORDER BY \"product_id\" ROWS BETWEEN 3 PRECEDING AND CURRENT
ROW), 0)"
+ + " AS DOUBLE PRECISION) "
+ + "ELSE NULL END / (COUNT(\"net_weight\")"
+ + " OVER (ORDER BY \"product_id\" ROWS BETWEEN 3 PRECEDING AND CURRENT
ROW))\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(query)
+ .withPostgresql()
+ .ok(expectedPostgresql);
+ }
+
+ /** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-1946";>[CALCITE-1946]
* JDBC adapter should generate sub-SELECT if dialect does not support nested
* aggregate functions</a>. */
