Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails
 with "could not find pathkey item t"
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: feasiblechart@gmail.com
Reply-To: feasiblechart@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Sat, 03 Oct 2026 16:17:03 +0000
Message-ID: <19742-dc403ca277cad1d3@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19742-dc403ca277cad1d3%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19742
Logged by:          Junwen AN
Email address:      feasiblechart@gmail.com
PostgreSQL version: 19beta4
Operating system:   Linux
Description:       =20

Please see the repro. Seems like a regression; 19beta4 and the current main
branch both have this error raised, but 18.6 works fine. I ran it with psql

CREATE TABLE d (a int);
INSERT INTO d VALUES (1), (1), (2), (NULL), (3);
SELECT * FROM (SELECT a FROM d INTERSECT ALL SELECT a FROM d
               UNION ALL SELECT a FROM d WHERE false) s
WHERE a =3D 1;
--   ERROR:  XX000: could not find pathkey item to sort
(prepare_sort_from_pathkeys, createplan.c)

-- 18.6:            a =3D 1, 1
-- 19beta4 / main:  ERROR:  could not find pathkey item to sort
-- EXPLAIN (without ANALYZE) fails the same way: the error is raised while
planning.

Did some more digging with LLM, and it seems this works fine
-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D workaround: the same query without =
a sorted SetOp
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
SET enable_sort =3D off;            -- or enable_hashagg =3D on with statis=
tics
that favour hashing
SELECT * FROM (SELECT a FROM d INTERSECT ALL SELECT a FROM d
               UNION ALL SELECT a FROM d WHERE false) s
WHERE a =3D 1;                      -- 1, 1 (HashSetOp Intersect All)
RESET enable_sort;