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

From: Nitin Motiani <nitinmotiani(at)google(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: tharakan(at)gmail(dot)com, 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-20 10:52:30
Message-ID: CAH5HC97oEGSV4g5z=qxM=UpiJUtGJRNRvRv8NH7i77o0LAyGiw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

I looked into this following Tom's suggestion.

On Fri, Nov 20, 2026 at 3:17 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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.
>

Thanks for the idea. I checked this and it indeed seemed to be the case.
I'm attaching a patch with the fix in this area. It detaches any resjunk
entries from the recursive term's tlist, adds the SEARCH/CYCLE columns,
then re-attaches the resjunk entries with new resnos, the same way
rewriteTargetListIU() handles junk entries.

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

I checked this using LLM and the left side seems fine.
rewriteSearchAndCycle() wraps the non-recursive term in a new subquery
without touching its tlist, and the outer query only references columns
1..n of it. Here is one example I tested that was working fine on master.

WITH RECURSIVE t(x) AS (
SELECT max(n) OVER (ORDER BY s) FROM (VALUES (1, 'a')) v(n, s)
UNION ALL
SELECT x+1 FROM t WHERE x < 3
) SEARCH DEPTH FIRST BY x SET o CYCLE x SET m USING p
SELECT * FROM t;

x | o | m | p
---+---------------+---+---------------
1 | {(1)} | f | {(1)}
2 | {(1),(2)} | f | {(1),(2)}
3 | {(1),(2),(3)} | f | {(1),(2),(3)}

Thanks & Regards,
Nitin Motiani
Google

Attachment Content-Type Size
v1-0001-Fix-SEARCH-CYCLE-when-the-recursive-term-has-resj.patch application/x-patch 6.5 KB

In response to

Browse pgsql-bugs by date

  From Date Subject
Previous Message Gaurav Singh 2026-11-20 10:49:38 Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters