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 1xJmBR-00000000QYG-0TFE for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 06:21:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJmBO-00000009A72-36oE for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 06:21:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJevj-00000007RVk-3GXU for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 22:37:07 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJevh-00000000ITm-1IPZ for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 22:37:06 +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=O7ORP25MrMD2mRWpjWd+Tfb4Z19Nhog/9wILETRvXNQ=; b=I+86Kc53gbSnM72N0O+FFoPq0c B34oPFdEVdjIa15MdTvcZc0W2BmDacr0LWglr6jzWdg5i8Nx536xj90rBA3cqevjDhOmS3A8m2C9f EFRXxqSubL3SQFr0Xw5cRWLBBVcFNqxB/0Bmgg0unTfU4UtVBIO75VndIvMh3W6jvxPfcyzeIeK7G la7fOja8cx9wSQO0bsGpbfl4WnrQVn4nD/F66UKmvYuYh4HrXHLhUVAmXz6XXnSqgK8PDYfXcX8y4 C1FQWa/cK9K1ICNmsmvpCOz/JH+yJD82AETVGQ60fv/d7pVNP8B+Zs5vL6koUmGI560hDzpMcHk8H fwTdyzcw==; 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 1xJevg-00000000m7k-2hM1 for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 22:37: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 1xJevf-00000002DNf-2Zrx for pgsql-bugs@lists.postgresql.org; Wed, 21 Oct 2026 22:37:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19787: "could not find pathkey item to sort" for Gather Merge over a merge join below reordered LEFT JOINs 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: Wed, 21 Oct 2026 22:37:01 +0000 Message-ID: <19787-d14d7f0f320f4495@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: 19787 Logged by: Alperen Keles Email address: alpkeles99@gmail.com PostgreSQL version: 18.6 Operating system: Ubuntu 24.04 x86_64 Description: =20 Hello, This query fails at plan time on empty tables, once a few cost settings make a parallel merge join attractive: CREATE TABLE a(x int); CREATE TABLE b(x int); CREATE TABLE c(x int); CREATE TABLE d(x int); VACUUM b, d; SET cpu_tuple_cost =3D 1; SET parallel_tuple_cost =3D 0; SET min_parallel_table_scan_size =3D 0; SELECT 1 FROM a LEFT JOIN b ON true LEFT JOIN c ON c.x =3D b.x LEFT JOIN d ON d.x =3D c.x; ERROR: could not find pathkey item to sort EXPLAIN fails with the same error. With SELECT c.x instead of SELECT 1, or with enable_mergejoin, enable_gathermerge or enable_sort off, or with join_collapse_limit =3D 1, the query returns no rows as expected. Reproduced on master a261fc2b722, 19rc1 (REL_19_STABLE 1212d5d33b1) and 18.6 (REL_18_STABLE dfbc1507cdb), with --enable-cassert, on Ubuntu 24.04 x86_64. 14.24 (REL_14_STABLE 14904052e65) plans it with hash joins and returns no rows. Bisecting on master gives 9df8f903eb6 ("Fix some issues with improper placement of outer join clauses") as the first commit that fails; its parent 867be9c0738 returns no rows. From a debugger, the chosen path is Nested Loop Left Join -> a -> Material -> Gather Merge (pathkey c.x) -> Merge Left Join (d.x =3D c.x) -> Sort (c.x) -> b LEFT JOIN c -> Sort (d.x) -> d i.e. the joins are reordered to a LEFT JOIN (b LEFT JOIN c LEFT JOIN d). Nothing above the {b,c,d} join needs c.x, so that joinrel's reltarget is empty, but the partial merge join path keeps its outer ordering c.x as pathkeys, and generate_gather_paths() puts a Gather Merge with those pathkeys on it. In create_gather_merge_plan(), prepare_sort_from_pathkeys() gets the merge join's empty tlist and cannot find or compute c.x. As far as I can tell, c.x survives as a pathkey because of the clone clauses for the commuted outer joins. The {b,c,d} joinrel's joininfo still holds the is_clone copies "c.x =3D b.x(nulled by the a/b join)" and "d.x =3D c.x(nulled by the b/c join)". Since 9df8f903eb6 the commuted joinrel does not include those outer joins' relids yet, so these clones are not considered applied, although their non-clone versions were already applied inside it. pathkeys_useful_for_merging() finds the first clone in rel->joininfo, whose left_ec is the c.x pathkey's EC, so truncate_useless_pathkeys() keeps c.x, although no later join can use that clause. Found with a query fuzzer. Regards, Alperen Keles