Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18

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