What does the spec have to say? It bothers me somewhat that an AVG is
expected to return an integer result at all. Isn't the Average of 1 and 2,
1.5 not 1?
just my $0.02,-DEJ
> -----Original Message-----
> From: Tom Lane [SMTP:tgl@sss.pgh.pa.us]
> Sent: Wednesday, June 16, 1999 9:52 AM
> To: Thomas Lockhart
> Cc: Gene Sokolov; pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] 6.5.0 - Overflow bug in AVG( )
>
> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> >> Some broader solution should be considered though if you
> >> want AVG to work on numeric/decimal as well.
>
> > The implementation can be specified for each datatype individually,
>
> In the current implementation, each datatype does use its own type as
> the accumulator --- and also as the counter. float8 and numeric are
> fine, float4 is sort of OK (a float8 accumulator would be better for
> accuracy reasons), int4 loses, int2 loses *bad*.
>
> To fix it we'd need to invent operators that do the appropriate cross-
> data-type operations. For example, int4 avg using float8 accumulator
> would need "float8 + int4 yielding float8" and "float8 / int4 yielding
> int4", neither of which are to be found in pg_proc at the moment. But
> it's a straightforward thing to do.
>
> int8 is the only integer type that I wouldn't want to use a float8
> accumulator for. Maybe numeric would be the appropriate thing here,
> slow though it be.
>
> Note that switching over to float accumulation would *not* be real
> palatable until we have fixed the memory-leak issue. avg() on int4
> doesn't leak memory currently, but it would with a float accumulator...
>
> regards, tom lane