| 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 |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Gaurav Singh | 2026-11-20 10:49:38 | Re: postgres_fdw returns wrong rows for age(timestamptz, timestamptz) filters |