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

On Thu Jul 16, 2026 at 11:12 AM -03, PG Bug reporting form wrote:
> 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).
>

Hi, thank you for the report and for the script reproducer.

According to my findings, the issue seems to be in
remove_useless_results_recurse(), specifically in find_dependent_phvs().
When a LEFT JOIN's RHS reduces to an RTE_RESULT, that function is used
to check whether any PlaceHolderVar still depends on the RTE_RESULT
being considered for removal; if not, the join is discarded along with
the RTE_RESULT. The check was testing whether a PHV's phrels was exactly
equal to {varno}, but that's too narrow. In your example, the constant
"7" gets wrapped in a PlaceHolderVar whose phrels ends up as the union
of both RTE_RESULTs in the inner commuted left join (the ss1 "WHERE
false" result and the ss3 "SELECT 8" result), because the pulled-up
subquery's output needs to be nullable by the outer join. Since phrels
was {ss1, ss3} rather than exactly {ss1}, find_dependent_phvs() wrongly
concluded the outer join had no remaining dependents and dropped it,
taking the join's null-extension semantics with it. That's why the
constant got emitted unconditionally instead of being nulled out for the
"no match" case.

Attached patch seems to fix find_dependent_phvs() to test membership
rather than exact equality, since that's what actually indicates the
PHV's value depends on that relation. find_dependent_phvs_in_jointree(),
used for a different purpose so keeps the exact-match behavior it had,
as that's it seems to still correct for its use case. I added a field to
the shared context struct to keep both behaviors sharing one walker.

Verified against your original repro and a version using tables instead
of VALUES/constants (make it more easier for me to debug):

create table t (id int, name text);
insert into t values (1, 'Alice'), (2, 'Bob');

select c.id, c.name, promo.discount
from t c
left join (
select discount
from (select 0.20 as discount from (select where false) no_matching_promo) rates
left join (select true as eligible) elig on true
) promo on true;

--
Matheus Alcantara
EDB: https://www.enterprisedb.com