agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: leis@in.tum.de
Subject: BUG #19553: Wrong results from nested LEFT JOINs over an empty subquery (regression since v16)
Date: Thu, 16 Jul 2026 14:12:34 +0000
Message-ID: <19553-4561747f93f368a7@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19553
Logged by: Viktor Leis
Email address: leis@in.tum.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
view thread (13+ messages) latest in thread
Message-ID: <19553-4561747f93f368a7@postgresql.org>
Permalink: ../19553-4561747f93f368a7@postgresql.org/
Also on: postgresql.org/message-id/19553-4561747f93f368a7@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, leis@in.tum.de
Subject: Re: BUG #19553: Wrong results from nested LEFT JOINs over an empty subquery (regression since v16)
In-Reply-To: <19553-4561747f93f368a7@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