Re: BUG #19460: FULL JOIN rewriting issue on empty queries

On Mon, Apr 20, 2026 at 6:10 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> This turns out to be because somebody long ago thought that outer join
> removal could be lazy about how much of the planner's data structures
> it needs to update.  Specifically, when the lower LEFT OUTER JOIN
> gets removed, we failed to remove the associated relids from the
> left_relids and right_relids of the upper "ON rhs.id = lhs.id" clause,
> and that blocks recognition of the applicability of a hash or merge
> join, because clause_sides_match_join() fails.

I came to the same conclusion.

> The fix seems pretty trivial, as attached.  (While I'm only certain
> that we have to fix left_relids and right_relids, this discovery
> makes it seem like it'd be pretty foolish not to fix all the relid
> sets of a RestrictInfo.)  I didn't make a regression test case yet,
> but we need one since no existing test results change (!?).

This fix LGTM.  I think it'd be better to have a regression test case.
How about this one:

create table t (id int unique);

explain (costs off)
select t1.*
from t t1 full join
  (select 1 as x
   from t t2 left join t t3 on t2.id = t3.id
  ) sub on t1.id = sub.x;
ERROR:  FULL JOIN is only supported with merge-joinable or
hash-joinable join conditions

> I'm feeling a tad nervous about pushing this into released branches.
> It seems likely that it might enable quite a few join plans that were
> previously not considered, and people tend not to like plan changes in
> stable branches.  However, (a) it's hard to argue that this isn't a
> regression from pre-v16, and (b) since this change affects no existing
> test, maybe the blast radius isn't as big as I fear.

Fair points on both sides.  I'd lean slightly toward back-patching
this fix, mostly because of your points (a) and (b).  Without a
back-patch, users like François would need to adjust affected queries
when upgrading from pre-v16 to v16–v18, which feels a bit unfortunate.

- Richard