pgsql: Fix SEARCH/CYCLE when the recursive term is SELECT DISTINCT.

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 is SELECT DISTINCT.
Date: 2026-11-20 19:53:16
Message-ID: E1xUUfb-00000000YNh-1sWt@gemulon.postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Fix SEARCH/CYCLE when the recursive term is SELECT DISTINCT.

rewriteSearchAndCycle() appended the extra SEARCH/CYCLE columns to
the end of the recursive term's targetlist, but did not consider
the interaction of that with SELECT DISTINCT. Since we don't (and
shouldn't) mark them resjunk, they should logically be included in
the unique-ification action of SELECT DISTINCT, ie get added to the
distinctClause. We didn't do that, which confuses recent planner
versions into assert'ing or building an incorrect plan.

The simplest fix is to mark the query as hasDistinctOn, so that
it looks like it was originally written with a DISTINCT ON list
containing just the original columns. Aside from being a trivial
change, this restores the behavior as it was pre-v18.

In principle, it should not matter whether we add the extra columns
to the distinctClause or not, as they should be immutable functions
of the original columns and thus couldn't add extra rows to the
de-duplicated output. Per the discussion thread, it's possible to
construct artificial cases where the <cycle mark column> is only
a stable function, and maybe even a volatile function if weirdly-
behaving casts are involved. But even then it seems better if the
implementation arbitrarily selects one mark value rather than
duplicating original rows.

Although no bad behavior currently manifests pre-v18, that seems
like luck rather than something to count on: we might back-patch some
fix that requires consistency of distinctClause and hasDistinctOn.
So back-patch to all supported branches.

Bug: #19923
Reported-by: Robins Tharakan <tharakan(at)gmail(dot)com>
Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Discussion: https://postgr.es/m/19923-24cef1e958fa2370@postgresql.org
Backpatch-through: 15

Branch
------
REL_19_STABLE

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

Modified Files
--------------
src/backend/rewrite/rewriteSearchCycle.c | 9 +++++++++
src/test/regress/expected/with.out | 13 +++++++++++++
src/test/regress/sql/with.sql | 8 ++++++++
3 files changed, 30 insertions(+)

Browse pgsql-committers by date

  From Date Subject
Next Message Melanie Plageman 2026-11-20 20:43:38 pgsql: Reduce WAL generated when copying relation storage
Previous Message Masahiko Sawada 2026-11-20 18:22:00 pgsql: Use an index scan in RemoveSubscriptionRel() when possible.