[CALCITE-4617] Wrong offset when SortJoinTransposeRule pushes a Sort with an offset

• Type: Bug
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.26.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Ruben Q L
• Reporter: Sylvain Crozon
• Created: 21/May/21 18:38
• Updated: 26/Apr/22 14:21

Description

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

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]])

the SortJoinTransposeRule should convert to

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]])

Which will result in applying the offset twice. Instead the LogicalSort on top of the join should just have a null offset

Issue Links

• relates to: CALCITE-1507 OFFSET cannot be pushed through a JOIN if the non-preserved side of outer join is not count-preserving
• links to: GitHub Pull Request #2417