agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable
2+ messages / 2 participants
[nested] [flat]
* BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable
@ 2026-10-02 22:38 PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:34 ` Re: BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-10-02 22:38 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: theshallow27@gmail.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable
2026-10-02 22:38 BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable PG Bug reporting form <noreply@postgresql.org>
@ 2026-10-03 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2026-10-03 21:34 UTC (permalink / raw)
To: theshallow27@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-10-03 21:34 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-02 22:38 BUG #19741: `avg(double precision)` overflows on cancelling finite inputs whose mean is representable PG Bug reporting form <noreply@postgresql.org>
2026-10-03 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox