diff options
| -rw-r--r-- | src/backend/optimizer/plan/analyzejoins.c | 7 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 16 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 6 |
3 files changed, 23 insertions, 6 deletions
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 98cf3494e6..657b6e5907 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -466,12 +466,7 @@ remove_rel_from_query(PlannerInfo *root, int relid, int ojrelid, remove_join_clause_from_rels(root, rinfo, rinfo->required_relids); - /* - * If the qual lists ojrelid in its required_relids, it must have come - * from above the outer join we're removing (so we need to keep it); - * if it does not, then it didn't and we can discard it. - */ - if (bms_is_member(ojrelid, rinfo->required_relids)) + if (RINFO_IS_PUSHED_DOWN(rinfo, joinrelids)) { /* * There might be references to relid or ojrelid in the diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 5d59ed7890..b5f440e43e 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -5320,6 +5320,22 @@ select 1 from (select a.id FROM a left join b on a.b_id = b.id) q, Filter: (a.id = i) (4 rows) +-- check join removal within RHS of an outer join +explain (costs off) +select c.id, ss.a from c + left join (select d.a from onerow, d left join b on d.a = b.id) ss + on c.id = ss.a; + QUERY PLAN +-------------------------------- + Hash Right Join + Hash Cond: (d.a = c.id) + -> Nested Loop + -> Seq Scan on onerow + -> Seq Scan on d + -> Hash + -> Seq Scan on c +(7 rows) + CREATE TEMP TABLE parted_b (id int PRIMARY KEY) partition by range(id); CREATE TEMP TABLE parted_b1 partition of parted_b for values from (0) to (10); -- test join removals on a partitioned table diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index a630f58b57..437934e80b 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1927,6 +1927,12 @@ explain (costs off) select 1 from (select a.id FROM a left join b on a.b_id = b.id) q, lateral generate_series(1, q.id) gs(i) where q.id = gs.i; +-- check join removal within RHS of an outer join +explain (costs off) +select c.id, ss.a from c + left join (select d.a from onerow, d left join b on d.a = b.id) ss + on c.id = ss.a; + CREATE TEMP TABLE parted_b (id int PRIMARY KEY) partition by range(id); CREATE TEMP TABLE parted_b1 partition of parted_b for values from (0) to (10); |