[
https://issues.apache.org/jira/browse/CALCITE-4206?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17210047#comment-17210047
]
Stamatis Zampetakis commented on CALCITE-4206:
----------------------------------------------
[~danny0405], to be honest I haven't though of how you can decorrelate this
case in general. There are cases where you can remove the Sort if it has only
limit in subqueries but this is not something to be done in decorrelator.
> RelDecorrelator outputs wrong plan for correlate sort with fetch limit
> ----------------------------------------------------------------------
>
> Key: CALCITE-4206
> URL: https://issues.apache.org/jira/browse/CALCITE-4206
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.25.0
> Reporter: Danny Chen
> Assignee: Danny Chen
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.26.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> For query
> {code:sql}
> SELECT deptno, ename
> FROM
> (SELECT DISTINCT deptno FROM emp) t1,
> LATERAL (
> SELECT ename, sal
> FROM emp
> WHERE deptno = t1.deptno
> ORDER BY sal
> DESC LIMIT 3
> )
> {code}
> The current plan after decorrelation is
> {code:xml}
> LogicalProject(DEPTNO=[$0], ENAME=[$1])
> LogicalJoin(condition=[=($0, $3)], joinType=[inner])
> LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalSort(sort0=[$1], dir0=[DESC], fetch=[3])
> LogicalProject(ENAME=[$1], SAL=[$5], DEPTNO=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> which is wrong, because the partition sort(on sal) changes to global.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
