MySQL Bugs: #6251: ROUND() Function does not always work correctly

Bug #6251	ROUND() Function does not always work correctly
Submitted:	25 Oct 2004 19:33	Modified:	25 Oct 2004 22:38
Reporter:	Chris Calender	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	4.0.20	OS:	Linux (Linux/RH 9)
Assigned to:		CPU Architecture:	Any

Description:
I seem to be having problems returning the expected results when using the mysql ROUND() function.

Rounding 3.565 to 2 decimal places I would expect to return 3.57 however using

mysql> SELECT ROUND(3.565, 2);
+-----------------+
| ROUND(3.565, 2) |
+-----------------+
|            3.56 |
+-----------------+
1 row in set (0.02 sec)

The above should return 3.57.

mysql> SELECT ROUND(3.575, 2);
+-----------------+
| ROUND(3.575, 2) |
+-----------------+
|            3.58 |
+-----------------+
1 row in set (0.00 sec)

This one works as expected returning 3.58.

I am using mysql version 4.0.20.  I have also heard of the problem occurring on version 3.23.54 as well.  Keep up the great work!!!

Have a great day,
Chris Calender

How to repeat:
It seems to happen when the second decimal place is a 6.

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 the documentation on Round: 

http://dev.mysql.com/doc/mysql/en/Mathematical_functions.html#IDX1397

[...]
Note that 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.