hanke580 opened a new issue, #25398:
URL: https://github.com/apache/datafusion/issues/25398
### Describe the bug
When an ordered aggregate's `ORDER BY` list mentions the same expression
twice, DataFusion
builds two internal descriptions of that ordering with different lengths,
and every consumer
that compares them fails. Depending on which consumer is reached, the query
either **panics** a
worker task or fails with an **internal Arrow error** that no user-facing
mistake can explain.
```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;
```
```
thread 'tokio-rt-worker' panicked at
datafusion/functions-aggregate/src/first_last.rs:602:13:
assertion `left == right` failed
left: 2
right: 1
Error: Join Error
caused by
External error: task 131 panicked with message "assertion `left == right`
failed\n left: 2\n right: 1"
```
Ten rows and one column; no configuration, no memory pressure, no particular
partition count.
A duplicated sort key is legal SQL — the repetition is simply redundant,
since the second copy
can never break a tie the first did not. PostgreSQL and DuckDB both accept
it and answer
correctly (transcripts under *Additional context*).
**`DISTINCT ON` is the way this is most likely to be hit by accident**,
because `DISTINCT ON`
already requires its key to lead the `ORDER BY`; a user who then also names
the key explicitly
writes the duplicate without noticing:
```sql
SELECT DISTINCT ON (b) b FROM t ORDER BY b, b; -- same panic, same line
```
#### Symptom matrix
All rows are the same defect reaching a different consumer. Output is from
`main`
`6c27203c8e`, `datafusion-cli 55.1.0`, each statement in a fresh process.
| Query | Result |
|---|---|
| `SELECT b, first_value(id ORDER BY b, b) FROM t GROUP BY b` | **panic**
`first_last.rs:602`, `left: 2, right: 1` |
| `SELECT DISTINCT ON (b) b FROM t ORDER BY b, b` | **panic**, same line
(plans to `first_value`) |
| `SELECT b, last_value(id ORDER BY b, b) FROM t GROUP BY b` | **panic**,
same line |
| `SELECT b, first_value(id ORDER BY b ASC, b DESC) FROM t GROUP BY b` |
**panic**, same line |
| `SELECT b, first_value(id ORDER BY b, id, b) FROM t GROUP BY b` |
**panic**, `left: 3, right: 2` |
| `SELECT b, first_value(id ORDER BY b, b, b) FROM t GROUP BY b` |
**panic**, `left: 3, right: 1` |
| `SELECT b, first_value(id ORDER BY b+0, b+0) FROM t GROUP BY b` |
**panic** — it is not only column references |
| `SELECT b, array_agg(id ORDER BY b, b) FROM t GROUP BY b` | `Arrow error:
Invalid argument error: Incorrect number of arrays provided to RowConverter,
expected 1 got 2` |
| `SELECT b, string_agg(CAST(id AS VARCHAR), ',' ORDER BY b, b) FROM t GROUP
BY b` | same `RowConverter` error |
| `SELECT b, nth_value(id, 1 ORDER BY b, b) FROM t GROUP BY b` | `Arrow
error: ... expected List(Struct("b@0": Int64, "b@0": Int64)) but found
List(Struct("b@0": Int64)) at column index 2` |
| `SELECT b, min(id ORDER BY b, b) FROM t GROUP BY b` | `Arrow error: ...
number of columns(2) must match number of fields(4) in schema` |
| `SELECT first_value(id ORDER BY b, b) FROM t` (ungrouped) | `Arrow error:
... number of columns(3) must match number of fields(4) in schema` |
| **any of the above with the duplicate removed** | **correct** |
Two negative controls that bound the defect — both are fine, so this is
specific to *ordered
aggregates*, not to duplicated sort keys in general:
| Query | Result |
|---|---|
| `SELECT * FROM t ORDER BY b, b LIMIT 3` | correct |
| `SELECT first_value(id) OVER (ORDER BY b, b) FROM t LIMIT 3` | correct
(window path keeps both keys) |
Note the `ORDER BY b ASC, b DESC` row: the deduplication is on the
*expression*, ignoring the
sort options, so two entries that are not even semantically identical
collapse to one.
### To Reproduce
```console
$ datafusion-cli --version
datafusion-cli 55.1.0
$ datafusion-cli -q \
-c "CREATE TABLE t AS SELECT i % 3 AS b, i AS id FROM (SELECT
unnest(range(0,10)) AS i);" \
-c "SELECT b, first_value(id ORDER BY b, b) FROM t GROUP BY b;"
thread 'tokio-rt-worker' panicked at
datafusion/functions-aggregate/src/first_last.rs:602:13:
assertion `left == right` failed
left: 2
right: 1
```
Backtrace of the panicking frame:
```
4:
FirstLastGroupsAccumulator<PrimitiveValueState<Int64Type>>::get_filtered_extreme_of_each_group
5: <FirstLastGroupsAccumulator<...> as GroupsAccumulator>::update_batch
6:
aggregates::aggregate_hash_table::common::HashAggregateAccumulator::update_batch
7:
aggregates::group_values::metrics::GroupByMetrics::time_aggregation::<...>
8: aggregates::hash_stream::PartialHashAggregateStream::handle_input_batch
```
### Expected behavior
`first_value(id ORDER BY b, b)` answers as `first_value(id ORDER BY b)` does
— the duplicate key
is redundant and can be dropped — instead of panicking; and the `array_agg`
/ `nth_value` /
`min` / ungrouped forms return a result instead of an internal Arrow error.
For comparison, on the same data:
```console
$ psql -U postgres -c "CREATE TABLE tdup AS SELECT i%3 AS b, i AS id FROM
generate_series(0,9) i;" \
-c "SELECT b, array_agg(id ORDER BY b, b) FROM tdup GROUP
BY b ORDER BY b;" \
-c "SELECT DISTINCT ON (b) b FROM tdup ORDER BY b, b;" \
-c "SELECT b, min(id ORDER BY b ASC, b DESC) FROM tdup
GROUP BY b ORDER BY b;"
SELECT 10
b | array_agg
---+-----------
0 | {0,3,6,9}
1 | {4,1,7}
2 | {5,8,2}
(3 rows)
b
---
0
1
2
(3 rows)
b | min
---+-----
0 | 0
1 | 1
2 | 2
(3 rows)
```
(PostgreSQL 18.6.)
```console
$ python3 -c "import duckdb; con=duckdb.connect()
con.execute('CREATE TABLE t AS SELECT i%3 AS b, i AS id FROM range(0,10)
tt(i)')
print(con.execute('SELECT b, first(id ORDER BY b, b), list(id ORDER BY b, b)
FROM t GROUP BY b ORDER BY b').fetchall())"
[(0, 0, [0, 3, 6, 9]), (1, 1, [1, 4, 7]), (2, 2, [2, 5, 8])]
```
(DuckDB 1.6.0.dev214; it also accepts `ORDER BY b ASC, b DESC` and the
`DISTINCT ON` form.)
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
