public inbox for [email protected]  

help / color / mirror / Atom feed

From: PG Bug reporting form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: BUG #19460: FULL JOIN rewriting issue on empty queries
Date: Sun, 19 Apr 2026 17:57:59 +0000
Message-ID: <[email protected]> (raw)

The following bug has been logged on the website:

Bug reference:      19460
Logged by:          François Jehl
Email address:      [email protected]
PostgreSQL version: 17.9
Operating system:   Linux
Description:        

Good evening,

After migrating from version 15 to 17.9, the following query fails with:

ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join
conditions

This is weird because the FULL JOIN is a basic equi-join that succeeds on
PG15 (we tested it; it also fails on 16).

It requires a table t(id UUID PRIMARY KEY) but here is a repro on DbFiddle
https://www.db-fiddle.com/f/hCq5S13Zs3EV8f86Mxxh3B/3.
                                                     
  SELECT COALESCE(lhs.id, rhs.id) AS id
  FROM (SELECT gen_random_uuid() AS id) AS lhs
  FULL OUTER JOIN (
    SELECT sub.id
    FROM (
      SELECT empty_source.id
      FROM (SELECT NULL::UUID AS id WHERE FALSE) AS empty_source
      LEFT OUTER JOIN (
        SELECT t.id FROM t WHERE t.id =
'26c5112c-0a8f-4315-9ff5-7dcb59b8359e'::UUID
      ) AS sub ON sub.id = empty_source.id
    ) AS sub
  ) AS rhs ON rhs.id = lhs.id;
                                                                                                                                                         
Adding OFFSET 0 to the empty subquery on the RHS prevents the error,
suggesting the query rewriter is doing something it should not!
Another thing: removing the PK constraint on t.id, or removing/changing the
WHERE filter to a non-PK column, makes the query succeed. The PK equality
filter is required to trigger the failure (maybe because it generates some
additional inlining, which then allows the empty subquery to collapse).

We're happy to provide more context or test patches if helpful.

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]
  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