Skip to content

Commit 18cbb87

Browse files
adriangbclaude
andauthored
fix: do not use the top-k aggregate stream for a DISTINCT limit with no ordering direction (apache#25349)
## Which issue does this PR close? - Closes apache#25350. ## Rationale for this change `SELECT DISTINCT ... LIMIT n` with no `ORDER BY` fails at execution with `Internal error: Ordering direction required for DISTINCT with limit` when one of the projected columns is a window function: ```sql CREATE TABLE t (env VARCHAR, region VARCHAR, deployment VARCHAR); INSERT INTO t VALUES ('prod','a','x'), ('prod','a','y'), ('staging','b','x'); SELECT DISTINCT env, region, count(*) OVER (PARTITION BY env, deployment) AS n FROM t LIMIT 30; ``` The mechanism: 1. `LimitedDistinctAggregation` pushes `LimitOptions::new(limit)` (no direction, `descending: None`) into the `AggregateExec` while `is_unordered_unfiltered_group_by_distinct()` is true. 2. `EnsureRequirements` then inserts the sort that the window function needs below the aggregation. `replace_children_if_necessary` rebuilds the aggregation through `replace_children` with `ChildrenPropertiesMode::Recompute`. This derives the properties again from the sorted child but keeps `limit_options`. The rebuilt node has `ordering_mode=PartiallySorted(..)` and an output ordering, so the predicate is now false. 3. `execute_typed` selects `GroupedTopKAggregateStream`. Its constructor needs an ordering direction, and no rule supplied one, so it returns the error. The top-k stream also reads only the first group key (`aggr.group_expr().expr()[0]`). Thus it would give wrong results for a multi-column `DISTINCT` even with a direction. The correct behavior is to not select that stream in this case. ## What changes are included in this PR? `AggregateExec::execute_typed` selects the top-k path only when an ordering direction is available, which is what `GroupedTopKAggregateStream::new` requires: - `limit_options().descending` is set. `TopKAggregation` sets it, and that rule accepts only a single group key. - Or `get_minmax_desc()` supplies a direction. Otherwise, execution uses the regular grouped streams. This is correct because they treat the pushed limit as a soft limit, and the `LIMIT` above the aggregation still truncates the result. The limit pushdown and the plans do not change. Only the stream selection at execution changes. ## What is the testing strategy for this PR? A new case in `datafusion/sqllogictest/test_files/aggregate.slt` runs the multi-column and single-column forms of the query and checks the rows (the same rows as PostgreSQL 16.15 and DuckDB 1.5.2). Without the `mod.rs` change, both queries fail with the internal error. With the change, they pass. Also run locally, on this branch rebased onto current `main`: - `cargo test -p datafusion-physical-plan -p datafusion-physical-optimizer` - `cargo test --profile ci -p datafusion-sqllogictest --test sqllogictests` (all files) ## Are there any user-facing changes? Queries that failed with `Internal error: Ordering direction required for DISTINCT with limit` now return the correct rows. There are no API changes and no plan changes. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude <[email protected]>
1 parent be5a96e commit 18cbb87

2 files changed

Lines changed: 47 additions & 1 deletion

File tree

‎datafusion/physical-plan/src/aggregates/mod.rs‎

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1219,9 +1219,26 @@ impl AggregateExec {
12191219
)?));
12201220
}
12211221

1222-
// grouping by an expression that has a sort/limit upstream
1222+
// Grouping by an expression that has a sort/limit upstream.
1223+
//
1224+
// `GroupedTopKAggregateStream` keeps a priority queue, so it only works
1225+
// when an ordering direction is available: either this is a MIN/MAX
1226+
// aggregate, whose direction is implied by the accumulator, or the
1227+
// optimizer pushed a direction down next to the limit.
1228+
//
1229+
// A direction-less limit is a soft hint pushed by
1230+
// `LimitedDistinctAggregation`, which only pushes it while
1231+
// `is_unordered_unfiltered_group_by_distinct()` holds. That predicate is
1232+
// not stable: a later rule such as `EnsureRequirements` can insert the
1233+
// sort a window function requires below this aggregate, which gives the
1234+
// rebuilt aggregate an output ordering and makes the predicate false
1235+
// without ever supplying a direction. Falling back to the regular
1236+
// grouped streams is correct in that case, because they treat the limit
1237+
// as a soft limit and the `LIMIT` above this aggregate still truncates
1238+
// the result.
12231239
if let Some(config) = self.limit_options
12241240
&& !self.is_unordered_unfiltered_group_by_distinct()
1241+
&& (config.descending.is_some() || self.get_minmax_desc().is_some())
12251242
{
12261243
return Ok(StreamType::GroupedPriorityQueue(
12271244
GroupedTopKAggregateStream::new(self, context, partition, config.limit)?,

‎datafusion/sqllogictest/test_files/aggregate.slt‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7522,6 +7522,35 @@ set datafusion.optimizer.enable_distinct_aggregation_soft_limit = true;
75227522
statement ok
75237523
set datafusion.optimizer.repartition_aggregations = true;
75247524

7525+
# A window function in the projection makes `EnsureRequirements` add a sort below
7526+
# the distinct aggregation, so the aggregation ends up with an output ordering after
7527+
# `LimitedDistinctAggregation` pushed a direction-less limit into it. The query
7528+
# must still execute and return the distinct rows.
7529+
statement ok
7530+
CREATE TABLE distinct_limit_window_table(env VARCHAR, region VARCHAR, deployment VARCHAR) AS VALUES
7531+
('prod', 'a', 'x'),
7532+
('prod', 'a', 'y'),
7533+
('staging', 'b', 'x');
7534+
7535+
query TTI rowsort
7536+
SELECT DISTINCT env, region, count(*) OVER (PARTITION BY env, deployment) AS n
7537+
FROM distinct_limit_window_table
7538+
LIMIT 30;
7539+
----
7540+
prod a 1
7541+
staging b 1
7542+
7543+
query TI rowsort
7544+
SELECT DISTINCT env, count(*) OVER (PARTITION BY env) AS n
7545+
FROM distinct_limit_window_table
7546+
LIMIT 30;
7547+
----
7548+
prod 2
7549+
staging 1
7550+
7551+
statement ok
7552+
DROP TABLE distinct_limit_window_table;
7553+
75257554
#
75267555
# regr_*() tests
75277556
#

0 commit comments

Comments
 (0)