DuckDB favg returns the wrong average when the running sum overflows
In DuckDB 1.5.5 and 2.0.0, favg returns the wrong average when the running sum overflows.
When DuckDB calculates favg from three doubles 8.988465674311579e307, 8.988465674311579e307, and 8.988465674311579e307, it returns -nan. It should return 8.988465674311579e+307.
Reproduce it
SELECT favg(v) FROM (VALUES (8.988465674311579e307::DOUBLE), (8.988465674311579e307), (8.988465674311579e307)) t(v);
DuckDB 2.0.0 returns:
-nan
The correct answer
The correct answer is 8.988465674311579e+307 since the mean of three equal values is the same value. The Python code determines the exact mean with fractions and rounds it once to a double:
from fractions import Fraction as F
def favg(values):
# the exact mean, rounded once to a double
return float(sum(F(v) for v in values) / len(values))
print(favg([8.988465674311579e307] * 3)) # 8.988465674311579e+307
Why DuckDB gets it wrong
DuckDB's favg keeps its Kahan running sum in a double, so the sum overflows once it passes the exponent bit limitations of a double.
Kahan Algorithm
- adjusted value = value − correction
- new sum = old sum + adjusted value
- correction = (new sum − old sum) − adjusted value
- merge: add the sum, then minus the correction, into an empty sum with steps 1–3
- average = sum / count − correction / count
| Value | Adjusted | Sum | Correction | Explanation |
|---|---|---|---|---|
| — | — | 0 | 0 | init |
| 8.988465674311579e307 | 8.988465674311579e307 | 8.988465674311579e307 | 0 | First value into the sum |
| 8.988465674311579e307 | 8.988465674311579e307 | 1.7976931348623157e308 | 0 | The sum reaches the largest double |
| 8.988465674311579e307 | 8.988465674311579e307 | inf | inf | The sum passes the largest double, so the sum and the correction become inf |
| merge: inf | inf | inf | nan | Merging the sum into an empty one: (inf − 0) − inf = nan |
| merge: −inf | nan | nan | nan | Merging minus the correction carries the nan into the sum |
Result: nan / 3 − nan / 3 = nan, which DuckDB prints as -nan.
How to fix it
A fixed-point sum translates each value into a whole number, so the running sum avoids the exponent bit limitations of being expressed as a double.
Fixed-Point Algorithm
- Start with a sum of 0
- Turn each value into a whole number of units of the smallest double (value × 2^1074)
- Add that whole number to the sum exactly
Final Step: result = sum × 2^−1074 / count, rounded once
| Value | Units | Sum (units) | Explanation |
|---|---|---|---|
| — | — | 0 | init |
| 8.988465674311579e307 | 8.988465674311579e307 × 2^1074 | 8.988465674311579e307 × 2^1074 | First value into the sum, exactly |
| 8.988465674311579e307 | 8.988465674311579e307 × 2^1074 | 2 × 8.988465674311579e307 × 2^1074 | Added exactly |
| 8.988465674311579e307 | 8.988465674311579e307 × 2^1074 | 3 × 8.988465674311579e307 × 2^1074 | Added exactly, with no overflow |
Result: 3 × 8.988465674311579e307 × 2^1074 × 2^−1074 / 3 = 8.988465674311579e+307.
Vendor issue
Reported to DuckDB as duckdb/duckdb#28145.
writePath
FAVG function
writePath's FAVG uses an exact fixed-point sum. No precision is lost because every value is added as a whole number. The sum is divided by the count and rounded once at the end, so the result is the most precise double. The result does not depend on row order.
About us
writePath is an AWS Marketplace SaaS for sub-millisecond aggregations over the first or last N rows of your data with no pre-computation. It offers 65 high-precision aggregation functions tested against reference implementations.