[CALCITE-4206] RelDecorrelator outputs wrong plan for correlate sort with fetch limit

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.25.0
• Fix Version/s: 1.26.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Danny Chen
• Created: 01/Sep/20 10:22
• Updated: 11/Dec/20 19:21
• Resolved: 07/Sep/20 04:40

Description

For query

SELECT deptno, ename
  FROM
    (SELECT DISTINCT deptno FROM emp) t1,
      LATERAL (
        SELECT ename, sal
        FROM emp
        WHERE deptno = t1.deptno
        ORDER BY sal
        DESC LIMIT 3
      )

The current plan after decorrelation is

LogicalProject(DEPTNO=[$0], ENAME=[$1])
  LogicalJoin(condition=[=($0, $3)], joinType=[inner])
    LogicalAggregate(group=[{0}])
      LogicalProject(DEPTNO=[$7])
        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
    LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
      LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
        LogicalTableScan(table=[[CATALOG, SALES, EMP]])

which is wrong, because the partition sort(on sal) changes to global.

Issue Links

• causes: CALCITE-4333 The Sort rel should be decorrelated even though it has fetch or limit when its parent is not a Correlate
• causes: CALCITE-4437 The Sort rel should be decorrelated even though it has fetch or limit when it is not inside a Correlate
• links to: GitHub Pull Request #2129