You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by el...@apache.org on 2017/03/30 21:38:06 UTC
[16/50] [abbrv] calcite-avatica git commit: [CALCITE-1665] HAVING
support in RelToSqlConverter (Zhiqiang He)
[CALCITE-1665] HAVING support in RelToSqlConverter (Zhiqiang He)
Close apache/calcite#388
Project: http://git-wip-us.apache.org/repos/asf/calcite-avatica/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite-avatica/commit/60777142
Tree: http://git-wip-us.apache.org/repos/asf/calcite-avatica/tree/60777142
Diff: http://git-wip-us.apache.org/repos/asf/calcite-avatica/diff/60777142
Branch: refs/heads/master
Commit: 60777142e0b7be9302e22c1dd934020c6e64be69
Parents: 69e9fe7
Author: Zhiqiang-He <ab...@qq.com>
Authored: Mon Mar 6 14:24:01 2017 +0800
Committer: Jess Balint <jb...@gmail.com>
Committed: Fri Mar 10 08:20:46 2017 -0600
----------------------------------------------------------------------
.../calcite/rel/rel2sql/RelToSqlConverter.java | 22 ++++++--
.../calcite/rel/rel2sql/SqlImplementor.java | 5 ++
.../java/org/apache/calcite/sql/SqlSelect.java | 4 ++
.../rel/rel2sql/RelToSqlConverterTest.java | 55 +++++++++++++++++---
4 files changed, 74 insertions(+), 12 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/60777142/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index eefe8b1..1231dbc 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -133,11 +133,23 @@ public class RelToSqlConverter extends SqlImplementor
/** @see #dispatch */
public Result visit(Filter e) {
- Result x = visitChild(0, e.getInput());
- final Builder builder =
- x.builder(e, Clause.WHERE);
- builder.setWhere(builder.context.toSql(null, e.getCondition()));
- return builder.result();
+ final RelNode input = e.getInput();
+ Result x = visitChild(0, input);
+ if (input instanceof Aggregate) {
+ final Builder builder;
+ if (((Aggregate) input).getInput() instanceof Project) {
+ builder = x.builder(e);
+ builder.clauses.add(Clause.HAVING);
+ } else {
+ builder = x.builder(e, Clause.HAVING);
+ }
+ builder.setHaving(builder.context.toSql(null, e.getCondition()));
+ return builder.result();
+ } else {
+ final Builder builder = x.builder(e, Clause.WHERE);
+ builder.setWhere(builder.context.toSql(null, e.getCondition()));
+ return builder.result();
+ }
}
/** @see #dispatch */
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/60777142/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 e9c148f..f3287f0 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
@@ -1106,6 +1106,11 @@ public abstract class SqlImplementor {
select.setGroupBy(nodeList);
}
+ public void setHaving(SqlNode node) {
+ assert clauses.contains(Clause.HAVING);
+ select.setHaving(node);
+ }
+
public void setOrderBy(SqlNodeList nodeList) {
assert clauses.contains(Clause.ORDER_BY);
select.setOrderBy(nodeList);
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/60777142/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSelect.java b/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
index ea89b37..4854696 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSelect.java
@@ -160,6 +160,10 @@ public class SqlSelect extends SqlCall {
return having;
}
+ public void setHaving(SqlNode having) {
+ this.having = having;
+ }
+
public final SqlNodeList getSelectList() {
return selectList;
}
http://git-wip-us.apache.org/repos/asf/calcite-avatica/blob/60777142/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 b6d4780..34b9303 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
@@ -186,10 +186,53 @@ public class RelToSqlConverterTest {
String query = "select count(*) from \"product\" group by \"product_class_id\","
+ " \"product_id\" having \"product_id\" > 10";
final String expected = "SELECT COUNT(*)\n"
- + "FROM (SELECT \"product_class_id\", \"product_id\", COUNT(*)\n"
+ "FROM \"foodmart\".\"product\"\n"
- + "GROUP BY \"product_class_id\", \"product_id\") AS \"t0\"\n"
- + "WHERE \"product_id\" > 10";
+ + "GROUP BY \"product_class_id\", \"product_id\"\n"
+ + "HAVING \"product_id\" > 10";
+ sql(query).ok(expected);
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1665">[CALCITE-1665]
+ * Aggregates and having cannot be combined</a>. */
+ @Test public void testSelectQueryWithGroupByHaving2() {
+ String query = " select \"product\".\"product_id\",\n"
+ + " min(\"sales_fact_1997\".\"store_id\")\n"
+ + " from \"product\"\n"
+ + " inner join \"sales_fact_1997\"\n"
+ + " on \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"\n"
+ + " group by \"product\".\"product_id\"\n"
+ + " having count(*) > 1";
+
+ String expected = "SELECT \"product\".\"product_id\", "
+ + "MIN(\"sales_fact_1997\".\"store_id\")\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "INNER JOIN \"foodmart\".\"sales_fact_1997\" "
+ + "ON \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"\n"
+ + "GROUP BY \"product\".\"product_id\"\n"
+ + "HAVING COUNT(*) > 1";
+ sql(query).ok(expected);
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-1665">[CALCITE-1665]
+ * Aggregates and having cannot be combined</a>. */
+ @Test public void testSelectQueryWithGroupByHaving3() {
+ String query = " select * from (select \"product\".\"product_id\",\n"
+ + " min(\"sales_fact_1997\".\"store_id\")\n"
+ + " from \"product\"\n"
+ + " inner join \"sales_fact_1997\"\n"
+ + " on \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"\n"
+ + " group by \"product\".\"product_id\"\n"
+ + " having count(*) > 1) where \"product_id\" > 100";
+
+ String expected = "SELECT *\n"
+ + "FROM (SELECT \"product\".\"product_id\", MIN(\"sales_fact_1997\".\"store_id\")\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "INNER JOIN \"foodmart\".\"sales_fact_1997\" ON \"product\".\"product_id\" = \"sales_fact_1997\".\"product_id\"\n"
+ + "GROUP BY \"product\".\"product_id\"\n"
+ + "HAVING COUNT(*) > 1) AS \"t2\"\n"
+ + "WHERE \"t2\".\"product_id\" > 100";
sql(query).ok(expected);
}
@@ -458,11 +501,9 @@ public class RelToSqlConverterTest {
+ "group by \"product_class_id\", \"product_id\" "
+ "having \"product_id\" > 10";
final String expected = "SELECT COUNT(*)\n"
- + "FROM (SELECT product.product_class_id, product.product_id, COUNT"
- + "(*)\n"
+ "FROM foodmart.product AS product\n"
- + "GROUP BY product.product_class_id, product.product_id) AS t0\n"
- + "WHERE t0.product_id > 10";
+ + "GROUP BY product.product_class_id, product.product_id\n"
+ + "HAVING product.product_id > 10";
sql(query).dialect(DatabaseProduct.DB2.getDialect()).ok(expected);
}