diff options
| -rw-r--r-- | src/backend/optimizer/plan/initsplan.c | 25 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 31 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 17 |
3 files changed, 72 insertions, 1 deletions
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index 662e15b7e61..adc8c12994b 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -662,9 +662,32 @@ make_outerjoininfo(PlannerInfo *root, { SpecialJoinInfo *otherinfo = (SpecialJoinInfo *) lfirst(l); - /* ignore full joins --- other mechanisms preserve their ordering */ + /* + * A full join is an optimization barrier: we can't associate into or + * out of it. Hence, if it overlaps either LHS or RHS of the current + * rel, expand that side's min relset to cover the whole full join. + */ if (otherinfo->jointype == JOIN_FULL) + { + if (bms_overlap(left_rels, otherinfo->syn_lefthand) || + bms_overlap(left_rels, otherinfo->syn_righthand)) + { + min_lefthand = bms_add_members(min_lefthand, + otherinfo->syn_lefthand); + min_lefthand = bms_add_members(min_lefthand, + otherinfo->syn_righthand); + } + if (bms_overlap(right_rels, otherinfo->syn_lefthand) || + bms_overlap(right_rels, otherinfo->syn_righthand)) + { + min_righthand = bms_add_members(min_righthand, + otherinfo->syn_lefthand); + min_righthand = bms_add_members(min_righthand, + otherinfo->syn_righthand); + } + /* Needn't do anything else with the full join */ continue; + } /* * For a lower OJ in our LHS, if our join condition uses the lower diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 4979d3adca0..3fc2cfd2daf 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -3455,6 +3455,37 @@ select * from (2 rows) -- +-- test successful handling of full join underneath left join (bug #14105) +-- +explain (costs off) +select * from + (select 1 as id) as xx + left join + (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id)) + on (xx.id = coalesce(yy.id)); + QUERY PLAN +--------------------------------------- + Nested Loop Left Join + Join Filter: ((1) = COALESCE((1))) + -> Result + -> Hash Full Join + Hash Cond: (a1.unique1 = (1)) + -> Seq Scan on tenk1 a1 + -> Hash + -> Result +(8 rows) + +select * from + (select 1 as id) as xx + left join + (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id)) + on (xx.id = coalesce(yy.id)); + id | unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | id +----+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---- + 1 | 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx | 1 +(1 row) + +-- -- test ability to push constants through outer join clauses -- explain (costs off) diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index df48b240092..c0461c25279 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1066,6 +1066,23 @@ select * from on i8.q1 = i4.f1; -- +-- test successful handling of full join underneath left join (bug #14105) +-- + +explain (costs off) +select * from + (select 1 as id) as xx + left join + (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id)) + on (xx.id = coalesce(yy.id)); + +select * from + (select 1 as id) as xx + left join + (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id)) + on (xx.id = coalesce(yy.id)); + +-- -- test ability to push constants through outer join clauses -- |