Re: [HACKERS] Re: bug on aggregate function AVG()

"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
>> I see that AVG() and SUM() uses an accumulator not enough big to hold
>> the result of calculation, but the point is: should we consider this
>> thing a "terrible" bug or an acceptable feature ?
>> What about to convert every accumulator to float8 ?

> imho we can't do that because we lose the exact qualities of integers.
> If you accumulate in float8, and if you take a sum over a very large
> table, you might start ignoring values.

I think that SUM() on an int column ought to produce an exact result.
AVG() is a different story --- I think you could make a good case that
it ought to produce a float result even when the input is integers,
since the exact right answer would typically not be integral anyway.
(A programmer who wants the average rounded to integer should have to
write something like ROUND(AVG(x)), I think.)

One way you could postpone the overflow problem for SUM() is to
accumulate the running sum in a "long", or even better "long long" where
available, even if the input datatype is a smaller flavor of int.
You might still find that the end result overflows, but if the incoming
values are not all the same sign then this might avoid an unnecessary
intermediate overflow.
        regards, tom lane