| Bug #10526 | MySQL floating point math bug in ceiling() function | ||
|---|---|---|---|
| Submitted: | 10 May 2005 21:57 | Modified: | 11 May 2005 6:00 |
| Reporter: | Tolga Tarhan | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | mysql-4.1.10-standard | OS: | Linux (Linux 2.4.21 (RHEL 3/U4)) |
| Assigned to: | CPU Architecture: | Any | |
[10 May 2005 22:02]
Tolga Tarhan
Found a workaround, and thus I'm downgrading the severity.
The following expression returns the expected result of 7. This is very likely due to the fact that concat() forces the value to be cast to a string and then the ceiling() function converts it back to a numeric value -- therefore getting rid of any floating point math errors from the original multiplication.
select ceiling(concat(0.070 * 100));
[10 May 2005 22:05]
Tolga Tarhan
As more proof that this is a problem with the original floating point operation, the following also works and returns 7 as expected (which is what I assume concat() was doing internally anyway):
select ceiling(cast(0.070 * 100 as char));
[11 May 2005 6:00]
Hartmut Holzgraefe
The internal binary representation of FLOAT values does not exactly represent decimal fractions, you always have to expect rounding errors. This is not a MySQL specific issue. The problem especially surfaces with CEIL() and FLOOR() as these are operating on the full precision value whereas rounding is applied when converting a FLOAT to a STRING value. In your case 0.7 is actually something like 0.7000......1 internally so ceil() rounds it up. Starting with MySQL 5.0 you can use the DECIMAL type to avoid these rounding issues, you have to be aware though that DECIMAL calculations are slower than FLOAT calculations as the later ones are directly implemented within the floating point unit of the CPU
Description: The following expression should return a value of 7, however, it returns the value 8: select ceiling(.07 * 100); The problem only happens with certain numbers, but is reproducable reliably when using .07 as the floating-point value in the expression. The problem appears to be with the ceiling function, as the following expression returns 7.00 as expected: select .07 * 100; How to repeat: Use the MySQL command line client and execute the query in the description. Suggested fix: Not yet known.