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.

        Activity

        Hide
        maryannxue Maryann Xue added a comment -

        Could you please kindly review my PR for this issue (https://github.com/apache/calcite/pull/333)?
        In order to verify the count-preserving case in testSortJoinTranspose6(), I added unique keys into SALES tables. As a result, AggregateFilterTransposeRule and AggregateJoinTransposeRule were not fired in a few tests in RelOptRuleTest, since they bail out for already unique group keys. Also, change of uniqueness will affect row count number in RelMetadataTest. So I made some adjustment to the affected tests by replacing unique key columns with non-unique key columns.

        Show
        maryannxue Maryann Xue added a comment - Could you please kindly review my PR for this issue ( https://github.com/apache/calcite/pull/333)? In order to verify the count-preserving case in testSortJoinTranspose6() , I added unique keys into SALES tables. As a result, AggregateFilterTransposeRule and AggregateJoinTransposeRule were not fired in a few tests in RelOptRuleTest, since they bail out for already unique group keys. Also, change of uniqueness will affect row count number in RelMetadataTest. So I made some adjustment to the affected tests by replacing unique key columns with non-unique key columns.
        Hide
        julianhyde Julian Hyde added a comment -

        +1 looks good; thanks for your diligence updating all those tests.

        In RelOptRulesTest, rather than checkPlanning can you please use the fluid API if possible, as it makes tests easier to maintain/extend. Something like this:

            sql(sql)
                .withPre(preProgram)
                .with(program)
                .checkUnchanged();
        
        Show
        julianhyde Julian Hyde added a comment - +1 looks good; thanks for your diligence updating all those tests. In RelOptRulesTest , rather than checkPlanning can you please use the fluid API if possible, as it makes tests easier to maintain/extend. Something like this: sql(sql) .withPre(preProgram) .with(program) .checkUnchanged();
        Hide
        maryannxue Maryann Xue added a comment -
        Show
        maryannxue Maryann Xue added a comment - Fixed in https://git1-us-west.apache.org/repos/asf?p=calcite.git;a=commit;h=106dcc0 . Thanks a lot for the review, Julian Hyde !
        Hide
        julianhyde Julian Hyde added a comment -

        Resolved in release 1.11.0 (2017-01-11).

        Show
        julianhyde Julian Hyde added a comment - Resolved in release 1.11.0 (2017-01-11).

          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:

              Development