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