public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Richard Guo <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Robert Haas <[email protected]>
Subject: Re: BUG #19460: FULL JOIN rewriting issue on empty queries
Date: Mon, 20 Apr 2026 15:01:20 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMbWs4_nNfYktwQu4vNbienz+6oKXeE1Vzq9K_qH9F19MaJoMQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAMbWs48E2qqd4B6AdvJDyPBi685-_bDEvoe0aJnYzAPL1v30zg@mail.gmail.com>
	<[email protected]>
	<CAMbWs49PPxhrqBjHj3Wy4hK4=_DoHkJdAHL_q1mqpyyCk5MdMw@mail.gmail.com>
	<[email protected]>
	<CAMbWs4_nNfYktwQu4vNbienz+6oKXeE1Vzq9K_qH9F19MaJoMQ@mail.gmail.com>

Richard Guo <[email protected]> writes:
> On Mon, Apr 20, 2026 at 11:32 AM Tom Lane <[email protected]> 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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: BUG #19460: FULL JOIN rewriting issue on empty queries
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox