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.

        Issue Links

          Activity

          Hide
          vvysotskyi Volodymyr Vysotskyi added a comment -

          I have created a pull request PR-517 for this Jira. Julian Hyde could you please take a look? Thanks!

          Show
          vvysotskyi Volodymyr Vysotskyi added a comment - I have created a pull request PR-517 for this Jira. Julian Hyde could you please take a look? Thanks!
          Hide
          julianhyde Julian Hyde added a comment -

          Reviewing and testing now.

          Show
          julianhyde Julian Hyde added a comment - Reviewing and testing now.
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/18300409 ; thanks for the PR, Volodymyr Vysotskyi !
          Hide
          michaelmior Michael Mior added a comment -

          Resolved in release 1.14.0 (2017-10-01)

          Show
          michaelmior Michael Mior added a comment - Resolved in release 1.14.0 (2017-10-01)

            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:

                Development