Wrong results from join removal with DISTINCT ON + SRF subquery

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Wrong results from join removal with DISTINCT ON + SRF subquery
Date: 2026-08-22 01:06:33
Message-ID: CAMbWs4-hfd1Pyy_zBejsVUSy-3dx16rz2hgUakkKnAg3qg2q=Q@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I was working on the UniqueKeys patch, and then I got confused by the
output of a DISTINCT ON + SRF query:

create table t (a int);
insert into t values (10), (20);

select distinct on (a) a, generate_series(1, 2) g from t;
a | g
----+---
10 | 1
20 | 1
(2 rows)

select distinct on (a) a, generate_series(1, 2) g from t order by a;
a | g
----+---
10 | 1
10 | 2
20 | 1
20 | 2
(4 rows)

I find it kind of mind-boggling that the same query returns a
different number of rows depending on whether there is an ORDER BY.

Some investigation shows that this comes from make_sort_input_target().
When there is an ORDER BY, SRFs that are not in any sort/group column
are postponed to after the Sort, and since Unique can't project, that
also means after the Unique. Without ORDER BY no postponement happens
and the SRF is expanded below the Unique.

I'm not sure whether this is a bug or not. But I do notice that
tsrf.sql has test cases that pin down exactly this with-vs-without
ORDER BY behavior for DISTINCT ON, so maybe this is expected, and I'm
not proposing to change that here.

What I do think is a bug is that the planner elsewhere assumes a
DISTINCT ON subquery is unique over its DISTINCT ON columns.
query_is_distinct_for() says

/*
* DISTINCT (including DISTINCT ON) guarantees uniqueness if all the
* columns in the DISTINCT clause appear in colnos and operator semantics
* match. This is true even if there are SRFs in the DISTINCT columns or
* elsewhere in the tlist.
*/

which is no longer true for DISTINCT ON given the postponement above.
So join removal and unique-inner joins can produce wrong results. As
an example, consider:

select t1.a from t t1
left join (select distinct on (a) a, generate_series(1,2) g from t
order by a) ss
on t1.a = ss.a;

Attached is a patch to not rely on DISTINCT ON for distinctness if
there are any tlist SRFs. This is more conservative than necessary,
since the SRFs are only postponed when there is an ORDER BY and none
of them is in a sort/group column, but I don't think it's worth
duplicating that logic in analyzejoins.c.

Any thoughts?

- Richard

Attachment Content-Type Size
v1-0001-Don-t-assume-DISTINCT-ON-implies-uniqueness-when-.patch application/octet-stream 5.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2026-08-22 01:15:52 Re: Wrong results from join removal with DISTINCT ON + SRF subquery
Previous Message Tatsuya Kawata 2026-08-22 00:58:37 Re: [PATCH] Fix compilation of nodeMergejoin.c with EXEC_MERGEJOINDEBUG