cblp opened a new issue, #26222:
URL: https://github.com/apache/datafusion/issues/26222
### Describe the bug
`sum` and `avg` of `Float64` over a sliding window frame (`ROWS BETWEEN n
PRECEDING AND CURRENT ROW`) are not the sum and average of the values in the
frame:
- Once `Infinity`, `-Infinity` or `NaN` has been in the frame, every later
frame returns `NaN`, even frames that contain only finite values.
- Finite values lose precision: a frame can return a value that differs from
the sum of its values by more than rounding.
Both come from how values leaving the frame are removed:
`SlidingSumAccumulator::retract_batch`
(`datafusion/functions-aggregate/src/sum.rs`) and
`AvgAccumulator::retract_batch`
(`datafusion/functions-aggregate/src/average.rs`) subtract them from the
running sum. `inf - inf` is `NaN`, and `NaN` never leaves the running sum. For
finite values, a small value added to a large one is lost, and subtracting the
large one does not bring it back.
### To Reproduce
In `datafusion-cli`:
```sql
SELECT i, x,
sum(x) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS s,
avg(x) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS a
FROM (VALUES (1, 1.0), (2, 'Infinity'), (3, 2.0), (4, 3.0), (5, 'NaN'), (6,
4.0), (7, 5.0)) AS t(i, x)
ORDER BY i;
```
```
+---+-----+-----+-----+
| i | x | s | a |
+---+-----+-----+-----+
| 1 | 1.0 | 1.0 | 1.0 |
| 2 | inf | inf | inf |
| 3 | 2.0 | inf | inf |
| 4 | 3.0 | NaN | NaN |
| 5 | NaN | NaN | NaN |
| 6 | 4.0 | NaN | NaN |
| 7 | 5.0 | NaN | NaN |
+---+-----+-----+-----+
```
Finite values only:
```sql
SELECT i, x, sum(x) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT
ROW) AS s
FROM (VALUES (1, 1e16), (2, 1.0), (3, 2.0)) AS t(i, x)
ORDER BY i;
```
```
+---+------+------+
| i | x | s |
+---+------+------+
| 1 | 1e16 | 1e16 |
| 2 | 1.0 | 1e16 |
| 3 | 2.0 | 2.0 |
+---+------+------+
```
### Expected behavior
Each row returns the sum (average) of the values in its frame, as the same
aggregate over just those values would:
- first query: row 4 (frame `[2, 3]`) returns `5` and `2.5`, row 7 (frame
`[4, 5]`) returns `9` and `4.5`; rows 5 and 6, whose frames contain `NaN`,
return `NaN`;
- second query: row 3 (frame `[1, 2]`) returns `3`.
### Additional context
Reproduced on `main` at commit 6663e4348 (debug build of `datafusion-cli`).
One way to fix it is to keep the running sum exact, so that removing a value
is exact: for example an integer fixed-point accumulator that holds any `f64`
sum exactly, with counts of `NaN`, `Infinity` and `-Infinity` in the frame
instead of adding them to the sum.
### 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]
