8000 Fix another oversight in checking if a join with LATERAL refs is legal. · home201448/postgres@25517ee · GitHub
[go: up one dir, main page]
Skip to content

Commit 25517ee

Browse files
tglsfdctglsfdc
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 to the 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't work if the subquery contains a lateral reference to the inner side of the outer join. In such a situation the outer join *must* be done first. join_is_legal() missed this consideration and would allow the join to be attempted, but the actual path-building code correctly decided that no valid 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 LATERAL support was added.
1 parent 9d1839f commit 25517ee

File tree

5 files changed

+130
-0
lines changed

5 files changed

+130
-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
bool must_be_leftjoin;
335335
bool lateral_fwd;
336336
bool lateral_rev;
337+
Relids join_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: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -478,6 +478,45 @@ build_join_rel(PlannerInfo *root,
478478
return joinrel;
479479
}
480480

481+
/*
482+
* min_join_parameterization
483+
*
484+
* Determine the minimum possible parameterization of a joinrel, that is, the
485+
* set of other rels it contains LATERAL references to.
486+
*/
487+
Relids
488+
min_join_parameterization(PlannerInfo *root, Relids joinrelids)
489+
{
490+
Relids result;
491+
ListCell *lc;
492+
493+
/* Easy if there are no lateral references */
494+
if (root->lateral_info_list == NIL)
495+
return NULL;
496+
497+
/*
498+
* Scan lateral_info_list to find all the lateral references occurring in
499+
* or below this join.
500+
*/
501+
result = NULL;
502+
foreach(lc, root->lateral_info_list)
503+
{
504+
LateralJoinInfo *ljinfo = (LateralJoinInfo *) lfirst(lc);
505+
506+
if (bms_is_subset(ljinfo->lateral_rhs, joinrelids))
507+
result = bms_add_members(result, ljinfo->lateral_lhs);
508+
}
509+
510+
/* Remove any rels that are already included in the join */
511+
result = bms_del_members(result, joinrelids);
512+
513+
/* Maintain invariant that result is exactly NULL if empty */
514+
if (bms_is_empty(result))
515+
result = NULL;
516+
517+
return result;
518+
}
519+
481520
/*
482521
* build_joinrel_tlist
483522
* 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
@@ -144,6 +144,7 @@ extern RelOptInfo *build_join_rel(PlannerInfo *root,
144144
RelOptInfo *inner_rel,
145145
SpecialJoinInfo *sjinfo,
146146
List **restrictlist_ptr);
147+
extern Relids min_join_parameterization(PlannerInfo *root, Relids joinrelids);
147148
extern RelOptInfo *build_empty_join_rel(PlannerInfo *root);
148149
extern AppendRelInfo *find_childrel_appendrelinfo(PlannerInfo *root,
149150
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 F438
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
on i8.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+
on i8.q2 = 123,
1127+
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
1128+
where t1.f1 = ss.f1;
1129+
1130+
select * from
1131+
text_tbl t1
1132+
left join int8_tbl i8
1133+
on i8.q2 = 123,
1134+
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
1135+
where t1.f1 = ss.f1;
1136+
11181137
--
11191138
-- test ability to push constants through outer join clauses
11201139
--

0 commit comments

Comments
 (0)
0