pgsql: Fix SEARCH/CYCLE when the recursive term has resjunk columns.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-committers(at)lists(dot)postgresql(dot)org
Subject: pgsql: Fix SEARCH/CYCLE when the recursive term has resjunk columns.
Date: 2026-11-20 16:52:33
Message-ID: E1xURqi-00000000XUY-0MeQ@gemulon.postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Fix SEARCH/CYCLE when the recursive term has resjunk columns.

rewriteSearchAndCycle() appended the extra SEARCH/CYCLE columns to
the end of the recursive term's targetlist. If that targetlist had
resjunk entries, for example a window function's ORDER BY column or a
GROUP BY column that is not selected, the new columns landed after
them. That breaks the expectation that resjunk entries come last, and
the column names added to the wrapping subquery then pointed at the
wrong entries. Depending on the query, this caused errors such as
"could not find replacement targetlist entry" or "attribute N of type
record has wrong type", an assertion failure in assert-enabled builds,
or silently wrong results.

Fix by detaching any resjunk entries before adding the new columns and
re-attaching them afterwards with renumbered resnos. Sort/group
clauses reference targetlist entries via ressortgroupref, not resno,
so renumbering them is safe.

The non-recursive term is not affected, since it is wrapped in a new
subquery without modifying its targetlist.

Bug: #19922
Reported-by: Robins Tharakan <tharakan(at)gmail(dot)com>
Diagnosed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Author: Nitin Motiani <nitinmotiani(at)google(dot)com>
Reviewed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Discussion: https://postgr.es/m/19922-a0d338245d361a4c@postgresql.org
Backpatch-through: 15

Branch
------
REL_18_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/fba1717d5a6537c54070f5f8eafba5ea6e3a1cbd

Modified Files
--------------
src/backend/rewrite/rewriteSearchCycle.c | 42 ++++++++++++++++++++++++++++++--
src/test/regress/expected/with.out | 40 ++++++++++++++++++++++++++++++
src/test/regress/sql/with.sql | 23 +++++++++++++++++
3 files changed, 103 insertions(+), 2 deletions(-)

Browse pgsql-committers by date

  From Date Subject
Next Message Masahiko Sawada 2026-11-20 18:22:00 pgsql: Use an index scan in RemoveSubscriptionRel() when possible.
Previous Message Heikki Linnakangas 2026-11-20 15:10:32 pgsql: Always prune dead members in MultiXactIdExpand, to fix assertion