Re: [QUESTIONS] Error on PostgreSQL agregate SUM() function??

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'