Re: ANSI SQL proposal: SELECT DISTINCT ON (... ORDER BY ...) and UNION DISTINCT ON (... ORDER BY ...)

Subject: [PATCH v3 0/5] Support UNION DISTINCT ON and inline DISTINCT
ON (... ORDER BY ...)

Hi hackers,

Attached is v3 of the patch series introducing:

1. Inline ORDER BY inside DISTINCT ON for SELECT queries:
SELECT DISTINCT ON (a ORDER BY b DESC) a, b, c FROM t ORDER BY c;
Along with HashAggregate support for DISTINCT ON tie-breaking via
ReplaceTupleHashEntryIfBetter().

2. UNION DISTINCT ON (expr [, ...] [ORDER BY sort_expr [, ...]]) for
non-recursive set operations:
SELECT a, b FROM t1
UNION DISTINCT ON (a ORDER BY b DESC)
SELECT a, b FROM t2;

3. Dynamic state-space pruning in recursive CTEs (WITH RECURSIVE) using
UNION DISTINCT ON (key ORDER BY cost), enabling Bellman-Ford /
shortest-path and optimal state-space search directly in SQL without
infinite loops or post-hoc filtering.

Changes in v3:
- Fixed tuple extraction in ReplaceTupleHashEntryIfBetter() to use a
dedicated TupleTableSlot and slot_getattr() instead of
minimal_tuple_getattr(), ensuring safe handling of pass-by-reference /
varlena attributes and arbitrary targetlist column positions.
- Evaluated sort comparators in table->tempcxt with per-comparison memory
context resets and added in-place MinimalTuple overwrite when the
replacement tuple fits in the existing allocation.
- Extracted shared ExecPrepareTupleSortSupport() helper and properly
initialized ssup_cxt in both HashAggregate and RecursiveUnion.
- Replaced RecursiveUnion result_table Tuplestore materialization with
direct TupleHashIterator scanning, added seed-phase deduplication before
populating working_table, and added intra-wave obsolete-tuple pruning.
- Replaced pre-parse col_in_distinct_on() inspection with standard
targetlist resolution via transformDistinctOnClause() and post-type-
resolution build_distinct_sort_clause().
- Normalized SetOperationStmt groupClauses and sortClauses so
tleSortGroupRef == resno holds uniformly across set-operation planning,
and enabled MergeAppend + Unique paths for UNION DISTINCT ON with inline
ORDER BY.
- Added support for nested / multi-way UNION DISTINCT ON set-operation
trees, guarded Append child flattening when groupClauses/sortClauses
differ, and extended subquery WHERE pushdown safety checks to inspect
nested set-operation trees recursively.
- Added EXPLAIN "Sort Key:" output for HashAggregate and RecursiveUnion
when tie-breaker sort columns are active, and added full pg_get_viewdef()
deparsing in ruleutils.c for both SELECT DISTINCT ON (... ORDER BY ...)
and UNION DISTINCT ON (... [ORDER BY ...]).
- Expanded regression test coverage in select_distinct_on.sql and
union.sql.

Hannu Krosing (5):
Refactor set operation group clauses to decouple from targetlist
positions
Support inline ORDER BY in DISTINCT ON for SELECT and enable HashAgg
Support UNION DISTINCT ON for non-recursive set operations
Support dynamic pruning in recursive UNION DISTINCT ON
Fix bugs, security issues, and optimizations in DISTINCT ON and UNION
DISTINCT ON

doc/src/sgml/ref/select.sgml | 47 ++-
src/backend/commands/explain.c | 25 +-
src/backend/executor/execGrouping.c | 136 ++++++
src/backend/executor/nodeAgg.c | 151 +++++--
src/backend/executor/nodeRecursiveunion.c | 234 ++++++++++-
src/backend/nodes/nodeFuncs.c | 2 +
src/backend/optimizer/path/allpaths.c | 62 ++-
src/backend/optimizer/path/equivclass.c | 45 +-
src/backend/optimizer/plan/createplan.c | 88 ++++
src/backend/optimizer/plan/planner.c | 239 +++++++----
src/backend/optimizer/prep/prepunion.c | 91 ++--
src/backend/optimizer/util/pathnode.c | 7 +-
src/backend/parser/analyze.c | 204 +++++++--
src/backend/parser/gram.y | 45 +-
src/backend/parser/parse_clause.c | 34 +-
src/backend/parser/parse_cte.c | 28 ++
src/backend/rewrite/rewriteSearchCycle.c | 27 +-
src/backend/utils/adt/ruleutils.c | 34 +-
src/include/executor/executor.h | 11 +
src/include/executor/nodeAgg.h | 2 +
src/include/nodes/execnodes.h | 6 +
src/include/nodes/parsenodes.h | 4 +
src/include/nodes/pathnodes.h | 2 +
src/include/nodes/plannodes.h | 14 +
src/include/optimizer/pathnode.h | 4 +-
src/include/parser/parse_clause.h | 3 +-
.../regress/expected/select_distinct_on.out | 239 +++++++++++
src/test/regress/expected/union.out | 390 ++++++++++++++++++
src/test/regress/sql/select_distinct_on.sql | 128 ++++++
src/test/regress/sql/union.sql | 202 +++++++++
30 files changed, 2251 insertions(+), 253 deletions(-)

On Fri, Sep 18, 2026 at 7:01 PM Hannu Krosing <hannuk(at)google(dot)com> wrote:
>
> At first glance it looks very much like what DuckDB does with USING
> KEY syntax they have described here
> https://www.cidrdb.org/cidr2023/papers/p14-hirn.pdf
>
> I learned about DuckDB USING KEY just 2 days ago from
> https://lnkd.in/p/ebCwa9Wk
>
> -----
> Hannu