Re: [HACKERS] Re: [BUGS] agregate function sum error

> > 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 can think of
arguments for both behaviors, but one problem is that at least one older platform
(vax) does not have IEEE arithmetic so can't work with "infinity" :(

We do not currently use "longlong" for any integers in the backend, because I
didn't know how this is implemented/supported for _every_ platform on our
supported list. I had put an "int8" package into the contrib directory to test
this, and to get feedback for other platforms, but have gotten only one response
and it was for a platform (alpha) which I already knew would work.

Perhaps I'll make a concerted effort to get this tested for v6.4; scrappy had
suggested just putting int8s into the backend and then working out the porting
issues and I think I'll do that :) Then, we can use int8 variables in other
places where necessary for v6.5...

                                                - Tom