[jira] [Created] (CALCITE-1944) Wrong plan for query with window functions and subquery with star

["Volodymyr Vysotskyi (JIRA)" <ji...@apache.org>]

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)