Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id DA3D9200C36 for ; Fri, 10 Mar 2017 15:22:21 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id D8F38160B79; Fri, 10 Mar 2017 14:22:21 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 04321160B69 for ; Fri, 10 Mar 2017 15:22:20 +0100 (CET) Received: (qmail 36664 invoked by uid 500); 10 Mar 2017 14:22:20 -0000 Mailing-List: contact commits-help@calcite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@calcite.apache.org Delivered-To: mailing list commits@calcite.apache.org Received: (qmail 36626 invoked by uid 99); 10 Mar 2017 14:22:20 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 10 Mar 2017 14:22:20 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id F157DDFDC8; Fri, 10 Mar 2017 14:22:19 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: jbalint@apache.org To: commits@calcite.apache.org Message-Id: <7a0ca3b86c8649e98e252bc8bdb43d1e@git.apache.org> X-Mailer: ASF-Git Admin Mailer Subject: calcite git commit: [CALCITE-1665] HAVING support in RelToSqlConverter (Zhiqiang He) Date: Fri, 10 Mar 2017 14:22:19 +0000 (UTC) archived-at: Fri, 10 Mar 2017 14:22:22 -0000 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 Authored: Mon Mar 6 14:24:01 2017 +0800 Committer: Jess Balint 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 + * [CALCITE-1665] + * Aggregates and having cannot be combined. */ + @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 + * [CALCITE-1665] + * Aggregates and having cannot be combined. */ + @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); }