Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19553: Wrong results from nested LEFT JOINs over an empty
 subquery (regression since v16)
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: leis@in.tum.de
Reply-To: leis@in.tum.de, pgsql-bugs@lists.postgresql.org
Date: Thu, 16 Jul 2026 14:12:34 +0000
Message-ID: <19553-4561747f93f368a7@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19553-4561747f93f368a7%40postgresql.org>
Precedence: bulk

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:       =20

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 =3D the VALUES rel, 9/10 =3D the RESULT rels deriving from
ss1 resp. ss3, 3/7 =3D the RTIs of the upper resp. inner left join), the
jointree is

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

and the output column q is

  PlaceHolderVar(Const 7, phrels=3D{7,9,10}, phnullingrels=3D{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=3Dfalse 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