Re: pg18 bug? SELECT query doesn't work

> 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 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.


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 WHERE animal 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