MySQL Bugs: #13767: ROUND returns different results for "same" math problem

Bug #13767	ROUND returns different results for "same" math problem
Submitted:	5 Oct 2005 9:45	Modified:	16 Dec 2005 9:44
Reporter:	Peter Reinhold	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.14-nt, 4.1.15-BK	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
There is an error with the ROUND() function, take a look at these two queries

mysql> select round(1.1*3*25);
+-----------------+
| round(1.1*3*25) |
+-----------------+
|              82 |
+-----------------+
1 row in set (0.00 sec)

mysql> select round(1.1*25*3);
+-----------------+
| round(1.1*25*3) |
+-----------------+
|              83 |
+-----------------+

Arguably, they should return the same results, but they do not.  This caused some SEVERE headaches in a large SQL query.

How to repeat:
Try these queries

select round(1.1*3*25);
select round(1.1*25*3);

Let me check... At least, there is a problem on 4.1.14-nt too:

mysql> select round(1.1*3*25);
+-----------------+
| round(1.1*3*25) |
+-----------------+
|              82 |
+-----------------+
1 row in set (0.02 sec)

mysql> select round(1.1*25*3);
+-----------------+
| round(1.1*25*3) |
+-----------------+
|              83 |
+-----------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.00 sec)

A workaround can be done like this (to replace the ROUND function)

FLOOR(<CALCULATION>+0.5)

Example : FLOOR(1.1*3*25+0.5)

This should (and does in this case), work like the ROUND function

Verified on latest 4.1.15-BK on Fedora Core 1:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.15-debug |
+--------------+
1 row in set (0.00 sec)

mysql> select round(1.1*3*25);
+-----------------+
| round(1.1*3*25) |
+-----------------+
|              82 |
+-----------------+
1 row in set (0.05 sec)

mysql> select round(1.1*25*3);
+-----------------+
| round(1.1*25*3) |
+-----------------+
|              83 |
+-----------------+
1 row in set (0.00 sec)

mysql> select round(1.1*25*3, 2);
+--------------------+
| round(1.1*25*3, 2) |
+--------------------+
|              82.50 |
+--------------------+
1 row in set (0.00 sec)

mysql> select round(1.1*3*25, 2);
+--------------------+
| round(1.1*3*25, 2) |
+--------------------+
|              82.50 |
+--------------------+
1 row in set (0.00 sec)

There is a sentence about ROUND implementation before 5.0.3 in the manual (http://dev.mysql.com/doc/mysql/en/mathematical-functions.html):

"Before MySQL 5.0.3, 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."

But it does not explain the presented behaviour, to my mind.

Hi!

I am the one who experienced it and consequently showed it to Peter Reinhold.

Its perhaps noting that the error is appearing without calculations in the parameters.

SELECT ROUND(72.5,0) gives 72

Which was where I started experiencing it.

It seems its is always rounding down when given a X.5 number directly. But that changes when there is math in the parameter... sometimes..
SELECT ROUND(1.1*75,0) rounds down
SELECT ROUND(1.1*25,0) rounds up

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

See http://dev.mysql.com/doc/mysql/en/problems-with-float.html

It is documented behaviour of all the versions up to 5.0.3. Not a bug. Although, many times duplicated complain.