You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2020/06/26 17:12:22 UTC
[calcite] 03/03: [CALCITE-4090] When generating SQL for DB2,
a complex SELECT above a sub-query generates a bad table alias
(Steven Talbot)
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 <st...@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\" "