Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1507

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.10.0
    • Fix Version/s: 1.11.0
    • Component/s: core
    • Labels:
      None

      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.

        Attachments

          Activity

            People

            • Assignee:
              maryannxue Maryann Xue
              Reporter:
              maryannxue Maryann Xue
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: