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

On Fri, Jul 17, 2026 at 8:23 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> So the simplest fix is probably to mask off OJ bits and consider only
> baserels when deciding if phrels equals the target. Unfortunately,
> this happens long before we compute root->all_baserels or anything
> like that, so remove_useless_result_rtes is on its own to figure out
> which those are. I think we can extend it to build a bitmapset of
> relevant baserel RT indexes while it is scanning the tree (so that we
> don't need an additional recursive scan just to get that).

It seems to me that with this approach the baserel RT indexes may not
be complete when a given PHV is examined, and I'm worried that that
could cause us to lose some optimizations. Maybe we can just call
get_relids_in_jointree((Node *) root->parse->jointree, false, false)
to get all baserel relids up front, though that costs an extra
recursive scan of the parsetree.

While looking into this, I was a little surprised that when we come to
consider RTE 9, phrels still contains ojrelid 7. I'd have expected
that to go away when we remove RTE 10, since the lower OJ is dropped
at the same time. The comment explains why it doesn't:

* ... We
* don't do this during the main recursion, for simplicity and because we
* can handle all such joins in a single pass over the parse tree.

So I'm thinking that we can subtract dropped_outer_joins from phrels
before comparing. The outer-join relids that show up here belong to
joins we have already decided to drop; their relids are stale only
because phrels does not get fixed up until the end of the pass. And a
surviving outer join in phrels always brings other baserels along with
it, so it cannot cause a false match. Also, the "dropped_outer_joins"
is already built up in remove_useless_results_recurse, so it is right
at hand.

I tried this idea and ended up with the attached.

- Richard