[
https://issues.apache.org/jira/browse/CALCITE-7150?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ruben Q L resolved CALCITE-7150.
--------------------------------
Resolution: Duplicate
> 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
> Priority: Major
>
> 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)
