Silun Dong created CALCITE-7596:
-----------------------------------
Summary: 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
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)
