BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253673psql -h localhost -U postgresBuilt from patchset v10 (message #10), September 07, 2026 at 08:43 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253673_10 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253673_10 && git checkout t253673_10Patchset v10 (message #10) is on t253673_10
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.
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
пт, 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 listFascinating. 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
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
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
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
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
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
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
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:
t253673_10v2-0001-Fix-nestloop-parameter-handling-for-PlaceHolderVa.patchapplication/octet-stream; name=v2-0001-Fix-nestloop-parameter-handling-for-PlaceHolderVa.patchDownload+188-5
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
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