calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jbal...@apache.org
Subject calcite git commit: [CALCITE-1665] HAVING support in RelToSqlConverter (Zhiqiang He)
Date Fri, 10 Mar 2017 14:22:19 GMT
Repository: calcite
Updated Branches:
  refs/heads/master 69e9fe7c2 -> 60777142e


[CALCITE-1665] HAVING support in RelToSqlConverter (Zhiqiang He)

Close apache/calcite#388


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/60777142
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/60777142
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/60777142

Branch: refs/heads/master
Commit: 60777142e0b7be9302e22c1dd934020c6e64be69
Parents: 69e9fe7
Author: Zhiqiang-He <absolute005@qq.com>
Authored: Mon Mar 6 14:24:01 2017 +0800
Committer: Jess Balint <jbalint@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/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/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/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/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);
   }
 


Mime
View raw message