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

Question

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

spencer7593 · Accepted Answer · 2012-08-09 16:55:00Z

3

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

edited Aug 9, 2012 at 16:55

answered Aug 9, 2012 at 16:16

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barmar Over a year ago

Actually, 5.0 (and any other integer) can be represented exactly in floating point. The problem is 0.0025.

Barmar Over a year ago

But you're wrong that 5.0 and 4.9999888419121 are represented the same. The problem is because the representation of .0025 is actually a little smaller than .0025, so when you multiple 2000 by this you get something a little smaller than 5.0.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related