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

"Jackson, DeJuan" <djackson(at)cpsgroup(dot)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 the scale 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 function to 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 the AVG 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