[CALCITE-1946] JDBC adapter should generate sub-SELECT if dialect does not support nested aggregate functions (Pawel Ruchaj)
Close apache/calcite#520 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/4d0e83e7 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/4d0e83e7 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/4d0e83e7 Branch: refs/heads/master Commit: 4d0e83e7e51b28acff7305e1342c5107f1357b1c Parents: 41687f3 Author: Pawel <[email protected]> Authored: Thu Aug 17 15:38:48 2017 +0100 Committer: Julian Hyde <[email protected]> Committed: Mon Oct 2 11:13:41 2017 -0700 ---------------------------------------------------------------------- .../calcite/rel/rel2sql/SqlImplementor.java | 32 +++++++++++++++ .../java/org/apache/calcite/sql/SqlDialect.java | 15 +++++++ .../rel/rel2sql/RelToSqlConverterTest.java | 41 ++++++++++++++++++++ 3 files changed, 88 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/4d0e83e7/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 57155b7..3a4b649 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 @@ -23,6 +23,7 @@ import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.core.AggregateCall; import org.apache.calcite.rel.core.CorrelationId; import org.apache.calcite.rel.core.JoinRelType; +import org.apache.calcite.rel.logical.LogicalAggregate; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeField; import org.apache.calcite.rex.RexCall; @@ -41,6 +42,7 @@ import org.apache.calcite.rex.RexSubQuery; import org.apache.calcite.rex.RexWindow; import org.apache.calcite.rex.RexWindowBound; import org.apache.calcite.sql.JoinType; +import org.apache.calcite.sql.SqlAggFunction; import org.apache.calcite.sql.SqlBasicCall; import org.apache.calcite.sql.SqlBinaryOperator; import org.apache.calcite.sql.SqlCall; @@ -92,6 +94,7 @@ import java.util.AbstractList; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; +import java.util.HashSet; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.LinkedHashSet; @@ -1023,6 +1026,12 @@ public abstract class SqlImplementor { needNew = true; } } + if (rel instanceof LogicalAggregate + && !dialect.supportsNestedAggregations() + && hasNestedAggregations((LogicalAggregate) rel)) { + needNew = true; + } + SqlSelect select; Expressions.FluentList<Clause> clauseList = Expressions.list(); if (needNew) { @@ -1066,6 +1075,29 @@ public abstract class SqlImplementor { needNew ? null : aliases); } + private boolean hasNestedAggregations(LogicalAggregate rel) { + List<AggregateCall> aggCallList = rel.getAggCallList(); + HashSet<Integer> aggregatesArgs = new HashSet<>(); + for (AggregateCall aggregateCall: aggCallList) { + aggregatesArgs.addAll(aggregateCall.getArgList()); + } + for (Integer aggregatesArg : aggregatesArgs) { + SqlNode selectNode = ((SqlSelect) node).getSelectList().get(aggregatesArg); + if (!(selectNode instanceof SqlBasicCall)) { + continue; + } + for (SqlNode operand : ((SqlBasicCall) selectNode).getOperands()) { + if (operand instanceof SqlCall) { + final SqlOperator operator = ((SqlCall) operand).getOperator(); + if (operator instanceof SqlAggFunction) { + return true; + } + } + } + } + return false; + } + // make private? public Clause maxClause() { Clause maxClause = null; http://git-wip-us.apache.org/repos/asf/calcite/blob/4d0e83e7/core/src/main/java/org/apache/calcite/sql/SqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java index 6bc25ca..dafa8d9 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -554,6 +554,21 @@ public class SqlDialect { } } + /** + * Returns whether the dialect supports nested aggregations, for instance + * {@code SELECT SUM(SUM(1)) }. + */ + public boolean supportsNestedAggregations() { + switch (databaseProduct) { + case MYSQL: + case VERTICA: + case POSTGRESQL: + return false; + default: + return true; + } + } + /** Returns how NULL values are sorted if an ORDER BY item does not contain * NULLS ASCENDING or NULLS DESCENDING. */ public NullCollation getNullCollation() { http://git-wip-us.apache.org/repos/asf/calcite/blob/4d0e83e7/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 b72edfc..fb5d118 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 @@ -190,6 +190,47 @@ public class RelToSqlConverterTest { sql(query).ok(expected); } + /** 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>. */ + @Test public void testNestedAggregates() { + // PostgreSQL, MySQL, Vertica do not support nested aggregate functions, so + // for these, the JDBC adapter generates a SELECT in the FROM clause. + // Oracle can do it in a single SELECT. + final String query = "select\n" + + " SUM(\"net_weight1\") as \"net_weight_converted\"\n" + + " from (" + + " select\n" + + " SUM(\"net_weight\") as \"net_weight1\"\n" + + " from \"foodmart\".\"product\"\n" + + " group by \"product_id\")"; + final String expectedOracle = "SELECT SUM(SUM(\"net_weight\")) \"net_weight_converted\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_id\""; + final String expectedMySQL = "SELECT SUM(`net_weight1`) AS `net_weight_converted`\n" + + "FROM (SELECT SUM(`net_weight`) AS `net_weight1`\n" + + "FROM `foodmart`.`product`\n" + + "GROUP BY `product_id`) AS `t1`"; + final String expectedVertica = "SELECT SUM(\"net_weight1\") AS \"net_weight_converted\"\n" + + "FROM (SELECT SUM(\"net_weight\") AS \"net_weight1\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_id\") AS \"t1\""; + final String expectedPostgresql = "SELECT SUM(\"net_weight1\") AS \"net_weight_converted\"\n" + + "FROM (SELECT SUM(\"net_weight\") AS \"net_weight1\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "GROUP BY \"product_id\") AS \"t1\""; + sql(query) + .dialect(DatabaseProduct.ORACLE.getDialect()) + .ok(expectedOracle) + .dialect(DatabaseProduct.MYSQL.getDialect()) + .ok(expectedMySQL) + .dialect(DatabaseProduct.VERTICA.getDialect()) + .ok(expectedVertica) + .dialect(DatabaseProduct.POSTGRESQL.getDialect()) + .ok(expectedPostgresql); + } + @Test public void testSelectQueryWithGroupByAndProjectList1() { String query = "select count(*) from \"product\" group by \"product_class_id\", \"product_id\"";
