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

On 11/11/2025 16:24, Tom Lane wrote:
> 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.


I don't think you are.  I was missing that you can't get to <aggregate 
function> without going through <set function specification> (or a 
window) so I did not see that rule.


I had a rummage through the archives but couldn't easily find the paper 
introducing aggregates so I can't see what the justification for that 
rule was. This language was not in 1989 but is in 1992. It may just be a 
case of "this is what we've implemented so this is what we are specifying."


>> ... 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.


Thank you for the explanation.

-- 

Vik Fearing