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 1xLRMU-00000001W3H-3fvb for pgsql-bugs@arkaria.postgresql.org; Mon, 26 Oct 2026 20:32:07 +0000 Received: from localhost ([127.0.0.1]:59958 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xLRMU-00000007kGo-18i8 for pgsql-bugs@arkaria.postgresql.org; Mon, 26 Oct 2026 20:32:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:55014) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLKOv-00000004jjK-0IcV for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 13:06:09 +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 1xLKOr-000000013ml-2zFY for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 13:06:08 +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=r8y/CIpp8EJfSOb365C5VbV7eTtEjyw1exjbLn4bDFc=; b=wkedqzE2PT95yRmLqHUKyjA2KQ wQ2Wf22h7bIdg+1ujuMHiU4phyHMKVrQeVOJ5ihLzhX+VDY+DEhn/W3wmBUrr7lP1xG5ZsOMyMa3s +tw14Z1qnu8Blgn+0HeDBTHX32zicuiL0Jf4mB2oOVxSpbB0wgomTiN5jqzSCwq2rBpBJNPKjsL4n h5QNBbsaV7pMD6g6N19hAOtqZxfjPapt6vhAuNk29KZwwYfBLOfSZiJL5WaTgphWGFhlztxKpHUjk BXhe6ocaBCrT0lFURSK5DWlM/47aTsZH2yjJ5S1gJB5CmYchVGUumf8vDHDNTezkurLG2iDjHwIty UFcohCBw==; 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 1xLKOq-00000002zLm-24p9 for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 13:06:04 +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 1xLKOo-00000001LSX-16UC for pgsql-bugs@lists.postgresql.org; Mon, 26 Oct 2026 13:06:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19798: "failed to build any 4-way joins" with ROLLUP over a LATERAL subquery that references the nullable s 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 13:05:24 +0000 Message-ID: <19798-5a5da856f5f71118@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: 19798 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, A query with grouping sets fails at plan time with an internal error when a LATERAL subquery over two tables returns a column of the nullable side of an earlier LEFT JOIN. The same query without grouping sets runs fine. CREATE TABLE t (x int); INSERT INTO t VALUES (1), (2); SELECT s.y FROM t b LEFT JOIN t a ON a.x =3D b.x CROSS JOIN LATERAL (SELECT a.x AS y FROM t c, t d) s GROUP BY ROLLUP (s.y); ERROR: XX000: failed to build any 4-way joins LOCATION: standard_join_search, allpaths.c:3538 (18.6) The table can be empty. CUBE (s.y), GROUPING SETS ((s.y), ()) and a RIGHT JOIN in place of the LEFT JOIN fail the same way. With three tables in the subquery it fails with "5-way joins", and with "t b LEFT JOIN (t a JOIN t f ON a.x =3D f.x) ON a.x =3D b.x" and three tables in the subquery with "6-way joins". The query works with a plain GROUP BY s.y or no GROUP BY, with one table in the subquery, with an inner join instead of the LEFT JOIN, when the subquery references b instead of a, or with join_collapse_limit =3D 1 and from_collapse_limit =3D 1. Reproduced on 18.6 (REL_18_STABLE 4d62ef92fda), 19rc1 (REL_19_STABLE 1212d5d33b1), 14.24 (REL_14_STABLE 14904052e65) and master 022a80fe6c1, --enable-cassert, Ubuntu 24.04 x86_64; also on master and 18.6 on macOS arm64. It is a plain ERROR, not an assertion failure. All of these builds include the fix for the similar case in the thread "failed to build any N-way joins" from a five-relation query (commit b84ad0cd904 on master), and that thread's query works on them, but this one still fails. The first failing commit is 90947674fc9 ("Fix incorrect handling of subquery pullup in the presence of grouping sets", 2018), which was back-patched to 9.5. It fails, and its parent ca4587f3f94 returns the rows. As far as I can tell, since that commit pull_up_simple_subquery() wraps the subquery output a.x, a lateral reference, in a PlaceHolderVar evaluated at {c,d} when the query has grouping sets. So c and d must be joined first, and the only legal 4-way join is {b,a} + {c,d}. No join clause connects these two. In the bushy loop of join_search_one_level() the pair is considered only with {b,a} as old_rel, since {b,a} comes first in the level-2 list. {b,a} is skipped there because it has no join clauses and has_join_restriction() is false for it: it contains its outer join, and a joinrel's lateral_referencers is always NULL. The 5-way and 6-way forms fail the same way, because there the pair is considered only with the smaller side, {b,a} or {b,a,f}, as old_rel. Found with a query fuzzer. Regards, Alperen Keles