agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
12+ messages / 4 participants
[nested] [flat]
* BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-04 06:41 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 12+ messages in thread
From: PG Bug reporting form @ 2026-09-04 06:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 10215501441@stu.ecnu.edu.cn
The following bug has been logged on the website:
Bug reference: 19653
Logged by: Annie
Email address: 10215501441@stu.ecnu.edu.cn
PostgreSQL version: 18.6
Operating system: Ubuntu 20.04.6 LTS (Focal Fossa)，x86_64
Description:
## Description
A query using a partitioned left/right join split, with
`enable_partitionwise_join`, parallel settings, and `GROUP BY ROLLUP` fails
at planning time with:
```
ERROR: variable not found in subplan target list
```
The same query against a single non‑partitioned table works correctly. The
error occurs during plan construction, not execution.
The problem arises when all of the following are true:
1. The right table of a join has **no statistics** (only left table is
analyzed), causing the planner to choose a **parameterized nested loop**.
2. **Parallel query** is enabled with very low cost parameters, leading to a
`Gather` node.
3. `GROUP BY ROLLUP` produces a **MixedAggregate** node that trims the child
targetlist to only required columns.
4. The inner index scan of the nested loop has a filter that references both
outer and inner columns (e.g., `m_l.tsvec @@ tsq`), but the inner subplan
targetlist no longer contains the outer column after trimming.
## How to reproduce
```sql
-- ============ Database setup ============
DROP DATABASE IF EXISTS repro_postgres810_db3_min;
CREATE DATABASE repro_postgres810_db3_min;
\c repro_postgres810_db3_min;
-- ============ Session parameters ============
SET enable_partitionwise_join = on;
SET enable_partition_pruning = on;
SET enable_partitionwise_aggregate = on;
SET enable_parallel_append = on;
SET enable_parallel_hash = on;
SET max_parallel_workers_per_gather = 2;
SET min_parallel_table_scan_size = 0;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
-- ============ Single source table ============
CREATE TABLE m_src(rowid bigint PRIMARY KEY, tsvec tsvector, tsq tsquery);
INSERT INTO m_src VALUES
(1, to_tsvector('english','quick brown fox'), to_tsquery('english','alpha &
beta')),
(2, to_tsvector('english','quick brown fox'), to_tsquery('english','alpha &
beta')),
(3, to_tsvector('english','quick brown fox'), to_tsquery('english','alpha &
beta'));
-- ============ Left/right partitioned tables ============
CREATE TABLE m_l(rowid bigint PRIMARY KEY, tsvec tsvector) PARTITION BY
RANGE (rowid);
CREATE TABLE m_l_p1 PARTITION OF m_l FOR VALUES FROM (1) TO (10);
INSERT INTO m_l SELECT rowid, tsvec FROM m_src;
CREATE TABLE m_r(rowid bigint PRIMARY KEY, tsq tsquery) PARTITION BY RANGE
(rowid);
CREATE TABLE m_r_p1 PARTITION OF m_r FOR VALUES FROM (1) TO (10);
INSERT INTO m_r SELECT rowid, tsq FROM m_src;
-- Only analyze left table; right table has no statistics
ANALYZE m_l;
-- ============ ① Single‑table query (works) ============
SELECT ARRAY['', '']::TEXT[] FROM m_src
WHERE NOT (m_src.tsvec @@ m_src.tsq)
GROUP BY ROLLUP (ARRAY['', '']::TEXT[]);
-- ============ ② Multi‑table query (fails) ============
SELECT ALL ARRAY['', '']::TEXT[] FROM (
SELECT COALESCE(m_l.rowid, m_r.rowid) AS rowid, m_l.tsvec AS tsvec,
m_r.tsq AS tsq
FROM m_l JOIN m_r ON m_l.rowid = m_r.rowid
) s
WHERE NOT (s.tsvec @@ s.tsq)
GROUP BY ROLLUP (ARRAY['', '']::TEXT[]);
```
## Expected behavior
The multi‑table query should return the same result as the single‑table
query: two rows (`""` and `{"",""}`), without any error.
## Actual behavior
The query fails with:
```
ERROR: variable not found in subplan target list
```
This error is raised during planning (in the `setrefs.c` phase), not during
execution.
## Additional notes
The plan shape for the failing query is roughly:
```
MixedAggregate
-> Gather
-> Nested Loop
-> Parallel Seq Scan on m_l_p1
-> Index Scan on m_r_p1
Index Cond: (rowid = m_l.rowid)
Filter: (m_l.tsvec @@ tsq)
```
After `MixedAggregate` trims the targetlist, the inner index scan still
references `m_l.tsvec` (an outer variable passed as a parameter), but it is
no longer present in the subplan targetlist, leading to the error.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-04 14:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 12+ messages in thread
From: Tom Lane @ 2026-09-04 14:01 UTC (permalink / raw)
To: 10215501441@stu.ecnu.edu.cn; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> A query using a partitioned left/right join split, with
> `enable_partitionwise_join`, parallel settings, and `GROUP BY ROLLUP` fails
> at planning time with:
> ERROR: variable not found in subplan target list
Fascinating. For me, this fails *only* in v18, not earlier or later
branches. That's not a usual pattern for our bugs ...
Will look closer in a bit, if nobody beats me to it.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-04 14:59 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Andrey Rachitskiy @ 2026-09-04 14:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 10215501441@stu.ecnu.edu.cn; pgsql-bugs@lists.postgresql.org
пт, 4 сент. 2026 г. в 19:01, Tom Lane <tgl@sss.pgh.pa.us>:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > A query using a partitioned left/right join split, with
> > `enable_partitionwise_join`, parallel settings, and `GROUP BY ROLLUP`
> fails
> > at planning time with:
> > ERROR: variable not found in subplan target list
>
> Fascinating. For me, this fails *only* in v18, not earlier or later
> branches. That's not a usual pattern for our bugs ...
>
> Will look closer in a bit, if nobody beats me to it.
>
> regards, tom lane
>
>
>
I tested the SQL from this report with the same setup pattern on these
branches:
- REL_18_STABLE: fails
- master: does not fail
- REL_17_STABLE: does not fail
On REL_18_STABLE, planning fails with:
ERROR: variable not found in subplan target list
LOCATION: fix_upper_expr_mutator, setrefs.c:3314
This failure is still reproducible with max_parallel_workers_per_gather = 0.
So in this repro, parallel Gather is not required.
In the same repro, these control variants succeed:
- enable_partitionwise_join = off
- ANALYZE on both joined sides (planner switches to hash join)
Observed plan difference in this test case:
- in REL_18_STABLE failing shape, the failing path reaches setrefs with
a NestLoopParam mapping failure
- in master and REL_17_STABLE runs, the observed plan includes outer scan
output with m_l.tsvec, and planning completes
In this code base snapshot, commit 014f9a831a3 ("Don't reset the pathlist
of partitioned joinrels") is present on master and absent on
REL_18_STABLE.
I ran gdb on REL_18_STABLE to confirm where the error is raised.
The observed stack at failure is:
set_append_references
-> set_plan_refs
-> set_join_references
-> fix_upper_expr (NRM_SUBSET for NestLoopParam)
-> fix_upper_expr_mutator
This confirms a planning-time mapping failure while processing
NestLoopParam expressions.
I then tested one code change in create_nestloop_plan:
- when a Var NestLoopParam is not present in outer_plan->targetlist,
add that Var to outer_tlist (same handling pattern already used there
for PHV NestLoopParams)
Observed result with that change:
- REL_18_STABLE no longer throws XX000 on the reporter query
- the query returns the expected two rows
- EXPLAIN for that shape shows the needed outer value emitted by outer scan
--
Regards,
Rachitskiy Andrey
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-04 15:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2026-09-04 15:27 UTC (permalink / raw)
To: 10215501441@stu.ecnu.edu.cn; +Cc: Richard Guo <guofenglinux@gmail.com>; Robert Haas <robertmhaas@gmail.com>; pgsql-bugs@lists.postgresql.org
I wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> A query using a partitioned left/right join split, with
>> `enable_partitionwise_join`, parallel settings, and `GROUP BY ROLLUP` fails
>> at planning time with:
>> ERROR: variable not found in subplan target list
> Fascinating. For me, this fails *only* in v18, not earlier or later
> branches. That's not a usual pattern for our bugs ...
A bit of quality time with "git bisect" found that the misbehavior
started at
cc5d98525d43c22b98f360ef0f2c8d7dc57f04dc is the first bad commit
commit cc5d98525d43c22b98f360ef0f2c8d7dc57f04dc
Author: Richard Guo <rguo@postgresql.org>
Date: Thu Mar 13 16:36:03 2025 +0900
Fix incorrect handling of subquery pullup
and was fixed by
014f9a831a320666bf2195949f41710f970c54ad is the first new commit
commit 014f9a831a320666bf2195949f41710f970c54ad
Author: Robert Haas <rhaas@postgresql.org>
Date: Fri Dec 5 11:05:12 2025 -0500
Don't reset the pathlist of partitioned joinrels.
The proximate cause of the failure is that we have a NestLoopParam
containing a Var, which we need to find in the tlist of the nestloop's
outer relation, but what is in the tlist is a PlaceHolderVar wrapping
that Var. So it's possible to see some connection to cc5d98525, but
it seems entirely accidental that 014f9a831 fixed it. I bet there
are related cases that are still broken.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-04 23:09 Richard Guo <guofenglinux@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Guo @ 2026-09-04 23:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 10215501441@stu.ecnu.edu.cn, Robert Haas <robertmhaas@gmail.com>; pgsql-bugs@lists.postgresql.org
On Sat, Sep 5, 2026 at 12:27 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The proximate cause of the failure is that we have a NestLoopParam
> containing a Var, which we need to find in the tlist of the nestloop's
> outer relation, but what is in the tlist is a PlaceHolderVar wrapping
> that Var. So it's possible to see some connection to cc5d98525, but
> it seems entirely accidental that 014f9a831 fixed it. I bet there
> are related cases that are still broken.
I think the connection to cc5d98525 is also accidental. cc5d98525
just makes it possible for plain Vars to be wrapped in PHV, but this
failure can be reproduced with PHVs built from other ways, such as
non-strict expressions from the nullable-side of an outer join. For
me, I can reproduce this same error on all branches from v14 to
master with the query below, using tables in partition_join.sql.
set enable_partitionwise_join to on;
EXPLAIN (COSTS OFF)
SELECT * 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;
ERROR: variable not found in subplan target list
It seems to me the root cause is that in a partitionwise child join,
root->curOuterRels holds child relids, but PlaceHolderInfo.ph_eval_at
is always expressed in top-parent relids. So in
replace_nestloop_params_mutator the subset check fails for a PHV
evaluated at the outer child rel.
It seems we can fix it by:
@@ -4374,8 +4374,15 @@ 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 */
+ /*
+ * For a nestloop, include outer relids in curOuterRels for inner side.
+ * If the outer rel is a child rel, also include its top parent's relids,
+ * since PlaceHolderInfo.ph_eval_at is expressed in terms of 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);
- Richard
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-05 13:26 Richard Guo <guofenglinux@gmail.com>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Guo @ 2026-09-05 13:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 10215501441@stu.ecnu.edu.cn, Robert Haas <robertmhaas@gmail.com>; pgsql-bugs@lists.postgresql.org
On Sat, Sep 5, 2026 at 8:09 AM Richard Guo <guofenglinux@gmail.com> wrote:
> It seems to me the root cause is that in a partitionwise child join,
> root->curOuterRels holds child relids, but PlaceHolderInfo.ph_eval_at
> is always expressed in top-parent relids. So in
> replace_nestloop_params_mutator the subset check fails for a PHV
> evaluated at the outer child rel.
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, and identify_current_nestloop_params() also matches it to
PlaceHolderInfo.ph_eval_at, which is always expressed in top-parent
relids.
I suspected this could cause a NestLoopParam for a PlaceHolderVar to
never be claimed by any nestloop node, resulting in "failed to assign
all NestLoopParams to plan nodes" errors. It took me quite a while,
but I eventually found a query that hits it. So it's real. (Please
see the test case in the attached patch.)
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;
ERROR: failed to assign all NestLoopParams to plan nodes
Attached is a patch to fix both bugs.
(I'm surprised it has taken us so long to find these bugs. I suspect
part of the reason is that partitionwise join is disabled by default.
AFAICS, enable_partitionwise_join and enable_partitionwise_aggregate
are the only planner method GUCs that are off by default. I wonder if
we should turn them on by default, so that bugs in these areas get
found sooner.)
- Richard
Attachments:
[application/octet-stream] v1-0001-Fix-nestloop-parameter-handling-for-PlaceHolderVa.patch (13.2K, ../../CAMbWs48OyF+JAiP-c3YxswVTK1mbjO-RNWFB0JXV7xdaC6HmPg@mail.gmail.com/2-v1-0001-Fix-nestloop-parameter-handling-for-PlaceHolderVa.patch)
download | inline diff:
From 5e2c8902aa9662f30e6c4e0fdc3b6bf78169610c Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Sat, 5 Sep 2026 10:19:17 +0900
Subject: [PATCH v1] 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>
Discussion: https://postgr.es/m/19653-9352cc6ba17b662f@postgresql.org
Backpatch-through: 14
---
src/backend/optimizer/plan/createplan.c | 34 +++++-
src/test/regress/expected/partition_join.out | 112 +++++++++++++++++++
src/test/regress/sql/partition_join.sql | 43 +++++++
3 files changed, 185 insertions(+), 4 deletions(-)
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 02a888c5996..6520e8a2312 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,15 @@ 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,
+ * since PlaceHolderInfo.ph_eval_at is expressed in terms of 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 +4279,31 @@ 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, since PlaceHolderInfo.ph_eval_at is expressed in terms of
+ * 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);
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-06 17:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2026-09-06 17:51 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: 10215501441@stu.ecnu.edu.cn, Robert Haas <robertmhaas@gmail.com>; pgsql-bugs@lists.postgresql.org
Richard Guo <guofenglinux@gmail.com> writes:
> On Sat, Sep 5, 2026 at 8:09 AM Richard Guo <guofenglinux@gmail.com> wrote:
>> It seems to me the root cause is that in a partitionwise child join,
>> root->curOuterRels holds child relids, but PlaceHolderInfo.ph_eval_at
>> is always expressed in top-parent relids. So in
>> replace_nestloop_params_mutator the subset check fails for a PHV
>> evaluated at the outer child rel.
> The required-outer set passed to identify_current_nestloop_params()
> has the same problem.
Right. (For anyone following along at home, the new test case fails
with "variable not found in subplan target list" if you run it against
HEAD. You need to apply the first part of Richard's patch to get to
"failed to assign all NestLoopParams to plan nodes".)
> Attached is a patch to fix both bugs.
Hmm, I'm not enamored of just union'ing the top_parent_relids with the
regular relids. I don't see us doing that anywhere else, so it smells
like a shortcut. Shouldn't we remove the child relids while adding
the parent relids?
> (I'm surprised it has taken us so long to find these bugs. I suspect
> part of the reason is that partitionwise join is disabled by default.
Probably.
> AFAICS, enable_partitionwise_join and enable_partitionwise_aggregate
> are the only planner method GUCs that are off by default. I wonder if
> we should turn them on by default, so that bugs in these areas get
> found sooner.)
I've not paid close attention to that stuff, but I had the impression
that it is disabled-by-default because it adds materially to planning
time and we don't trust the associated cost estimates too much.
Robert might have a better-informed opinion though.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-07 02:00 Richard Guo <guofenglinux@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Guo @ 2026-09-07 02:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 10215501441@stu.ecnu.edu.cn, Robert Haas <robertmhaas@gmail.com>; pgsql-bugs@lists.postgresql.org
On Mon, Sep 7, 2026 at 2:51 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Right. (For anyone following along at home, the new test case fails
> with "variable not found in subplan target list" if you run it against
> HEAD. You need to apply the first part of Richard's patch to get to
> "failed to assign all NestLoopParams to plan nodes".)
Yes. The second problem is masked until the first one is fixed.
Without the first change, the whole-PHV NestLoopParam never gets
created.
> Hmm, I'm not enamored of just union'ing the top_parent_relids with the
> regular relids. I don't see us doing that anywhere else, so it smells
> like a shortcut. Shouldn't we remove the child relids while adding
> the parent relids?
Yeah, we don't union child relids and parent relids anywhere else, and
I'm not entirely happy with it either. But I'm not sure we can simply
remove the child relids here, because the same set is used for two
different membership tests. For Vars, we check whether var->varno is
a member of the set, and within a child join the Vars carry child
relids. For PlaceHolderVars, we check whether ph_eval_at is a subset
of the set, and ph_eval_at always carries parent relids. So, AFAICS,
the set needs the child relids for the Var test and the parent relids
for the PHV test, and dropping the child relids would break the Var
test.
Maybe an alternative is to keep the set in top-parent terms and
translate each Var's varno to its top parent before the membership
test, or to leave the set alone and instead translate ph_eval_at into
child relids before the subset test. But AFAICS we need to update
quite a few places to make either way work, such as
replace_nestloop_params_mutator(), identify_current_nestloop_params(),
process_subquery_nestloop_params(), and maybe more. Not sure if this
is a better option.
Also, it just occured to me that a child join directly under an Append
that is parameterized by a parent rel keeps the parent relid in its
required-outer set, while its outer relids are child rels. That is to
say, the allleftrelids in identify_current_nestloop_params() is
already a mix of parent relids and child relids.
- Richard
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-07 03:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2026-09-07 03:30 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: 10215501441@stu.ecnu.edu.cn, Robert Haas <robertmhaas@gmail.com>; pgsql-bugs@lists.postgresql.org
Richard Guo <guofenglinux@gmail.com> writes:
> On Mon, Sep 7, 2026 at 2:51 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmm, I'm not enamored of just union'ing the top_parent_relids with the
>> regular relids. I don't see us doing that anywhere else, so it smells
>> like a shortcut. Shouldn't we remove the child relids while adding
>> the parent relids?
> Yeah, we don't union child relids and parent relids anywhere else, and
> I'm not entirely happy with it either. But I'm not sure we can simply
> remove the child relids here, because the same set is used for two
> different membership tests. For Vars, we check whether var->varno is
> a member of the set, and within a child join the Vars carry child
> relids. For PlaceHolderVars, we check whether ph_eval_at is a subset
> of the set, and ph_eval_at always carries parent relids. So, AFAICS,
> the set needs the child relids for the Var test and the parent relids
> for the PHV test, and dropping the child relids would break the Var
> test.
Yeah, I tried adjusting things like that and the regression tests
immediately crashed. So now I think we have to do it as you have it;
but maybe the comment could be improved to explain that we need to
match both Vars having the child relid and PHVs having top-parent
relids. (Could there be Vars having the parent relid? Not sure,
but if there are, I suppose we'd need to match them too.)
> Maybe an alternative is to keep the set in top-parent terms and
> translate each Var's varno to its top parent before the membership
> test, or to leave the set alone and instead translate ph_eval_at into
> child relids before the subset test. But AFAICS we need to update
> quite a few places to make either way work, such as
> replace_nestloop_params_mutator(), identify_current_nestloop_params(),
> process_subquery_nestloop_params(), and maybe more. Not sure if this
> is a better option.
Agreed. Quite aside from the number of places that'd have to be
touched, I'm not too comfortable with rethinking those design
decisions in a hasty back-patch. It seems not unlikely that
extensions contain code that expects the current data structure
definitions.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-07 08:27 Richard Guo <guofenglinux@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Guo @ 2026-09-07 08:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 10215501441@stu.ecnu.edu.cn, Robert Haas <robertmhaas@gmail.com>; pgsql-bugs@lists.postgresql.org
On Mon, Sep 7, 2026 at 12:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yeah, I tried adjusting things like that and the regression tests
> immediately crashed. So now I think we have to do it as you have it;
> but maybe the comment could be improved to explain that we need to
> match both Vars having the child relid and PHVs having top-parent
> relids.
Done in the attached.
> Agreed. Quite aside from the number of places that'd have to be
> touched, I'm not too comfortable with rethinking those design
> decisions in a hasty back-patch. It seems not unlikely that
> extensions contain code that expects the current data structure
> definitions.
Agreed. I'll leave the design as it is.
- Richard
Attachments:
[application/octet-stream] v2-0001-Fix-nestloop-parameter-handling-for-PlaceHolderVa.patch (13.5K, ../../CAMbWs4913G2ZWwEFbc=65zibQcqbY2nhxtFmHEx7KF+jfsGtVQ@mail.gmail.com/2-v2-0001-Fix-nestloop-parameter-handling-for-PlaceHolderVa.patch)
download | inline diff:
From 54c529a42202404979fd87a9cf265f1cb6ae635d Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Sat, 5 Sep 2026 10:19:17 +0900
Subject: [PATCH v2] 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);
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-07 15:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2026-09-07 15:08 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: 10215501441@stu.ecnu.edu.cn, Robert Haas <robertmhaas@gmail.com>; pgsql-bugs@lists.postgresql.org
Richard Guo <guofenglinux@gmail.com> writes:
> On Mon, Sep 7, 2026 at 12:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, I tried adjusting things like that and the regression tests
>> immediately crashed. So now I think we have to do it as you have it;
>> but maybe the comment could be improved to explain that we need to
>> match both Vars having the child relid and PHVs having top-parent
>> relids.
> Done in the attached.
This version LGTM.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
@ 2026-09-08 01:29 Richard Guo <guofenglinux@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Richard Guo @ 2026-09-08 01:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 10215501441@stu.ecnu.edu.cn, Robert Haas <robertmhaas@gmail.com>; pgsql-bugs@lists.postgresql.org
On Tue, Sep 8, 2026 at 12:08 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Richard Guo <guofenglinux@gmail.com> writes:
> > Done in the attached.
> This version LGTM.
Thanks! Pushed and back-patched to v14.
- Richard
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-09-08 01:29 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-04 06:41 BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop, PG Bug reporting form <noreply@postgresql.org>
2026-09-04 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 14:59 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-04 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-04 23:09 ` Richard Guo <guofenglinux@gmail.com>
2026-09-05 13:26 ` Richard Guo <guofenglinux@gmail.com>
2026-09-06 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-07 02:00 ` Richard Guo <guofenglinux@gmail.com>
2026-09-07 03:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-07 08:27 ` Richard Guo <guofenglinux@gmail.com>
2026-09-07 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-08 01:29 ` Richard Guo <guofenglinux@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox