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 2017/10/02 21:00:03 UTC
[03/15] calcite git commit: [CALCITE-1946] JDBC adapter should
generate sub-SELECT if dialect does not support nested aggregate functions
(Pawel Ruchaj)
[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 <pa...@profimedia.co.uk>
Authored: Thu Aug 17 15:38:48 2017 +0100
Committer: Julian Hyde <jh...@apache.org>
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\"";