Re: [HACKERS] Re: [BUGS] agregate function sum error - Mailing list pgsql-hackers

On Thu, 19 Feb 1998, Bruce Momjian wrote:

> >
> > On Tue, 17 Feb 1998, Thomas G. Lockhart wrote:
> >
> > > > > You can try to typecast to float8
> > > > >
> > > > > I have no idea how MySql is doing SUM() - probably it uses unsigned int
> > > > > for SUM() so this gives u twice bigger upper limit - try it with MySql
> > > > > with sum exceeding 2^32. I suppose the result will be negative ...
> > > > >
> > > >
> > > > Well, I asked this question to MySql mailing list:
> > > >
> > > > > sferac> What's meaning "inf" as a result during a SUM() ?
> > > > > sferac> Is it an overflow warning, and if so what's the max range of SUM()
> > > > > sferac> before to have an overflow ?
> > > > >
> > > > > mysql> update t set myfloat=myfloat*9.1;
> > > > > sferac> Query OK, 1415 rows affected (4.94 sec)
> > > > >
> > > > > mysql> select sum(myfloat) from t;
> > > > > sferac> 1 row in set (0.04 sec)
> > > > >
> > > > > sferac> +--------------+
> > > > > sferac> | sum(myfloat) |
> > > > > sferac> +--------------+
> > > > > sferac> |          Inf |
> > > > > sferac> +--------------+
> > > > >
> > > > > mysqld does all normal calculation with doubles (bit functions are
> > > > > done with longlong);  The range of a double is typical something like:
> > > > >
> > > > > #define       DBL_MAX         1.7976931348623157E+308
> > > > > #define       DBL_MIN         2.2250738585072014E-308
> > >
> > > The limits are the same as for Postgres. The difference is that Postgres throws
> > > an error on floating overflows, rather than returning "infinity".
> >
> > I think overflows on float8 is OK, the problem is about overflows on int4,
> > int2 and float4.
> > If PostgreSQL does all calculation using float8 (even on int2, int4 and float4)
> > we have acceptable overflows. Why we don't do this in this way ?
>
> Can you imagine the problems we would have?  Integral types and floats
> are two different things.  They behave differently in division,
> rounding, and overflows.  For those reasons, and performance, we can't
> just use floats all the time.
>
I see..., then what we need is int8 as Tom said, to avoid to write code as:

select cast mysmall as int4 * cast mysmall as int4 from mytable;
select mysmall::int4 * mysmall::int4 from mytable;
select int4(mysmall) * int4(mysmall) from mytable;
select sum(int4(mysmall)) from mytable;

that has nothing to do with SQL standard, and isn't too friendly,
those conversions should be done automaticly by PostgreSQL whitout
user's help as MySQL, Solid and other databases do.
-----------
PS: I installed int8 in my Linux box, but I have an error during insert time:

insert into qtest values('123','456');
ERROR:  fmgr_info: function 159745: cache lookup failed

insert into qtest values('123','4567890123456789');
ERROR:  fmgr_info: function 159745: cache lookup failed

insert into qtest values('4567890123456789','123');
ERROR:  fmgr_info: function 159745: cache lookup failed

insert into qtest values('4567890123456789','4567890123456789');
ERROR:  fmgr_info: function 159745: cache lookup failed

insert into qtest values('4567890123456789','-4567890123456789');
ERROR:  fmgr_info: function 159745: cache lookup failed

                                                            Ciao, Jose'