Bug #5883 FLOOR function error
Submitted: 4 Oct 2004 16:05 Modified: 5 Oct 2004 13:06
Reporter: jivko jeliazkov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.18-standard, 3.23.49-3 OS:Linux (Linux Red Hat 7.3 / 9)
Assigned to: CPU Architecture:Any

[4 Oct 2004 16:05] jivko jeliazkov
Description:

mysql> select floor(33/1.1),33/1.1,floor(30.000);

+---------------+--------+---------------+
| floor(33/1.1)    | 33/1.1 | floor(30.000)    |
+---------------+--------+---------------+
|            29       | 30.000 |            30      |
+---------------+--------+---------------+

---------floor(33/1.1) should return a value of "30"?----------

How to repeat:

select floor(33/1.1),33/1.1,floor(30.000);
[5 Oct 2004 13:06] Hartmut Holzgraefe
It is quite usual that simple decimal fractions like 0.1 or 0.7 cannot be converted into their internal binary counterparts without a little loss of precision. This can lead to confusing results: for example, floor((0.1+0.7)*10) will usually return 7 instead of the expected 8 as the result of the internal representation really being something like 7.9999999999....

This is related to the fact that it is impossible to exactly express some fractions in decimal notation with a finite number of digits. For instance, 1/3 in decimal form becomes 0.3333333. . ..

So never trust floating number results to the last digit and never compare floating point numbers for equality.