postgres.git / summary / log / commit / refs

commit    d1e045baf82f26fa0ae0bd4bd2bc09cd1081b4fd
Author:   Richard Guo <rguo@postgresql.org>
Date:     Fri Oct 30 01:49:26 2026 +0000
First released in: 18.7, 19.0

    Fix nestloop parameter assignment for PHVs evaluated above outer joins
    
    A NestLoop can supply a PlaceHolderVar as a parameter if the PHV can
    be evaluated from the lefthand rels plus the required-outer rels of
    the join.  A PHV's ph_eval_at can include outer-join relids, but
    required-outer sets need not: create_lateral_join_info() keeps
    outer-join relids out of the lateral dependencies it derives from
    ph_lateral.  So when an outer join within ph_eval_at had been computed
    by an enclosing nestloop's outer side, the test in
    identify_current_nestloop_params() failed and no plan node claimed the
    parameter.  This resulted in "failed to assign all NestLoopParams to
    plan nodes", or, if an unrelated upper NestLoop happened to cover
    ph_eval_at and claimed the parameter, "plan should not reference
    subplan's variable".
    
    To fix, have create_nestloop_plan() also count the outer joins that
    have already been computed by the outer rels in scope.
    
    Back-patch to v18, where commit a16ef313f made it possible to compute
    a PHV that is used as a nestloop parameter in the outer side of the
    NestLoop.  v16 and v17 can fail the same way, since ph_eval_at has
    included outer-join relids since v16, but they lack that machinery.
    Fixing them would mean adding join-order restrictions to
    have_dangerous_phv() that could change plans that work today, which
    does not seem worth the risk for a failure this unlikely.
    
    Author: Richard Guo <guofenglinux@gmail.com>
    Discussion: https://postgr.es/m/CAMbWs4-fcAULTpWthZMHGOFK1J+Hz=Dvr1TO-M20cC317rOGOA@mail.gmail.com
    Backpatch-through: 18


src/backend/optimizer/plan/createplan.c | 9 ++++++ src/test/regress/expected/join.out | 53 +++++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 18 +++++++++++ 3 files changed, 80 insertions(+) diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 3b4e395f7a1..f5d90469d04 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -4292,6 +4292,15 @@ create_nestloop_plan(PlannerInfo *root, } } + /* + * Required-outer sets can lack outer-join relids that appear in + * ph_eval_at (see create_lateral_join_info), so add the outer joins + * already computed in the rels supplying our parameters. + */ + req_outer = bms_union(req_outer, + bms_intersect(root->curOuterRels, + root->outer_join_rels)); + /* * Identify any nestloop parameters that should be supplied by this join * node, and remove them from root->curOuterParams. diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 921e6521e67..05b4786bce4 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4828,6 +4828,59 @@ select * from (21 rows) rollback; +-- nestloop parameter PHV whose ph_eval_at mentions an outer join that is +-- computed in the rels supplying the parameters +explain (verbose, costs off) +select v.k from (select 1) d + left join (select t1.f1, 1 from int4_tbl t1, onerow t2 + left join onerow t3 on true) s(x, y) on true + left join lateral (select s.y from onerow t4) v(k) on true +where v.k > s.x; + QUERY PLAN +-------------------------------------------- + Nested Loop + Output: ((1)) + -> Nested Loop Left Join + -> Seq Scan on pg_temp.onerow t2 + -> Seq Scan on pg_temp.onerow t3 + -> Nested Loop + Output: ((1)) + -> Seq Scan on public.int4_tbl t1 + Output: t1.f1, 1 + -> Seq Scan on pg_temp.onerow t4 + Output: (1) + Filter: ((1) > t1.f1) +(12 rows) + +-- likewise, where an upper nestloop could wrongly claim the parameter +explain (verbose, costs off) +select v.k, g from (select 1) d + left join (select t1.f1, 1 from int4_tbl t1, onerow t2 + left join onerow t3 on true) s(x, y) on true + left join lateral (select s.y from onerow t4) v(k) on true, + lateral generate_series(v.k, 1) g +where v.k > s.x; + QUERY PLAN +----------------------------------------------------- + Nested Loop + Output: ((1)), g.g + -> Nested Loop + Output: ((1)) + -> Nested Loop Left Join + -> Seq Scan on pg_temp.onerow t2 + -> Seq Scan on pg_temp.onerow t3 + -> Nested Loop + Output: ((1)) + -> Seq Scan on public.int4_tbl t1 + Output: t1.f1, 1 + -> Seq Scan on pg_temp.onerow t4 + Output: (1) + Filter: ((1) > t1.f1) + -> Function Scan on pg_catalog.generate_series g + Output: g.g + Function Call: generate_series(((1)), 1) +(17 rows) + -- PHVs containing SubLinks are quite tricky to get right explain (verbose, costs off) select * diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 8436fe21972..bb82c7501e1 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1636,6 +1636,24 @@ select * from rollback; +-- nestloop parameter PHV whose ph_eval_at mentions an outer join that is +-- computed in the rels supplying the parameters +explain (verbose, costs off) +select v.k from (select 1) d + left join (select t1.f1, 1 from int4_tbl t1, onerow t2 + left join onerow t3 on true) s(x, y) on true + left join lateral (select s.y from onerow t4) v(k) on true +where v.k > s.x; + +-- likewise, where an upper nestloop could wrongly claim the parameter +explain (verbose, costs off) +select v.k, g from (select 1) d + left join (select t1.f1, 1 from int4_tbl t1, onerow t2 + left join onerow t3 on true) s(x, y) on true + left join lateral (select s.y from onerow t4) v(k) on true, + lateral generate_series(v.k, 1) g +where v.k > s.x; + -- PHVs containing SubLinks are quite tricky to get right explain (verbose, costs off) select * [parent: aba8ef3dac86]