Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.500.181.1.5\))
Subject: Re: pg18 bug? SELECT query doesn't work
From: Eric Ridge <eebbrr@gmail.com>
In-Reply-To: <9A42D802-5994-4DAF-86FB-AB5954840216@gmail.com>
Date: Tue, 6 Jan 2026 13:28:27 -0500
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <07A953A0-D33C-481B-A411-93D7F89C290A@gmail.com>
References: <7900964C-F99E-481E-BEE5-4338774CEB9F@gmail.com>
 <CAKFQuwbQj6kLxiz=Ft=ykM2s+H0ytCL7C+Cg1G=7W8ifhFi0Ow@mail.gmail.com>
 <11E075EF-20F5-42CD-A014-16172FF05CCF@gmail.com>
 <CAKFQuwZS4dO2xJeL7yT-D20J-WR15rCPE9iXta8F9WuFHvxXTA@mail.gmail.com>
 <9A42D802-5994-4DAF-86FB-AB5954840216@gmail.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/07A953A0-D33C-481B-A411-93D7F89C290A%40gmail.com>
Precedence: bulk

> On Jan 6, 2026, at 1:10=E2=80=AFPM, Eric Ridge <eebbrr@gmail.com> =
wrote:
>=20
>> On Jan 6, 2026, at 12:00=E2=80=AFPM, David G. Johnston =
<david.g.johnston@gmail.com> wrote:
>=20
>=20
>=20
>> That the behavior depends on the chosen plan and plans differ when =
you do and do not materialize a CTE is likewise not surprising.=20
>=20
>=20
> 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 =20
--------
 CAT
 DOG
(2 rows)

I can't get an EXPLAIN for the former, but for the latter:

                                      QUERY PLAN                         =
              =20
=
--------------------------------------------------------------------------=
-------------
 HashAggregate  (cost=3D0.63..0.64 rows=3D1 width=3D32)
   Group Key: upper((unnest('{cat,dog}'::text[])))
   ->  Result  (cost=3D0.00..0.58 rows=3D20 width=3D32)
         ->  ProjectSet  (cost=3D0.00..0.28 rows=3D20 width=3D32)
               ->  Function Scan on generate_series  (cost=3D0.00..0.10 =
rows=3D10 width=3D0)
(5 rows)

That's all.  I promise!

eric=