diff options
| author | Alexander Korotkov | 2023-11-09 12:06:11 +0000 |
|---|---|---|
| committer | Alexander Korotkov | 2023-11-09 12:25:13 +0000 |
| commit | b44a1708abe306831073cf88bb0ba6a11e68daee (patch) | |
| tree | 011dbc9084fd0a5825ba166614a8eecbbe3cede5 /src/test | |
| parent | 3850d4dec1d91c4fdce274f42986840444d5593e (diff) | |
Fix the way SJE removes references from PHVs
Add missing replacement of relids in phv->phexpr. Also, remove extra
replace_relid() over phv->phrels.
Reported-by: Zuming Jiang
Bug: #18187
Discussion: https://postgr.es/m/flat/18187-831da249cbd2ff8e%40postgresql.org
Author: Richard Guo
Reviewed-by: Andrei Lepikhov
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/join.out | 19 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 8 |
2 files changed, 27 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 892ea5f1702..ddc4e692329 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6776,6 +6776,25 @@ SELECT c3.code FROM emp1 c3; -> Seq Scan on emp1 c3 (7 rows) +-- Check that SJE removes references from PHVs correctly +explain (costs off) +select * from emp1 t1 left join + (select coalesce(t3.code, 1) from emp1 t2 + left join (emp1 t3 join emp1 t4 on t3.id = t4.id) + on true) +on true; + QUERY PLAN +---------------------------------------------------- + Nested Loop Left Join + -> Seq Scan on emp1 t1 + -> Materialize + -> Nested Loop Left Join + -> Seq Scan on emp1 t2 + -> Materialize + -> Seq Scan on emp1 t4 + Filter: (id IS NOT NULL) +(8 rows) + -- We can remove the join even if we find the join can't duplicate rows and -- the base quals of each side are different. In the following case we end up -- moving quals over to s1 to make it so it can't match any rows. diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 559c36dc074..a41787d1f1e 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2580,6 +2580,14 @@ WHERE c2.id IS NOT NULL EXCEPT ALL SELECT c3.code FROM emp1 c3; +-- Check that SJE removes references from PHVs correctly +explain (costs off) +select * from emp1 t1 left join + (select coalesce(t3.code, 1) from emp1 t2 + left join (emp1 t3 join emp1 t4 on t3.id = t4.id) + on true) +on true; + -- We can remove the join even if we find the join can't duplicate rows and -- the base quals of each side are different. In the following case we end up -- moving quals over to s1 to make it so it can't match any rows. |