Horton

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Kamil Monicz" <kamil@monicz.dev>
Cc: pgsql-bugs@lists.postgresql.org
Date: 2025-11-10T17:09:20Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Don't allow CTEs to determine semantic levels of aggregates.

  2. Calculate agglevelsup correctly when Aggref contains a CTE.

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