In the last episode (Oct 27), sinisa@stripped said:
> There were questions on the list regarding a query like this:
> select floor(pow(2,63));
> returning negative result of -9223372036854775808.
> After some small analysis, I do have a definite answer for you.
> According to a standard, floor has to return an integer. MySQL uses
> long long type for it, because it is the largest signed integer type
Floor has to return an integral value, but it doesn't guarantee that
that value will fit in an integer-type variable.
> So, as :
> pow(2,63) = (double) 9223372036854775808.000000;
> floor( (double) 9223372036854775808.000000) = 9223372036854775808.000000;
> Largest long long number is 9223372036854775807 !!!!!!!
> Therefore (long long) 9223372036854775808.000000 = -9223372036854775808;
> Unsigned long long could not be uses, as it has to be signed integer
> So, everything is as it should be !
But, according to the X/Open manpage for floor() at
The integral value returned by floor() as a double might not be
expressible as an int or long int. The return value should be
tested before assigning it to an integer type to avoid the
undefined results of an integer overflow.
Mysql should either return some kind of error, or keep the result in
double format internally, or use fixed-point math..