Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1vIqEV-00GVpY-2v
	for pgsql-bugs@arkaria.postgresql.org;
	Tue, 11 Nov 2025 15:24:34 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.96)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1vIqET-006fn2-1d
	for pgsql-bugs@arkaria.postgresql.org;
	Tue, 11 Nov 2025 15:24:33 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1vIqET-006fmt-0o
	for pgsql-bugs@lists.postgresql.org;
	Tue, 11 Nov 2025 15:24:33 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by makus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1vIqER-006ZJn-1r
	for pgsql-bugs@lists.postgresql.org;
	Tue, 11 Nov 2025 15:24:32 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 5ABFOTmv2184533;
	Tue, 11 Nov 2025 10:24:29 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vik Fearing <vik@postgresfriends.org>
cc: Kamil Monicz <kamil@monicz.dev>, pgsql-bugs@lists.postgresql.org,
        Peter Eisentraut <peter@eisentraut.org>
Subject: Re: BUG #19106: Potential regression with CTE materialization
 planning in Postgres 18
In-reply-to: <36c1f631-f65f-4bb8-90af-9fafdb683547@postgresfriends.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>
Comments: In-reply-to Vik Fearing <vik@postgresfriends.org>
	message dated "Tue, 11 Nov 2025 16:16:06 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2184531.1762874669.1@sss.pgh.pa.us>
Date: Tue, 11 Nov 2025 10:24:29 -0500
Message-ID: <2184532.1762874669@sss.pgh.pa.us>
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/2184532.1762874669%40sss.pgh.pa.us>
Precedence: bulk

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