Unusual values with PHP/MYSQL floats

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?

score 0 · Accepted Answer · answered Sep 28 '13 at 00:54

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.

You're right about it being a duplicate. Thanks for the pointer! — Anthony, Sep 28 '13 at 01:00

Unusual values with PHP/MYSQL floats

1 Answers1