MySQL round weird bug

Question

I'm facing a really weird ¿bug? on mysql+php right now. Is a simple select, in the following example i'm using multiple fields to try to explain my problem:

• "field" is 11.5
• $phpvar is 1.15

MySQL query:

select round(field * " . $phpvar . " ,2) as a1, 
       round(field * 1.15 ,2) as a2, 
       round(11.5 * " . $phpvar . " ,2) as a3, 
       round(11.5 * 1.15 ,2) as a4, 
       field * " . $phpvar . " as a5 
from ...

ok, i'm trying to get 13.23. "field" * $phpvar = 13.225, so using round(13.225,2) I should get 13.23, right? well, yes and no.

query results:

• a1 [round(field * " . $phpvar . " ,2)] => 13.22
• a2 [round(field * 1.15 ,2)] => 13.22
• a3 [round(11.5 * " . $phpvar . " ,2)] => 13.23
• a4 [round(11.5 * 1.15 ,2)] => 13.23
• a5 [field * " . $phpvar . "] => 13.225 (without round)

what am I missing? how is it possible, when it comes to use "field", my result gets a fake round?

Answer 1

The problem is how the DOUBLE and FLOAT values are stored.

It is possible (and probable) that values like 11.5 or 22.475 coul be stored in approximated values like 11.499999999999~ or 22.475000000000000001 thus some calculations or roundings could lead to incorrect results.

It is always better to store float values into a DECIMAL coulmn type where the value is stored exactly with all the decimal digits and is not approximated.