postgres.git / summary / log / commit / refs
commit 35d3f2069a3c842665bbc020295fb165dcb2b6ec
Author: Richard Guo <rguo@postgresql.org>
Date: Tue Sep 08 01:04:37 2026 +0000
Fix nestloop parameter handling for PlaceHolderVars in child joins
When creating a nestloop plan for a partitionwise child join, the
outer rel's relids are child relids, but PlaceHolderInfo.ph_eval_at is
always expressed in terms of the topmost parent rels. As a result,
replace_nestloop_params() and identify_current_nestloop_params()
failed to recognize that a PlaceHolderVar evaluated at the outer child
rel can be supplied as a nestloop param. Instead, the Vars within the
PHV's expression were replaced with params, but the outer child rel
emits only the PHV, not those bare Vars, leading to "variable not
found in subplan target list" errors from setrefs.c.
To fix, also include the outer rel's top parent relids in the relid
set used for these checks, so that ph_eval_at comparisons are done in
terms of parent rels while Var checks continue to work in terms of
child rels.
On v18 and later, the required-outer set passed to
identify_current_nestloop_params() has the same problem: it is in
terms of child rels once a parameterized child join path has been
reparameterized by an upper child join. With the above fix in place,
a PlaceHolderVar that depends on both the outer rel and the parameter
source becomes a single NestLoopParam, and that param was never
claimed by any nestloop node, leading to "failed to assign all
NestLoopParams to plan nodes" errors. To fix, also include the top
parents of any child rels in that set. Older branches lack this code
path, so they receive only the first change.
Back-patch to all supported branches.
Bug: #19653
Reported-by: Annie <10215501441@stu.ecnu.edu.cn>
Author: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/19653-9352cc6ba17b662f@postgresql.org
Backpatch-through: 14
src/backend/optimizer/plan/createplan.c | 37 ++++++++-
src/test/regress/expected/partition_join.out | 112 +++++++++++++++++++++++++++
src/test/regress/sql/partition_join.sql | 43 ++++++++++
3 files changed, 188 insertions(+), 4 deletions(-)
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 02a888c5996..17ce9042c4e 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -4199,6 +4199,7 @@ create_nestloop_plan(PlannerInfo *root,
Plan *outer_plan;
Plan *inner_plan;
Relids outerrelids;
+ Relids req_outer;
Relids ojrelids;
List *tlist = build_path_tlist(root, &best_path->jpath.path);
List *joinrestrictclauses = best_path->jpath.joinrestrictinfo;
@@ -4229,8 +4230,17 @@ create_nestloop_plan(PlannerInfo *root,
/* NestLoop can project, so no need to be picky about child tlists */
outer_plan = create_plan_recurse(root, best_path->jpath.outerjoinpath, 0);
- /* For a nestloop, include outer relids in curOuterRels for inner side */
+ /*
+ * Include the outer relids in curOuterRels while building the inner side.
+ * If the outer rel is a child rel, also include its top parent's relids.
+ * We need both forms, since Vars in the inner side refer to the child rel
+ * while PlaceHolderInfo.ph_eval_at is expressed in terms of top parent
+ * rels.
+ */
outerrelids = best_path->jpath.outerjoinpath->parent->relids;
+ if (best_path->jpath.outerjoinpath->parent->top_parent_relids)
+ outerrelids = bms_union(outerrelids,
+ best_path->jpath.outerjoinpath->parent->top_parent_relids);
root->curOuterRels = bms_union(root->curOuterRels, outerrelids);
inner_plan = create_plan_recurse(root, best_path->jpath.innerjoinpath, 0);
@@ -4271,13 +4281,32 @@ create_nestloop_plan(PlannerInfo *root,
bms_union(best_path->jpath.outerjoinpath->parent->relids,
best_path->jpath.innerjoinpath->parent->relids));
+ /*
+ * The required-outer set may contain child rels if this path has been
+ * reparameterized by an upper child join. Include their top parents'
+ * relids too, so that we can match both Vars referring to the child rels
+ * and PlaceHolderVars whose PlaceHolderInfo.ph_eval_at is expressed in
+ * terms of top parent rels.
+ */
+ req_outer = PATH_REQ_OUTER((Path *) best_path);
+ if (req_outer)
+ {
+ int rti = -1;
+
+ while ((rti = bms_next_member(req_outer, rti)) >= 0)
+ {
+ RelOptInfo *rel = find_base_rel_ignore_join(root, rti);
+
+ if (rel && rel->top_parent_relids)
+ req_outer = bms_union(req_outer, rel->top_parent_relids);
+ }
+ }
+
/*
* Identify any nestloop parameters that should be supplied by this join
* node, and remove them from root->curOuterParams.
*/
- nestParams = identify_current_nestloop_params(root,
- outerrelids,
- PATH_REQ_OUTER((Path *) best_path));
+ nestParams = identify_current_nestloop_params(root, outerrelids, req_outer);
/*
* While nestloop parameters that are Vars had better be available from
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 38643d41fd7..a7e26afa793 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -717,6 +717,118 @@ SELECT a, b FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b)
RESET enable_partitionwise_aggregate;
RESET enable_hashjoin;
+-- bug with PlaceHolderVars supplied as nestloop params by a child join
+SET enable_hashjoin TO false;
+SET enable_mergejoin TO false;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 LEFT JOIN
+ (SELECT b, COALESCE(c, 'x') AS c FROM prt2 WHERE a = 0) t2 ON t1.a = t2.b
+ WHERE t1.c = t2.c ORDER BY t1.a, t2.b;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Append
+ -> Nested Loop
+ -> Seq Scan on prt2_p1 prt2_1
+ Filter: (a = 0)
+ -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
+ Index Cond: (a = prt2_1.b)
+ Filter: (((COALESCE(prt2_1.c, 'x'::character varying)))::text = (c)::text)
+ -> Nested Loop
+ -> Seq Scan on prt2_p2 prt2_2
+ Filter: (a = 0)
+ -> Index Scan using iprt1_p2_a on prt1_p2 t1_2
+ Index Cond: (a = prt2_2.b)
+ Filter: (((COALESCE(prt2_2.c, 'x'::character varying)))::text = (c)::text)
+ -> Nested Loop
+ -> Seq Scan on prt2_p3 prt2_3
+ Filter: (a = 0)
+ -> Index Scan using iprt1_p3_a on prt1_p3 t1_3
+ Index Cond: (a = prt2_3.b)
+ Filter: (((COALESCE(prt2_3.c, 'x'::character varying)))::text = (c)::text)
+(21 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 LEFT JOIN
+ (SELECT b, COALESCE(c, 'x') AS c FROM prt2 WHERE a = 0) t2 ON t1.a = t2.b
+ WHERE t1.c = t2.c ORDER BY t1.a, t2.b;
+ a | c | b | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 150 | 0150 | 150 | 0150
+ 300 | 0300 | 300 | 0300
+ 450 | 0450 | 450 | 0450
+(4 rows)
+
+-- same, with the PlaceHolderVar needed by a parameterized child join nested
+-- inside the child join that supplies the parameter
+CREATE TABLE prt3 (a int, b int, c varchar) PARTITION BY RANGE(a);
+CREATE TABLE prt3_p1 PARTITION OF prt3 FOR VALUES FROM (0) TO (250);
+CREATE TABLE prt3_p2 PARTITION OF prt3 FOR VALUES FROM (250) TO (500);
+CREATE TABLE prt3_p3 PARTITION OF prt3 FOR VALUES FROM (500) TO (600);
+INSERT INTO prt3 SELECT lb + i / 2, i % 25, to_char(i, 'FM0000') FROM generate_series(0, 99) i, (VALUES (0), (250), (500)) v(lb);
+CREATE INDEX iprt3_a ON prt3(a);
+ANALYZE prt3;
+CREATE TABLE prt4 (a int, b int, c varchar) PARTITION BY RANGE(a);
+CREATE TABLE prt4_p1 PARTITION OF prt4 FOR VALUES FROM (0) TO (250);
+CREATE TABLE prt4_p2 PARTITION OF prt4 FOR VALUES FROM (250) TO (500);
+CREATE TABLE prt4_p3 PARTITION OF prt4 FOR VALUES FROM (500) TO (600);
+INSERT INTO prt4 SELECT lb + i / 5, i % 25, to_char(i % 5, 'FM0000') FROM generate_series(0, 249) i, (VALUES (0), (250), (500)) v(lb);
+CREATE INDEX iprt4_c_a ON prt4(c, a);
+ANALYZE prt4;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt4 t1 LEFT JOIN
+ (SELECT t3.a, COALESCE(t3.c, t4.c) AS c FROM prt3 t3 JOIN prt1 t4 ON t3.a = t4.a
+ WHERE t4.b = 0) t2 ON t1.a = t2.a
+ WHERE t1.c = t2.c AND t2.a IS NOT NULL ORDER BY t1.a, t1.c;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t1.c
+ -> Append
+ -> Nested Loop
+ -> Nested Loop
+ -> Seq Scan on prt1_p1 t4_1
+ Filter: (b = 0)
+ -> Index Scan using prt3_p1_a_idx on prt3_p1 t3_1
+ Index Cond: ((a = t4_1.a) AND (a IS NOT NULL))
+ -> Index Only Scan using prt4_p1_c_a_idx on prt4_p1 t1_1
+ Index Cond: ((c = ((COALESCE(t3_1.c, t4_1.c)))::text) AND (a = t3_1.a))
+ -> Nested Loop
+ -> Nested Loop
+ -> Seq Scan on prt1_p2 t4_2
+ Filter: (b = 0)
+ -> Index Scan using prt3_p2_a_idx on prt3_p2 t3_2
+ Index Cond: ((a = t4_2.a) AND (a IS NOT NULL))
+ -> Index Only Scan using prt4_p2_c_a_idx on prt4_p2 t1_2
+ Index Cond: ((c = ((COALESCE(t3_2.c, t4_2.c)))::text) AND (a = t3_2.a))
+ -> Nested Loop
+ -> Seq Scan on prt1_p3 t4_3
+ Filter: (b = 0)
+ -> Nested Loop
+ Join Filter: (((COALESCE(t3_3.c, t4_3.c)))::text = (t1_3.c)::text)
+ -> Index Scan using prt3_p3_a_idx on prt3_p3 t3_3
+ Index Cond: ((a = t4_3.a) AND (a IS NOT NULL))
+ -> Index Only Scan using prt4_p3_c_a_idx on prt4_p3 t1_3
+ Index Cond: ((c = ((COALESCE(t3_3.c, t4_3.c)))::text) AND (a = t3_3.a))
+(28 rows)
+
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt4 t1 LEFT JOIN
+ (SELECT t3.a, COALESCE(t3.c, t4.c) AS c FROM prt3 t3 JOIN prt1 t4 ON t3.a = t4.a
+ WHERE t4.b = 0) t2 ON t1.a = t2.a
+ WHERE t1.c = t2.c AND t2.a IS NOT NULL ORDER BY t1.a, t1.c;
+ a | c | a | c
+-----+------+-----+------
+ 0 | 0000 | 0 | 0000
+ 0 | 0001 | 0 | 0001
+ 250 | 0000 | 250 | 0000
+ 250 | 0001 | 250 | 0001
+ 500 | 0000 | 500 | 0000
+ 500 | 0001 | 500 | 0001
+(6 rows)
+
+RESET enable_hashjoin;
+RESET enable_mergejoin;
-- bug in freeing the SpecialJoinInfo of a child-join
EXPLAIN (COSTS OFF)
SELECT * FROM prt1 t1 JOIN prt1 t2 ON t1.a = t2.a WHERE t1.a IN (SELECT a FROM prt1 t3);
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index c4549fc1ad8..3dc3e0bbc93 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -148,6 +148,49 @@ SELECT a, b FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b)
RESET enable_partitionwise_aggregate;
RESET enable_hashjoin;
+-- bug with PlaceHolderVars supplied as nestloop params by a child join
+SET enable_hashjoin TO false;
+SET enable_mergejoin TO false;
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 LEFT JOIN
+ (SELECT b, COALESCE(c, 'x') AS c FROM prt2 WHERE a = 0) t2 ON t1.a = t2.b
+ WHERE t1.c = t2.c ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 LEFT JOIN
+ (SELECT b, COALESCE(c, 'x') AS c FROM prt2 WHERE a = 0) t2 ON t1.a = t2.b
+ WHERE t1.c = t2.c ORDER BY t1.a, t2.b;
+
+-- same, with the PlaceHolderVar needed by a parameterized child join nested
+-- inside the child join that supplies the parameter
+CREATE TABLE prt3 (a int, b int, c varchar) PARTITION BY RANGE(a);
+CREATE TABLE prt3_p1 PARTITION OF prt3 FOR VALUES FROM (0) TO (250);
+CREATE TABLE prt3_p2 PARTITION OF prt3 FOR VALUES FROM (250) TO (500);
+CREATE TABLE prt3_p3 PARTITION OF prt3 FOR VALUES FROM (500) TO (600);
+INSERT INTO prt3 SELECT lb + i / 2, i % 25, to_char(i, 'FM0000') FROM generate_series(0, 99) i, (VALUES (0), (250), (500)) v(lb);
+CREATE INDEX iprt3_a ON prt3(a);
+ANALYZE prt3;
+
+CREATE TABLE prt4 (a int, b int, c varchar) PARTITION BY RANGE(a);
+CREATE TABLE prt4_p1 PARTITION OF prt4 FOR VALUES FROM (0) TO (250);
+CREATE TABLE prt4_p2 PARTITION OF prt4 FOR VALUES FROM (250) TO (500);
+CREATE TABLE prt4_p3 PARTITION OF prt4 FOR VALUES FROM (500) TO (600);
+INSERT INTO prt4 SELECT lb + i / 5, i % 25, to_char(i % 5, 'FM0000') FROM generate_series(0, 249) i, (VALUES (0), (250), (500)) v(lb);
+CREATE INDEX iprt4_c_a ON prt4(c, a);
+ANALYZE prt4;
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt4 t1 LEFT JOIN
+ (SELECT t3.a, COALESCE(t3.c, t4.c) AS c FROM prt3 t3 JOIN prt1 t4 ON t3.a = t4.a
+ WHERE t4.b = 0) t2 ON t1.a = t2.a
+ WHERE t1.c = t2.c AND t2.a IS NOT NULL ORDER BY t1.a, t1.c;
+SELECT t1.a, t1.c, t2.a, t2.c FROM prt4 t1 LEFT JOIN
+ (SELECT t3.a, COALESCE(t3.c, t4.c) AS c FROM prt3 t3 JOIN prt1 t4 ON t3.a = t4.a
+ WHERE t4.b = 0) t2 ON t1.a = t2.a
+ WHERE t1.c = t2.c AND t2.a IS NOT NULL ORDER BY t1.a, t1.c;
+
+RESET enable_hashjoin;
+RESET enable_mergejoin;
+
-- bug in freeing the SpecialJoinInfo of a child-join
EXPLAIN (COSTS OFF)
SELECT * FROM prt1 t1 JOIN prt1 t2 ON t1.a = t2.a WHERE t1.a IN (SELECT a FROM prt1 t3);