[CALCITE-1944] Window function applied to sub-query with dynamic star gives wrong plan

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.13.0
• Fix Version/s: 1.14.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Vova Vysotskyi
• Created: 15/Aug/17 15:50
• Updated: 02/Oct/19 18:38
• Resolved: 24/Aug/17 19:45

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

• relates to: DRILL-4469 SUM window query returns incorrect results over integer data