public inbox for [email protected]  

help / color / mirror / Atom feed

From: Richard Guo <[email protected]>
To: Tom Lane <[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 10:12:11 +0900
Message-ID: <CAMbWs48E2qqd4B6AdvJDyPBi685-_bDEvoe0aJnYzAPL1v30zg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

On Mon, Apr 20, 2026 at 6:10 AM Tom Lane <[email protected]> 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.

> The fix seems pretty trivial, as attached.  (While I'm only certain
> that we have to fix left_relids and right_relids, this discovery
> makes it seem like it'd be pretty foolish not to fix all the relid
> sets of a RestrictInfo.)  I didn't make a regression test case yet,
> but we need one since no existing test results change (!?).

This fix LGTM.  I think it'd be better to have a regression test case.
How about this one:

create table t (id int unique);

explain (costs off)
select t1.*
from t t1 full join
  (select 1 as x
   from t t2 left join t t3 on t2.id = t3.id
  ) sub on t1.id = sub.x;
ERROR:  FULL JOIN is only supported with merge-joinable or
hash-joinable join conditions

> I'm feeling a tad nervous about pushing this into released branches.
> It seems likely that it might enable quite a few join plans that were
> previously not considered, and people tend not to like plan changes in
> stable branches.  However, (a) it's hard to argue that this isn't a
> regression from pre-v16, and (b) since this change affects no existing
> test, maybe the blast radius isn't as big as I fear.

Fair points on both sides.  I'd lean slightly toward back-patching
this fix, mostly because of your points (a) and (b).  Without a
back-patch, users like François would need to adjust affected queries
when upgrading from pre-v16 to v16–v18, which feels a bit unfortunate.

- Richard

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: <CAMbWs48E2qqd4B6AdvJDyPBi685-_bDEvoe0aJnYzAPL1v30zg@mail.gmail.com>

* 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