Incorrect DELETE result after LEFT JOIN optimization

From: clhl <2320415112(at)qq(dot)com>
To: pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Incorrect DELETE result after LEFT JOIN optimization
Date: 2026-09-21 05:54:34
Message-ID: tencent_5A25AEA31910FDC48732AC14ACB56AD80407@qq.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: 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 (
&nbsp; &nbsp; c1 text,
&nbsp; &nbsp; c2 text
);

CREATE TEMP TABLE t2 (
&nbsp; &nbsp; c1 text,
&nbsp; &nbsp; c2 text,
&nbsp; &nbsp; UNIQUE (c2, c1)
);

CREATE TEMP TABLE t3 (
&nbsp; &nbsp; c1 integer PRIMARY KEY,
&nbsp; &nbsp; c2 integer
);

INSERT INTO t1 VALUES ('sample_key', 'sample_b');
INSERT INTO t3 VALUES (1, 0);

SAVEPOINT initial_state;

-- Test query
WITH t4 AS (
&nbsp; &nbsp; SELECT 'sample_a' AS c1
)
DELETE FROM t3
WHERE EXISTS (
&nbsp; &nbsp; SELECT 1
&nbsp; &nbsp; FROM t1
&nbsp; &nbsp; LEFT JOIN t2
&nbsp; &nbsp; &nbsp; ON t2.c1 = t1.c1
&nbsp; &nbsp; &nbsp;AND t2.c2 = 'sample_a'
&nbsp; &nbsp; LEFT JOIN t4 ON true
&nbsp; &nbsp; WHERE t1.c2 = t4.c1
)
RETURNING c1;

SELECT * FROM t3;

ROLLBACK TO SAVEPOINT initial_state;

-- Control query: only `=` is changed to `&gt;=`.
WITH t4 AS (
&nbsp; &nbsp; SELECT 'sample_a' AS c1
)
DELETE FROM t3
WHERE EXISTS (
&nbsp; &nbsp; SELECT 1
&nbsp; &nbsp; FROM t1
&nbsp; &nbsp; LEFT JOIN t2
&nbsp; &nbsp; &nbsp; ON t2.c1 = t1.c1
&nbsp; &nbsp; &nbsp;AND t2.c2 &gt;= 'sample_a'
&nbsp; &nbsp; LEFT JOIN t4 ON true
&nbsp; &nbsp; WHERE t1.c2 = t4.c1
)
RETURNING c1;

SELECT * FROM t3;

ROLLBACK;
```

Both statements run from the same state. Since `t2` is empty, changing `=` to
`&gt;=` 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 (
&nbsp; &nbsp; SELECT 'sample_a' AS c1
)
DELETE FROM t3
WHERE EXISTS (
&nbsp; &nbsp; SELECT 1
&nbsp; &nbsp; FROM t1
&nbsp; &nbsp; LEFT JOIN t2
&nbsp; &nbsp; &nbsp; ON t2.c1 = t1.c1
&nbsp; &nbsp; &nbsp;AND t2.c2 = 'sample_a'
&nbsp; &nbsp; LEFT JOIN t4 ON true
&nbsp; &nbsp; WHERE t1.c2 = t4.c1
)
RETURNING c1;

EXPLAIN (COSTS OFF)
WITH t4 AS (
&nbsp; &nbsp; SELECT 'sample_a' AS c1
)
DELETE FROM t3
WHERE EXISTS (
&nbsp; &nbsp; SELECT 1
&nbsp; &nbsp; FROM t1
&nbsp; &nbsp; LEFT JOIN t2
&nbsp; &nbsp; &nbsp; ON t2.c1 = t1.c1
&nbsp; &nbsp; &nbsp;AND t2.c2 &gt;= 'sample_a'
&nbsp; &nbsp; LEFT JOIN t4 ON true
&nbsp; &nbsp; WHERE t1.c2 = t4.c1
)
RETURNING c1;
```

Observed plan for the test query (`=`):

```text
Delete on t3
&nbsp; InitPlan 1
&nbsp; &nbsp; -&gt; &nbsp;Seq Scan on t1
&nbsp; -&gt; &nbsp;Result
&nbsp; &nbsp; &nbsp; &nbsp; One-Time Filter: (InitPlan 1).col1
&nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Seq Scan on t3
```

Observed plan for the control query (`&gt;=`):

```text
Delete on t3
&nbsp; InitPlan 1
&nbsp; &nbsp; -&gt; &nbsp;Hash Left Join
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Hash Cond: (t1.c1 = t2.c1)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Filter: (t1.c2 = 'sample_a'::text)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Seq Scan on t1
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Hash
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Bitmap Heap Scan on t2
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Recheck Cond: (c2 &gt;= 'sample_a'::text)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Bitmap Index Scan on t2_c2_c1_key
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Index Cond: (c2 &gt;= 'sample_a'::text)
&nbsp; -&gt; &nbsp;Result
&nbsp; &nbsp; &nbsp; &nbsp; One-Time Filter: (InitPlan 1).col1
&nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;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
&nbsp;c1
----
(0 rows)
```

After each statement, the target row should remain:

```text
&nbsp;c1 | c2
----+----
&nbsp; 1 | &nbsp;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
&nbsp;c1
----
&nbsp; 1
(1 row)

DELETE 1
```

The following `SELECT` returns no rows:

```text
&nbsp;c1 | c2
----+----
(0 rows)
```

After restoring the same initial state, the control query using `&gt;=` returns no
rows and leaves `(1, 0)` in `t3`:

```text
&nbsp;c1
----
(0 rows)

DELETE 0

&nbsp;c1 | c2
----+----
&nbsp; 1 | &nbsp;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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Kirill Reshke 2026-09-21 06:05:32 Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible
Previous Message Laurenz Albe 2026-09-21 05:48:45 Re: Detaching a child table makes an expression using it unrestorable