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.
