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

Commit7e19db0

Browse files
committed
Fix another oversight in checking if a join with LATERAL refs is legal.
It was possible for the planner to decide to join a LATERAL subquery tothe outer side of an outer join before the outer join itself is completed.Normally that's fine because of the associativity rules, but it doesn'twork if the subquery contains a lateral reference to the inner side of theouter join. In such a situation the outer join *must* be done first.join_is_legal() missed this consideration and would allow the join to beattempted, but the actual path-building code correctly decided that novalid join path could be made, sometimes leading to planner errors such as"failed to build any N-way joins".Per report from Andreas Seltenreich. Back-patch to 9.3 where LATERALsupport was added.
1 parent9821492 commit7e19db0

File tree

5 files changed

+132
-0
lines changed

5 files changed

+132
-0
lines changed

‎src/backend/optimizer/path/joinrels.c‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -334,6 +334,7 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
334334
boolmust_be_leftjoin;
335335
boollateral_fwd;
336336
boollateral_rev;
337+
Relidsjoin_lateral_rels;
337338
ListCell*l;
338339

339340
/*
@@ -569,6 +570,35 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
569570
}
570571
}
571572

573+
/*
574+
* LATERAL references could also cause problems later on if we accept this
575+
* join: if the join's minimum parameterization includes any rels that
576+
* would have to be on the inside of an outer join with this join rel,
577+
* then it's never going to be possible to build the complete query using
578+
* this join. We should reject this join not only because it'll save
579+
* work, but because if we don't, the clauseless-join heuristics might
580+
* think that legality of this join means that some other join rel need
581+
* not be formed, and that could lead to failure to find any plan at all.
582+
* It seems best not to merge this check into the main loop above, because
583+
* it is concerned with SJs that are not otherwise relevant to this join.
584+
*/
585+
join_lateral_rels=min_join_parameterization(root,joinrelids);
586+
if (join_lateral_rels)
587+
{
588+
foreach(l,root->join_info_list)
589+
{
590+
SpecialJoinInfo*sjinfo= (SpecialJoinInfo*)lfirst(l);
591+
592+
if (bms_overlap(sjinfo->min_righthand,join_lateral_rels)&&
593+
bms_overlap(sjinfo->min_lefthand,joinrelids))
594+
return false;/* will not be able to join to min_righthand */
595+
if (sjinfo->jointype==JOIN_FULL&&
596+
bms_overlap(sjinfo->min_lefthand,join_lateral_rels)&&
597+
bms_overlap(sjinfo->min_righthand,joinrelids))
598+
return false;/* will not be able to join to min_lefthand */
599+
}
600+
}
601+
572602
/* Otherwise, it's a valid join */
573603
*sjinfo_p=match_sjinfo;
574604
*reversed_p=reversed;

‎src/backend/optimizer/util/relnode.c‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -499,6 +499,47 @@ build_join_rel(PlannerInfo *root,
499499
returnjoinrel;
500500
}
501501

