Обсуждение: Re: [QUESTIONS] Error on PostgreSQL agregate SUM() function??
> 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...
- Tom
> name | population
> ----------+-----------
> RUSSIA | 281170000
> INDIA | 766140000
> CHINA | 1072220000
> JAPAN | 129947000
> CANADA | 25610000
> U.S.A. | 242080000
> MEXICO | 81160000
> BRAZIL | 141450000
> ARGENTINA | 31500000
>
> postgres=> select sum(populazione) from nations;
> sum
> -----------
> -1523690296
>
> mysql> select sum( populazione) from nations;
> 1 row in set (0.05 sec)
> +-------------------+
> | sum( population) |
> +-------------------+
> | 2771277000 |
> +-------------------+
>
> SOLID SQL Editor (teletype) v.02.20.0007
> (C) Copyright Solid Information Technology Ltd 1993-1997
> Execute SQL statements terminated by a semicolon.
> Exit by giving command: exit;
> Connected to default server.
>
> select sum(population) from nations;
> SUM(POPULATION)
> ----------------
> 2771277000
> 1 rows fetched.
Re: [HACKERS] Re: [QUESTIONS] Error on PostgreSQL agregate SUM() function??
От
The Hermit Hacker
Дата:
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.
Just to add in here...Oracle:
SQL> select sum(population) from nations;
SUM(POPULATION)
---------------
2771277000
SQL>
Re: [HACKERS] Re: [QUESTIONS] Error on PostgreSQL agregate SUM() function??
От
"Kent S. Gordon"
Дата:
>>>>> "scrappy" == The Hermit Hacker <scrappy@hub.org> writes:
> 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.
> Just to add in here...Oracle:
SQL> select sum(population) from nations;
> SUM(POPULATION) --------------- 2771277000
Oracle does all math in base 100 so it is not the same as using
integer.
SQL>
Kent S. Gordon
Architect
iNetSpace Co.
voice: (972)851-3494 fax:(972)702-0384 e-mail:kgor@inetspace.com
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'
> > > 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??
- Tom
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'