agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19710: Incorrect DELETE result after LEFT JOIN optimization
3+ messages / 3 participants
[nested] [flat]
* BUG #19710: Incorrect DELETE result after LEFT JOIN optimization
@ 2026-09-21 03:11 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-09-21 03:11 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 2320415112@qq.com
The following bug has been logged on the website:
Bug reference: 19710
Logged by: cl hl
Email address: 2320415112@qq.com
PostgreSQL version: 17.10
Operating system: Linux LAPTOP-2SQAVLB0 6.6.87.2-microsoft-standard-
Description:
## Description
On PostgreSQL 17.10, a `DELETE` statement containing an `EXISTS` subquery
with two `LEFT JOIN`s deletes a row even though the subquery predicate is
false.
The test contains one source row whose `c2` value is `sample_b`, while the
query compares it with the constant `sample_a`. Therefore, the `EXISTS`
condition should be false. However, PostgreSQL returns and deletes the
target row.
Tested version:
```text
PostgreSQL 17.10 (Debian 17.10-1.pgdg13+1) on x86_64-pc-linux-gnu
```
## How to reproduce
Run the following script in a new session. The transaction is rolled back at
the end, so it does not leave any objects behind.
```sql
BEGIN;
CREATE TEMP TABLE t1 (
c1 text,
c2 text
);
CREATE TEMP TABLE t2 (
c1 text,
c2 text,
UNIQUE (c2, c1)
);
CREATE TEMP TABLE t3 (
c1 integer PRIMARY KEY,
c2 integer
);
INSERT INTO t1 VALUES ('sample_key', 'sample_b');
INSERT INTO t3 VALUES (1, 0);
SAVEPOINT initial_state;
-- Test query
WITH t4 AS (
SELECT 'sample_a' AS c1
)
DELETE FROM t3
WHERE EXISTS (
SELECT 1
FROM t1
LEFT JOIN t2
ON t2.c1 = t1.c1
AND t2.c2 = 'sample_a'
LEFT JOIN t4 ON true
WHERE t1.c2 = t4.c1
)
RETURNING c1;
SELECT * FROM t3;
ROLLBACK TO SAVEPOINT initial_state;
-- Control query: only `=` is changed to `>=`.
WITH t4 AS (
SELECT 'sample_a' AS c1
)
DELETE FROM t3
WHERE EXISTS (
SELECT 1
FROM t1
LEFT JOIN t2
ON t2.c1 = t1.c1
AND t2.c2 >= 'sample_a'
LEFT JOIN t4 ON true
WHERE t1.c2 = t4.c1
)
RETURNING c1;
SELECT * FROM t3;
ROLLBACK;
```
Both statements run from the same state. Since `t2` is empty, changing `=`
to
`>=` cannot change the result of either `LEFT JOIN` for this data.
The issue can also be seen by comparing the execution plans:
```sql
EXPLAIN (COSTS OFF)
WITH t4 AS (
SELECT 'sample_a' AS c1
)
DELETE FROM t3
WHERE EXISTS (
SELECT 1
FROM t1
LEFT JOIN t2
ON t2.c1 = t1.c1
AND t2.c2 = 'sample_a'
LEFT JOIN t4 ON true
WHERE t1.c2 = t4.c1
)
RETURNING c1;
EXPLAIN (COSTS OFF)
WITH t4 AS (
SELECT 'sample_a' AS c1
)
DELETE FROM t3
WHERE EXISTS (
SELECT 1
FROM t1
LEFT JOIN t2
ON t2.c1 = t1.c1
AND t2.c2 >= 'sample_a'
LEFT JOIN t4 ON true
WHERE t1.c2 = t4.c1
)
RETURNING c1;
```
Observed plan for the test query (`=`):
```text
Delete on t3
InitPlan 1
-> Seq Scan on t1
-> Result
One-Time Filter: (InitPlan 1).col1
-> Seq Scan on t3
```
Observed plan for the control query (`>=`):
```text
Delete on t3
InitPlan 1
-> Hash Left Join
Hash Cond: (t1.c1 = t2.c1)
Filter: (t1.c2 = 'sample_a'::text)
-> Seq Scan on t1
-> Hash
-> Bitmap Heap Scan on t2
Recheck Cond: (c2 >= 'sample_a'::text)
-> Bitmap Index Scan on t2_c2_c1_key
Index Cond: (c2 >= 'sample_a'::text)
-> Result
One-Time Filter: (InitPlan 1).col1
-> Seq Scan on t3
```
## Expected behavior
The predicate inside the `EXISTS` subquery is logically equivalent to:
```text
t1.c2 = 'sample_a'
```
The only row in `t1` has `c2 = 'sample_b'`, and `t2` is empty. Therefore,
the `EXISTS` condition should be false.
Both `DELETE` statements should return no rows:
```text
c1
----
(0 rows)
```
After each statement, the target row should remain:
```text
c1 | c2
----+----
1 | 0
(1 row)
```
The plan should preserve or derive a restriction equivalent to:
```text
Filter: (c2 = 'sample_a'::text)
```
## Actual behavior
The test query using `=` returns and deletes `c1 = 1`:
```text
c1
----
1
(1 row)
DELETE 1
```
The following `SELECT` returns no rows:
```text
c1 | c2
----+----
(0 rows)
```
After restoring the same initial state, the control query using `>=` returns
no
rows and leaves `(1, 0)` in `t3`:
```text
c1
----
(0 rows)
DELETE 0
c1 | c2
----+----
1 | 0
(1 row)
```
The test-query plan scans `t1` without the required `c2 = 'sample_a'`
filter.
The control-query plan retains that filter. As a result, only the `=` form
makes
the `EXISTS` condition true and causes an incorrect persistent-state change.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19710: Incorrect DELETE result after LEFT JOIN optimization
@ 2026-09-21 08:54 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Ayush Tiwari @ 2026-09-21 08:54 UTC (permalink / raw)
To: 2320415112@qq.com; pgsql-bugs@lists.postgresql.org
Hi,
On Mon, 21 Sept 2026 at 12:05, PG Bug reporting form <noreply@postgresql.org>
wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19710
> Logged by: cl hl
> Email address: 2320415112@qq.com
> PostgreSQL version: 17.10
> Operating system: Linux LAPTOP-2SQAVLB0 6.6.87.2-microsoft-standard-
> Description:
>
> ## Description
>
> On PostgreSQL 17.10, a `DELETE` statement containing an `EXISTS` subquery
> with two `LEFT JOIN`s deletes a row even though the subquery predicate is
> false.
>
> The test contains one source row whose `c2` value is `sample_b`, while the
> query compares it with the constant `sample_a`. Therefore, the `EXISTS`
> condition should be false. However, PostgreSQL returns and deletes the
> target row.
>
> Tested version:
>
> ```text
> PostgreSQL 17.10 (Debian 17.10-1.pgdg13+1) on x86_64-pc-linux-gnu
> ```
>
> ## How to reproduce
>
> Run the following script in a new session. The transaction is rolled back
at
> the end, so it does not leave any objects behind.
>
> ```sql
> BEGIN;
>
> CREATE TEMP TABLE t1 (
> c1 text,
> c2 text
> );
>
> CREATE TEMP TABLE t2 (
> c1 text,
> c2 text,
> UNIQUE (c2, c1)
> );
>
> CREATE TEMP TABLE t3 (
> c1 integer PRIMARY KEY,
> c2 integer
> );
>
> INSERT INTO t1 VALUES ('sample_key', 'sample_b');
> INSERT INTO t3 VALUES (1, 0);
>
> SAVEPOINT initial_state;
>
> -- Test query
> WITH t4 AS (
> SELECT 'sample_a' AS c1
> )
> DELETE FROM t3
> WHERE EXISTS (
> SELECT 1
> FROM t1
> LEFT JOIN t2
> ON t2.c1 = t1.c1
> AND t2.c2 = 'sample_a'
> LEFT JOIN t4 ON true
> WHERE t1.c2 = t4.c1
> )
> RETURNING c1;
>
> SELECT * FROM t3;
>
> ROLLBACK TO SAVEPOINT initial_state;
>
> -- Control query: only `=` is changed to `>=`.
> WITH t4 AS (
> SELECT 'sample_a' AS c1
> )
> DELETE FROM t3
> WHERE EXISTS (
> SELECT 1
> FROM t1
> LEFT JOIN t2
> ON t2.c1 = t1.c1
> AND t2.c2 >= 'sample_a'
> LEFT JOIN t4 ON true
> WHERE t1.c2 = t4.c1
> )
> RETURNING c1;
>
> SELECT * FROM t3;
>
> ROLLBACK;
> ```
>
> Both statements run from the same state. Since `t2` is empty, changing `=`
> to
> `>=` cannot change the result of either `LEFT JOIN` for this data.
>
> The issue can also be seen by comparing the execution plans:
>
> ```sql
> EXPLAIN (COSTS OFF)
> WITH t4 AS (
> SELECT 'sample_a' AS c1
> )
> DELETE FROM t3
> WHERE EXISTS (
> SELECT 1
> FROM t1
> LEFT JOIN t2
> ON t2.c1 = t1.c1
> AND t2.c2 = 'sample_a'
> LEFT JOIN t4 ON true
> WHERE t1.c2 = t4.c1
> )
> RETURNING c1;
>
> EXPLAIN (COSTS OFF)
> WITH t4 AS (
> SELECT 'sample_a' AS c1
> )
> DELETE FROM t3
> WHERE EXISTS (
> SELECT 1
> FROM t1
> LEFT JOIN t2
> ON t2.c1 = t1.c1
> AND t2.c2 >= 'sample_a'
> LEFT JOIN t4 ON true
> WHERE t1.c2 = t4.c1
> )
> RETURNING c1;
> ```
>
> Observed plan for the test query (`=`):
>
> ```text
> Delete on t3
> InitPlan 1
> -> Seq Scan on t1
> -> Result
> One-Time Filter: (InitPlan 1).col1
> -> Seq Scan on t3
> ```
>
> Observed plan for the control query (`>=`):
>
> ```text
> Delete on t3
> InitPlan 1
> -> Hash Left Join
> Hash Cond: (t1.c1 = t2.c1)
> Filter: (t1.c2 = 'sample_a'::text)
> -> Seq Scan on t1
> -> Hash
> -> Bitmap Heap Scan on t2
> Recheck Cond: (c2 >= 'sample_a'::text)
> -> Bitmap Index Scan on t2_c2_c1_key
> Index Cond: (c2 >= 'sample_a'::text)
> -> Result
> One-Time Filter: (InitPlan 1).col1
> -> Seq Scan on t3
> ```
>
> ## Expected behavior
>
> The predicate inside the `EXISTS` subquery is logically equivalent to:
>
> ```text
> t1.c2 = 'sample_a'
> ```
>
> The only row in `t1` has `c2 = 'sample_b'`, and `t2` is empty. Therefore,
> the `EXISTS` condition should be false.
>
> Both `DELETE` statements should return no rows:
>
> ```text
> c1
> ----
> (0 rows)
> ```
>
> After each statement, the target row should remain:
>
> ```text
> c1 | c2
> ----+----
> 1 | 0
> (1 row)
> ```
>
> The plan should preserve or derive a restriction equivalent to:
>
> ```text
> Filter: (c2 = 'sample_a'::text)
> ```
>
> ## Actual behavior
>
> The test query using `=` returns and deletes `c1 = 1`:
>
> ```text
> c1
> ----
> 1
> (1 row)
>
> DELETE 1
> ```
>
> The following `SELECT` returns no rows:
>
> ```text
> c1 | c2
> ----+----
> (0 rows)
> ```
>
> After restoring the same initial state, the control query using `>=`
returns
> no
> rows and leaves `(1, 0)` in `t3`:
>
> ```text
> c1
> ----
> (0 rows)
>
> DELETE 0
>
> c1 | c2
> ----+----
> 1 | 0
> (1 row)
> ```
>
> The test-query plan scans `t1` without the required `c2 = 'sample_a'`
> filter.
> The control-query plan retains that filter. As a result, only the `=` form
> makes
> the `EXISTS` condition true and causes an incorrect persistent-state
change.
Thanks for the report.
AFAICS this has been fixed[1] and will be part of next minor versions.
Regards,
Ayush
[1]
https://github.com/postgres/postgres/commit/2ebf25e7d70a8fce31ace78d723fa9271ab8af72
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19710: Incorrect DELETE result after LEFT JOIN optimization
@ 2026-09-21 13:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2026-09-21 13:49 UTC (permalink / raw)
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: 2320415112@qq.com; pgsql-bugs@lists.postgresql.org
Ayush Tiwari <ayushtiwari.slg01@gmail.com> writes:
> On Mon, 21 Sept 2026 at 12:05, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> On PostgreSQL 17.10, a `DELETE` statement containing an `EXISTS` subquery
>> with two `LEFT JOIN`s deletes a row even though the subquery predicate is
>> false.
> AFAICS this has been fixed[1] and will be part of next minor versions.
Yeah, this example is pretty nearly a duplicate of bug #19560 [1],
which prompted that rewrite.
regards, tom lane
[1] https://www.postgresql.org/message-id/19560-54cd7ede78d5e355%40postgresql.org
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-21 13:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-21 03:11 BUG #19710: Incorrect DELETE result after LEFT JOIN optimization PG Bug reporting form <noreply@postgresql.org>
2026-09-21 08:54 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-21 13:49 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox