From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Kamil Monicz" <kamil@monicz.dev>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19106: Potential regression with CTE materialization
 planning in Postgres 18
In-reply-to: <b23b48b7-0222-4b5e-a986-1d79920a7298@app.fastmail.com>
References: <19106-9dd3668a0734cd72@postgresql.org>
 <1650412.1762703636@sss.pgh.pa.us>
 <b23b48b7-0222-4b5e-a986-1d79920a7298@app.fastmail.com>
Comments: In-reply-to "Kamil Monicz" <kamil@monicz.dev>
	message dated "Mon, 10 Nov 2025 04:21:20 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1886976.1762794560.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 10 Nov 2025 12:09:20 -0500
Message-ID: <1886977.1762794560@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1886977.1762794560%40sss.pgh.pa.us>
Precedence: bulk

"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