CODEWITHSUNDEEP
mysqlfloating-pointroundingprecision
Andrews B Anthony
Andrews B Anthony

Reputation: 1381

MySQL rounding behaviour does not work as intended ..?

MySQL rounding behaviour terms can be found here.. Where they promise that the rounding of the Approximate(FLOAT, DOUBLE) values will be HALF_EVEN. But when tried the same, I could not produce what was said in the document. It clearly works as HALF_UP for DOUBLE datatypes. Is there anything I am missing here ..?

CREATE TABLE `doubleValues` (
  `val` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select* from doubleValues;
+-------+
| val   |
+-------+
| 1.245 |
| 0.555 |
+-------+

select round(val,2) from doubleValues;
+--------------+
| round(val,2) |
+--------------+
|         1.25 |
|         0.56 |
+--------------+
2 rows in set (0.00 sec)

Upvotes: 0

Views: 283

Answers (2)

Eric Postpischil
Eric Postpischil

Reputation: 222650

The numbers 1.245 and 0.555 do not exist in binary floating-point; they are not exactly representable. What you are seeing as “1.245” and “0.555” are likely 1.24500000000000010658141036401502788066864013671875 and 0.55500000000000004884981308350688777863979339599609375. These are the closest values to 1.245 and 0.555 in 64-bit binary IEEE-754 floating-point. (Although these are long when expressed in decimal, they are short in binary.)

When these are rounded to two decimal points, the part that has to be rounded is slightly above ½ at the rounding position. The first is .500000000000010658141036401502788066864013671875, and the second is .500000000000004884981308350688777863979339599609375. Therefore, the rule about what to do when rounding a fraction that is exactly ½ does not come into play. These are both above ½, so they are rounded up.

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

From the docs you linked your request to:

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 other words: the rounding method is undefined.

And anyway: Why use approximate types at all? Do you need numbers with that many digits? I can hardly imagine a database where that would be the case. Some astronomy database maybe.

Upvotes: 2

Related Questions