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

Started by Tom Lane2 days ago1 messagescommitters
Jump to latest
#1Tom Lane
tgl@sss.pgh.pa.us

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@gmail.com>
Diagnosed-by: Tom Lane <tgl@sss.pgh.pa.us>
Author: Nitin Motiani <nitinmotiani@google.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: /messages/by-id/19922-a0d338245d361a4c@postgresql.org
Backpatch-through: 15

Branch
------
REL_16_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/0e7dabc852d40d33d1341a4c227b1c26460f1a81

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(-)