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

"Kamil Monicz" <kamil(at)monicz(dot)dev> writes:
> It's my first time here (and realistically on a proper mailing list), so please excuse me. Here's the small, self-contained reproduction:

Thanks. After a bit of fooling around I was able to convert this
to something without any PostGIS dependency:

EXPLAIN
SELECT *
FROM (
SELECT ARRAY[1, 2] AS arr
) r
CROSS JOIN LATERAL (
WITH a AS (
SELECT id FROM unnest(r.arr) AS id
),
b AS (
SELECT max((SELECT sum(id) FROM a)) AS agg
)
SELECT
(SELECT agg FROM b)
) s;

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 ...

regards, tom lane