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
Appearance settings

Commit 7e19db0

Browse filesBrowse 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 9821492 commit 7e19db0
Copy full SHA for 7e19db0

File tree

Expand file treeCollapse file tree

5 files changed

+132
-0
lines changed
Filter options
Expand file treeCollapse file tree

5 files changed

+132
-0
lines changed

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

Copy file name to clipboardExpand all lines: src/backend/optimizer/path/joinrels.c
+30Lines 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

Copy file name to clipboardExpand all lines: src/backend/optimizer/util/relnode.c
+41Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -499,6 +499,47 @@ build_join_rel(PlannerInfo *root,
499499
return joinrel;
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, Relids joinrelids)
512+
{
513+
Relids result;
514+
ListCell *lc;
515+
516+
/* Easy if there are no lateral references */
517+
if (root->lateral_info_list == NIL)
518+
return NULL;
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+
return result;
541+
}
542+
502543
/*
503544
* build_joinrel_tlist
504545
* Builds a join relation's target list from an input relation.

‎src/include/optimizer/pathnode.h

Copy file name to clipboardExpand all lines: src/include/optimizer/pathnode.h
+1Lines 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+
extern Relids min_join_parameterization(PlannerInfo *root, Relids joinrelids);
150151
extern RelOptInfo *build_empty_join_rel(PlannerInfo *root);
151152
extern AppendRelInfo *find_childrel_appendrelinfo(PlannerInfo *root,
152153
RelOptInfo *rel);

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/join.out
+41Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/sql/join.sql
+19Lines 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)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.