postgres.git / summary / log / commit / refs
commit 8fc5b57ae644a6ef1c8dcc28ce09dae583949ffc
Author: Richard Guo <rguo@postgresql.org>
Date: Tue Aug 25 01:16:58 2026 +0000
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 <guofenglinux@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
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(-)
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index ec554b7c55c..9858a60c2f7 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -984,8 +984,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 */
@@ -1069,10 +1070,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 eb6723784a9..6c0c52c9ff1 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5857,6 +5857,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)
+
-- join removal is not possible here
explain (costs off)
select 1 from a t1
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 48e963f4396..23e4efd86f9 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2141,6 +2141,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;
+
-- join removal is not possible here
explain (costs off)
select 1 from a t1
[parent: 4dd227f51d46]