Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit0c141fc

Browse files
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 intothe outer join's RHS even though the clause is not degenerate --- thiscan happen if we choose to make a parameterized path for the RHS. Ifthe clause ends up attached to a lower outer join, we'd misclassify itas 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'srequired_relids, not just its is_pushed_down flag. (The latter nowseems vestigial, or at least in need of rethinking, but we won't doanything 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 thinkthat a lateral reference may be required to get the planner to selecta broken plan; though I wouldn't swear to that. In any case, even ifLATERAL is needed to trigger the bug, it still affects all supportedbranches, so back-patch to all.Per report from Andreas Karlsson. Thanks to Andrew Gierth forpreliminary investigation.Discussion:https://postgr.es/m/f8128b11-c5bf-3539-48cd-234178b2314d@proxel.se
1 parentb3f4742 commit0c141fc

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
@@ -3447,6 +3447,7 @@ create_nestloop_plan(PlannerInfo *root,
34473447
if (IS_OUTER_JOIN(best_path->jointype))
34483448
{
34493449
extract_actual_join_clauses(joinrestrictclauses,
3450+
best_path->path.parent->relids,
34503451
&joinclauses,&otherclauses);
34513452
}
34523453
else
@@ -3559,6 +3560,7 @@ create_mergejoin_plan(PlannerInfo *root,
35593560
if (IS_OUTER_JOIN(best_path->jpath.jointype))
35603561
{
35613562
extract_actual_join_clauses(joinclauses,
3563+
best_path->jpath.path.parent->relids,
35623564
&joinclauses,&otherclauses);
35633565
}
35643566
else
@@ -3852,6 +3854,7 @@ create_hashjoin_plan(PlannerInfo *root,
38523854
if (IS_OUTER_JOIN(best_path->jpath.jointype))
38533855
{
38543856
extract_actual_join_clauses(joinclauses,
3857+
best_path->jpath.path.parent->relids,
38553858
&joinclauses,&otherclauses);
38563859
}
38573860
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+
Relidsjoinrelids,
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
externList*extract_actual_clauses(List*restrictinfo_list,
3737
boolpseudoconstant);
3838
externvoidextract_actual_join_clauses(List*restrictinfo_list,
39+
Relidsjoinrelids,
3940
List**joinquals,
4041
List**otherquals);
4142
externbooljoin_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
@@ -3349,6 +3349,33 @@ order by fault;
33493349
| 123 | 122
33503350
(1 row)
33513351

3352+
explain (costs off)
3353+
select * from
3354+
(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
3355+
left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
3356+
left join unnest(v1ys) as u1(u1y) on u1y = v2y;
3357+
QUERY PLAN
3358+
-------------------------------------------------------------
3359+
Nested Loop Left Join
3360+
-> Values Scan on "*VALUES*"
3361+
-> Hash Right Join
3362+
Hash Cond: (u1.u1y = "*VALUES*_1".column2)
3363+
Filter: ("*VALUES*_1".column1 = "*VALUES*".column1)
3364+
-> Function Scan on unnest u1
3365+
-> Hash
3366+
-> Values Scan on "*VALUES*_1"
3367+
(8 rows)
3368+
3369+
select * from
3370+
(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys)
3371+
left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x
3372+
left join unnest(v1ys) as u1(u1y) on u1y = v2y;
3373+
v1x | v1ys | v2x | v2y | u1y
3374+
-----+---------+-----+-----+-----
3375+
1 | {10,20} | 1 | 10 | 10
3376+
2 | {20,30} | 2 | 20 | 20
3377+
(2 rows)
3378+
33523379
--
33533380
-- test handling of potential equivalence clauses above outer joins
33543381
--

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1019,6 +1019,17 @@ select * from
10191019
where fault=122
10201020
order by fault;
10211021

1022+
explain (costs off)
1023+
select*from
1024+
(values (1, array[10,20]), (2, array[20,30]))as v1(v1x,v1ys)
1025+
left join (values (1,10), (2,20))as v2(v2x,v2y)on v2x= v1x
1026+
left join unnest(v1ys)as u1(u1y)on u1y= v2y;
1027+
1028+
select*from
1029+
(values (1, array[10,20]), (2, array[20,30]))as v1(v1x,v1ys)
1030+
left join (values (1,10), (2,20))as v2(v2x,v2y)on v2x= v1x
1031+
left join unnest(v1ys)as u1(u1y)on u1y= v2y;
1032+
10221033
--
10231034
-- test handling of potential equivalence clauses above outer joins
10241035
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp