Bug #15589 Trouble with ROUND, <=, >=

• Submitted: 8 Dec 2005 14:43
• Modified: 8 Dec 2005 15:41
• Reporter: Mathieu Virbel
• Status: Won't fix
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.15
• OS: pc-linux-gnu
• Assigned to: Aleksey Kishkin
• CPU Architecture: Any

[8 Dec 2005 14:43] Mathieu Virbel

Description:
I have trouble with ROUND(). Here an example:

A: SELECT ROUND(61.025, 2); 
-> 61.02

B: SELECT 61.025 - ROUND(61.025, 2); 
-> 0.005

C: SELECT IF((61.025 - ROUND(61.025, 2)) >= 0.005, 1, 0); 
-> 0

D: SELECT IF((61.025 - ROUND(61.025, 2)) <= 0.005, 1, 0); 
-> 1

E: SELECT IF((61.025 - ROUND(61.025, 2)) = 0.005, 1, 0); 
-> 0

Only D give me a correct result. C and E are wrong.

Tested on : 
mysql  Ver 12.22 Distrib 4.0.16, for pc-linux (i686)
mysql  Ver 12.22 Distrib 4.0.23, for pc-linux-gnu (i386)
mysql  Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i486)
mysql  Ver 14.7 Distrib 4.1.15, for portbld-freebsd4.10 (i386) using readline 5.0
mysql  Ver 14.7 Distrib 4.1.15, for pc-linux-gnu (i486) using readline 5.0

How to repeat:
First step : 
SELECT ROUND(61.025, 2); 
-> 61.02 (if you don't have this result, this is not a bug for you)

Second Step :
SELECT IF((61.025 - ROUND(61.025, 2)) >= 0.005, 1, 0); 
-> 0 (wrong result)

[8 Dec 2005 15:41] Aleksey Kishkin

Mathieu,

according to documentation, in 4.1 and below:

 "The behavior of ROUND() when the argument is halfway between two integers depends on the C library implementation. Different implementations round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function such as TRUNCATE() or FLOOR()  instead."

in 5.0 and above:
" As of MySQL 5.0.3, ROUND() uses the precision math library for exact-value arguments when the first argument is a decimal value:

    *  For exact-value numbers, ROUND() uses the “round half up” or “round toward nearest” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.
    *  For approximate-value numbers, the result depends on the C library. On many systems, this means that ROUND() uses the "round to nearest even" rule: A value with any fractional part is rounded to the nearest even integer.

The following example shows how rounding differs for exact and approximate values:

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+

For more information, see Chapter 21, Precision Math. "

About comparision - it's also know issue: please check http://dev.mysql.com/doc/refman/4.1/en/problems-with-float.html

Floating point ariphmetics will not be changed in the 4.1 and below. Please use 5.1 for more accurate floating point expression.

[12 Dec 2005 19:15] Aleksey Kishkin

I meant "please use _5.0_ for more accurate (etc etc)" :-)