Repository navigation
Commit 9e36344
fix: ignore repeated sort keys in an ordered aggregate's ORDER BY (apache#25400)
## Which issue does this PR close?
- Closes apache#25398.
## Rationale for this change
If an ordered aggregate's `ORDER BY` names the same expression more than
once, the query panics or fails with an internal Arrow error:
```sql
CREATE TABLE t AS SELECT i % 3 AS b, i AS id FROM (SELECT unnest(range(0,10)) AS i);
SELECT b, first_value(id ORDER BY b, b) FROM t GROUP BY b;
-- panicked at datafusion/functions-aggregate/src/first_last.rs:602:13
-- assertion `left == right` failed left: 2 right: 1
```
Repeating a key is legal SQL. `DISTINCT ON` is the easy way to hit this
by accident, because its key has to lead the `ORDER BY`, and writing the
key out again produces the duplicate: `SELECT DISTINCT ON (b) b FROM t
ORDER BY b, b`.
The two sides of the ordered aggregate count the sort keys differently:
- `AggregateExprBuilder::build` derives the ordering state fields
(`ordering_fields`) from the `ORDER BY` list as written, so `b, b`
becomes two fields.
- Every accumulator (`first_value`, `last_value`, `nth_value`,
`array_agg`, `string_agg`) builds its ordering with `LexOrdering::new`,
which drops a sort key whose expression already appeared, so `b, b`
becomes one key.
Depending on which consumer compares them first, the mismatch shows up
as the `first_last.rs` assertion, `Incorrect number of arrays provided
to RowConverter`, or a column-count mismatch in the state schema.
## What changes are included in this PR?
`AggregateExprBuilder::build` now passes `order_bys` through
`LexOrdering` before deriving the ordering types and fields. The state
and the accumulators then agree on the number of sort keys.
A repeated key can never break a tie the earlier one left, so dropping
it doesn't change results. As with `LexOrdering` elsewhere, the first
occurrence wins, so `ORDER BY b ASC, b DESC` orders like `ORDER BY b
ASC`.
## What is the testing strategy for this PR?
A new block in `aggregate.slt` next to the existing ordered `string_agg`
tests. Its table has a unique sort key, so each expected value is
decided by the ordering, not by a tie. It covers:
- `first_value`, `last_value` and `nth_value`, with `nth_value` using an
expression key (`k + 0, k + 0`)
- `array_agg`, plus `string_agg` with `ORDER BY k DESC, k ASC`
- `DISTINCT ON (g) ... ORDER BY g, g, k`
Without the change, three of the new queries fail: the two panics and
the RowConverter error from the issue. With it, they pass. The other
`.slt` files that exercise ordered aggregates (`aggregate`, `array_agg`,
`group_by`, `first_last_*`, `distinct_on`, `window`, `order`,
`subquery_sort`) still pass.
I also ran every query in the issue's matrix and compared it with the
same query with the repeated key removed. All twelve return the same
rows.
## Are there any user-facing changes?
Queries that failed now return results. No API changes.
## Notes for reviewers
- **`min`/`max` are no longer part of this.** The schema mismatch they
hit was a separate bug, fixed by apache#25402 (merged). This PR is the
duplicate-sort-key fix on top of it: `build()` now clears the ORDER BY
for an order-insensitive aggregate, and otherwise drops repeated sort
keys.
- **`with_new_expressions` rebuilds `order_bys` by zipping the existing
keys with new expressions.** In principle a rewrite could map two
distinct sort keys onto the same expression and reintroduce a repeat.
Checking the callers, the only production one that passes the order-by
list is the physical planner (`core/src/physical_planner.rs`), and it
passes the keys back unchanged; the window sliding-aggregate caller
passes an empty list. Thanks to @haochunchang for independently going
through these.1 parent ba19bbf commit 9e36344
2 files changed
Lines changed: 59 additions & 4 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
61 | 61 | | |
62 | 62 | | |
63 | 63 | | |
64 | | - | |
| 64 | + | |
65 | 65 | | |
66 | 66 | | |
67 | 67 | | |
| |||
265 | 265 | | |
266 | 266 | | |
267 | 267 | | |
268 | | - | |
269 | | - | |
| 268 | + | |
| 269 | + | |
| 270 | + | |
| 271 | + | |
| 272 | + | |
| 273 | + | |
270 | 274 | | |
271 | 275 | | |
272 | 276 | | |
273 | | - | |
| 277 | + | |
| 278 | + | |
| 279 | + | |
274 | 280 | | |
275 | 281 | | |
276 | 282 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
8230 | 8230 | | |
8231 | 8231 | | |
8232 | 8232 | | |
| 8233 | + | |
| 8234 | + | |
| 8235 | + | |
| 8236 | + | |
| 8237 | + | |
| 8238 | + | |
| 8239 | + | |
| 8240 | + | |
| 8241 | + | |
| 8242 | + | |
| 8243 | + | |
| 8244 | + | |
| 8245 | + | |
| 8246 | + | |
| 8247 | + | |
| 8248 | + | |
| 8249 | + | |
| 8250 | + | |
| 8251 | + | |
| 8252 | + | |
| 8253 | + | |
| 8254 | + | |
| 8255 | + | |
| 8256 | + | |
| 8257 | + | |
| 8258 | + | |
| 8259 | + | |
| 8260 | + | |
| 8261 | + | |
| 8262 | + | |
| 8263 | + | |
| 8264 | + | |
| 8265 | + | |
| 8266 | + | |
| 8267 | + | |
| 8268 | + | |
| 8269 | + | |
| 8270 | + | |
| 8271 | + | |
| 8272 | + | |
| 8273 | + | |
| 8274 | + | |
| 8275 | + | |
| 8276 | + | |
| 8277 | + | |
| 8278 | + | |
| 8279 | + | |
| 8280 | + | |
| 8281 | + | |
8233 | 8282 | | |
8234 | 8283 | | |
8235 | 8284 | | |
| |||
0 commit comments