cblp opened a new issue, #26223:
URL: https://github.com/apache/datafusion/issues/26223
### Describe the bug
With two or more `SUM(x + <literal>)` aggregates over the same `x`, the
optimizer rewrites them to `SUM(x) + <literal> * COUNT(x)`
(`Sum::simplify_expr_op_literal`, the ClickBench Q29 optimization). For
`Float64` this is not an equivalent transformation: floating point addition is
not associative, so the rewritten expression can return a different value. The
same aggregate returns one value on its own and another next to a similar
aggregate.
This is related to #25943, which covers the same rewrite failing or changing
the result type for intervals and decimals. Here the query succeeds and
silently returns a different value.
### To Reproduce
In `datafusion-cli`:
```sql
SELECT sum(x + 1.0) FROM (VALUES (1e16), (1.0), (-1e16)) AS t(x);
-- 2.0
SELECT sum(x + 1.0), sum(x + 2.0) FROM (VALUES (1e16), (1.0), (-1e16)) AS
t(x);
-- 4.0 | 7.0
```
`EXPLAIN` of the second query shows the rewrite:
```
Projection: sum(t.x) + __common_expr_1 AS sum(t.x + Float64(1)), sum(t.x) +
Float64(2) * __common_expr_1 AS sum(t.x + Float64(2))
Projection: CAST(count(t.x) AS Float64) AS __common_expr_1, sum(t.x)
Aggregate: groupBy=[[]], aggr=[[sum(t.x), count(t.x)]]
```
The values of `x + 1.0` are `1e16` (`1e16 + 1` rounds to `1e16`), `2.0` and
`-1e16` (`-1e16 + 1` rounds to `-1e16`), and their sum is `2.0`. The rewrite
instead computes `sum(x) + 1.0 * count(x) = 1.0 + 3.0 = 4.0`.
### Expected behavior
`sum(x + 1.0)` returns `2.0` in both queries: the sum of the values of `x +
1.0`, independent of the other aggregates in the query.
For integer types the rewrite is exact (with wrapping arithmetic), so it
could be skipped when the literal or the aggregate type is floating point.
### Additional context
Reproduced on `main` at commit 6663e4348 (debug build of `datafusion-cli`).
### Willingness to contribute
None
--
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]
