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]