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

Vik Fearing <vik(at)postgresfriends(dot)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