Обсуждение: pg18 bug? SELECT query doesn't work
Hi all!
I ran into a situation where a query that worked just fine on pg15 fails on pg18.1 with an ERROR.
I've compiled pg18.1 from source:
# select version();
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.1 on aarch64-apple-darwin24.4.0, compiled by Apple clang version 17.0.0 (clang-1700.0.13.5), 64-bit
(1 row)
$ pg_config --configure
'--prefix=/path/to/pg18' '--with-pgport=5418' '--enable-debug' '--enable-cassert' 'CPPFLAGS= -DUSE_ASSERT_CHECKING=1
-DRANDOMIZE_ALLOCATED_MEMORY=1' 'PKG_CONFIG_PATH=/opt/homebrew/opt/icu4c/lib/pkgconfig' 'CFLAGS=-O0 -g'
'CXXFLAGS=-I/opt/homebrew/include'
Here's a reduced test case:
drop table if exists wth;
create table wth (id serial8, json_data json);
insert into wth (json_data) values ('[{"animal": "cats"}, {"animal": "dogs"}]');
-- this ERRORs on pg18
select animal
from (select upper(json_array_elements(json_data) ->> 'animal') animal, count(*) from wth group by 1) x
where animal ilike 'c%';
On pg15 I get the expected result of:
animal
--------
CATS
(1 row)
On pg18 I'm presented with:
ERROR: set-valued function called in context that cannot accept a set
LINE 1: select animal from (select upper(json_array_elements(json_da...
With pg18 I messed around with rewriting it and discovered another inconsistency:
# with animals as (
select animal
from (select upper(json_array_elements(json_data) ->> 'animal') animal, count(*) from wth group by 1) x)
select * from animals where animal ilike 'c%';
ERROR: set-valued function called in context that cannot accept a set
LINE 3: from (select upper(json_array_elements(json_data) ->> 'anima...
v/s
# with animals as MATERIALIZED (
select animal
from (select upper(json_array_elements(json_data) ->> 'animal') animal, count(*) from wth group by 1) x)
select * from animals where animal ilike 'c%';
animal
--------
CATS
(1 row)
I'd expect both those queries to return "CATS", in addition to the original query that worked on (at least) pg15.
Just thought I'd bring this to y'alls attention.
Thanks and happy 2026!
eric
> On Jan 6, 2026, at 10:22 AM, Eric Ridge <eebbrr@gmail.com> wrote:
>
> Here's a reduced test case:
Here's an even more reduced test case. No tables or data:
# SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as animal FROM generate_series(1, 10) GROUP BY 1) x WHERE
animalilike 'c%';
pg15 returns:
animal
--------
CAT
(1 row)
and pg18 says:
# SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as animal FROM generate_series(1, 10) GROUP BY 1) x WHERE
animalilike 'c%';
ERROR: set-valued function called in context that cannot accept a set
LINE 1: SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as ...
^
Thanks!
eric
On Tue, Jan 6, 2026 at 8:22 AM Eric Ridge <eebbrr@gmail.com> wrote:
drop table if exists wth;
create table wth (id serial8, json_data json);
insert into wth (json_data) values ('[{"animal": "cats"}, {"animal": "dogs"}]');
-- this ERRORs on pg18
select animal
from (select upper(json_array_elements(json_data) ->> 'animal') animal, count(*) from wth group by 1) x
where animal ilike 'c%';
On pg18 I'm presented with:
ERROR: set-valued function called in context that cannot accept a set
LINE 1: select animal from (select upper(json_array_elements(json_da...
Just thought I'd bring this to y'alls attention.
It was an intentional change. You now should be putting set-producing functions into the FROM clause of a query or subquery. A lateral join is often required.
David J.
On Jan 6, 2026, at 11:04 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:It was an intentional change. You now should be putting set-producing functions into the FROM clause of a query or subquery. A lateral join is often required.
It is very surprising to me that Postgres would intentionally break previously-working SELECT statements and that the CTE version is inconsistent between "AS MATERIALIZED". The WITH MATERIALIZED docs don't mention anything about certain query shapes being incompatible.
eric
On Tue, Jan 6, 2026 at 9:16 AM Eric Ridge <eebbrr@gmail.com> wrote:
On Jan 6, 2026, at 11:04 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:It was an intentional change. You now should be putting set-producing functions into the FROM clause of a query or subquery. A lateral join is often required.I'm willing to accept that, but I can't find this called out in the release notes between 15 and 18.1. I could have overlooked it, of course.It is very surprising to me that Postgres would intentionally break previously-working SELECT statements and that the CTE version is inconsistent between "AS MATERIALIZED". The WITH MATERIALIZED docs don't mention anything about certain query shapes being incompatible.
While I haven't dug into the actual specifics of this report in detail, the change in question happened back in v10.
The failure to emit an error when it probably should have is likely a bug in older versions since fixed.
Or, it may be an actual bug. But we did tighten things up here and encourage/require a non-problematic query form (place set-returning constructs in the from clause) in some situations now that we did not before. So I'm willing to presume the error being reported here is valid.
That the behavior depends on the chosen plan and plans differ when you do and do not materialize a CTE is likewise not surprising. Though as a practical matter it would be nice if the test was more resilient in face of different syntactic forms; so bug or not, maybe something could be done to make the failure more consistent.
David J.
On Jan 6, 2026, at 12:00 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:While I haven't dug into the actual specifics of this report in detail, the change in question happened back in v10.
The failure to emit an error when it probably should have is likely a bug in older versions since fixed.
Fair enough.
That the behavior depends on the chosen plan and plans differ when you do and do not materialize a CTE is likewise not surprising.
I guess I wouldn't expect Postgres to generate a plan that it then can't execute. That's what's surprising to me.
But it's fine. In all my years of using Postgres this is the first time I've run into a query that no longer executes, so I wanted to bring it to y'alls attention.
Thanks again!
eric
> On Jan 6, 2026, at 1:10 PM, Eric Ridge <eebbrr@gmail.com> wrote:
>
>> On Jan 6, 2026, at 12:00 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
>
>
>> That the behavior depends on the chosen plan and plans differ when you do and do not materialize a CTE is likewise
notsurprising.
>
>
> I guess I wouldn't expect Postgres to generate a plan that it then can't execute. That's what's surprising to me.
Sorry, one more thing. The reduced case fails on v18:
# explain SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as animal FROM generate_series(1, 10) GROUP BY 1) x
WHEREanimal ILIKE 'c%';
ERROR: set-valued function called in context that cannot accept a set
LINE 1: explain SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog...
^
But if you remove the outer WHERE clause it works:
# SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as animal FROM generate_series(1, 10) GROUP BY 1) x;
animal
--------
CAT
DOG
(2 rows)
I can't get an EXPLAIN for the former, but for the latter:
QUERY PLAN
---------------------------------------------------------------------------------------
HashAggregate (cost=0.63..0.64 rows=1 width=32)
Group Key: upper((unnest('{cat,dog}'::text[])))
-> Result (cost=0.00..0.58 rows=20 width=32)
-> ProjectSet (cost=0.00..0.28 rows=20 width=32)
-> Function Scan on generate_series (cost=0.00..0.10 rows=10 width=0)
(5 rows)
That's all. I promise!
eric
Eric Ridge <eebbrr@gmail.com> writes:
> Here's an even more reduced test case. No tables or data:
> # SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as animal FROM generate_series(1, 10) GROUP BY 1) x WHERE
animalilike 'c%';
> pg15 returns:
> animal
> --------
> CAT
> (1 row)
> and pg18 says:
> # SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as animal FROM generate_series(1, 10) GROUP BY 1) x WHERE
animalilike 'c%';
> ERROR: set-valued function called in context that cannot accept a set
> LINE 1: SELECT * FROM (SELECT upper(unnest(ARRAY['cat', 'dog'])) as ...
> ^
I agree that this is a bug. "git bisect" says it broke at
247dea89f7616fdf06b7272b74abafc29e8e5860 is the first bad commit
commit 247dea89f7616fdf06b7272b74abafc29e8e5860 (HEAD)
Author: Richard Guo <rguo@postgresql.org>
Date: Tue Sep 10 12:35:34 2024 +0900
Introduce an RTE for the grouping step
I've not probed further than that, but my guess is that now we check
for set-returning tlist items while the tlist still has grouping Vars,
thus missing the fact that there's a SRF represented by one of those
Vars. This prompts us to flatten a subquery we shouldn't have
flattened (because that ends by introducing a SRF into the outer
WHERE).
regards, tom lane
> On Jan 6, 2026, at 1:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Eric Ridge <eebbrr@gmail.com> writes: >> Here's an even more reduced test case. No tables or data: > > I agree that this is a bug. "git bisect" says it broke at Thanks for the confirmation and your investigation. Also, thanks for all you (all of you!) do for Postgres. Many in the world, myself included, wouldn't be where we are inlife without your work. eric
On Tue, 6 Jan 2026 14:25:07 -0500
Eric Ridge <eebbrr@gmail.com> wrote:
> Many in the world, myself included, wouldn't be where we are in life without your work.
>
I concur heartily.
Best wishes to all.
--
Bien à vous, Vincent Veyron
https://compta.libremen.com
Logiciel libre de comptabilité générale et analytique en partie double