502+
/*
503+
* min_join_parameterization
504+
*
505+
* Determine the minimum possible parameterization of a joinrel, that is, the
506+
* set of other rels it contains LATERAL references to. We save this value in
507+
* the join's RelOptInfo. This function is split out of build_join_rel()
508+
* because join_is_legal() needs the value to check a prospective join.
509+
*/
510+
Relids
511+
min_join_parameterization(PlannerInfo*root,Relidsjoinrelids)
512+
{
513+
Relidsresult;
514+
ListCell*lc;
515+
516+
/* Easy if there are no lateral references */
517+
if (root->lateral_info_list==NIL)
518+
returnNULL;
519+
520+
/*
521+
* Scan lateral_info_list to find all the lateral references occurring in
522+
* or below this join.
523+
*/
524+
result=NULL;
525+
foreach(lc,root->lateral_info_list)
526+
{
527+
LateralJoinInfo*ljinfo= (LateralJoinInfo*)lfirst(lc);
528+
529+
if (bms_is_subset(ljinfo->lateral_rhs,joinrelids))
530+
result=bms_add_members(result,ljinfo->lateral_lhs);
531+
}
532+
533+
/* Remove any rels that are already included in the join */
534+
result=bms_del_members(result,joinrelids);
535+
536+
/* Maintain invariant that result is exactly NULL if empty */
537+
if (bms_is_empty(result))
538+
result=NULL;
539+
540+
returnresult;
541+
}
542+
502543
/*
503544
* build_joinrel_tlist
504545
* Builds a join relation's target list from an input relation.

‎src/include/optimizer/pathnode.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -147,6 +147,7 @@ extern RelOptInfo *build_join_rel(PlannerInfo *root,
147147
RelOptInfo*inner_rel,
148148
SpecialJoinInfo*sjinfo,
149149
List**restrictlist_ptr);
150+
externRelidsmin_join_parameterization(PlannerInfo*root,Relidsjoinrelids);
150151
externRelOptInfo*build_empty_join_rel(PlannerInfo*root);
151152
externAppendRelInfo*find_childrel_appendrelinfo(PlannerInfo*root,
152153
RelOptInfo*rel);

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

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3579,6 +3579,47 @@ select * from
35793579
doh! | 123 | 456 | hi de ho neighbor |
35803580
(2 rows)
35813581

3582+
--
3583+
-- test for appropriate join order in the presence of lateral references
3584+
--
3585+
explain (verbose, costs off)
3586+
select * from
3587+
text_tbl t1
3588+
left join int8_tbl i8
3589+
on i8.q2 = 123,
3590+
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
3591+
where t1.f1 = ss.f1;
3592+
QUERY PLAN
3593+
--------------------------------------------------
3594+
Nested Loop
3595+
Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
3596+
Join Filter: (t1.f1 = t2.f1)
3597+
-> Nested Loop Left Join
3598+
Output: t1.f1, i8.q1, i8.q2
3599+
-> Seq Scan on public.text_tbl t1
3600+
Output: t1.f1
3601+
-> Materialize
3602+
Output: i8.q1, i8.q2
3603+
-> Seq Scan on public.int8_tbl i8
3604+
Output: i8.q1, i8.q2
3605+
Filter: (i8.q2 = 123)
3606+
-> Limit
3607+
Output: (i8.q1), t2.f1
3608+
-> Seq Scan on public.text_tbl t2
3609+
Output: i8.q1, t2.f1
3610+
(16 rows)
3611+
3612+
select * from
3613+
text_tbl t1
3614+
left join int8_tbl i8
3615+
on i8.q2 = 123,
3616+
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
3617+
where t1.f1 = ss.f1;
3618+
f1 | q1 | q2 | q1 | f1
3619+
------+------------------+-----+------------------+------
3620+
doh! | 4567890123456789 | 123 | 4567890123456789 | doh!
3621+
(1 row)
3622+
35823623
--
35833624
-- test ability to push constants through outer join clauses
35843625
--

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1115,6 +1115,25 @@ select * from
11151115
left join int4_tbl i4
11161116
oni8.q1=i4.f1;
11171117

1118+
--
1119+
-- test for appropriate join order in the presence of lateral references
1120+
--
1121+
1122+
explain (verbose, costs off)
1123+
select*from
1124+
text_tbl t1
1125+
left join int8_tbl i8
1126+
oni8.q2=123,
1127+
lateral (selecti8.q1,t2.f1from text_tbl t2limit1)as ss
1128+
wheret1.f1=ss.f1;
1129+
1130+
select*from
1131+
text_tbl t1
1132+
left join int8_tbl i8
1133+
oni8.q2=123,
1134+
lateral (selecti8.q1,t2.f1from text_tbl t2limit1)as ss
1135+
wheret1.f1=ss.f1;
1136+
11181137
--
11191138
-- test ability to push constants through outer join clauses
11201139
--

0 commit comments

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