[CALCITE-1507] OFFSET cannot be pushed through a JOIN if the non-preserved side of outer join is not count-preserving

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.10.0
• Fix Version/s: 1.11.0
• Component/s: core
• Assignee: Wei Xue
• Reporter: Wei Xue
• Created: 23/Nov/16 20:57
• Updated: 25/May/21 09:10
• Resolved: 07/Dec/16 06:25

Description

If the non-preserved side of the outer join is not count-preserving, for each row from the preserved side, there can be zero, one or multiple matches from the non-preserved side, which means the join can produce one or multiple rows. So it is safe to push a LIMIT through, but it is invalid to push an OFFSET through.
Take this query as an example:

select d.deptno, empno
    from sales.dept d
    left join sales.emp e using (deptno)
    order by d.deptno offset 1

And rows from "dept" and "emp" tables are like:

"dept"
  deptno
  10
  20
  30

"emp"
  empno    deptno
  101      10
  102      10
  105      30

The expected output is:

d.deptno    e.empno
10          102
20          null
30          105

While after applying SortJoinTransposeRule, the rel becomes:

LogicalProject(DEPTNO=[$0], EMPNO=[$2])
  LogicalSort(sort0=[$0], dir0=[ASC], offset=[1])
    LogicalJoin(condition=[=($0, $9)], joinType=[left])
      LogicalSort(sort0=[$0], dir0=[ASC], offset=[1])
        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])

And the output will now be:

d.deptno    e.empno
20          null
30          105

because deptno "10" has been skipped from the left relation by the pushed through Sort node.

Issue Links

• is related to: CALCITE-4617 Wrong offset when SortJoinTransposeRule pushes a Sort with an offset