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

On Thu, Nov 13, 2025 at 4:32 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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?"

So, IIUC, the confusion arises in cases where an aggregation is to be
assigned to the outer side of its syntactic level.  With the current
patch, if the aggregation does not reference any CTEs, it would be
evaluated at the outer query level.  If the aggregation references any
CTEs, it'd be evaluated at its syntactic query level.

However, I still find this behavior somewhat confusing.  For example,
one might expect that an inlined CTE should be semantically equivalent
to a subquery, yet the following two queries can produce different
results.

create table t (a int);
insert into t values (1), (2);

with ss as not materialized (select * from t)
select (select sum((select a from ss where a = t.a limit 1))) from t;
 sum
-----
   1
   2
(2 rows)

select (select sum((select a from (select * from t) ss where a = t.a
limit 1))) from t;
 sum
-----
   3
(1 row)

I don't have much experience reading the SQL spec, but from the
discussions, it seems that the spec does not provide guidance on this
case.  So the current behavior may be acceptable.  I think it might be
helpful to explicitly document this behavior somewhere.

- Richard