Message-ID: <f2171b06-8970-4946-9092-72b4668c58e4@postgresfriends.org>
Date: Tue, 11 Nov 2025 17:53:45 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #19106: Potential regression with CTE materialization
 planning in Postgres 18
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kamil Monicz <kamil@monicz.dev>, pgsql-bugs@lists.postgresql.org,
 Peter Eisentraut <peter@eisentraut.org>
References: <19106-9dd3668a0734cd72@postgresql.org>
 <1650412.1762703636@sss.pgh.pa.us>
 <b23b48b7-0222-4b5e-a986-1d79920a7298@app.fastmail.com>
 <1886977.1762794560@sss.pgh.pa.us> <1980046.1762808758@sss.pgh.pa.us>
 <36c1f631-f65f-4bb8-90af-9fafdb683547@postgresfriends.org>
 <2184532.1762874669@sss.pgh.pa.us>
Content-Language: en-US
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: <2184532.1762874669@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/f2171b06-8970-4946-9092-72b4668c58e4%40postgresfriends.org>
Precedence: bulk


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