Thread

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

   Jackson, DeJuan <djackson@cpsgroup.com> — 1999-06-16T15:21:28Z

   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
   
   

2. Re: [HACKERS] 6.5.0 - Overflow bug in AVG( )

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-06-16T15:29:58Z

   > 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?
   
   Yeah, well, it's a holdover from the original Postgres code. We just
   haven't made an effort to change it yet, but it seems a good candidate
   for a makeover, no?
   
   I'm pretty sure that the spec would suggest a float8 return value for
   avg(int), but I haven't looked recently to refresh my memory.
   
                   - Thomas
   
   -- 
   Thomas Lockhart				lockhart@alumni.caltech.edu
   South Pasadena, California