8000 Fix incorrect handling of join clauses pushed into parameterized paths. · MSPawanRanjith/postgres@e1d4398 · GitHub
[go: up one dir, main page]
Skip to content

Commit e1d4398

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 070179a commit e1d4398

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
@@ -2071,6 +2071,7 @@ create_nestloop_plan(PlannerInfo *root,
20712071
if (IS_OUTER_JOIN(best_path->jointype))
20722072
{
20732073
extract_actual_join_clauses(joinrestrictclauses,
2074+
best_path->path.parent->relids,
20742075
&joinclauses, &otherclauses);
20752076
}
20762077
else
@@ -2171,6 +2172,7 @@ create_mergejoin_plan(PlannerInfo *root,
21712172
if (IS_OUTER_JOIN(best_path->jpath.jointype))
21722173
{
21732174
extract_actual_join_clauses(joinclauses,
2175+
best_path->jpath.path.parent->relids,
21742176
&joinclauses, &otherclauses);
21752177
}
21762178
else
@@ -2454,6 +2456,7 @@ create_hashjoin_plan(PlannerInfo *root,
24542456
if (IS_OUTER_JOIN(best_path->jpath.jointype))
24552457
{
24562458
extract_actual_join_clauses(joinclauses,
2459+
best_path->jpath.path.parent->relids,
24572460
&joinclauses, &otherclauses);
24582461
}
24592462
else

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

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -605,6 +605,7 @@ extract_actual_clauses(List *restrictinfo_list,
605605
*/
606606
void
607607
extract_actual_join_clauses(Lis 10000 t *restrictinfo_list,
608+
Relids joinrelids,
608609
List **joinquals,
609610
List **otherquals)
610611
{
@@ -619,7 +620,15 @@ extract_actual_join_clauses(List *restrictinfo_list,
619620

620621
Assert(IsA(rinfo, RestrictInfo));
621622

622-
if (rinfo->is_pushed_down)
623+
/*
624+
* We must check both is_pushed_down and required_relids, since an
625+
* outer-join clause that's been pushed down to some lower join level
626+
* via path parameterization will not be marked is_pushed_down;
627+
* nonetheless, it must be treated as a filter clause not a join
628+
* clause so far as the lower join level is concerned.
629+
*/
630+
if (rinfo->is_pushed_down ||
631+
!bms_is_subset(rinfo->required_relids, joinrelids))
623632
{
624633
if (!rinfo->pseudoconstant)
625634
*otherquals = lappend(*otherquals, rinfo->clause);

‎src/include/optimizer/restrictinfo.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,7 @@ extern List *get_all_actual_clauses(List *restrictinfo_list);
3939
extern List *extract_actual_clauses(List *restrictinfo_list,
4040
bool pseudoconstant);
4141
extern void extract_actual_join_clauses(List *restrictinfo_list,
42+
Relids joinrelids,
4243
List **joinquals,
4344
List **otherquals);
4445
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
@@ -3207,6 +3207,33 @@ order by fault;
32073207
| 123 | 122
32083208
(1 row)
32093209

3210+
explain (costs off)
3211+
select * from
3212+
(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
3213+
left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
3214+
left join unnest(v1ys) as u1(u1y) on u1y = v2y;
3215+
QUERY PLAN
3216+
-------------------------------------------------------------
3217+
Nested Loop Left Join
3218+
-> Values Scan on "*VALUES*"
3219+
-> Hash Right Join
3220+
Hash Cond: (u1.u1y = "*VALUES*_1".column2)
3221+
Filter: ("*VALUES*_1".column1 = "*VALUES*".column1)
3222+
-> Function Scan on unnest u1
3223+
-> Hash
3224+
-> Values Scan on "*VALUES*_1"
3225+
(8 rows)
3226+
3227+
select * from
3228+
(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
3229+
left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
3230+
left join unnest(v1ys) as u1(u1y) on u1y = v2y;
3231+
v1x | v1ys | v2x | v2y | u1y
3232+
-----+---------+-----+-----+-----
3233+
1 | {10,20} | 1 | 10 | 10
3234+
2 | {20,30} | 2 | 20 | 20
3235+
(2 rows)
3236+
32103237
--
32113238
-- test handling of potential equivalence clauses above outer joins
32123239
--

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -971,6 +971,17 @@ select * from
971971
where fault = 122
972972
order by fault;
973973

974+
explain (costs off)
975+
select * from
976+
(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
977+
left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
978+
left join unnest(v1ys) as u1(u1y) on u1y = v2y;
979+
980+
select * from
981+
(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
982+
left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
983+
left join unnest(v1ys) as u1(u1y) on u1y = v2y;
984+
974985
--
975986
-- test handling of potential equivalence clauses above outer joins
976987
--

0 commit comments

Comments
 (0)
0