query calculation error

Question

I've seen some errors in my life, but this one caught me by surprise the most.

I have a float column containing 0.02.

Now i'm trying this query :

select rma.*,120.0/60.0*rma.amount as reward

Now math tells us the result will be 0.04, But suprisingly enough MySql (Version 5.5.28) claims otherwise, and says it's :

0.03999999910593033

I've also tried without the .0 with the numbers, and using braces, still same result.

Now the same results happens with all cases of uneven numbers. for example 4.1 will be 8.199999809265137

Anyone knows why that happens?

When you say the column contains 0.02 are you sure that is the value or could it possibly be a rounded representation of the actual number stored? Is the column itself a calculated value? — Eric J. Price, Jan 10 '13 at 15:29
It's ok the answers below both solved and explain the problem. Thank you for your time! — eric.itzhak, Jan 10 '13 at 15:34

score 0 · Accepted Answer · answered Jan 10 '13 at 15:25

0

Try to CAST it like so:

select rma.*, CAST((120.0 / 60.0 * rma.amount) AS DECIMAL(10,2)) as reward

answered Jan 10 '13 at 15:25

Mahmoud Gamal

Well that did solve it. Any idea why that happend? why do you have to cast it? can you provide a short explination please. – eric.itzhak Jan 10 '13 at 15:26
@eric.itzhak - I am not sure how MySQL treats a new created column from a multiplied columns, it seems that it doesn't perceive a specific precision for it. So `DECIMAL(10, 2)` will give you in two digit precision. – Mahmoud Gamal Jan 10 '13 at 15:31

1 Answers1