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

["Maryann Xue (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1507?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15723361#comment-15723361 ] 

Maryann Xue commented on CALCITE-1507:
--------------------------------------

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.

> OFFSET cannot be pushed through a JOIN if the non-preserved side of outer join is not count-preserving
> ------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-1507
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1507
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.10.0
>            Reporter: Maryann Xue
>            Assignee: Maryann Xue
>            Priority: Minor
>
> 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:
> {code}
> select d.deptno, empno
>     from sales.dept d
>     left join sales.emp e using (deptno)
>     order by d.deptno offset 1
> {code}
> And rows from "dept" and "emp" tables are like:
> {code}
> "dept"
>   deptno
>   10
>   20
>   30
> "emp"
>   empno    deptno
>   101      10
>   102      10
>   105      30
> {code}
> The expected output is:
> {code}
> d.deptno    e.empno
> 10          102
> 20          null
> 30          105
> {code}
> While after applying SortJoinTransposeRule, the rel becomes:
> {code}
> 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]])
> {code}
> And the output will now be:
> {code}
> d.deptno    e.empno
> 20          null
> 30          105
> {code}
> because deptno "10" has been skipped from the left relation by the pushed through Sort node.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)