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
