From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: francois.jehl@pigment.com, pgsql-bugs@lists.postgresql.org,
        Robert Haas <robertmhaas@gmail.com>
Subject: Re: BUG #19460: FULL JOIN rewriting issue on empty queries
In-reply-to: <CAMbWs4_nNfYktwQu4vNbienz+6oKXeE1Vzq9K_qH9F19MaJoMQ@mail.gmail.com>
References: <19460-5625143cef66012f@postgresql.org> <53936.1776633020@sss.pgh.pa.us> <CAMbWs48E2qqd4B6AdvJDyPBi685-_bDEvoe0aJnYzAPL1v30zg@mail.gmail.com> <81857.1776648374@sss.pgh.pa.us> <CAMbWs49PPxhrqBjHj3Wy4hK4=_DoHkJdAHL_q1mqpyyCk5MdMw@mail.gmail.com> <88899.1776652344@sss.pgh.pa.us> <CAMbWs4_nNfYktwQu4vNbienz+6oKXeE1Vzq9K_qH9F19MaJoMQ@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Mon, 20 Apr 2026 11:51:02 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <418973.1776711680.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Mon, 20 Apr 2026 15:01:20 -0400
Message-ID: <418974.1776711680@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/418974.1776711680%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> On Mon, Apr 20, 2026 at 11:32 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmm.  The bug also goes away if "t" doesn't have a unique/pkey
>> constraint, and I find that easy to understand: we can't apply outer
>> join removal unless rel_supports_distinctness/rel_is_distinct_for
>> succeed, so that this buggy code in remove_rel_from_restrictinfo
>> is not reached.  But that logic doesn't consider WHERE constraints
>> AFAICS.  So I think there is some other code path involved.

> Hmm, relation_has_unique_index_for does consider the lower "WHERE t.id
> = ..." clause, as that clause is a restriction clause for "t", and
> relation_has_unique_index_for automatically adds any usable
> restriction clauses for the rel.

Ah, I finally got it through my head that there are two distinct proof
paths by which we might reach the conclusion that the lower left join
is removable.  I had been thinking that we were proving that from the
combination of the "sub.id = empty_source.id" clause with the unique
index on t.id.  But we're not, in the query as-submitted, because
we pull up the NULL::UUID constant and const-fold that clause to
NULL.  Instead, it's the lowest "WHERE t.id = ..." that is combined
with the unique index to make the proof.  So without an equality
test there, we don't think the inner side is unique and don't do
join removal, thus dodging the bug.  The WHERE FALSE bit masks this
omission because it causes us to reduce the outer join to a dummy
relation anyway, later on.  But if you take that out, you can see
that join removal is not being performed.

I thought it was worth memorializing these two variants in separate
test queries, so I did that.  The variant without the lowest WHERE
has a non-null constant in the LOJ's left-hand side, so it's able to
make the removal proof from the "sub.id = empty_source.id" clause.

Pushed at cfcd57111 et al.  Thanks again for the report!

			regards, tom lane