CODEWITHSUNDEEP
mysql
BrandonS
BrandonS

Reputation: 932

MySQL running query with a math function on float with decimal returning wrong result

Ok so I have a weird issue where I am running a query that in the select it is taking the result and multiplying that by 2000 i.e. 0.0025 * 2000 and giving me 4.9999888419121 or something when it should be 5. I have seen that there are issues with this but I have yet to find if there was a solution. I thought that math was like a basic computing function why would it fail in such a simple request as multiplication.

I am not sure that there is a fix for this, I really have been researching it but I figure that if anyone knew of one it would be here.

thanks again

example:

SELECT (table.rate * 2000) as per_ton ...

I have tried the + 0.0000 tricks and all without luck

Upvotes: 2

Views: 3406

Answers (1)

spencer7593
spencer7593

Reputation: 108400

This issue is not unique to MySQL. It's a common issue with IEEE floating point representation, which store approximations of decimal values. (The IEEE floating point types store exact base 2 representations.)

So, it's not a "failure" of a multiplication operation. The value you stored for rate was (apparently) decimal value of .0025. This value can't be represented EXACTLY in an IEEE floating point, so what gets stored is the closest possible approximation. When that approximate value is multiplied by 2000, the result is also an approximate value, which is then being converted back to decimal for display.

The "fix" is to use the DECIMAL datatype, rather than the floating point types, and have the multiplication operation be performed using "big decimal" operations, rather than floating point operations.

You can attempt to have that approximation reversed, by converting it back to decimal of specified precision, before you do the multiplication:

SELECT (CAST(table.rate AS DECIMAL(18,4) * 2000) AS per_ton ...

Or, you can try using the ROUND() function to trim off the digits of precision you don't need, e.g.

SELECT ROUND((table.rate * 2000),4) AS per_ton ...

That will get the result rounded up to 5.0000

The real "fix" though, is to define your columns with DECIMAL datatype, and AVOID using floating point columns at all.

This behavior of floating point values is well documented, albeit, at the end of the manual:

http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html

Upvotes: 3

Related Questions