Thread
-
Re: [QUESTIONS] Error on PostgreSQL agregate SUM() function??
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-11T14:28:41Z
> 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??
Marc G. Fournier <scrappy@hub.org> — 1998-02-11T15:14:15Z
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 <kgor@inetspace.com> — 1998-02-11T19:27:54Z
>>>>> "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 -
Re: [QUESTIONS] Error on PostgreSQL agregate SUM() function??
jose' soares <sferac@bo.nettuno.it> — 1998-02-12T10:23:10Z
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' -
Re: [QUESTIONS] Error on PostgreSQL agregate SUM() function??
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-12T16:57:12Z
> > > 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 -
Re: [QUESTIONS] Error on PostgreSQL agregate SUM() function??
jose' soares <sferac@bo.nettuno.it> — 1998-02-13T13:36:10Z
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'