| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | tharakan(at)gmail(dot)com |
| Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org, Peter Eisentraut <peter(at)eisentraut(dot)org> |
| Subject: | Re: BUG #19923: SEARCH/CYCLE with DISTINCT in the recursive term: rows depend on input order |
| Date: | 2026-11-20 17:42:26 |
| Message-ID: | 1535856.1795196546@sss.pgh.pa.us |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> A recursive query with a SEARCH or CYCLE clause returns rows that
> depend on the order of its input when its recursive term uses DISTINCT
> or GROUP BY.
Hm, I see no GROUP BY in your examples?
The proximate cause of the problem seems to be that we add extra
columns to the righthand subquery without any consideration for
the fact that if it has a DISTINCT clause then that clause suggests
that we need to include the new columns in the DISTINCT-ing.
But we don't actually do so. Pre-v18 planner versions seem to
tolerate the inconsistency, at least to the point of not crashing,
while currently we don't. But I think this is definitely the
rewriter's error not the planner's.
Before we invest a lot of effort in fixing that, I think we
ought to push back a little on whether this is actually a sensible
thing to do. I see nothing in the SQL spec forbidding it, but
including the extra columns in the DISTINCT list seems to carry
a risk of splitting what the user expected to be a single output
row into multiple rows. OTOH, if we avoided that by converting
DISTINCT to DISTINCT ON, we'd have uncertainty about which
extra-column values get emitted. The only way that this is stable
is if the extra columns are absolutely, positively guaranteed to
be immutable functions of the regular columns. Do we want to
assume that? The main hazard I see is that we allow a much
wider range of <cycle mark value> and <non-cycle mark value>
than the spec contemplates, eg we will happily take
"timestamptz 'now'" as a mark value. I'm not entirely convinced
that mark values of non-built-in datatypes couldn't cause a problem.
The SQL spec's definition of SEARCH/CYCLE via rewrite rule definitely
results in including the added columns in any DISTINCT behavior.
If the committee thought about this issue, they must have decided that
they believe in the immutability of the added columns --- which may
well be the reason that they restrict the mark values to be literals.
Thoughts?
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Colin McDonnell | 2026-11-20 17:46:40 | Re: Anti-wraparound autovacuum stops for good after no slot available for new autovacuum worker process |
| Previous Message | Sami Imseih | 2026-11-20 17:19:47 | Re: Anti-wraparound autovacuum stops for good after "no slot available for new autovacuum worker process" |