The following bug has been logged on the website:
Bug reference: 18652
Logged by: Alexander Lakhin
Email address: exclusion(at)gmail(dot)com
PostgreSQL version: 17.0
Operating system: Ubuntu 22.04
Description:
The following query:
CREATE TABLE t(i int, j int);
CREATE INDEX idx on t((i + 0));
SELECT * FROM t,
(SELECT i + 0 AS i FROM
(SELECT i FROM t UNION ALL SELECT i + 1 FROM t) AS t1
) AS t2
WHERE t2.i = t.j;
fails with:
ERROR: XX000: could not find pathkey item to sort
LOCATION: prepare_sort_from_pathkeys, createplan.c:6350
The error occurs only when the expression in SELECT i + 0 AS i FROM
matches the expression in an index.
Reproduced on REL_10_STABLE .. master.