Maryann Xue created CALCITE-1507: ------------------------------------ Summary: 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)