BUG #19742: `INTERSECT` under a `UNION ALL` with an empty arm fails with "could not find pathkey item t" - Mailing list pgsql-bugs

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:

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 = 1;
--   ERROR:  XX000: could not find pathkey item to sort
(prepare_sort_from_pathkeys, createplan.c)

-- 18.6:            a = 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
-- ============ workaround: the same query without a sorted SetOp
============
SET enable_sort = off;            -- or enable_hashagg = on with statistics
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 = 1;                      -- 1, 1 (HashSetOp Intersect All)
RESET enable_sort;