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

Commit666a4de

Browse files
committed
Fix miscomputation of direct_lateral_relids for join relations.
If a PlaceHolderVar is to be evaluated at a join relation, butits value is only needed there and not at higher levels, we neglectedto update the joinrel's direct_lateral_relids to include the PHV'ssource rel. This causes problems because join_is_legal() then won'tallow joining the joinrel to the PHV's source rel at all, leadingto "failed to build any N-way joins" planner failures.Per report from Andreas Seltenreich. Back-patch to 9.5where the problem originated.Discussion:https://postgr.es/m/87blfgqa4t.fsf@aurora.ydns.eu
1 parent74d6fb0 commit666a4de

File tree

3 files changed

+118
-11
lines changed

3 files changed

+118
-11
lines changed

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

Lines changed: 27 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -404,8 +404,10 @@ add_placeholders_to_base_rels(PlannerInfo *root)
404404
*and if they contain lateral references, add those references to the
405405
*joinrel's direct_lateral_relids.
406406
*
407-
* A join rel should emit a PlaceHolderVar if (a) the PHV is needed above
408-
* this join level and (b) the PHV can be computed at or below this level.
407+
* A join rel should emit a PlaceHolderVar if (a) the PHV can be computed
408+
* at or below this join level and (b) the PHV is needed above this level.
409+
* However, condition (a) is sufficient to add to direct_lateral_relids,
410+
* as explained below.
409411
*/
410412
void
411413
add_placeholders_to_joinrel(PlannerInfo*root,RelOptInfo*joinrel,
@@ -418,11 +420,11 @@ add_placeholders_to_joinrel(PlannerInfo *root, RelOptInfo *joinrel,
418420
{
419421
PlaceHolderInfo*phinfo= (PlaceHolderInfo*)lfirst(lc);
420422

421-
/* Is itstill needed above this joinrel? */
422-
if (bms_nonempty_difference(phinfo->ph_needed,relids))
423+
/* Is itcomputable here? */
424+
if (bms_is_subset(phinfo->ph_eval_at,relids))
423425
{
424-
/* Is itcomputable here? */
425-
if (bms_is_subset(phinfo->ph_eval_at,relids))
426+
/* Is itstill needed above this joinrel? */
427+
if (bms_nonempty_difference(phinfo->ph_needed,relids))
426428
{
427429
/* Yup, add it to the output */
428430
joinrel->reltarget->exprs=lappend(joinrel->reltarget->exprs,
@@ -450,12 +452,26 @@ add_placeholders_to_joinrel(PlannerInfo *root, RelOptInfo *joinrel,
450452
joinrel->reltarget->cost.startup+=cost.startup;
451453
joinrel->reltarget->cost.per_tuple+=cost.per_tuple;
452454
}
453-
454-
/* Adjust joinrel's direct_lateral_relids as needed */
455-
joinrel->direct_lateral_relids=
456-
bms_add_members(joinrel->direct_lateral_relids,
457-
phinfo->ph_lateral);
458455
}
456+
457+
/*
458+
* Also adjust joinrel's direct_lateral_relids to include the
459+
* PHV's source rel(s). We must do this even if we're not
460+
* actually going to emit the PHV, otherwise join_is_legal() will
461+
* reject valid join orderings. (In principle maybe we could
462+
* instead remove the joinrel's lateral_relids dependency; but
463+
* that's complicated to get right, and cases where we're not
464+
* going to emit the PHV are too rare to justify the work.)
465+
*
466+
* In principle we should only do this if the join doesn't yet
467+
* include the PHV's source rel(s). But our caller
468+
* build_join_rel() will clean things up by removing the join's
469+
* own relids from its direct_lateral_relids, so we needn't
470+
* account for that here.
471+
*/
472+
joinrel->direct_lateral_relids=
473+
bms_add_members(joinrel->direct_lateral_relids,
474+
phinfo->ph_lateral);
459475
}
460476
}
461477
}

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

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3009,6 +3009,70 @@ order by 1,2;
30093009
4567890123456789 | 4567890123456789 | 4567890123456789 | 42
30103010
(8 rows)
30113011

3012+
--
3013+
-- variant where a PlaceHolderVar is needed at a join, but not above the join
3014+
--
3015+
explain (costs off)
3016+
select * from
3017+
int4_tbl as i41,
3018+
lateral
3019+
(select 1 as x from
3020+
(select i41.f1 as lat,
3021+
i42.f1 as loc from
3022+
int8_tbl as i81, int4_tbl as i42) as ss1
3023+
right join int4_tbl as i43 on (i43.f1 > 1)
3024+
where ss1.loc = ss1.lat) as ss2
3025+
where i41.f1 > 0;
3026+
QUERY PLAN
3027+
--------------------------------------------------
3028+
Nested Loop
3029+
-> Nested Loop
3030+
-> Seq Scan on int4_tbl i41
3031+
Filter: (f1 > 0)
3032+
-> Nested Loop
3033+
Join Filter: (i41.f1 = i42.f1)
3034+
-> Seq Scan on int8_tbl i81
3035+
-> Materialize
3036+
-> Seq Scan on int4_tbl i42
3037+
-> Materialize
3038+
-> Seq Scan on int4_tbl i43
3039+
Filter: (f1 > 1)
3040+
(12 rows)
3041+
3042+
select * from
3043+
int4_tbl as i41,
3044+
lateral
3045+
(select 1 as x from
3046+
(select i41.f1 as lat,
3047+
i42.f1 as loc from
3048+
int8_tbl as i81, int4_tbl as i42) as ss1
3049+
right join int4_tbl as i43 on (i43.f1 > 1)
3050+
where ss1.loc = ss1.lat) as ss2
3051+
where i41.f1 > 0;
3052+
f1 | x
3053+
------------+---
3054+
123456 | 1
3055+
123456 | 1
3056+
123456 | 1
3057+
123456 | 1
3058+
123456 | 1
3059+
123456 | 1
3060+
123456 | 1
3061+
123456 | 1
3062+
123456 | 1
3063+
123456 | 1
3064+
2147483647 | 1
3065+
2147483647 | 1
3066+
2147483647 | 1
3067+
2147483647 | 1
3068+
2147483647 | 1
3069+
2147483647 | 1
3070+
2147483647 | 1
3071+
2147483647 | 1
3072+
2147483647 | 1
3073+
2147483647 | 1
3074+
(20 rows)
3075+
30123076
--
30133077
-- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE
30143078
--

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

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -904,6 +904,33 @@ where
904904
1= (select1from int8_tbl t3wheress.yis not nulllimit1)
905905
order by1,2;
906906

907+
--
908+
-- variant where a PlaceHolderVar is needed at a join, but not above the join
909+
--
910+
911+
explain (costs off)
912+
select*from
913+
int4_tblas i41,
914+
lateral
915+
(select1as xfrom
916+
(selecti41.f1as lat,
917+
i42.f1as locfrom
918+
int8_tblas i81, int4_tblas i42)as ss1
919+
right join int4_tblas i43on (i43.f1>1)
920+
wheress1.loc=ss1.lat)as ss2
921+
wherei41.f1>0;
922+
923+
select*from
924+
int4_tblas i41,
925+
lateral
926+
(select1as xfrom
927+
(selecti41.f1as lat,
928+
i42.f1as locfrom
929+
int8_tblas i81, int4_tblas i42)as ss1
930+
right join int4_tblas i43on (i43.f1>1)
931+
wheress1.loc=ss1.lat)as ss2
932+
wherei41.f1>0;
933+
907934
--
908935
-- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE
909936
--

0 commit comments

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