Mysql calculation issues: 1+1=1.999999999

Question

The problem is this, when I add two or more doubles from a table to a view, instead of giving me the right results, it adds a about ten or so more digits. For example 0.5+1.5=1.99999999998 or 5.5+8.5=14.0000000001. Any ideas? (I know this is sort of n00b question and I remember having to deal with stuff like that in the exams at 9th grade, but I just cannot remember how I did it back then :P)

Answer 1

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_format

You can format numbers this way if thats what your after?

Answer 2

Adding 1 and 1 as floats or doubles should not result in anything but 2.

I find it hard to believe that 0.5 + 1.5 comes out to anything but 2.

All these numbers can be represented correctly in binary floating point.

I hate to say I don't believe your examples, but I don't. :-)

However, I do believe that you might have trouble with a number such as 1.1.

Why? Because 1/10 turns out to be a repeating decimal in binary.

The problem comes when trying to convert floating point numbers between decimal and binary representations. Some numbers make the trip fine, but others are only approximated.

---

However, if your examples really work like that, I have no idea what is happening, and I'd love to know.