BUG #19461: MERGE with EXISTS subquery referencing USING clause alias fails with "variable not found in subplan

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 303677365(at)qq(dot)com
Subject: BUG #19461: MERGE with EXISTS subquery referencing USING clause alias fails with "variable not found in subplan
Date: 2026-04-20 09:29:49
Message-ID: 19461-8d03395b71f86664@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19461
Logged by: chunling qin
Email address: 303677365(at)qq(dot)com
PostgreSQL version: 15.0
Operating system: centos
Description:

## Summary

A MERGE statement that includes an EXISTS condition with a subquery
referencing an alias from the USING clause fails with the error: `ERROR:
variable not found in subplan target lists`.

## PostgreSQL Version

```
PostgreSQL 15devel on x86_64-pc-linux-gnu, compiled by clang version 17.0.6,
64-bit
```

Tested on REL_15_STABLE branch.

## Steps to Reproduce

```sql
-- Setup
DROP TABLE IF EXISTS t1, t2, t3 CASCADE;
CREATE TABLE t1 (col_int INT);
CREATE TABLE t2 (col_int INT);
CREATE TABLE t3 (col_int INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1);

-- Bug reproduction
MERGE INTO t1 t11
USING (t2 t12 NATURAL INNER JOIN t3 t13)
ON TRUE
WHEN MATCHED AND EXISTS (
SELECT 1
FROM (
SELECT col_int FROM t1
EXCEPT
SELECT col_int FROM t3 WHERE t13.col_int IS NOT NULL
) mm
) THEN DELETE;
```

## Expected Behavior

The MERGE statement should execute successfully. The EXISTS subquery
references `t13.col_int` from the USING clause, which should be valid within
the WHEN MATCHED condition context.

This issue could not be reproduced with the latest version, but we can
easily reproduced by PostgreSQL 15devel. I am submitting this report for
official confirmation.

postgres=# postgres=# postgres=# postgres-# postgres-# postgres-# postgres(#
postgres(# postgres(# postgres(# postgres(# postgres(# postgres(#
ERROR: variable not found in subplan target lists
postgres=# select version();
version

--------------------------------------------------------------------------------------
-------------------------------------------------
PostgreSQL 15devel on x86_64-pc-linux-gnu, compiled by clang version 17.0.6
(TencentO
S 17.0.6-8.tl4.ap.2), 64-bit (commit:7103ebb7aa

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2026-04-20 09:34:09 BUG #19462: MERGE on partitioned table with INSERT DEFAULT VALUES fails with "unknown action in MERGE WHEN clau
Previous Message Amit Langote 2026-04-20 09:05:33 Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>