agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 1482694023@qq.com
Subject: BUG #19707: Reproduction of BUG #19553 on PostgreSQL 18.4: nested LEFT JOIN returns a constant instead of NULL
Date: Sun, 20 Sep 2026 11:33:00 +0000
Message-ID: <19707-da66e5a04b617fd4@postgresql.org> (raw)

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:        

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.

view thread (2+ messages)  latest in thread

Message-ID: <19707-da66e5a04b617fd4@postgresql.org>
Permalink:  ../19707-da66e5a04b617fd4@postgresql.org/
Also on:    postgresql.org/message-id/19707-da66e5a04b617fd4@postgresql.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, 1482694023@qq.com
  Subject: Re: BUG #19707: Reproduction of BUG #19553 on PostgreSQL 18.4: nested LEFT JOIN returns a constant instead of NULL
  In-Reply-To: <19707-da66e5a04b617fd4@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox