[
https://issues.apache.org/jira/browse/CALCITE-7596?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7596:
------------------------------------
Labels: pull-request-available (was: )
> TopDownGeneralDecorrelator omits rewriting the ORDER BY clause in window
> function within correlated subquery
> ------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7596
> URL: https://issues.apache.org/jira/browse/CALCITE-7596
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0
> Reporter: Silun Dong
> Assignee: Silun Dong
> Priority: Major
> Labels: pull-request-available
>
> TopDownGeneralDecorrelator omits rewriting the {{ORDER BY}} clause in window
> function when rewriting correlated subquery (i.e. it does not replace free
> variables in the {{ORDER BY}} clause with local properties).
> For the sql:
> {code:java}
> select empno,
> (
> select row_number() over (
> partition by dname order by emp.sal <- emp.sal is free variable
> )
> from dept where dept.deptno = emp.deptno
> ) as rn
> from emp; {code}
> after decorrelation:
> {code:java}
> LogicalProject(EMPNO=[$0], RN=[$5])
> LogicalJoin(condition=[AND(IS NOT DISTINCT FROM($1, $3), IS NOT DISTINCT
> FROM($2, $4))], joinType=[left])
> LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalAggregate(group=[{1, 2}], agg#0=[SINGLE_VALUE($0)])
> LogicalProject($f0=[ROW_NUMBER() OVER (PARTITION BY $1, $3, $4 ORDER BY
> $cor1.SAL)], SAL=[$3], DEPTNO0=[$4]) <- omits rewriting the $cor1.SAL
> LogicalJoin(condition=[=($0, $4)], joinType=[inner])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(SAL=[$5], DEPTNO=[$7])
> LogicalTableScan(table=[[scott, EMP]]){code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
