BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable
BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website: Bug reference: 19741 Logged by: Shallow Email address: theshallow27@gmail.com PostgreSQL version: 18.6 Operating system: Linux Description: The arithmetic mean of two finite values, `1e154` and `-1e154`, is exactly zero. PostgreSQL's `avg(double precision)` raises an overflow instead. The same input's sum is zero and its numeric average is zero. The aggregate's shared float8 accumulation state appears to overflow while accumulating values used for variance, although `avg` needs only the count and sum for its final result. **Reproduction:** ```sql SELECT avg(x) FROM unnest(ARRAY[1e154, -1e154]::double precision[]) AS t(x); ``` **Actual result:** SQLSTATE `22003`, `value out of range: overflow`. Reference calculation: ```sql SELECT sum(x), avg(x::numeric) FROM unnest(ARRAY[1e154, -1e154]::double precision[]) AS t(x); -- 0 | 0 ``` **Expected result:** `avg(double precision)` should return `0` for these finite inputs rather than failing due to an intermediate value that does not contribute to the arithmetic mean.
Re: BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
PG Bug reporting form writes: > The arithmetic mean of two finite values, `1e154` and `-1e154`, is exactly > zero. PostgreSQL's `avg(double precision)` raises an overflow instead. That happens because avg() shares its transition function "float8_accum" with some other aggregates that require tracking sum(x^2) as well as sum(x); it's the sum(x^2) that overflows. We could avoid it by giving avg() a dedicated function that only counts sum(x) and N ... but I'm skeptical that that's worth the trouble. If you're trying to perform calculations that are as numerically unstable as this example in float8, it's probably mostly garbage-in-garbage-out anyway. If I had to do something like this in float8, I'd probably do select sum(x order by abs(x)) / count(x) from ... to try to reduce roundoff and cancellation error. But we're not going to make the bare aggregate do that. Another answer could be to cast the aggregate input to numeric, though that'll be a good deal slower in its own way. regards, tom lane