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

"Jackson, DeJuan" <djackson@cpsgroup.com> writes:
> 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?

That bothered me too.  The draft spec that I have sez:
 b) If SUM is specified and DT is exact numeric with scale    S, then the data type of the result is exact numeric with
  implementation-defined precision and scale S.
 
  c) If AVG is specified and DT is exact numeric, then the data    type of the result is exact numeric with
implementation-   defined precision not less than the precision of DT and    implementation-defined scale not less than
thescale of DT.
 
  d) If DT is approximate numeric, then the data type of the    result is approximate numeric with
implementation-defined   precision not less than the precision of DT.
 
 65)Subclause 6.5, "<set function specification>": The precision of    the value derived from application of the SUM
functionto a data    type of exact numeric is implementation-defined.
 
 66)Subclause 6.5, "<set function specification>": The precision and    scale of the value derived from application of
theAVG function    to a data type of exact numeric is implementation-defined.
 
 67)Subclause 6.5, "<set function specification>": The preci-    sion of the value derived from application of the SUM
func-   tion or AVG function to a data type of approximate numeric is    implementation-defined.
 


This would seem to give license for the result of AVG() on an int4 field
to be NUMERIC with a fraction part, but not FLOAT.  But I suspect we
could get away with making it be FLOAT anyway.  Anyone know what other
databases do?
        regards, tom lane