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