Is this a malfunction of the ROUND function in MariaDB?

user_6843253 0
Viewed 192

In a project I am working on, I somewhat found myself doing the same computation at back end and front end. The computation is rounding off the product of a ratio of two numbers and another number i.e. the expression to round of is of the form

x / y * n

In front end I am using JavaScript and back end I am using MariaDB. With x=41, y=60 and n=30, the front end gives me 21 which is correct, because

41 / 60 * 30

is 20.5 but MariaDB's implementation of ROUND() function gives me 20 when I pass to it this expression!

ROUND(41/60*30,0)

even though it (MariaDB) evaluates

41 / 60 * 30

to the correct result 20.5000

If I pass 20.5 (20.5000) to MariaDB's ROUND() function, the function gives back the correct result 21! I have summarised this mystery (to me at least) with the following SQL statement

SELECT 41/60*30, ROUND(41/60*30,0), ROUND(20.5,0), ROUND(20.5000,0)

whose output is below...

enter image description here

Is something wrong with the implementation of ROUND() function in MariaDB?

mariadb backend rdbms rounding mysql
2 Answers

This is a floating point precision issue; if you were to

SELECT CAST(41/60*30 AS DECIMAL(22,20))

You would get:

20.49999999000000000000

So when it gets rounded, you get 20. You can work around this by casting to a DECIMAL with less precision (e.g. (5,3)) or double rounding; for example:

SELECT ROUND(CAST(41/60*30 AS DECIMAL(5, 3))) AS crnd, ROUND(ROUND(41/60*30,3)) AS drnd

Output:

crnd    drnd
21      21

Note that you may get more precise results with floating point if you multiply before dividing.

user_94737640

It is true like all are pointing out that

this is a floating point precision issue

I may have found a shorter/simpler (not sure if works for all situations) workaround than the one proposed by Nick. We only cast the ratio to float and that is all... thus

ROUND(CAST(41/60 AS FLOAT)*30 ,0)

this gives the correct result

21

But still I wonder why ROUND() gives 21 and 20 with the 'same' input. Can someone give us the possible reason?

user_68432530
Related