diff options
| -rw-r--r-- | src/backend/optimizer/plan/analyzejoins.c | 2 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 20 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 8 |
3 files changed, 29 insertions, 1 deletions
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 8a2ccbb6047..7dcb74572ad 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -456,7 +456,7 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel, Assert(sjinfo == NULL || !bms_is_member(relid, phinfo->ph_lateral)); if (bms_is_subset(phinfo->ph_needed, joinrelids) && bms_is_member(relid, phinfo->ph_eval_at) && - !bms_is_member(ojrelid, phinfo->ph_eval_at)) + (sjinfo == NULL || !bms_is_member(ojrelid, phinfo->ph_eval_at))) { root->placeholder_list = foreach_delete_current(root->placeholder_list, l); diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index e0418a3ae75..a2fad81d7af 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6821,6 +6821,26 @@ on true; Filter: (id IS NOT NULL) (8 rows) +-- Check that SJE removes the whole PHVs correctly +explain (verbose, costs off) +select 1 from emp1 t1 left join + ((select 1 as x, * from emp1 t2) s1 inner join + (select * from emp1 t3) s2 on s1.id = s2.id) + on true +where s1.x = 1; + QUERY PLAN +--------------------------------------------------------- + Nested Loop + Output: 1 + -> Seq Scan on public.emp1 t1 + Output: t1.id, t1.code + -> Materialize + Output: t3.id + -> Seq Scan on public.emp1 t3 + Output: t3.id + Filter: ((t3.id IS NOT NULL) AND (1 = 1)) +(9 rows) + -- Check that PHVs do not impose any constraints on removing self joins explain (verbose, costs off) select * from emp1 t1 join emp1 t2 on t1.id = t2.id left join diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index e272ff5c14c..e1db2025db6 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2600,6 +2600,14 @@ select * from emp1 t1 left join on true) on true; +-- Check that SJE removes the whole PHVs correctly +explain (verbose, costs off) +select 1 from emp1 t1 left join + ((select 1 as x, * from emp1 t2) s1 inner join + (select * from emp1 t3) s2 on s1.id = s2.id) + on true +where s1.x = 1; + -- Check that PHVs do not impose any constraints on removing self joins explain (verbose, costs off) select * from emp1 t1 join emp1 t2 on t1.id = t2.id left join |