From 7e29fcd1ae0083a7fd45f14ab7b9ad3ea0c97e9c Mon Sep 17 00:00:00 2001 From: Richard Guo Date: Tue, 25 Aug 2026 10:22:12 +0900 Subject: Don't assume DISTINCT ON implies uniqueness when the tlist has SRFs query_is_distinct_for() treated a subquery's DISTINCT ON clause as proof that its output is unique over the DISTINCT ON columns, even if the targetlist contains set-returning functions. That's not true: when the query has an ORDER BY, the planner postpones evaluation of SRFs that are not DISTINCT ON or ORDER BY columns until after the Unique step, so the subquery can produce duplicates of the DISTINCT ON columns. Relying on this bogus uniqueness proof allowed join removal and unique-inner joins to produce wrong results. Plain DISTINCT is not affected, since all tlist columns are DISTINCT columns there, and so any SRFs get expanded before the Unique step. To fix, make query_supports_distinctness() and query_is_distinct_for() refuse to prove distinctness via DISTINCT ON if the targetlist contains any SRFs. This is more conservative than necessary, since the SRFs are only postponed when there is an ORDER BY and none of them appear in a sort/group column, but it doesn't seem worth the trouble to check that precisely. Author: Richard Guo Reviewed-by: Tom Lane Discussion: https://postgr.es/m/CAMbWs4-hfd1Pyy_zBejsVUSy-3dx16rz2hgUakkKnAg3qg2q=Q@mail.gmail.com Backpatch-through: 14 --- src/backend/optimizer/plan/analyzejoins.c | 16 ++++++++++----- src/test/regress/expected/join.out | 33 +++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 12 +++++++++++ 3 files changed, 56 insertions(+), 5 deletions(-) (limited to 'src') diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 0e6969c5d5a..bf0c43b23ee 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -757,8 +757,9 @@ rel_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, List *clause_list) bool query_supports_distinctness(Query *query) { - /* SRFs break distinctness except with DISTINCT, see below */ - if (query->hasTargetSRFs && query->distinctClause == NIL) + /* SRFs break distinctness except with plain DISTINCT, see below */ + if (query->hasTargetSRFs && + (query->distinctClause == NIL || query->hasDistinctOn)) return false; /* check for features we can prove distinctness with */ @@ -842,10 +843,15 @@ query_is_distinct_for_with_collations(Query *query, List *distinct_cols) /* * 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. + * match. With plain DISTINCT this is true even if there are SRFs in the + * tlist, since they are all DISTINCT columns and hence get expanded + * before the Unique step. But with DISTINCT ON, the planner may postpone + * SRFs that are not DISTINCT ON or ORDER BY columns until after the + * Unique step, which can produce duplicates of the DISTINCT ON columns; + * so we can't rely on DISTINCT ON if there are any tlist SRFs. */ - if (query->distinctClause) + if (query->distinctClause && + !(query->hasTargetSRFs && query->hasDistinctOn)) { foreach(l, query->distinctClause) { diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 2eca3782e60..4c5920df8b3 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4678,6 +4678,39 @@ select d.* from d left join (select distinct * from b) s -> Seq Scan on d (9 rows) +-- join removal is not possible when the subquery has DISTINCT ON and a +-- set-returning function that is not a DISTINCT ON column +explain (costs off) +select d.* from d left join + (select distinct on (id) id, generate_series(1, 2) as g from b order by id) s + on d.a = s.id + order by 1, 2; + QUERY PLAN +----------------------------------------------------------------------- + Sort + Sort Key: d.a, d.b + -> Hash Left Join + Hash Cond: (d.a = s.id) + -> Seq Scan on d + -> Hash + -> Subquery Scan on s + -> ProjectSet + -> Unique + -> Index Only Scan using b_pkey on b +(10 rows) + +select d.* from d left join + (select distinct on (id) id, generate_series(1, 2) as g from b order by id) s + on d.a = s.id + order by 1, 2; + a | b +---+--- + 1 | 3 + 1 | 3 + 2 | 2 + 3 | 1 +(4 rows) + -- check join removal works when uniqueness of the join condition is enforced -- by a UNION explain (costs off) diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 9d622d18a12..146a262ddf4 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1622,6 +1622,18 @@ explain (costs off) select d.* from d left join (select distinct * from b) s on d.a = s.id; +-- join removal is not possible when the subquery has DISTINCT ON and a +-- set-returning function that is not a DISTINCT ON column +explain (costs off) +select d.* from d left join + (select distinct on (id) id, generate_series(1, 2) as g from b order by id) s + on d.a = s.id + order by 1, 2; +select d.* from d left join + (select distinct on (id) id, generate_series(1, 2) as g from b order by id) s + on d.a = s.id + order by 1, 2; + -- check join removal works when uniqueness of the join condition is enforced -- by a UNION explain (costs off) -- cgit v1.2.3