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

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

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