Re: BUG #19922: CYCLE/SEARCH with a window in the recursive term: could not find replacement targetlist entry

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: tharakan(at)gmail(dot)com
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org, Peter Eisentraut <peter(at)eisentraut(dot)org>
Subject: Re: BUG #19922: CYCLE/SEARCH with a window in the recursive term: could not find replacement targetlist entry
Date: 2026-11-19 21:47:12
Message-ID: 1250317.1795124832@sss.pgh.pa.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> A recursive query with a CYCLE or SEARCH clause fails when its
> recursive term has a resjunk column, such as a window function's
> ORDER BY column.

Yeah; you can make it fail with a resjunk GROUP BY term too, such as

WITH RECURSIVE t(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM t WHERE x < 3 GROUP BY x
) SEARCH DEPTH FIRST BY x SET o
SELECT * FROM t;
ERROR: attribute 2 of type record has wrong type
DETAIL: Table has type integer, but query expects record[].

I think the problem is that rewriteSearchAndCycle() is appending extra
items to the right subquery's tlist without regard for the possibility
that there are resjunk items in it. This goes against our general
expectation that resjunk entries come last, and in particular it
breaks the correspondence between the subquery's column name lists and
their corresponding tlist entries. The names that are meant to refer
to the added extra items instead refer to resjunk entries, causing
all sorts of jolliness later.

There may be a similar problem with the left subquery too, not sure.

I think the way to fix this is to inject the added columns before any
resjunk columns (renumbering the latter). I've got a hard stop coming
up shortly, but can look at this tomorrow if no one beats me to it.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Melanie Plageman 2026-11-19 22:26:04 Re: PostgreSQL 18.6/17.11: standby PANIC on restart after VM truncation
Previous Message Daniel Gustafsson 2026-11-19 20:49:44 Re: BUG #19908: pg_upgrade --check crashes instance if running in a different PID Namespace then the old cluster