Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19707: Reproduction of BUG #19553 on PostgreSQL 18.4: nested
 LEFT JOIN returns a constant instead of NULL
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 1482694023@qq.com
Reply-To: 1482694023@qq.com, pgsql-bugs@lists.postgresql.org
Date: Sun, 20 Sep 2026 11:33:00 +0000
Message-ID: <19707-da66e5a04b617fd4@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19707-da66e5a04b617fd4%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19707
Logged by:          N J
Email address:      1482694023@qq.com
PostgreSQL version: 18.4
Operating system:   Windows 11 64-bit
Description:       =20

Environment:
PostgreSQL: 18.4
Client: pgAdmin 4
Operating system: Windows 11 64-bit

The following query returns a constant from the nullable side of a LEFT
JOIN,
although the corresponding subquery is guaranteed to be empty.

Reproduction query:

SELECT
    input_rows.sample_id,
    nullable_side.payload
FROM (VALUES (11), (22)) AS input_rows(sample_id)
LEFT JOIN (
    SELECT payload
    FROM (
        SELECT 37 AS payload
        FROM (SELECT WHERE FALSE) AS guaranteed_empty
    ) AS projected_empty
    LEFT JOIN (
        SELECT 99 AS auxiliary_value
    ) AS one_row_helper
    ON TRUE
) AS nullable_side
ON TRUE;

Observed result on PostgreSQL 18.4:
 sample_id | payload
-----------+---------
        11 |      37
        22 |      37

Expected result:
 sample_id | payload
-----------+---------
        11 | NULL
        22 | NULL

The guaranteed_empty subquery cannot produce any rows. Therefore, the
right-hand side of the outer LEFT JOIN is empty and payload should be
NULL-extended for both input rows.

Instead, the constant value 37 is emitted for both rows. EXPLAIN (VERBOSE)
may show that the right-hand side has been optimized away while the constant
is retained in the output expression.