Zhen Chen created CALCITE-7150:
----------------------------------
Summary: SORT pushdown JOIN did not correctly handle OFFSET.
Key: CALCITE-7150
URL: https://issues.apache.org/jira/browse/CALCITE-7150
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.40.0
Reporter: Zhen Chen
When running a RIGHT JOIN with OFFSET, enabling the SORT_JOIN_TRANSPOSE rule
returns fewer rows than expected. The reason is that after the SORT is pushed
down, the OFFSET from the upper SORT is not eliminated. This issue can be
reproduced by adding the following test in "planner.iq".
{code:java}
!use scott
!set planner-rules "
-CoreRules.SORT_JOIN_TRANSPOSE"
select d.deptno, empno from "scott"."DEPT" d
right join "scott"."EMP" e using (deptno) limit 10 offset 2;
+--------+-------+
| DEPTNO | EMPNO |
+--------+-------+
| 10 | 7934 |
| 20 | 7369 |
| 20 | 7566 |
| 20 | 7788 |
| 20 | 7876 |
| 20 | 7902 |
| 30 | 7499 |
| 30 | 7521 |
| 30 | 7654 |
| 30 | 7698 |
+--------+-------+
(10 rows)
!ok
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableLimit(offset=[2], fetch=[10])
EnumerableHashJoin(condition=[=($0, $2)], joinType=[right])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
!plan
!set planner-rules original
select d.deptno, empno from "scott"."DEPT" d
right join "scott"."EMP" e using (deptno) limit 10 offset 2;
+--------+-------+
| DEPTNO | EMPNO |
+--------+-------+
| 20 | 7566 |
| 20 | 7788 |
| 20 | 7876 |
| 30 | 7521 |
| 30 | 7654 |
| 30 | 7698 |
| 30 | 7844 |
| 30 | 7900 |
+--------+-------+
(8 rows)
!ok
EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
EnumerableLimit(offset=[2], fetch=[10]) <--- The OFFSET here should be
eliminated.
EnumerableHashJoin(condition=[=($0, $2)], joinType=[right])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
EnumerableLimit(offset=[2], fetch=[10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
