[ https://issues.apache.org/jira/browse/CALCITE-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Ruben Q L updated CALCITE-4437: ------------------------------- 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): {code} @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(); } {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} was: 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): {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} > 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 > Priority: Major > 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 (same test as CALCITE-4333, just > with an extra LogicalProject on top of the LogicalSort): > {code} > @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(); > } > {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)