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

PostgreSQL:
^^^^^^^^^^^
prova=> select min(a), max(a), avg(a) from aa;
min|max|avg
---+---+---
1| 2| 1
(1 row)

informix:----------- hygea(at)hygea ------------ Press CTRL-W for Help --------

^^^^^^^^^
(min) (max) (avg)

1 2 1.50000000000000

oracle:
^^^^^^^
SQL> select min(a), max(a), avg(a) from aa;

MIN(A) MAX(A) AVG(A)
---------- ---------- ----------
1 2 1.5

Tom Lane ha scritto:

> "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

______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'