[jira] [Created] (CALCITE-7596) TopDownGeneralDecorrelator omits rewriting the ORDER BY clause in window function within correlated subquery

["Silun Dong (Jira)" <ji...@apache.org>]

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)