public inbox for [email protected]
help / color / mirror / Atom feedRe: pg18 bug? SELECT query doesn't work
4+ messages / 2 participants
[nested] [flat]
* Re: pg18 bug? SELECT query doesn't work
@ 2026-01-06 16:16 Eric Ridge <[email protected]>
2026-01-06 17:00 ` Re: pg18 bug? SELECT query doesn't work David G. Johnston <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Eric Ridge @ 2026-01-06 16:16 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-general
> On Jan 6, 2026, at 11:04 AM, David G. Johnston <[email protected]> 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.
eric
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg18 bug? SELECT query doesn't work
2026-01-06 16:16 Re: pg18 bug? SELECT query doesn't work Eric Ridge <[email protected]>
@ 2026-01-06 17:00 ` David G. Johnston <[email protected]>
2026-01-06 18:10 ` Re: pg18 bug? SELECT query doesn't work Eric Ridge <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2026-01-06 17:00 UTC (permalink / raw)
To: Eric Ridge <[email protected]>; +Cc: pgsql-general
On Tue, Jan 6, 2026 at 9:16 AM Eric Ridge <[email protected]> wrote:
> On Jan 6, 2026, at 11:04 AM, David G. Johnston <[email protected]>
> 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.
https://www.postgresql.org/docs/10/release-10.html
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg18 bug? SELECT query doesn't work
2026-01-06 16:16 Re: pg18 bug? SELECT query doesn't work Eric Ridge <[email protected]>
2026-01-06 17:00 ` Re: pg18 bug? SELECT query doesn't work David G. Johnston <[email protected]>
@ 2026-01-06 18:10 ` Eric Ridge <[email protected]>
2026-01-06 18:28 ` Re: pg18 bug? SELECT query doesn't work Eric Ridge <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Eric Ridge @ 2026-01-06 18:10 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-general
> On Jan 6, 2026, at 12:00 PM, David G. Johnston <[email protected]> wrote:
>
> While I haven't dug into the actual specifics of this report in detail, the change in question happened back in v10.
>
> https://www.postgresql.org/docs/10/release-10.html
Thanks. I wouldn't have thought to look back that far since the query worked on v15. Interesting.
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg18 bug? SELECT query doesn't work
2026-01-06 16:16 Re: pg18 bug? SELECT query doesn't work Eric Ridge <[email protected]>
2026-01-06 17:00 ` Re: pg18 bug? SELECT query doesn't work David G. Johnston <[email protected]>
2026-01-06 18:10 ` Re: pg18 bug? SELECT query doesn't work Eric Ridge <[email protected]>
@ 2026-01-06 18:28 ` Eric Ridge <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Eric Ridge @ 2026-01-06 18:28 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-general
> On Jan 6, 2026, at 1:10 PM, Eric Ridge <[email protected]> wrote:
>
>> On Jan 6, 2026, at 12:00 PM, David G. Johnston <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-01-06 18:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-06 16:16 Re: pg18 bug? SELECT query doesn't work Eric Ridge <[email protected]>
2026-01-06 17:00 ` David G. Johnston <[email protected]>
2026-01-06 18:10 ` Eric Ridge <[email protected]>
2026-01-06 18:28 ` Eric Ridge <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox