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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
