Skip to content

Commit a347d52

Browse files
tglsfdctglsfdc
committed
Fix incorrect handling of join clauses pushed into parameterized paths.
In some cases a clause attached to an outer join can be pushed down into the outer join's RHS even though the clause is not degenerate --- this can happen if we choose to make a parameterized path for the RHS. If the clause ends up attached to a lower outer join, we'd misclassify it as being a "join filter" not a plain "filter" condition at that node, leading to wrong query results. To fix, teach extract_actual_join_clauses to examine each join clause's required_relids, not just its is_pushed_down flag. (The latter now seems vestigial, or at least in need of rethinking, but we won't do anything so invasive as redefining it in a bug-fix patch.) This has been wrong since we introduced parameterized paths in 9.2, though it's evidently hard to hit given the lack of previous reports. The test case used here involves a lateral function call, and I think that a lateral reference may be required to get the planner to select a broken plan; though I wouldn't swear to that. In any case, even if LATERAL is needed to trigger the bug, it still affects all supported branches, so back-patch to all. Per report from Andreas Karlsson. Thanks to Andrew Gierth for preliminary investigation. Discussion: https://postgr.es/m/f8128b11-c5bf-3539-48cd-234178b2314d@proxel.se
1 parent e668507 commit a347d52

File tree

5 files changed

+52
-1
lines changed

5 files changed

+52
-1
lines changed

‎src/backend/optimizer/plan/createplan.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2062,6 +2062,7 @@ create_nestloop_plan(PlannerInfo *root,
20622062
if (IS_OUTER_JOIN(best_path->jointype))
20632063
{
20642064
extract_actual_join_clauses(joinrestrictclauses,
2065+
best_path->path.parent->relids,
20652066
&joinclauses, &otherclauses);
20662067
}
20672068
else
@@ -2162,6 +2163,7 @@ create_mergejoin_plan(PlannerInfo *root,
21622163
if (IS_OUTER_JOIN(best_path->jpath.jointype))
21632164
{
21642165
extract_actual_join_clauses(joinclauses,
2166+
best_path->jpath.path.parent->relids,
21652167
&joinclauses, &otherclauses);
21662168
}
21672169
else
@@ -2445,6 +2447,7 @@ create_hashjoin_plan(PlannerInfo *root,
24452447
if (IS_OUTER_JOIN(best_path->jpath.jointype))
24462448
{
24472449
extract_actual_join_clauses(joinclauses,
2450+
best_path->jpath.path.parent->relids,
24482451
&joinclauses, &otherclauses);
24492452
}
24502453
else

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

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -418,6 +418,7 @@ extract_actual_clauses(List *restrictinfo_list,
418418
*/
419419
void
420420
extract_actual_join_clauses(List *restrictinfo_list,
421+
Relids joinrelids,
421422
List **joinquals,
422423
List **otherquals)
423424
{
@@ -432,7 +433,15 @@ extract_actual_join_clauses(List *restrictinfo_list,
432433

433434
Assert(IsA(rinfo, RestrictInfo));
434435

435-
if (rinfo->is_pushed_down)
436+
/*
437+
* We must check both is_pushed_down and required_relids, since an
438+
* outer-join clause that's been pushed down to some lower join level
439+
* via path parameterization will not be marked is_pushed_down;
440+
* nonetheless, it must be treated as a filter clause not a join
441+
* clause so far as the lower join level is concerned.
442+
*/
443+
if (rinfo->is_pushed_down ||
444+
!bms_is_subset(rinfo->required_relids, joinrelids))
436445
{
437446
if (!rinfo->pseudoconstant)
438447
*otherquals = lappend(*otherquals, rinfo->clause);

‎src/include/optimizer/restrictinfo.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ extern List *get_all_actual_clauses(List *restrictinfo_list);
3636
extern List *extract_actual_clauses(List *restrictinfo_list,
3737
bool pseudoconstant);
3838
extern void extract_actual_join_clauses(List *restrictinfo_list,
39+
Relids joinrelids,
3940
List **joinquals,
4041
List **otherquals);
4142
extern bool join_clause_is_movable_to(RestrictInfo *rinfo, RelOptInfo *baserel);

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

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3281,6 +3281,33 @@ order by fault;
32813281
| 123 | 122
32823282
(1 row)
32833283

3284+
explain (costs off)
3285+
select * from
3286+
(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
3287+
left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
3288+
left join unnest(v1ys) as u1(u1y) on u1y = v2y;
3289+
QUERY PLAN
3290+
-------------------------------------------------------------
3291+
Nested Loop Left Join
3292+
-> Values Scan on "*VALUES*"
3293+
-> Hash Right Join
3294+
Hash Cond: (u1.u1y = "*VALUES*_1".column2)
3295+
Filter: ("*VALUES*_1".column1 = "*VALUES*".column1)
3296+
-> Function Scan on unnest u1
3297+
-> Hash
3298+
-> Values Scan on "*VALUES*_1"
3299+
(8 rows)
3300+
3301+
select * from
3302+
(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
3303+
left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
3304+
left join unnest(v1ys) as u1(u1y) on u1y = v2y;
3305+
v1x | v1ys | v2x | v2y | u1y
3306+
-----+---------+-----+-----+-----
3307+
1 | {10,20} | 1 | 10 | 10
3308+
2 | {20,30} | 2 | 20 | 20
3309+
(2 rows)
3310+
32843311
--
32853312
-- test handling of potential equivalence clauses above outer joins
32863313
--

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -987,6 +987,17 @@ select * from
987987
where fault = 122
988988
order by fault;
989989

990+
explain (costs off)
991+
select * from
992+
(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
993+
left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
994+
left join unnest(v1ys) as u1(u1y) on u1y = v2y;
995+
996+
select * from
997+
(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
998+
left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
999+
left join unnest(v1ys) as u1(u1y) on u1y = v2y;
1000+
9901001
--
9911002
-- test handling of potential equivalence clauses above outer joins
9921003
--

0 commit comments

Comments
 (0)