Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18 - Mailing list pgsql-bugs

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

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