Re: Floor Decimal Math

[Adam de Zoete <lists@xxxxxxxxxxxxx>]

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.

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.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx