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: | |
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
[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.