MIME-Version: 1.0
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>
In-Reply-To: <88899.1776652344@sss.pgh.pa.us>
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 20 Apr 2026 11:51:02 +0900
Message-ID: <CAMbWs4_nNfYktwQu4vNbienz+6oKXeE1Vzq9K_qH9F19MaJoMQ@mail.gmail.com>
Subject: Re: BUG #19460: FULL JOIN rewriting issue on empty queries
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: francois.jehl@pigment.com, pgsql-bugs@lists.postgresql.org, 
	Robert Haas <robertmhaas@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAMbWs4_nNfYktwQu4vNbienz%2B6oKXeE1Vzq9K_qH9F19MaJoMQ%40mail.gmail.com>
Precedence: bulk

On Mon, Apr 20, 2026 at 11:32=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote=
:
> Richard Guo <guofenglinux@gmail.com> writes:
> > On Mon, Apr 20, 2026 at 10:26=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> w=
rote:
> >> Thanks for looking at it!  There is a loose end still bothering me:
> >> if you remove the lower "WHERE t.id =3D ..." clause, or change it to b=
e
> >> 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.

> 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
=3D ..." 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.

- Richard