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 7e557390a08a583e07faf51ee2c8e03829b57b06 Author: Steven Talbot <ste...@looker.com> AuthorDate: Wed Jun 24 16:57:33 2020 -0700 [CALCITE-4090] When generating SQL for DB2, a complex SELECT above a sub-query generates a bad table alias (Steven Talbot) Close apache/calcite#2045 --- .../apache/calcite/rel/rel2sql/SqlImplementor.java | 5 ++- .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 51 ++++++++++++++++++++++ 2 files changed, 54 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 2396c3f..8d2739f 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 @@ -1359,6 +1359,7 @@ public abstract class SqlImplementor { } clauseList.appendAll(clauses); final Context newContext; + Map<String, RelDataType> newAliases = null; final SqlNodeList selectList = select.getSelectList(); if (selectList != null) { newContext = new Context(dialect, selectList.size()) { @@ -1408,7 +1409,7 @@ public abstract class SqlImplementor { if (needNew && neededAlias != null && (aliases.size() != 1 || !aliases.containsKey(neededAlias))) { - final Map<String, RelDataType> newAliases = + newAliases = ImmutableMap.of(neededAlias, rel.getInput(0).getRowType()); newContext = aliasContext(newAliases, qualified); } else { @@ -1416,7 +1417,7 @@ public abstract class SqlImplementor { } } return new Builder(rel, clauseList, select, newContext, isAnon(), - needNew ? null : aliases); + needNew && !aliases.containsKey(neededAlias) ? newAliases : aliases); } /** Returns whether a new sub-query is required. */ 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 d8681db..b134a16 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 @@ -2514,6 +2514,57 @@ class RelToSqlConverterTest { sql(query).withDb2().ok(expected); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-4090">[CALCITE-4090] + * DB2 aliasing breaks with a complex SELECT above a sub-query</a>. */ + @Test void testDb2SubQueryAlias() { + String query = "select count(foo), \"units_per_case\"\n" + + "from (select \"units_per_case\", \"cases_per_pallet\",\n" + + " \"product_id\", 1 as foo\n" + + " from \"product\")\n" + + "where \"cases_per_pallet\" > 100\n" + + "group by \"product_id\", \"units_per_case\"\n" + + "order by \"units_per_case\" desc"; + final String expected = "SELECT COUNT(*), t.units_per_case\n" + + "FROM (SELECT product.units_per_case, product.cases_per_pallet, " + + "product.product_id, 1 AS FOO\n" + + "FROM foodmart.product AS product) AS t\n" + + "WHERE t.cases_per_pallet > 100\n" + + "GROUP BY t.product_id, t.units_per_case\n" + + "ORDER BY t.units_per_case DESC"; + sql(query).withDb2().ok(expected); + } + + @Test void testDb2SubQueryFromUnion() { + String query = "select count(foo), \"units_per_case\"\n" + + "from (select \"units_per_case\", \"cases_per_pallet\",\n" + + " \"product_id\", 1 as foo\n" + + " from \"product\"\n" + + " where \"cases_per_pallet\" > 100\n" + + " union all\n" + + " select \"units_per_case\", \"cases_per_pallet\",\n" + + " \"product_id\", 1 as foo\n" + + " from \"product\"\n" + + " where \"cases_per_pallet\" < 100)\n" + + "where \"cases_per_pallet\" > 100\n" + + "group by \"product_id\", \"units_per_case\"\n" + + "order by \"units_per_case\" desc"; + final String expected = "SELECT COUNT(*), t3.units_per_case\n" + + "FROM (SELECT product.units_per_case, product.cases_per_pallet, " + + "product.product_id, 1 AS FOO\n" + + "FROM foodmart.product AS product\n" + + "WHERE product.cases_per_pallet > 100\n" + + "UNION ALL\n" + + "SELECT product0.units_per_case, product0.cases_per_pallet, " + + "product0.product_id, 1 AS FOO\n" + + "FROM foodmart.product AS product0\n" + + "WHERE product0.cases_per_pallet < 100) AS t3\n" + + "WHERE t3.cases_per_pallet > 100\n" + + "GROUP BY t3.product_id, t3.units_per_case\n" + + "ORDER BY t3.units_per_case DESC"; + sql(query).withDb2().ok(expected); + } + @Test void testDb2DialectSelectQueryWithGroup() { String query = "select count(*), sum(\"employee_id\") " + "from \"reserve_employee\" "