From: Kamil Monicz <kamil@monicz.dev>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18
Date: Mon, 10 Nov 2025 04:21:20 +0100
Message-ID: <b23b48b7-0222-4b5e-a986-1d79920a7298@app.fastmail.com> (raw)
In-Reply-To: <1650412.1762703636@sss.pgh.pa.us>
References: <19106-9dd3668a0734cd72@postgresql.org>
<1650412.1762703636@sss.pgh.pa.us>
On Sun, Nov 9, 2025, at 16:53, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > After upgrading from Postgres 17 to 18, one of my queries started raising an
> > error:
> > "unexpected outer reference in CTE query"
>
> I agree that sounds like a bug ...
>
> > The problematic query is:
> > https://github.com/openstreetmap-ng/openstreetmap-ng/blob/eb805d8766fb4b359b96eb6b50acc8c2a835a165/a...
>
> ... but I am not going to spend time trying to reproduce it given
> this amount of detail. There's too much missing context, like what
> data you were running the query on. I could spend all day, not
> see the failure, and be left no wiser than before as to whether
> it's already fixed or I just didn't duplicate your context closely
> enough. Please see if you can reduce the problem case to a
> self-contained SQL script.
>
> regards, tom lane
>
Hello Tom,
It's my first time here (and realistically on a proper mailing list), so please excuse me. Here's the small, self-contained reproduction:
```
EXPLAIN
SELECT *
FROM (
SELECT ARRAY[1, 2] AS arr
) r
CROSS JOIN LATERAL (
WITH a AS (
SELECT
CASE
WHEN id = 1 THEN ST_GeomFromText('LINESTRING(0 0,1 0,1 1)')
ELSE ST_GeomFromText('POINT(0 0)')
END AS geom
FROM unnest(r.arr) AS id
),
b AS (
SELECT ST_Polygonize(
(SELECT ST_UnaryUnion(ST_Collect(geom)) FROM a)
) AS st_polygonize
)
SELECT
(SELECT st_polygonize FROM b),
(SELECT st_polygonize FROM b)
) s;
```
```
ERROR: unexpected outer reference in CTE query
SQL state: XX000
```
It depends on PostGIS being installed and loaded. In my case, it's version 3.6.0. I tried to make it work without it, but I couldn't figure it out.
-Kamil Monicz
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: kamil@monicz.dev, tgl@sss.pgh.pa.us, 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>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox