On Thu, 12 Feb 1998, Thomas G. Lockhart wrote:
> > > > PostgreSQL SUMs population column given -1523690296 (overflow)
> > > > While SOLID and MySQL gives 2771277000.
> > > > Is it correct to have an overflow with SUM() function ?
> > > Do you know what technique Solid and/or MySQL use to allow an integer summation to
> > > exceed the range of a signed 32-bit integer? Do they do summations using floating
> > > point? Let us know...
> >
> > To have overflows isn't a merit but a lack.
> > MySQL and SOLID don't use overflow even on SUM(float):
>
> Yes, I understood your point. However, we need a description of an alternative
> implementation to evaluate; can you describe the implementation of Solid or MySQL for
> the sum() operator wrt integers?? Does it use a float8 as the accumulator?
>
> > ... PostgreSQL isn't coherent;
> > it gives an overflow message on sum(float)
> > and nothing when overflow on sum(int) or sum(smallint).
>
> Yes, on some or all platforms Postgres allows silent overflows on integer types. I'm
> not certain about the behavior for all platforms. Is the "float" type on MySQL and
> Solid 4 bytes or eight? If eight, how do they "allow" overflows??
------------------------------------------------------------------
Both MySQL and Solid they have types like:
float (4 byte)
double precision (8 byte)
and they use 8 bytes as the accumulator
look at this:
--SOLID-------------------------------------------
drop table t;
create table t ( mydouble double precision);
insert into t values(8.0e+307);
insert into t values(8.0e+307);
select * from t;
MYDOUBLE
--------
8e+307
8e+307
2 rows fetched.
select sum(mydouble) from t;
SOLID Table Error 13072: Numerical value out of range
-MySQL--------------------------------------------
mysql> update t set mydouble=mydouble*1.1;
Query OK, 2 rows affected (0.01 sec)
mysql> select sum(mydouble) from t;
1 row in set (0.01 sec)
+---------------+
| sum(mydouble) |
+---------------+
| Inf | <-- (seems that "Inf" meaning overflow)
+---------------+
Ciao, Jose'