BUG #19707: Reproduction of BUG #19553 on PostgreSQL 18.4: nested LEFT JOIN returns a constant instead of NULL

The following bug has been logged on the website:

Bug reference: 19707
Logged by: N J
Email address: 1482694023(at)qq(dot)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.