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

Commit 0a34ff7

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 fd52958 commit 0a34ff7

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
@@ -465,6 +465,45 @@ build_join_rel(PlannerInfo *root,
465465
return joinrel;
466466
}
467467

468+
/*
469+
* min_join_parameterization
D7AF 470+
*
471+
* Determine the minimum possible parameterization of a joinrel, that is, the
472+
* set of other rels it contains LATERAL references to.
473+
*/
474+
Relids
475+
min_join_parameterization(PlannerInfo *root, Relids joinrelids)
476+
{
477+
Relids result;
478+
ListCell *lc;
479+
480+
/* Easy if there are no lateral references */
481+
if (root->lateral_info_list == NIL)
482+
return NULL;
483+
484+
/*
485+
* Scan lateral_info_list to find all the lateral references occurring in
486+
* or below this join.
487+
*/
488+
result = NULL;
489+
foreach(lc, root->lateral_info_list)
490+
{
491+
LateralJoinInfo *ljinfo = (LateralJoinInfo *) lfirst(lc);
492+
493+
if (bms_is_subset(ljinfo->lateral_rhs, joinrelids))
494+
result = bms_add_members(result, ljinfo->lateral_lhs);
495+
}
496+
497+
/* Remove any rels that are already included in the join */
498+
result = bms_del_members(result, joinrelids);
499+
500+
/* Maintain invariant that result is exactly NULL if empty */
501+
if (bms_is_empty(result))
502+
result = NULL;
503+
504+
return result;
505+
}
506+
468507
/*
469508
* build_joinrel_tlist
470509
* 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
@@ -142,6 +142,7 @@ extern RelOptInfo *build_join_rel(PlannerInfo *root,
142142
RelOptInfo *inner_rel,
143143
SpecialJoinInfo *sjinfo,
144144
List **restrictlist_ptr);
145+
extern Relids min_join_parameterization(PlannerInfo *root, Relids joinrelids);
145146
extern AppendRelInfo *find_childrel_appendrelinfo(PlannerInfo *root,
146147
RelOptInfo *rel);
147148
extern RelOptInfo *find_childrel_top_parent(PlannerInfo *root, RelOptInfo *rel);

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

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

3468+
--
3469+
-- test for appropriate join order in the presence of lateral references
3470+
--
3471+
explain (verbose, costs off)
3472+
select * from
3473+
text_tbl t1
3474+
left join int8_tbl i8
3475+
on i8.q2 = 123,
3476+
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
3477+
where t1.f1 = ss.f1;
3478+
QUERY PLAN
3479+
--------------------------------------------------
3480+
Nested Loop
3481+
Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
3482+
Join Filter: (t1.f1 = t2.f1)
3483+
-> Nested Loop Left Join
3484+
Output: t1.f1, i8.q1, i8.q2
3485+
-> Seq Scan on public.text_tbl t1
3486+
Output: t1.f1
3487+
-> Materialize
3488+
Output: i8.q1, i8.q2
3489+
-> Seq Scan on public.int8_tbl i8
3490+
Output: i8.q1, i8.q2
3491+
Filter: (i8.q2 = 123)
3492+
-> Limit
3493+
Output: (i8.q1), t2.f1
3494+
-> Seq Scan on public.text_tbl t2
3495+
Output: i8.q1, t2.f1
3496+
(16 rows)
3497+
3498+
select * from
3499+
text_tbl t1
3500+
left join int8_tbl i8
3501+
on i8.q2 = 123,
3502+
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
3503+
where t1.f1 = ss.f1;
3504+
f1 | q1 | q2 | q1 | f1
3505+
------+------------------+-----+------------------+------
3506+
doh! | 4567890123456789 | 123 | 4567890123456789 | doh!
3507+
(1 row)
3508+
34683509
--
34693510
-- test ability to push constants through outer join clauses
34703511
--

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1083,6 +1083,25 @@ select * from
10831083
left join int4_tbl i4
10841084
on i8.q1 = i4.f1;
10851085

1086+
--
1087+
-- test for appropriate join order in the presence of lateral references
1088+
--
1089+
1090+
explain (verbose, costs off)
1091+
select * from
1092+
text_tbl t1
1093+
left join int8_tbl i8
1094+
on i8.q2 = 123,
1095+
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
1096+
where t1.f1 = ss.f1;
1097+
1098+
select * from
1099+
text_tbl t1
1100+
left join int8_tbl i8
1101+
on i8.q2 = 123,
1102+
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
1103+
where t1.f1 = ss.f1;
1104+
10861105
--
10871106
-- test ability to push constants through outer join clauses
10881107
--

0 commit comments

Comments
 (0)
0