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

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