Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLRNL-00000001W3d-2BaK for pgsql-bugs@arkaria.postgresql.org; Mon, 26 Oct 2026 20:33:00 +0000 Received: from localhost ([127.0.0.1]:58498 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xLRNL-00000007mZ7-0BED for pgsql-bugs@arkaria.postgresql.org; Mon, 26 Oct 2026 20:32:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:60030) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLO2Q-00000006hqY-1R1f for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 16:59:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLO2N-0000000160I-3F9o for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 16:59:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=0gJFMfl8KPgSVdx83SREvQgHp14C2d2iil33Deow8ZQ=; b=OqOkAvUL5vCVkTmoIKCVki4VIA sHCgpAu724Vb7k7CHmiIcUAh8Rl+RIImVgY15AHlSxBaujZ4UP49q5RlEasNDwDQrbfB93Q1J4eSe 7ta31ZwbnKsXuk4T+WcWTN0Fh+Afvk5y2g1pdHJHbqi4cKS1xd8W1LyNePW3nQYgqoane78zCxdPf 8jd+d0rg4THYSHFDDMp2nD7lKZqMkBrshPxLoPuEWopxowBmaA+3jqvA3z9Ztrng17z7Gzi1/X7Xs Be3pHMDN/xcJgnV+1mQTKqSgj/XqFIq7xCMK6c6fohbtzNuMhjq6u4wlrBLbLJI8xEBZP8PPkTXUp PQQBDE2w==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLO2M-0000000345L-1r5B for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 16:59:06 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xLO2J-00000001WT1-3aTo for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 16:59:04 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19799: Self-join elimination: "failed to build any 3-way joins" with LATERAL To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: alpkeles99@gmail.com Reply-To: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org Date: Mon, 26 Oct 2026 16:58:54 +0000 Message-ID: <19799-dab86d769b08dc53@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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 =3D 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 =3D 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 =3D 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 =3D 1 and from_collapse_limit =3D 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