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

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> After migrating from version 15 to 17.9, the following query fails with:
> ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join
> conditions

Thanks for the report!

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.

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 (!?).

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.

Thoughts?

regards, tom lane