| 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
| 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> |