Window function applied to sub-query with dynamic star gives wrong plan

XMLWordPrintableJSON

Details

• Bug
• Status: Closed
• Major
• Resolution: Fixed
• 1.13.0
• None

Description

Problem description
Calcite builds the wrong plan for the query with a window function and subquery with a star:

```SELECT SUM(n_nationkey) OVER w
FROM
(SELECT *
FROM SALES.NATION) subQry WINDOW w AS (PARTITION BY REGION
ORDER BY n_nationkey)
```

Plan:

```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]])
```

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

```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]])
```

Root cause
In CALCITE-1150 added dynamic star column and dynamic record type support but SqlValidatorImpl.validateWindowClause() method does not call expand(SqlNode expr, SqlValidatorScope scope) for columns in partition and order lists.

Therefore when executes this line
windowList.validate(this, windowScope);
in the validateIdentifier(SqlIdentifier id, SqlValidatorScope scope) method simple field name is replaced by the star field.

People

Julian Hyde
Vova Vysotskyi