Horton

Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18

Tom Lane <tgl@sss.pgh.pa.us>

View thread
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vik Fearing <vik@postgresfriends.org>
Cc: Kamil Monicz <kamil@monicz.dev>, pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>
Date: 2025-11-11T15:24:29Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Don't allow CTEs to determine semantic levels of aggregates.

  2. Calculate agglevelsup correctly when Aggref contains a CTE.

Vik Fearing <vik@postgresfriends.org> writes:
> On 10/11/2025 22:05, Tom Lane wrote:
>> I looked at the SQL standard for possible guidance and found none:
>> they disallow subqueries altogether within aggregate arguments,
>> so they need not consider such cases.

> I am not seeing that restriction in the standard.

Maybe I'm misunderstanding what I read, but in SQL:2021
6.9 <set function specification> SR1 says

    If <aggregate function> specifies a <general set function>, then
    the <value expression> simply contained in the <general set
    function> shall not contain a <set function specification>
    or a <query expression>.

The predecessor text in SQL99 says

    4) The <value expression> simply contained in <set function
       specification> shall not contain a <set function specification>
       or a <subquery>.

I don't think replacing <subquery> with <query expression> moved the
goalposts at all, but maybe I'm missing something.

> ... MATERIALIZEDing either or both CTEs 
> has no effect, which I find strange.

The fundamental problem is that the parser is mis-assigning
agglevelsup; given that, the planner is very likely to get
confused no matter what other details there are.

			regards, tom lane