Incorrect DELETE result after LEFT JOIN optimization - Mailing list pgsql-bugs
| From | clhl |
|---|---|
| Subject | Incorrect DELETE result after LEFT JOIN optimization |
| Date | |
| Msg-id | tencent_5A25AEA31910FDC48732AC14ACB56AD80407@qq.com Whole thread |
| List | pgsql-bugs |
## 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.
pgsql-bugs by date: