Minor 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
Issue Links
- is related to
-
CALCITE-4617 Wrong offset when SortJoinTransposeRule pushes a Sort with an offset
-
- In Progress
-