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@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@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 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'