Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.26.0
Description
Check this test in SqlToRelConverterTest:
@Test void testSortLimitWithCorrelateInput() { final String sql = "" + "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(); }
The current plan is:
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]])
It actually can be decorrelated to:
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]])
Attachments
Issue Links
- is caused by
-
CALCITE-4206 RelDecorrelator outputs wrong plan for correlate sort with fetch limit
-
- Closed
-
- is related to
-
CALCITE-1317 Rewrite "MAX" as "ORDER BY ... LIMIT ..."
-
- Open
-
- relates to
-
CALCITE-4437 The Sort rel should be decorrelated even though it has fetch or limit when it is not inside a Correlate
-
- Closed
-
- links to