Message-ID: <36c1f631-f65f-4bb8-90af-9fafdb683547@postgresfriends.org>
Date: Tue, 11 Nov 2025 16:16:06 +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>, Kamil Monicz <kamil@monicz.dev>
Cc: 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>
Content-Language: en-US
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: <1980046.1762808758@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/36c1f631-f65f-4bb8-90af-9fafdb683547%40postgresfriends.org>
Precedence: bulk


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