Dandandan opened a new pull request, #24789:
URL: https://github.com/apache/datafusion/pull/24789
## Which issue does this PR close?
- Closes #.
> Built on #24787, which this needs to be correct. The first commit here is
> that fix; review it there. Without it, an aggregate above a list unnest
would
> be removed on the strength of a key the unnest has already invalidated.
## Rationale for this change
When the GROUP BY expressions include a unique key of the input, every group
holds exactly one row. The aggregate then produces one output row per input
row, like a projection, and every aggregate function returns the value of
that
single row.
DataFusion did not use this. With `lineitem` keyed by `(l_orderkey,
l_linenumber)`:
```sql
SELECT l_orderkey, l_linenumber, sum(l_quantity)
FROM lineitem GROUP BY l_orderkey, l_linenumber
```
still built a hash table over all 60M rows to put one row in each group.
## What changes are included in this PR?
A new rule, `EliminateAggregate`, that replaces such an aggregate with a
projection:
```
Projection: l_orderkey, l_linenumber, CAST(l_quantity AS Decimal128(25, 2))
--TableScan: lineitem
```
The single-row value is known for `min`, `max`, `sum`, `avg`, `first_value`
and
`last_value` (the argument, cast to the aggregate's return type where that
differs) and for `count` (1, or 0 when the argument is NULL). Any other
aggregate keeps the aggregate node.
`DISTINCT`, `ORDER BY` and `IGNORE NULLS` make no difference to a group of
one
row. A `FILTER` does: it can exclude the row and leave the aggregate with no
input at all, which is a different value for every function, so those keep
the
aggregate.
Two cases are not eligible:
- an empty `GROUP BY`, which returns one row for an empty input where a
projection returns none;
- grouping sets, which do not group by every expression at once, so a key
among
them proves nothing.
The rule also removes a `SELECT DISTINCT` over a unique key, which the
planner
turns into an aggregate with no aggregate expressions.
## Benchmarks
TPC-H SF10 with the keys declared, median of 5, three interleaved rounds:
| | without the key | with the key | |
|---|---|---|---|
| `sum(q)` over `GROUP BY l_orderkey, l_linenumber` | 751 ms | 31 ms | 24x |
| `SELECT DISTINCT c_custkey, c_name, c_address` | 51 ms | 19 ms | 2.7x |
Both return identical results.
No TPC-H or TPC-DS query is affected, because those tables declare no keys.
## Are these changes tested?
Yes. `functional_dependencies.slt` gains a section covering the rewrite for
each supported aggregate, NULL handling for all of them, extra grouping
expressions beyond the key, and the four cases that keep the aggregate
(FILTER,
grouping sets, an unsupported aggregate, and grouping by a non-key).
The rule fires in existing tests in `aggregate.slt`, `distinct_on.slt`,
`explain.slt`, `functional_dependencies.slt` and `group_by.slt`; those
expected
plans are updated and no expected result changes. The full sqllogictest suite
(504 files) passes.
## Are there any user-facing changes?
Queries that group by a unique key, or take a DISTINCT over one, no longer
run
an aggregate. Results are unchanged.
--
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]
