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)
