List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 22 2008 1:18am
Subject:Re: Floor Decimal Math
View as plain text  
On May 16, 2008, at 2:42 AM, Adam de Zoete wrote:

> Thanks for your responses, i thought it was a float problem so i was  
> trying to CAST as a DECIMAL to fix it. It turns out (and the manual  
> does not document this) that casting as decimals doesn't actually  
> work in mysql 4.1.20. ROUND() is needed instead.

DECIMAL is not listed for CAST() in the 4.1 manual because it is not  
supported in 4.1.

DECIMAL is listed for CAST() in the 5.0 as supported from 5.0.8 on.


> mysql> select ROUND(11.11-FLOOR(11.11),2)>=0.11;
> +-----------------------------------+
> | ROUND(11.11-FLOOR(11.11),2)>=0.11 |
> +-----------------------------------+
> | 1                                 |
> +-----------------------------------+
>
> Thanks for all your help,
>
> Adam
>
>
>
> Jerry Schwartz wrote:
>> Don't feel bad, many an experience programmer has been bitten by  
>> this.
>> The problem is that many decimal fractions do not have exact  
>> representations
>> as binary fractions. .01 is an example of this.
>> I'm not sure how MySQL does arithmetic internally, but (11.11 - 11)  
>> is just
>> a hair under .11:
>> mysql> SELECT (11.11 - 11) >= .10999999999999;
>> +---------------------------------+
>> | (11.11 - 11) >= .10999999999999 |
>> +---------------------------------+
>> |                               1 |
>> +---------------------------------+
>> You need to allow for a slight fudge factor. This is even a problem  
>> at the
>> hardware design level.
>> The best way to handle this is to make sure all of your operators  
>> are type
>> DECIMAL. You might have to cast them.
>> Apparently MySQL 5+ introduced some algorithms that helped with these
>> problems, although not with yours.

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com

Thread
Floor Decimal MathAdam de Zoete15 May
  • Re: Floor Decimal Mathwim.delvaux15 May
  • RE: Floor Decimal MathJerry Schwartz15 May
    • Re: Floor Decimal MathAdam de Zoete16 May
      • Re: Floor Decimal MathPaul DuBois22 May
  • RE: Floor Decimal MathJohn Trammell15 May