Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19460: FULL JOIN rewriting issue on empty queries
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: francois.jehl@pigment.com
Reply-To: francois.jehl@pigment.com, pgsql-bugs@lists.postgresql.org
Date: Sun, 19 Apr 2026 17:57:59 +0000
Message-ID: <19460-5625143cef66012f@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: <https://www.postgresql.org/message-id/19460-5625143cef66012f%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19460
Logged by:          Fran=C3=A7ois Jehl
Email address:      francois.jehl@pigment.com
PostgreSQL version: 17.9
Operating system:   Linux
Description:       =20

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.
                                                    =20
  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 =3D
'26c5112c-0a8f-4315-9ff5-7dcb59b8359e'::UUID
      ) AS sub ON sub.id =3D empty_source.id
    ) AS sub
  ) AS rhs ON rhs.id =3D lhs.id;
                                                                           =
                                                                           =
  =20
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.