2

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?

Nick
  • 138,499
  • 22
  • 57
  • 95
owino
  • 151
  • 1
  • 3
  • 11
  • 1
    Rounding float/double 0.5 is always a lottery due to non-precise value (+- 1 in last digit). – Akina Apr 19 '21 at 08:47
  • 1
    Everyone who ever computes with floating point math should read a textbook introduction to computing with floating point math. – philipxy Apr 19 '21 at 09:37

2 Answers2

2

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.

Nick
  • 138,499
  • 22
  • 57
  • 95
0

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?

owino
  • 151
  • 1
  • 3
  • 11
  • 1
    I think the point is that it's not the same input. If you `CAST(CAST(41/60 AS float)*30 AS DECIMAL(22, 20))` you get `20.50000011920929000000`, so that is why it rounds up to 21. I think you'll find the cast to decimal/double rounding method to be more reliable. For example https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=e4df8feb2e7ac537f61fd5ce3d3b54b2 – Nick Apr 19 '21 at 12:08
  • True! My workaround is not universal. I take your advice and so I go with 'cast to decimal/double'. Thank you so much. – owino Apr 19 '21 at 12:21