[ https://issues.apache.org/jira/browse/CALCITE-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17280837#comment-17280837 ]
Ruben Q L commented on CALCITE-4437: ------------------------------------ [~danny0405], friendly reminder, do you think it could be possible for you to take a look at this one, please (to see if we can squeeze it into 1.27)? Thanks > 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 > 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 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)