Re: [HACKERS] 6.5.0 - Overflow bug in AVG( ) - Mailing list pgsql-hackers

<tt>PostgreSQL:</tt><br /><tt>^^^^^^^^^^^</tt><br /><tt>prova=> select min(a), max(a), avg(a) from aa;</tt><br
/><tt>min|max|avg</tt><br/><tt>---+---+---</tt><br /><tt>  1|  2|  1</tt><br /><tt>(1 row)</tt><br
/><tt></tt> <tt></tt><p><tt>informix:-----------hygea@hygea ------------ Press CTRL-W for Help --------</tt><br
/><tt>^^^^^^^^^</tt><br/><tt>      (min)       (max)            (avg)</tt><tt></tt><p><tt>          1          
2                1.50000000000000</tt><tt></tt><p><tt>oracle:</tt><br /><tt>^^^^^^^</tt><br /><tt>SQL> select
min(a),max(a), avg(a) from aa;</tt><tt></tt><p><tt>    MIN(A)        MAX(A)       AVG(A)</tt><br /><tt>----------   
----------   ----------</tt><br /><tt>         1             2           1.5</tt><br /><tt></tt>  <br /><tt></tt>  <br
/> <p>Tom Lane ha scritto: <blockquote type="CITE">"Jackson, DeJuan" <djackson@cpsgroup.com> writes: <br />>
Whatdoes the spec have to say?  It bothers me somewhat that an AVG is <br />> expected to return an integer result
atall.  Isn't the Average of 1 <br />> and 2, 1.5 not 1? <p>That bothered me too.  The draft spec that I have sez:
<p> b) If SUM is specified and DT is exact numeric with scale <br />     S, then the data type of the result is exact
numericwith <br />     implementation-defined precision and scale S. <p>   c) If AVG is specified and DT is exact
numeric,then the data <br />     type of the result is exact numeric with implementation- <br />     defined precision
notless than the precision of DT and <br />     implementation-defined scale not less than the scale of DT. <p>   d) If
DTis approximate numeric, then the data type of the <br />     result is approximate numeric with
implementation-defined<br />     precision not less than the precision of DT. <p>  65)Subclause 6.5, "<set function
specification>":The precision of <br />     the value derived from application of the SUM function to a data <br
/>    type of exact numeric is implementation-defined. <p>  66)Subclause 6.5, "<set function specification>": The
precisionand <br />     scale of the value derived from application of the AVG function <br />     to a data type of
exactnumeric is implementation-defined. <p>  67)Subclause 6.5, "<set function specification>": The preci- <br
/>    sion of the value derived from application of the SUM func- <br />     tion or AVG function to a data type of
approximatenumeric is <br />     implementation-defined. <p>This would seem to give license for the result of AVG() on
anint4 field <br />to be NUMERIC with a fraction part, but not FLOAT.  But I suspect we <br />could get away with
makingit be FLOAT anyway.  Anyone know what other <br />databases do? <p>                        regards, tom
lane</blockquote><p><br/>______________________________________________________________ <br />PostgreSQL 6.5.0 on
i586-pc-linux-gnu,compiled by gcc 2.7.2.3 <br />^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ <br
/>Jose'<br />