MySQL Round() function for decimal stored in string

Question

MySQL version is 5.66.22

When I use ROUND function for the decimal numbers stored in varchar I see strange behavior for all .5 numbers

Select round(0.5)
1

Select round('0.5')
0

Select round('26.5' + 0.00)
26

but

Select round(1.5)
2

Select round('1.5')
2

Select round(0.55, 1)
0.6

Select round('0.55', 1)
0.6

I checked ROUND function in Oracle DB (12c) it works as expected

Select round('0.5') from dual 
1

Select round(0.5) from dual 
1

Anybody knows how to explain that ?

The described mysql round() function behaviour causing "rounding" issue in the application. For fixing the issue I use:

Select round (CAST('0.5' AS DECIMAL(10,2)))
1

I understand that storing numbers in the varchar is bad design but this application was written long time ago and no one wants to refactor the code now

Answer 1

Interesting. The behavior can be explained as follows:

1) MySQL converts strings to floating-point values when used in numeric context (ref):

CREATE TABLE test AS (
    SELECT 0.5, '0.5' * 1 AS str_to_numeric
);

DESCRIBE test;

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| 0.5            | decimal(2,1) | NO   |     | 0.0     |       |
| str_to_numeric | double       | NO   |     | 0       |       |
+----------------+--------------+------+-----+---------+-------+

2) As described in the manual:

Rounding Behavior

The ROUND() function rounds differently depending on whether its argument is exact or approximate:

• For exact-value numbers, ROUND() uses the “round half up” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

• 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.

Here are some tests illustrating the final behavior of ROUND function:

CREATE TABLE test(
   fix DECIMAL(10,2),
   arb DOUBLE
);

INSERT INTO test(fix, arb) VALUES
    (0.5, 0.5),
    (1.5, 1.5),
    (2.5, 2.5),
    (3.5, 3.5);

SELECT fix, ROUND(fix) fix_roundex, arb, ROUND(arb) arb_rounded
FROM test

+------+-------------+------+-------------+
| fix  | fix_roundex | arb  | arb_rounded |
+------+-------------+------+-------------+
| 0.50 |           1 |  0.5 |           0 |
| 1.50 |           2 |  1.5 |           2 |
| 2.50 |           3 |  2.5 |           2 |
| 3.50 |           4 |  3.5 |           4 |
+------+-------------+------+-------------+

Your solution, explicitly casting numeric strings to DECIMAL, is correct.