BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,

agora inbox for pgsql-bugs@postgresql.org  
help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 10215501441@stu.ecnu.edu.cn
Subject: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
Date: Fri, 04 Sep 2026 06:41:33 +0000
Message-ID: <19653-9352cc6ba17b662f@postgresql.org> (raw)

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.

view thread (12+ messages)  latest in thread

Message-ID: <19653-9352cc6ba17b662f@postgresql.org>
Permalink:  ../19653-9352cc6ba17b662f@postgresql.org/
Also on:    postgresql.org/message-id/19653-9352cc6ba17b662f@postgresql.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 10215501441@stu.ecnu.edu.cn
  Subject: Re: BUG #19653: "variable not found in subplan target list" during planning with parallel parameterized nested loop,
  In-Reply-To: <19653-9352cc6ba17b662f@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox