Re: BUG #19923: SEARCH/CYCLE with DISTINCT in the recursive term: rows depend on input order

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Eisentraut <peter(at)eisentraut(dot)org>
Cc: tharakan(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #19923: SEARCH/CYCLE with DISTINCT in the recursive term: rows depend on input order
Date: 2026-11-20 19:16:50
Message-ID: 1597504.1795202210@sss.pgh.pa.us
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I wrote:
> Oh, I have it: the right fix here is to leave the right subquery's
> tlist entirely alone, and instead compute the extra columns in its
> parent query.

Ah, that doesn't work: there's no way to get the sequence column
out of the subquery.

What does work is to make the DISTINCT structure valid again by
converting it to DISTINCT ON, as in the trivial patch attached.
Despite my worries an hour ago, it doesn't seem like this will
cause a problem for any remotely-sane behavior of the mark column.

Also, if I'm not mistaken, it reverts the behavior to what we did
pre-v18. I've not dug to see exactly where the planner goes wrong
now with the inconsistent hasDistinctOn setting, but it doesn't
matter: rewriteSearchAndCycle is clearly at fault for generating
an inconsistent query.

regards, tom lane

Attachment Content-Type Size
bug-19923-draft-fix.patch text/x-diff 844 bytes

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Christophe Pettus 2026-11-20 19:35:39 Re: BUG #19927: PostgreSQL 16: SQL dump adds experimental/incompatible options like \restrict
Previous Message Maciej Jaros 2026-11-20 18:41:32 Re: BUG #19927: PostgreSQL 16: SQL dump adds experimental/incompatible options like \restrict