List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 15 1999 11:02am
Subject:Re: Problems with floor
View as plain text  
>>>>> "Dan" == Dan Nelson <dnelson@stripped> writes:

Dan> In the last episode (Oct 27), sinisa@stripped said:
>> Dan Nelson writes:
>> > In the last episode (Oct 27), sinisa@stripped said:
>> > > 
>> > > select floor(pow(2,63));
>> > > 
>> > > returning negative result of -9223372036854775808.
>> > 
>> > Mysql should either return some kind of error, or keep the result in
>> > double format internally, or use fixed-point math..
>> 
>> Yes, for X/OPen it is double, for ANSI SQL it is integer. Which one is 
>> more relevant for MySQL ????

Dan> Dunno.  Does ANSI SQL specify that the application must store the
Dan> number in a machine integral type?  What's wrong with using a double
Dan> internally if the number doesn't fit in a longlong?  For what it's
Dan> worth, the largest power that Oracle can process correctly is 2^132. 
Dan> Past that it starts losing precision, but still works.

Hi!

MySQL calculates floor() in double, but the default value for floor()
in integer/string context is integer.  In the context:

select floor(xx), MySQL calculates the values of floor as a double,
but then cast the result to an integer.

If one uses floor() in a real number context like:

select floor(xxx)+0.0;

MySQL will return the answer as a double().

As MySQL doesn't have transactions and can't abort anywhere without
causing even more problems (as there isn't rollbacks), for example in
an update of type:

UPDATE table set A=floor(B);

it would not be good to stop halfways if B for one value would be
'too' big.

MySQL generally returns some 'reasonable' value for each expression
instead of generating an error (like 1/0 -> NULL). I know this is not
perfect, but the question is if this isn't good enough for most cases.

Regards,
Monty

PS: In MySQL 3.23.6 I have at least fixed the problem with core
    dumping on floor(pow(2,63)).
Thread
Re: Problems with floorsinisa27 Oct
  • Re: Problems with floorDan Nelson27 Oct
    • Re: Problems with floorsinisa27 Oct
      • Re: Problems with floorDan Nelson27 Oct
        • Re: Problems with floorMichael Widenius15 Nov
  • Re: Problems with floorJeremy Cole28 Oct
    • Re: Problems with floorsinisa28 Oct