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

[ cc'ing Peter and Vik for possible input on SQL-standard question ]

I wrote:
> This worked up until commit b0cc0a71e, and since then it hits an
> assertion failure in check_agglevels_and_constraints(); or if you
> don't have asserts enabled then the planner gets confused, because
> the max() aggregate function is given the wrong agglevelsup.
> I need to think through what is the correct behavior for cross-CTE
> references like these.  Sadly, this is too late for this week's
> releases ...

Here is a draft fix for this.  What it basically decides is that
commit b0cc0a71e was in error to suppose that an outer CTE reference
should work like an outer Var reference.  In this even-more-simplified
test case:

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;

it's pretty obvious that the reference to "a" should not cause us to
act as though the max() aggregate belongs to the outer "WITH ...
SELECT agg FROM b" query level.  However, if it doesn't belong there,
where does it belong?

The draft patch takes the approach of forcing agglevelsup to zero
anytime we find a CTE reference within an aggregate's arguments
(unless it is to a CTE defined inside those arguments).  That
means such an aggregate always has its syntactic level and cannot
be treated as an outer aggregate even if it otherwise references
(only) outer Vars.

I don't have a huge amount of confidence in this being the final
answer; it seems like it might still be too simplistic.  But I'm
not sure what to do instead.  It's at least less likely to break
cases that worked before b0cc0a71e, since "it's level zero" is
pretty much the default answer anyway.

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.

			regards, tom lane