Volodymyr Vysotskyi created CALCITE-1944: --------------------------------------------
Summary: Wrong plan for query with window functions and subquery with star Key: CALCITE-1944 URL: https://issues.apache.org/jira/browse/CALCITE-1944 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.13.0 Reporter: Volodymyr Vysotskyi Assignee: Julian Hyde *Problem description* Calcite builds the wrong plan for the query with a window function and subquery with a star: {code:sql} SELECT SUM(n_nationkey) OVER w FROM (SELECT * FROM SALES.NATION) subQry WINDOW w AS (PARTITION BY REGION ORDER BY n_nationkey) {code} Plan: {noformat} 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]]) {noformat} 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 {noformat} 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]]) {noformat} *Root cause* In CALCITE-1150 added dynamic star column and dynamic record type support but [SqlValidatorImpl.validateWindowClause()|https://github.com/apache/calcite/blob/01c5446138d419a85678bff7db06eabb4cd39846/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L3354] method does not call [expand(SqlNode expr, SqlValidatorScope scope)|https://github.com/apache/calcite/blob/01c5446138d419a85678bff7db06eabb4cd39846/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L4795] for columns in partition and order lists. Therefore when executes this line [windowList.validate(this, windowScope);|https://github.com/apache/calcite/blob/01c5446138d419a85678bff7db06eabb4cd39846/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L3392] in the [validateIdentifier(SqlIdentifier id, SqlValidatorScope scope)|https://github.com/apache/calcite/blob/01c5446138d419a85678bff7db06eabb4cd39846/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L2778] method simple field name is replaced by the star field. -- This message was sent by Atlassian JIRA (v6.4.14#64029)