raams95 opened a new issue, #26439:
URL: https://github.com/apache/datafusion/issues/26439
### Describe the bug
`array_agg(DISTINCT x ORDER BY x)` (and `string_agg(DISTINCT x, ... ORDER BY
x)`) returns its values in the opposite order to the one requested when an
earlier aggregate in the same query orders by the same column in the other
direction. NULL placement is flipped as well.
The same aggregate on its own, or listed before the other one, returns the
right order.
### To Reproduce
```sql
SELECT array_agg(x ORDER BY x DESC), array_agg(DISTINCT x ORDER BY x)
FROM (VALUES (2), (NULL), (1), (2)) t(x);
```
returns
```
[NULL, 2, 2, 1] [NULL, 2, 1]
```
The second column should be `[1, 2, NULL]`. With the aggregates swapped:
```sql
SELECT array_agg(DISTINCT x ORDER BY x), array_agg(x ORDER BY x DESC)
FROM (VALUES (2), (NULL), (1), (2)) t(x);
```
the result is correct: `[1, 2, NULL] [NULL, 2, 2, 1]`.
It happens with `GROUP BY` too, and with `string_agg`:
```sql
SELECT string_agg(x, ',' ORDER BY x DESC), string_agg(DISTINCT x, ',' ORDER
BY x)
FROM (VALUES ('a'), ('b'), ('c')) t(x);
-- c,b,a c,b,a (expected: c,b,a a,b,c)
```
### Expected behavior
`array_agg(DISTINCT x ORDER BY x)` returns the distinct values in the order
given by its own `ORDER BY`, whatever other aggregates are in the query.
### Additional context
When the aggregates in a query need opposite orderings,
`get_finer_aggregate_exprs_requirement` replaces one of them with its reverse
expression (`array_agg` reports `ReversedUDAF::Reversed`). The reversed
expression has its `ORDER BY` flipped and `is_reversed` set, so the accumulator
is expected to undo the reversal. `OrderSensitiveArrayAggAccumulator` does
that, but the `DISTINCT` path in `ArrayAgg::accumulator` builds
`DistinctArrayAggAccumulator` from the flipped sort options and ignores
`is_reversed`. That accumulator sorts its values itself, so it returns them in
the flipped order. `string_agg` delegates to the same accumulator.
Reproduced on `main` at 2c568b478.
### Willingness to contribute
I plan to contribute a fix for this bug independently
--
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]
