From: Vik Fearing <vik@postgresfriends.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: 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 16:16:06 +0100
Message-ID: <36c1f631-f65f-4bb8-90af-9fafdb683547@postgresfriends.org> (raw)
In-Reply-To: <1980046.1762808758@sss.pgh.pa.us>
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>
On 10/11/2025 22:05, Tom Lane wrote:
> [ cc'ing Peter and Vik for possible input on SQL-standard question ]
Thanks!
> WITH a AS (
> SELECT id FROM (VALUES (1), (2)) AS v(id)
> ),
> b AS (
> SELECT max((SELECT sum(id) FROM a)) AS agg
> )
> SELECT agg FROM b;
snip
> 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 curious though whether
> Peter or Vik know if the committee ever considered relaxing that
> restriction, and if so whether they stopped to think about this
> particular point.
I am not seeing that restriction in the standard. For this test case,
we have MAX which has the lineage:
<aggregate function>
<general set function>
<set function type>
<computational operation>
MAX
Its argument, (SELECT SUM(id) FROM a), has this lineage:
<value expression>
<common value expression>
<numeric value expression>
<term>
<factor>
<numeric primary>
<value expression primary>
<non-parenthesized value expression primary>
<scalar subquery>
<subquery>
Since there are no outer column references, the subquery should be
independent. And if we inline it:
WITH
b (agg) AS (
SELECT MAX((
SELECT SUM(id)
FROM (VALUES (1), (2)) AS v (id)
))
-- FROM nothing
)
TABLE b
then the query works as expected. MATERIALIZEDing either or both CTEs
has no effect, which I find strange.
--
Vik Fearing
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: vik@postgresfriends.org, tgl@sss.pgh.pa.us, kamil@monicz.dev, 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>
* 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