[
https://issues.apache.org/jira/browse/CALCITE-1944?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-1944.
----------------------------------
Resolution: Fixed
Fix Version/s: 1.14.0
Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/18300409; thanks
for the PR, [~vvysotskyi]!
> Window function applied to sub-query with dynamic star gives wrong plan
> -----------------------------------------------------------------------
>
> 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
> Fix For: 1.14.0
>
>
> *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)
