From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: alpkeles99@gmail.com
Subject: BUG #19799: Self-join elimination: "failed to build any 3-way joins" with LATERAL
Date: Mon, 26 Oct 2026 16:58:54 +0000
Message-ID: <19799-dab86d769b08dc53@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19799
Logged by: Alperen Keles
Email address: alpkeles99@gmail.com
PostgreSQL version: 18.6
Operating system: Ubuntu 24.04 x86_64 (also macOS 26 arm64)
Description:
Hello,
With self-join elimination, this query fails at plan time with an
internal error:
CREATE TABLE t (a int PRIMARY KEY);
INSERT INTO t VALUES (1), (2);
SELECT s.y
FROM t d LEFT JOIN
(t x CROSS JOIN LATERAL (SELECT x.a AS v OFFSET 0) b
CROSS JOIN LATERAL (SELECT coalesce(b.v, 0) FROM t c
WHERE c.a = x.a) s(y))
ON true;
ERROR: XX000: failed to build any 3-way joins
LOCATION: standard_join_search, allpaths.c:3537 (18.6)
On builds with --enable-cassert the backend crashes instead:
TRAP: failed Assert("!bms_is_member(rti, lateral_relids)"),
File: "initsplan.c", Line: 1370 (master; Line 1086 on REL_18_STABLE)
With SET enable_self_join_elimination = off the query returns the 4
expected rows (each of 1 and 2 twice). The table can be empty. It also
fails with FULL JOIN or RIGHT JOIN in place of the LEFT JOIN, with
(SELECT 1) in place of d, and with b written as LATERAL
generate_series(1, x.a) or a two-row LATERAL VALUES list; with
geqo_threshold = 2 the error is "geqo failed to make a valid plan". It
works without the outer join, without the unique key, with b.v or b.v
+ 1 in place of coalesce(b.v, 0), without OFFSET 0 in b, and with
join_collapse_limit = 1 and from_collapse_limit = 1.
Reproduced on 18.6 (REL_18_6, without assertions, macOS arm64),
REL_18_STABLE 4d62ef92fda, 19rc1 (REL_19_STABLE 1212d5d33b1) and
master 022a80fe6c1 (--enable-cassert, Ubuntu 24.04 x86_64), and master
a261fc2b722 without assertions. 17 and earlier have no self-join
elimination; 14 returns the rows.
The same assertion was reported in the thread "Assert failure in
create_lateral_join_info" and fixed by 3138f248cd3 ("Don't remove an
RTE_RESULT needed by a nested PlaceHolderVar"). That query was
reached through RTE_RESULT removal and works on master 022a80fe6c1,
which contains 3138f248cd3, but this one still fails there.
Found with a query fuzzer.
Regards,
Alperen Keles
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, alpkeles99@gmail.com
Subject: Re: BUG #19799: Self-join elimination: "failed to build any 3-way joins" with LATERAL
In-Reply-To: <19799-dab86d769b08dc53@postgresql.org>
References: <19799-dab86d769b08dc53@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox