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

Commitd9742ac

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 parent82bf369 commitd9742ac

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
Original file line numberDiff line numberDiff line change
@@ -3659,6 +3659,37 @@ where ss1.c2 = 0;
36593659
----+----+----+----+----+----
36603660
(0 rows)
36613661

3662+
--
3663+
-- test successful handling of full join underneath left join (bug #14105)
3664+
--
3665+
explain (costs off)
3666+
select * from
3667+
(select 1 as id) as xx
3668+
left join
3669+
(tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
3670+
on (xx.id = coalesce(yy.id));
3671+
QUERY PLAN
3672+
---------------------------------------
3673+
Nested Loop Left Join
3674+
Join Filter: ((1) = COALESCE((1)))
3675+
-> Result
3676+
-> Hash Full Join
3677+
Hash Cond: (a1.unique1 = (1))
3678+
-> Seq Scan on tenk1 a1
3679+
-> Hash
3680+
-> Result
3681+
(8 rows)
3682+
3683+
select * from
3684+
(select 1 as id) as xx
3685+
left join
3686+
(tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id))
3687+
on (xx.id = coalesce(yy.id));
3688+
id | unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | id
3689+
----+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+----
3690+
1 | 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx | 1
3691+
(1 row)
3692+
36623693
--
36633694
-- test ability to push constants through outer join clauses
36643695
--

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

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

1164+
--
1165+
-- test successful handling of full join underneath left join (bug #14105)
1166+
--
1167+
1168+
explain (costs off)
1169+
select*from
1170+
(select1as id)as xx
1171+
left join
1172+
(tenk1as a1 fulljoin (select1as id)as yyon (a1.unique1=yy.id))
1173+
on (xx.id= coalesce(yy.id));
1174+
1175+
select*from
1176+
(select1as id)as xx
1177+
left join
1178+
(tenk1as a1 fulljoin (select1as id)as yyon (a1.unique1=yy.id))
1179+
on (xx.id= coalesce(yy.id));
1180+
11641181
--
11651182
-- test ability to push constants through outer join clauses
11661183
--

0 commit comments

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