BUG #19553: Wrong results from nested LEFT JOINs over an empty subquery (regression since v16)

The following bug has been logged on the website:

Bug reference: 19553
Logged by: Viktor Leis
Email address: leis(at)in(dot)tum(dot)de
PostgreSQL version: 19beta2
Operating system: Ubuntu 26.04
Description:

Hi,

The following self-contained query returns wrong results on every release
since v16:

select * from (values (1),(2)) v(x)
left join (select q from (select 7 as q from (select where false) ss1)
ss2
left join (select 8 as z) ss3 on true) ss4 on true;

x | q
---+---
1 | 7
2 | 7
(2 rows)

The right-hand side of the top left join is provably empty (ss1 produces no
rows, and the inner left join preserves that), so the correct result
null-extends both rows:

x | q
---+---
1 |
2 |
(2 rows)

EXPLAIN (VERBOSE) on affected versions shows that the entire RHS has been
optimized away and the constant is emitted unconditionally:

Values Scan on "*VALUES*"
Output: "*VALUES*".column1, 7

I bisected the regression to commit 3af87736bf5 ("Fix another cause of
'wrong varnullingrels' planner failures" by Tom Lane).

Claude Code Analysis:

After subquery pullup, everything is still correct. Using the RT indexes
of the example (4 = the VALUES rel, 9/10 = the RESULT rels deriving from
ss1 resp. ss3, 3/7 = the RTIs of the upper resp. inner left join), the
jointree is

VALUES(4) leftjoin[3] ( FromExpr(RESULT(9), quals=false) leftjoin[7]
RESULT(10) )

and the output column q is

PlaceHolderVar(Const 7, phrels={7,9,10}, phnullingrels={3})

Then remove_useless_results_recurse() goes wrong in three steps:

1. The mechanism added by 3af87736bf5 hoists the constant-false qual from
the single-child FromExpr (ss1's WHERE clause) through the inner join's
parent_quals pointer into the *upper* join's quals, collapsing the
FromExpr to a bare RangeTblRef of RESULT(9).

2. The inner left join (RTI 7, ON true against the one-row RESULT(10)) is
dropped; that is fine in itself. remove_result_refs() substitutes
10 -> {9} in the PHV's phrels, giving {7,9}. Crucially, the dropped
join's RTI 7 stays in phrels: cleanup of dropped-join RTIs is deferred
to a single remove_nulling_relids() pass at the end of
remove_useless_result_rtes().

3. For the upper join (RTI 3), now with quals=false over a bare RESULT(9),
removal is only legal if no PHV must be evaluated at the RESULT rel;
the guard find_dependent_phvs(root, 9) tests
bms_equal(phv->phrels, {9}). Because of the stale RTI the PHV's phrels
is {7,9}, the exact-match test misses it, and the join is dropped even
though its constant-false quals mean every LHS row must be
null-extended. remove_result_refs() then relocates the PHV to the
VALUES rel and the end-of-pass cleanup strips RTI 3 from its
phnullingrels, leaving a never-nulled Const 7.

So the guard itself is fine; it is defeated by phrels not being maintained
while the recursion is still running. Note that the end-of-pass cleanup
cannot simply be moved earlier, because remove_nulling_relids() is a
mutator that would invalidate the jointree surgery in progress.

Best regards,
Viktor Leis