From: | sferac(at)bo(dot)nettuno(dot)it |
---|---|
To: | "Thomas G(dot) Lockhart" <lockhart(at)alumni(dot)caltech(dot)edu> |
Cc: | Postgres Hackers List <hackers(at)postgresql(dot)org> |
Subject: | Re: [QUESTIONS] Error on PostgreSQL agregate SUM() function?? |
Date: | 1998-02-12 10:23:10 |
Message-ID: | Pine.LNX.3.96.980212095212.4464B-100000@nero |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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'
From | Date | Subject | |
---|---|---|---|
Next Message | The Hermit Hacker | 1998-02-12 11:48:03 | Re: [HACKERS] PostGreSQL v6.2.1 for Linux Alpha |
Previous Message | Kenji T. Hollis | 1998-02-12 10:10:15 | Re: [HACKERS] PostGreSQL v6.2.1 for Linux Alpha |