[CALCITE-4333] The Sort rel should be decorrelated even though it has fetch or limit when its parent is not a Correlate

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.26.0
• Fix Version/s: 1.27.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Danny Chen
• Created: 12/Oct/20 11:11
• Updated: 03/Jun/21 22:32
• Resolved: 14/Oct/20 04:15

Description

Check this test in SqlToRelConverterTest:

@Test void testSortLimitWithCorrelateInput() {
    final String sql = "" +
        "SELECT deptno, ename\n" +
        "    FROM\n" +
        "        (SELECT DISTINCT deptno FROM emp) t1,\n" +
        "          LATERAL (\n" +
        "            SELECT ename, sal\n" +
        "            FROM emp\n" +
        "            WHERE deptno = t1.deptno)\n" +
        "    ORDER BY ename DESC\n" +
        "    LIMIT 3";
    sql(sql).ok();
  }

The current plan is:

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

It actually can be decorrelated to:

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

Issue Links

• is caused by: CALCITE-4206 RelDecorrelator outputs wrong plan for correlate sort with fetch limit
• is related to: CALCITE-1317 Rewrite "MAX" as "ORDER BY ... LIMIT ..."
• relates to: 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 #2208