You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2017/08/24 19:46:00 UTC
[jira] [Resolved] (CALCITE-1944) Window function applied to
sub-query with dynamic star gives wrong plan
[ 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)