Re: BUG #17564: Planner bug in combination of generate_series(), unnest() and ORDER BY

Hoi Tom, pgsql-bugs,

Now it's morning I've thought of a way to reproduce it more easily, see the attached script. The tricky part is getting the tuples in a position that triggers the planner in the right way. So the script includes a list of (ctid, primary key) and creates a table using that with quite a large amount of bloat. It then creates some constraints, vacuums and runs the offending query. On my system it reproduces with 100% reliability (so far anyway).

10:41 $ PGPASSWORD=pass psql -h 127.0.0.1 -U postgres db2 < /tmp/reproduce2.sql
                                                           version                                                          
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.4 (Debian 14.4-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

DROP TABLE
DROP TABLE
DROP TABLE
NOTICE:  table "input" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
COPY 1438
INSERT 0 166
ALTER TABLE
SELECT 192000
SELECT 192000
DELETE 1356
ALTER TABLE
DELETE 189206
VACUUM
VACUUM
ERROR:  set-valued function called in context that cannot accept a set
LINE 2:        unnest(ARRAY[]::jsonb[]) ->> 'timestamp'  AS timestam...
               ^

Hope this helps,

Martijn