Bug #87081 round(25E-1)'s result is different from Oracle
Submitted: 15 Jul 2017 1:29 Modified: 17 Jul 2017 6:58
Reporter: Wang Yuming Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.18 OS:CentOS (6.7)
Assigned to: CPU Architecture:Any

[15 Jul 2017 1:29] Wang Yuming
Description:
MySQL's round(25E-1) result is 2, but Oracle's 3, I'm not sure is it a MySQL bug?

How to repeat:
mysql> select round(25E-1);
+--------------+
| round(25E-1) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)
[15 Jul 2017 6:45] MySQL Verification Team
Hello Wang Yuming,

Thank you for the report.

Thanks,
Umesh
[17 Jul 2017 6:58] Knut Anders Hatlen
I believe this is the intended behaviour, described in the documentation at https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_round. Specifically, it says: "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." In MySQL, the type of 25E-1 is DOUBLE, which is an approximate type, so rounding to the nearest even (2) is used.

Oracle Database behaves similarly for its approximate types. The documentation at https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm has the following example:

SELECT ROUND(1.5f), ROUND(2.5f) FROM DUAL;

ROUND(1.5F) ROUND(2.5F)
----------- -----------
   2.0E+000    2.0E+000

The difference between the two database systems in how the expression ROUND(25e-1) is handled, is probably because Oracle interprets 25e-1 as an exact numeric type, whereas MySQL interprets it as a floating-point value.