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

Window function applied to sub-query with dynamic star gives wrong plan

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.13.0
    • Fix Version/s: 1.14.0
    • Component/s: core
    • Labels:
      None

      Description

      Problem description
      Calcite builds the wrong plan for the query with a window function and subquery with a star:

      SELECT SUM(n_nationkey) OVER w
      FROM
        (SELECT *
         FROM SALES.NATION) subQry WINDOW w AS (PARTITION BY REGION
                                                ORDER BY n_nationkey)
      

      Plan:

      LogicalProject(EXPR$0=[CASE(>(COUNT(ITEM($0, 'N_NATIONKEY')) OVER (PARTITION BY $0 ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0), $SUM0(ITEM($0, 'N_NATIONKEY')) OVER (PARTITION BY $0 ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), null)])
        LogicalProject(**=[$0])
          LogicalTableScan(table=[[CATALOG, SALES, NATION]])
      

      Columns in PARTITION BY and ORDER BY clauses are $0 but they should be ITEM($0, 'REGION') and ITEM($0, 'N_NATIONKEY') respectively.
      So correct plan should be

      LogicalProject(EXPR$0=[CASE(>(COUNT(ITEM($0, 'N_NATIONKEY')) OVER (PARTITION BY ITEM($0, 'REGION') ORDER BY ITEM($0, 'N_NATIONKEY') RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0), $SUM0(ITEM($0, 'N_NATIONKEY')) OVER (PARTITION BY ITEM($0, 'REGION') ORDER BY ITEM($0, 'N_NATIONKEY') RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), null)])
        LogicalProject(**=[$0])
          LogicalTableScan(table=[[CATALOG, SALES, NATION]])
      

      Root cause
      In CALCITE-1150 added dynamic star column and dynamic record type support but SqlValidatorImpl.validateWindowClause() method does not call expand(SqlNode expr, SqlValidatorScope scope) for columns in partition and order lists.

      Therefore when executes this line
      windowList.validate(this, windowScope);
      in the validateIdentifier(SqlIdentifier id, SqlValidatorScope scope) method simple field name is replaced by the star field.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                vvysotskyi Volodymyr Vysotskyi
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: