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


On Tue, 2 Aug 2022 at 00:17, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> PG Bug reporting form <noreply@postgresql.org> writes:
> > db=# explain SELECT generate_subscripts(ARRAY[]::integer[], 1) AS id,
> >        unnest(ARRAY[]::jsonb[]) ->> 'timestamp'  AS timestamp
> > FROM results
> > JOIN groups ON groups.id = results.group_id
> > WHERE results.search_id = 3336
> > order by timestamp;
>
> > The error is:
>
> > ERROR:  set-valued function called in context that cannot accept a set
> > LINE 2:        unnest(ARRAY[]::jsonb[]) ->> 'timestamp'  AS timestam...
>
> Hmm, that certainly seems like a bug, but I fear it's impossible
> to investigate without a reproducible test case.
>
>                         regards, tom lane
>


-- 
Martijn van Oosterhout <kleptog@gmail.com> http://svana.org/kleptog/