Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4090

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

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.23.0
    • Fix Version/s: 1.24.0
    • Component/s: None
    • Labels:
      None

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                swtalbot Steven Talbot
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: