[
https://issues.apache.org/jira/browse/CALCITE-4617?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17355112#comment-17355112
]
Sylvain Crozon commented on CALCITE-4617:
-----------------------------------------
[~nobigo] the query doesn't specify we need to get the top 10 rows by deptno,
nor is it what the test should be about. We just want to make sure that we
don't skip rows we shouldn't be skipping. I added {{order by empno}} to make
sure the results are consistent, but for this test, the order by clause doesn't
matter.
[~rubenql] I reverted the changes with {{offset+fetch}}, but kept the test with
the query from Julian. Are there more tests you would like to add?
> Wrong offset when SortJoinTransposeRule pushes a sort node with an offset
> -------------------------------------------------------------------------
>
> Key: CALCITE-4617
> URL: https://issues.apache.org/jira/browse/CALCITE-4617
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.26.0
> Reporter: Sylvain Crozon
> Assignee: Ruben Q L
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.28.0
>
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> The SortJoinTransposeRule will push a sort node past a join, and then
> duplicate the sort node on top of the join. When the sort node has an offset,
> we should only apply it once, otherwise we end up skipping twice as many rows
> as we should. The sort node added on top of the join should have a null
> offset.
>
> For example the testSortJoinTranspose6 test checks that for this initial plan
> {code}
> LogicalProject(DEPTNO=[$0], EMPNO=[$2])
> LogicalSort(offset=[2], fetch=[10])
> LogicalJoin(condition=[=($0, $9)], joinType=[right])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> the SortJoinTransposeRule should convert to
> {code}
> LogicalProject(DEPTNO=[$0], EMPNO=[$2])
> LogicalSort(offset=[2], fetch=[10])
> LogicalJoin(condition=[=($0, $9)], joinType=[right])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalSort(offset=[2], fetch=[10])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> Which will result in applying the offset twice. Instead the LogicalSort on
> top of the join should just have a null offset
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
