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)
