pg18 bug? SELECT query doesn't work - Mailing list pgsql-general

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