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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)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(at)gmail(dot)com> http://svana.org/kleptog/