[ https://issues.apache.org/jira/browse/CALCITE-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Ruben Q L reassigned CALCITE-4437: ---------------------------------- Assignee: Thomas Rebele (was: Ruben Q L) > 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 > Assignee: Thomas Rebele > Priority: Major > Labels: pull-request-available > Fix For: 1.27.0 > > Time Spent: 10m > Remaining Estimate: 0h > > 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 be 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)