Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4437

The Sort rel should be decorrelated even though it has fetch or limit when it is not inside a Correlate

    XMLWordPrintableJSON

    Details

      Description

      The fix applied for CALCITE-4206 was "too drastic" and it resulted in Sort with fetch/offset being impossible to decorrelate in all cases.
      CALCITE-4333 addressed this issue but only partially (when the Sort with fetch/offset is on top on the plan). However, this solution is insufficient, because any Sort with fetch/offset that is not inside a Correlate can be decorrelated.

      Check this test in SqlToRelConverterTest (same test as CALCITE-4333, just with an extra LogicalProject on top of the LogicalSort):

        @Test void testProjectSortLimitWithCorrelateInput() {
          final String sql = ""
              + "SELECT ename||deptno FROM\n"
              + "    (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:

      LogicalProject(EXPR$0=[||($1, CAST($0):VARCHAR NOT NULL)])
        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 can actually be decorrelated as:

      LogicalProject(EXPR$0=[||($1, CAST($0):VARCHAR NOT NULL)])
        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]])
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                thomas.rebele Thomas Rebele
                Reporter:
                rubenql Ruben Q L
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 2h
                  2h