> 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
