On Wed, 11 Feb 1998, Thomas G. Lockhart wrote:
> > PostgreSQL SUMs population column given -1523690296 (overflow)
> > While SOLID and MySQL gives 2771277000.
> >
> > Who are right PostgreSQL or SOLID and MySQL ?
>
> Duh.
>
> > 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):
mysql> select * from t;
2 rows in set (0.01 sec)
+--------------------------------------------+------------+---------+
| myfloat | myint | mysmall |
+--------------------------------------------+------------+---------+
| 340282346638528859811704183484516925440.00 | 2147483647 | 32767 |
| 340282346638528859811704183484516925440.00 | 2147483647 | 32767 |
+--------------------------------------------+------------+---------+
mysql> select sum(myfloat),sum(myint),sum(mysmall) from t;
1 row in set (0.00 sec)
+--------------------------------------------+------------+--------------+
| sum(myfloat) | sum(myint) | sum(mysmall) |
+--------------------------------------------+------------+--------------+
| 680564693277057719623408366969033850880.00 | 4294967294 | 65534 |
+--------------------------------------------+------------+--------------+
-----------------------------------------------------------
(C) Copyright Solid Information Technology Ltd 1993-1997
Execute SQL statements terminated by a semicolon.
Exit by giving command: exit;
Connected to default server.
mysql> select * from t;
MYFLOAT MYINT MYSMALL
------- ----- -------
3.40282347e+38 2147483647 32767
3.40282347e+38 2147483647 32767
2 rows fetched.
select sum(myfloat),sum(myint),sum(mysmall) from t;
SUM(MYFLOAT) SUM(MYINT) SUM(MYSMALL)
------------ ---------- ------------
6.80564694e+38 4294967294 65534
1 rows fetched.
-----------------------------------------------------------
... PostgreSQL isn't coherent;
it gives an overflow message on sum(float)
and nothing when overflow on sum(int) or sum(smallint).
postgres=> select * from t;
myfloat | myint|mysmall
---------------+----------+-------
9.99999999e+307|2147483647| 32767
9.99999999e+307|2147483647| 32767
(2 rows)
postgres=> select sum(myfloat) from t;
ERROR: Bad float8 input format -- overflow
postgres=> select sum(myint),sum(mysmall) from t;
sum|sum
---+---
-2| -2
(1 row)
Ciao, Jose'
