From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Guo <guofenglinux@gmail.com>
cc: Vik Fearing <vik@postgresfriends.org>, Kamil Monicz <kamil@monicz.dev>,
        pgsql-bugs@lists.postgresql.org,
        Peter Eisentraut <peter@eisentraut.org>
Subject: Re: BUG #19106: Potential regression with CTE materialization
 planning in Postgres 18
In-reply-to: 
 <CAMbWs48G6Fk+5YOhz-4u0+R1+sQZhC_bVTZBY7cqbG6wFWcKDA@mail.gmail.com>
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>
 <2184532.1762874669@sss.pgh.pa.us>
 <f2171b06-8970-4946-9092-72b4668c58e4@postgresfriends.org>
 <2226630.1762892879@sss.pgh.pa.us>
 <CAMbWs48G6Fk+5YOhz-4u0+R1+sQZhC_bVTZBY7cqbG6wFWcKDA@mail.gmail.com>
Comments: In-reply-to Richard Guo <guofenglinux@gmail.com>
	message dated "Wed, 12 Nov 2025 16:04:05 +0900"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2394347.1762975973.1@sss.pgh.pa.us>
Date: Wed, 12 Nov 2025 14:32:53 -0500
Message-ID: <2394348.1762975973@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2394348.1762975973%40sss.pgh.pa.us>
Precedence: bulk

Richard Guo <guofenglinux@gmail.com> writes:
> I played with this patch, but I couldn't quite wrap my head around the
> expected behavior of using subqueries as arguments to aggregate
> functions.  The outputs of the following queries are confusing to me.
> ...

> Query 2:
> select (select sum((select a from t t1 where a = t2.a or true limit
> 1))) from t t2;
>  sum
> -----
>    2
> (1 row)

> I don't quite understand the output of Query 2.  The subquery is now
> correlated with the outer table t2, but I believe it's still in the
> same form as Query 1, so I would expect it to also produce one output
> row per table row.

I believe the critical point about Q2 is that the presence of the
reference to t2.a causes the sum() aggregate to be assigned to the
outer query level.  Now, that outer query is an aggregation query
so it will produce only one row, aggregated over both rows of t2
(for each of which, the bottom sub-select produces the value "1").

I've never totally understood the rationale for the SQL standard
to assign aggregates to outer query levels, but it's definitely
their fault not ours.

> Query 3:
> with t as (select a from (values (1), (2)) as v(a))
> select (select sum((select a from t t1 where a = t2.a or true limit
> 1))) from t t2;
>  sum
> -----
>    1
>    1
> (2 rows)

Actually, as of HEAD we produce:

regression=# with t as (select a from (values (1), (2)) as v(a))
select (select sum((select a from t t1 where a = t2.a or true limit
1))) from t t2;
 sum 
-----
   2
(1 row)

and it's the same if you remove the t2.a reference:

regression=# with t as (select a from (values (1), (2)) as v(a))
select (select sum((select a from t t1 where  true limit
1))) from t t2;
 sum 
-----
   2
(1 row)

In both cases we assign the aggregate to the outer-level SELECT.
I assume you were testing with my patch, which forces the sum()
to be level zero, that is belonging to the intermediate sub-select.

> Query 3 replaces the physical table with a CTE that produces the same
> logical table content, so I would expect the query's output to remain
> unchanged.  So the differing outputs of Query 2 and Query 3 are also
> confusing to me.

The sticky point here is that a CTE reference isn't quite as absolute
as a physical-table reference: the CTE name only has meaning within
a portion of the query.  So the problem that b0cc0a71e tried to solve
is "what do we do if the SQL-standard rules about semantic level of
an aggregate would result in putting the aggregate outside of the
scope of a CTE it references?"

I suppose another answer would be to throw up our hands and give
an error if that happens, rather than trying to fix the level
assigned to the aggregate.

			regards, tom lane