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

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 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
>
>

_______________________________________________________________________________
  Aleksandr Belinsky
     Sternberg  Astronomical Institute,  Moscow  University  (Russia)
         E-mail: aleks@sai.msu.su  http://zeus.sai.msu.su/~aleks/
              _____________________________________________