From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vik Fearing <vik@postgresfriends.org>
Cc: Kamil Monicz <kamil@monicz.dev>
Cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>
Subject: Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18
Date: Tue, 11 Nov 2025 10:24:29 -0500
Message-ID: <2184532.1762874669@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, vik@postgresfriends.org, kamil@monicz.dev, peter@eisentraut.org
Subject: Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18
In-Reply-To: <2184532.1762874669@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox