Ruben Q L created CALCITE-4437: ---------------------------------- Summary: The Sort rel should be decorrelated even though it has fetch or limit when it is not inside a Correlate Key: CALCITE-4437 URL: https://issues.apache.org/jira/browse/CALCITE-4437 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.26.0 Reporter: Ruben Q L Fix For: 1.27.0
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: {code} @Test void testSortLimitWithCorrelateInput2() { 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(); } {code} The current plan is: {noformat} 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]]) {noformat} It can actually decorrelated as: {noformat} 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]]) {noformat} -- This message was sent by Atlassian Jira (v8.3.4#803005)