[
https://issues.apache.org/jira/browse/CALCITE-4617?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17351048#comment-17351048
]
Sylvain Crozon commented on CALCITE-4617:
-----------------------------------------
[~rubenql] my understanding of
https://issues.apache.org/jira/browse/CALCITE-1507 is that it was preventing
pushing the offset past the join when it would result in the wrong results
because the join may discard a row from the input based on the join condition.
This doesn't change that behaviour, but makes sure that if we are able to push
the offset past the join, that we only apply it once (in the join's input where
it makes sense) rather than both on the join's input and then again after the
join, which results in skipping twice as many rows.
> 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.27.0
>
> Time Spent: 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)
