This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit e20f8819e8017e9bde7317a43f2c56cb39bdab8d Author: hannerwang <[email protected]> AuthorDate: Wed Aug 25 16:48:09 2021 +0800 [CALCITE-4740] JDBC adapter generates incorrect HAVING clause in BigQuery dialect Close apache/calcite#2504 --- .../apache/calcite/rel/rel2sql/SqlImplementor.java | 5 +++-- .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 24 ++++++++++++++++++++++ 2 files changed, 27 insertions(+), 2 deletions(-) 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 5e35f4f..1773a28 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 @@ -1682,13 +1682,14 @@ public abstract class SqlImplementor { switch (selectItem.getKind()) { case AS: final SqlCall asCall = (SqlCall) selectItem; - if (aliasRef) { + SqlNode alias = asCall.operand(1); + if (aliasRef && !SqlUtil.isGeneratedAlias(((SqlIdentifier) alias).getSimple())) { // For BigQuery, given the query // SELECT SUM(x) AS x FROM t HAVING(SUM(t.x) > 0) // we can generate // SELECT SUM(x) AS x FROM t HAVING(x > 0) // because 'x' in HAVING resolves to the 'AS x' not 't.x'. - return asCall.operand(1); + return alias; } return asCall.operand(0); default: 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 50f66c6..1b700f3 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 @@ -6167,6 +6167,30 @@ class RelToSqlConverterTest { .withBigQuery().ok(expected); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-4740";>[CALCITE-4740] + * JDBC adapter generates incorrect HAVING clause in BigQuery dialect</a>. */ + @Test void testBigQueryHavingWithoutGeneratedAlias() { + final String sql = "" + + "SELECT \"DEPTNO\", COUNT(DISTINCT \"EMPNO\")\n" + + "FROM \"EMP\"\n" + + "GROUP BY \"DEPTNO\"\n" + + "HAVING COUNT(DISTINCT \"EMPNO\") > 0\n" + + "ORDER BY COUNT(DISTINCT \"EMPNO\") DESC"; + final String expected = "" + + "SELECT DEPTNO, COUNT(DISTINCT EMPNO)\n" + + "FROM SCOTT.EMP\n" + + "GROUP BY DEPTNO\n" + + "HAVING COUNT(DISTINCT EMPNO) > 0\n" + + "ORDER BY COUNT(DISTINCT EMPNO) IS NULL DESC, COUNT(DISTINCT EMPNO) DESC"; + + // Convert rel node to SQL with BigQuery dialect, + // in which "isHavingAlias" is true. + sql(sql) + .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT) + .withBigQuery().ok(expected); + } + /** Fluid interface to run tests. */ static class Sql { private final CalciteAssert.SchemaSpec schemaSpec;
