Re: BUG #19460: FULL JOIN rewriting issue on empty queries - Mailing list pgsql-bugs

On Mon, Apr 20, 2026 at 10:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thanks for looking at it!  There is a loose end still bothering me:
> if you remove the lower "WHERE t.id = ..." clause, or change it to be
> something other than an equality constraint on t.id, the bug doesn't
> manifest.  The reason for that is un-obvious.

The reason seems to be that the equality constraint is a restriction
clause for the inner relation 't', and is needed to determine that the
relation has a matching unique index and is therefore distinct.  If we
remove it, or change it to something that isn't mergejoinable, we
won't be able to prove the inner side of the left join is distinct,
and thus won't be able to remove that left join.

I think the qual clause "sub.id = empty_source.id" might be confusing,
because empty_source.id is constant NULL, and this clause would be
simplified to constant NULL during const-folding.

- Richard