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: <CAMbWs48E2qqd4B6AdvJDyPBi685-_bDEvoe0aJnYzAPL1v30zg@mail.gmail.com>
References: <19460-5625143cef66012f@postgresql.org> <53936.1776633020@sss.pgh.pa.us> <CAMbWs48E2qqd4B6AdvJDyPBi685-_bDEvoe0aJnYzAPL1v30zg@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Mon, 20 Apr 2026 10:12:11 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <81856.1776648374.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Sun, 19 Apr 2026 21:26:14 -0400
Message-ID: <81857.1776648374@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/81857.1776648374%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> 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.

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.  I suppose it's somehow
related to the code that tries to push equality-to-a-constant through
outer join clauses, but that code shouldn't be able to produce any new
clauses here, so why is there a visible effect?  I'm too tired to look
right now, and was planning to study it more tomorrow.  But if you
are interested in digging before that, feel free.

			regards, tom lane