8000 Fix planner failure with full join in RHS of left join. · prmdeveloper/postgres@3232c24 · GitHub
[go: up one dir, main page]
Skip to content

Commit 3232c24

Browse files
tglsfdctglsfdc
committed
Fix planner failure with full join in RHS of left join.
Given a left join containing a full join in its righthand side, with the left join's joinclause referencing only one side of the full join (in a non-strict fashion, so that the full join doesn't get simplified), the planner could fail with "failed to build any N-way joins" or related errors. This happened because the full join was seen as overlapping the left join's RHS, and then recent changes within join_is_legal() caused that function to conclude that the full join couldn't validly be formed. Rather than try to rejigger join_is_legal() yet more to allow this, I think it's better to fix initsplan.c so that the required join order is explicit in the SpecialJoinInfo data structure. The previous coding there essentially ignored full joins, relying on the fact that we don't flatten them in the joinlist data structure to preserve their ordering. That's sufficient to prevent a wrong plan from being formed, but as this example shows, it's not sufficient to ensure that the right plan will be formed. We need to work a bit harder to ensure that the right plan looks sane according to the SpecialJoinInfos. Per bug #14105 from Vojtech Rylko. This was apparently induced by commit 8703059 (though now that I've seen it, I wonder whether there are related cases that could have failed before that); so back-patch to all active branches. Unfortunately, that patch also went into 9.0, so this bug is a regression that won't be fixed in that branch.
1 parent 56dee70 commit 3232c24

File tree

3 files changed

+72
-1
lines changed

3 files changed

+72
-1
lines changed

‎src/backend/optimizer/plan/initsplan.c

Lines changed: 24 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1203,9 +1203,32 @@ make_outerjoininfo(PlannerInfo *root,
12031203
{
12041204
SpecialJoinInfo *otherinfo = (SpecialJoinInfo *) lfirst(l);
12051205

1206-
/* ignore full joins --- other mechanisms preserve their ordering */
1206+
/*
1207+
* A full join is an optimization barrier: we can't associate into or
1208+
* out of it. Hence, if it overlaps either LHS or RHS of the current
1209+
* rel, expand that side's min relset to cover the whole full join.
1210+
*/
12071211
if (otherinfo->jointype == JOIN_FULL)
1212+
{
1213+
if (bms_overlap(left_rels, otherinfo->syn_lefthand) ||
1214+
bms_overlap(left_rels, otherinfo->syn_righthand))
1215+
{
1216+
min_lefthand = bms_add_members(min_lefthand,
1217+
otherinfo->syn_lefthand);
1218+
min_lefthand = bms_add_members(min_lefthand,
1219+
otherinfo->syn_righthand);
1220+
}
1221+
if (bms_overlap(right_rels, otherinfo->syn_lefthand) ||
1222+
bms_overlap(right_rels, otherinfo->syn_righthand))
1223+
{
1224+
min_righthand = bms_add_members(min_righthand,
1225+
otherinfo->syn_lefthand);
1226+
min_righthand = bms_add_members(min_righthand,
1227+
otherinfo->syn_righthand);
1228+
}
1229+
/* Needn't do anything else with the full join */
12081230
continue;
1231+
}
12091232

12101233
/*
12111234
* For a lower OJ in our LHS, if our join condition uses the lower

‎src/test/regress/expected/join.out

Lines changed: 31 additions & 0 deletions
8000
Original file line numberDiff line numberDiff line change
@@ -3733,6 +3733,37 @@ where ss1.c2 = 0;
37333733
----+----+----+----+----+----
37343734
(0 rows)
37353735

3736+
--
3737+
-- test successful handling of full join underneath left join (bug #14105)
3738+
--
3739+
explain (costs off)
3740+
select * from
3741+
(select 1 as id) as xx
3742+
left join
3743+
(tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
3744+
on (xx.id = coalesce(yy.id));
3745+
QUERY PLAN
3746+
---------------------------------------
3747+
Nested Loop Left Join
3748+
Join Filter: ((1) = COALESCE((1)))
3749+
-> Result
3750+
-> Hash Full Join
3751+
Hash Cond: (a1.unique1 = (1))
3752+
-> Seq Scan on tenk1 a1
3753+
-> Hash
3754+
-> Result
3755+
(8 rows)
3756+
3757+
select * from
3758+
(select 1 as id) as xx
3759+
left join
3760+
(tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
3761+
on (xx.id = coalesce(yy.id));
3762+
id | unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | id
3763+
----+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+----
3764+
1 | 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx | 1
3765+
(1 row)
3766+
37363767
--
37373768
-- test ability to push constants through outer join clauses
37383769
--

‎src/test/regress/sql/join.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1177,6 +1177,23 @@ select ss2.* from
11771177
lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2
11781178
where ss1.c2 = 0;
11791179

1180+
--
1181+
-- test successful handling of full join underneath left join (bug #14105)
1182+
--
1183+
1184+
explain (costs off)
1185+
select * from
1186+
(select 1 as id) as xx
1187+
left join
1188+
(tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
1189+
on (xx.id = coalesce(yy.id));
1190+
1191+
select * from
1192+
(select 1 as id) as xx
1193+
left join
1194+
(tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
1195+
on (xx.id = coalesce(yy.id));
1196+
11801197
--
11811198
-- test ability to push constants through outer join clauses
11821199
--

0 commit comments

Comments
 (0)
0