RE: [HACKERS] 6.5.0 - Overflow bug in AVG( )

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