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

On Thu Jul 16, 2026 at 8:23 PM -03, Tom Lane 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). But I've
> not tried to write any code yet; do you feel like attacking that?
>

Attached patch does this: remove_useless_result_rtes() now precomputes a
Relids of all non-join RT indexes by scanning root->parse->rtable once
up front (rather than doing a second recursive walk), and threads it
through remove_useless_results_recurse() down to both
find_dependent_phvs() and find_dependent_phvs_in_jointree(). Both now
intersect a candidate PHV's phrels with that baserels set before
comparing to the target relid, instead of comparing phrels as-is.

I checked that a plain flat scan of the rtable is sufficient here rather
than needing to accumulate the set incrementally during the jointree
recursion. Every PHV this code ever matches has phlevelsup ==
sublevels_up, which by construction means its phrels are relids in the
outermost query's rangetable, not some inner subquery's. So a single
baserels set computed once from root->parse->rtable is valid at every
recursion depth, and we don't need to recompute or thread a different
set per query level.

Note: Richard have also shared a fix for this bug on this thread, it's
also seems to fix the issue and IIUC it fixes without the extra loop to
collect the baserels. I decided to continue with my attempt following
your suggestion so we can discuss both approaches (and also because I'm
still not super familiar with this part of the code and I wanted to
learn more about it).

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