[CALCITE-4090] When generating SQL for DB2, a complex SELECT above a sub-query generates a bad table alias

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.23.0
• Fix Version/s: 1.24.0
• Assignee: Julian Hyde
• Reporter: Steven Talbot
• Created: 24/Jun/20 23:13
• Updated: 25/Jul/20 14:59
• Resolved: 26/Jun/20 17:14

Description

Test in RelToSqlConverterTest:

@Test void testDb2DialectSubselect() {
  String query = "select count(foo), \"units_per_case\" "
      + "from (select \"units_per_case\", \"cases_per_pallet\", \"product_id\", 1 as foo from \"product\") where \"cases_per_pallet\" > 100 "
      + "group by \"product_id\", \"units_per_case\" "
      + "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);
}

The test fails with the "t." alias qualifier in the group by/order by/main select actually being "t0.". 

From stepping through the code in the debugger, I believe this is a general problem with the way aliases are calculated in situations like this by SqlImplementor, but other dialects with hasImplicitTableAlias() do not force qualified contexts and therefore do not hit this.

Issue Links

• links to: PR with a draft of a proposed fix