Unusual values with PHP/MYSQL floats [duplicate]

Question

I just hit an odd issue. I have the following tables in MySQL:

table:deposits
user_id    amount
1          0.50

table:withdrawls
user_id    amount
1          0.01
1          0.01
1          0.01

To get the balance, I run this:

SELECT (IFNULL((SELECT sum(amount) FROM deposits WHERE user_id = 1),0) - IFNULL((SELECT sum(amount) FROM withdrawls WHERE user_id = 1),0) ) as balance

I then return it as

return (float) $row['balance']

For some weird reason, the result is float(0.47000000067055). Does anyone know why there is bizarre rounding?

Eric Postpischil · Accepted Answer · 2013-09-28 00:54:19Z

0

Floating-point arithmetic does not represent all real numbers exactly. 0.01 is converted to a representable value, which is, in essence, a binary numeral with a limited number of significant bits. Since 1/100 cannot be represented exactly in binary with a finite number of digits (the same way 1/3 in decimal requires an infinite number of digits: .3333…), the conversion to floating-point rounds the value.

That said, I do not see exactly how 0.47000000067055 is produced. Converting 0.01 to IEEE-754 64-bit binary floating-point with correct rounding to nearest produces 0.01000000000000000020816681711721685132943093776702880859375. Subtracting that three times from .5, rounding each time, produces 0.4699999999999999733546474089962430298328399658203125. Subtracting three times the value from .5 produces the same value.

I suspect that your PHP implementation converted 0.01 to floating-point in a less than optimal way.

answered Sep 28, 2013 at 0:54

Eric Postpischil

233k15 gold badges198 silver badges379 bronze badges

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

1 Comment

Anthony Over a year ago

You're right about it being a duplicate. Thanks for the pointer!

Collectives™ on Stack Overflow

Unusual values with PHP/MYSQL floats [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related