Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit80f66a9

Browse files
committed
Fix planner failure with full join in RHS of left join.
Given a left join containing a full join in its righthand side, withthe 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 relatederrors. This happened because the full join was seen as overlapping theleft join's RHS, and then recent changes within join_is_legal() causedthat 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 orderis explicit in the SpecialJoinInfo data structure. The previous codingthere essentially ignored full joins, relying on the fact that we don'tflatten them in the joinlist data structure to preserve their ordering.That's sufficient to prevent a wrong plan from being formed, but as thisexample shows, it's not sufficient to ensure that the right plan willbe formed. We need to work a bit harder to ensure that the right planlooks sane according to the SpecialJoinInfos.Per bug #14105 from Vojtech Rylko. This was apparently induced bycommit8703059 (though now that I've seen it, I wonder whether thereare related cases that could have failed before that); so back-patchto 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 parent125ad53 commit80f66a9

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
@@ -1176,9 +1176,32 @@ make_outerjoininfo(PlannerInfo *root,
11761176
{
11771177
SpecialJoinInfo*otherinfo= (SpecialJoinInfo*)lfirst(l);
11781178

1179-
/* ignore full joins --- other mechanisms preserve their ordering */
1179+
/*
1180+
* A full join is an optimization barrier: we can't associate into or
1181+
* out of it. Hence, if it overlaps either LHS or RHS of the current
1182+
* rel, expand that side's min relset to cover the whole full join.
1183+
*/
11801184
if (otherinfo->jointype==JOIN_FULL)
1185+
{
1186+
if (bms_overlap(left_rels,otherinfo->syn_lefthand)||
1187+
bms_overlap(left_rels,otherinfo->syn_righthand))
1188+
{
1189+
min_lefthand=bms_add_members(min_lefthand,
1190+
otherinfo->syn_lefthand);
1191+
min_lefthand=bms_add_members(min_lefthand,
1192+
otherinfo->syn_righthand);
1193+
}
1194+
if (bms_overlap(right_rels,otherinfo->syn_lefthand)||
1195+
bms_overlap(right_rels,otherinfo->syn_righthand))
1196+
{
1197+
min_righthand=bms_add_members(min_righthand,
1198+
otherinfo->syn_lefthand);
1199+
min_righthand=bms_add_members(min_righthand,
1200+
otherinfo->syn_righthand);
1201+
}
1202+
/* Needn't do anything else with the full join */
11811203
continue;
1204+
}
11821205

11831206
/*
11841207
* 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
Original file line numberDiff line numberDiff line change
@@ -3801,6 +3801,37 @@ where ss1.c2 = 0;
38013801
----+----+----+----+----+----
38023802
(0 rows)
38033803

3804+
--
3805+
-- test successful handling of full join underneath left join (bug #14105)
3806+
--
3807+
explain (costs off)
3808+
select * from
3809+
(select 1 as id) as xx
3810+
left join
3811+
(tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
3812+
on (xx.id = coalesce(yy.id));
3813+
QUERY PLAN
3814+
---------------------------------------
3815+
Nested Loop Left Join
3816+
Join Filter: ((1) = COALESCE((1)))
3817+
-> Result
3818+
-> Hash Full Join
3819+
Hash Cond: (a1.unique1 = (1))
3820+
-> Seq Scan on tenk1 a1
3821+
-> Hash
3822+
-> Result
3823+
(8 rows)
3824+
3825+
select * from
3826+
(select 1 as id) as xx
3827+
left join
3828+
(tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
3829+
on (xx.id = coalesce(yy.id));
3830+
id | unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | id
3831+
----+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+----
3832+
1 | 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx | 1
3833+
(1 row)
3834+
38043835
--
38053836
-- test ability to push constants through outer join clauses
38063837
--

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1209,6 +1209,23 @@ select ss2.* from
12091209
lateral (select i41.*, i8.*, ss1.*from text_tbllimit1) ss2
12101210
wheress1.c2=0;
12111211

1212+
--
1213+
-- test successful handling of full join underneath left join (bug #14105)
1214+
--
1215+
1216+
explain (costs off)
1217+
select*from
1218+
(select1as id)as xx
1219+
left join
1220+
(tenk1as a1 fulljoin (select1as id)as yyon (a1.unique1=yy.id))
1221+
on (xx.id= coalesce(yy.id));
1222+
1223+
select*from
1224+
(select1as id)as xx
1225+
left join
1226+
(tenk1as a1 fulljoin (select1as id)as yyon (a1.unique1=yy.id))
1227+
on (xx.id= coalesce(yy.id));
1228+
12121229
--
12131230
-- test ability to push constants through outer join clauses
12141231
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp