Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19710: Incorrect DELETE result after LEFT JOIN optimization
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 2320415112@qq.com
Reply-To: 2320415112@qq.com, pgsql-bugs@lists.postgresql.org
Date: Mon, 21 Sep 2026 03:11:05 +0000
Message-ID: <19710-08547ac48c09de62@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19710-08547ac48c09de62%40postgresql.org>
Precedence: bulk

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:       =20

## 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 =3D t1.c1
     AND t2.c2 =3D 'sample_a'
    LEFT JOIN t4 ON true
    WHERE t1.c2 =3D t4.c1
)
RETURNING c1;

SELECT * FROM t3;

ROLLBACK TO SAVEPOINT initial_state;

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

SELECT * FROM t3;

ROLLBACK;
```

Both statements run from the same state. Since `t2` is empty, changing `=3D`
to
`>=3D` 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 =3D t1.c1
     AND t2.c2 =3D 'sample_a'
    LEFT JOIN t4 ON true
    WHERE t1.c2 =3D 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 =3D t1.c1
     AND t2.c2 >=3D 'sample_a'
    LEFT JOIN t4 ON true
    WHERE t1.c2 =3D t4.c1
)
RETURNING c1;
```

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

```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 (`>=3D`):

```text
Delete on t3
  InitPlan 1
    ->  Hash Left Join
          Hash Cond: (t1.c1 =3D t2.c1)
          Filter: (t1.c2 =3D 'sample_a'::text)
          ->  Seq Scan on t1
          ->  Hash
                ->  Bitmap Heap Scan on t2
                      Recheck Cond: (c2 >=3D 'sample_a'::text)
                      ->  Bitmap Index Scan on t2_c2_c1_key
                            Index Cond: (c2 >=3D '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 =3D 'sample_a'
```

The only row in `t1` has `c2 =3D '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 =3D 'sample_a'::text)
```

## Actual behavior

The test query using `=3D` returns and deletes `c1 =3D 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 `>=3D` retu=
rns
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 =3D 'sample_a'`
filter.
The control-query plan retains that filter. As a result, only the `=3D` form
makes
the `EXISTS` condition true and causes an incorrect persistent-state change.