MySQL floating point rounding error

Question

Internally, what is going on with the floating point representation on the right to get false?

mysql> SELECT 3.1415 + 0.9585 = 4.1, 3.1415E0 + 0.9585E0 = 4.1E0;
+-----------------------+-----------------------------+
| 3.1415 + 0.9585 = 4.1 | 3.1415E0 + 0.9585E0 = 4.1E0 |
+-----------------------+-----------------------------+
|                     1 |                           0 |
+-----------------------+-----------------------------+

edit Conclusion: Don't use Float or Double for precision math. Use Decimal.

There should be a mandatory floating point quiz to pass before being allowed to post a question. — recursive
– recursive, Commented Nov 19, 2009 at 21:30
They're called "floats" because the bodies of programmers who use them where precision is required are often found floating in the harbor after the application goes into production. — Larry Lustig
– Larry Lustig, Commented Nov 25, 2009 at 15:03

ty812 · Accepted Answer · 2009-11-19 21:11:42Z

2

First of all, you should never use = with floating points, as you simply cannot predict if no rounding errors occur. Always use < or >, dependent on what you want to achieve.

Second: Nothing is wrong with either formula. Internally, both variants get encoded in their variables to the nearest possible binary representation, which slightly differs from the value you set.

One digit before the decimal point is standard, so all following digits are used. On the right side, you specifically define where the comma occurs, thus "wasting" a certain part at the end of the binary representation of your variable - which leads to rounding errors (in this specific case).

answered Nov 19, 2009 at 21:11

ty812

3,33322 silver badges37 bronze badges

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

Comments

NDM · Accepted Answer · 2009-11-19 21:10:11Z

1

floating points like FLOAT or DOUBLE are always inprecise because they're an approximity, not an exact value, you should not use them for calculations. Use the DECIMAL data type instead.

answered Nov 19, 2009 at 21:10

NDM

6,8483 gold badges42 silver badges55 bronze badges

1 Comment

ty812 Over a year ago

There are valid uses for FLOAT that cannot be satisfied with DECIMAL, however.

Abel · Accepted Answer · 2009-11-19 21:16:32Z

MySQL, just as almost any computer implementation that uses floating point arithmetic, employs the IEEE-754 standard. This is binary floating point.

Just as it is impossible to write 1/3 in decimal, it's impossible to write infinitely many digits in binary floating point (given that computers are restricted in time and space). A float uses 32 bits, a double uses 64 bits, mathematically, that's very little space for infinite many fractions.

The Wikipedia article above is a good read about floating point. In general, when dealing with computer match, it is imperative to know a bit how computers use limited storage for unlimited fractions.

Exact decimals also exist, but are limited to a fixed range (i.e. no high exponent). In MySQL you can use DECIMAL for fixed point numbers.

wallyk · Accepted Answer · 2009-11-19 21:26:47Z

In this, it was lucky that the first one compared exactly:

SELECT 3.1415 + 0.9585 = 4.1, 3.1415E0 + 0.9585E0 = 4.1E0;

Test floating points for near equality in this way:

mysql> SELECT abs(3.1415 + 0.9585 - 4.1) < 0.0001, abs(3.1415E0 + 0.9585E0 - 4.1E0) < 0.0001;
+-------------------------------------+-------------------------------------------+
| abs(3.1415 + 0.9585 - 4.1) < 0.0001 | abs(3.1415E0 + 0.9585E0 - 4.1E0) < 0.0001 |
+-------------------------------------+-------------------------------------------+
|                                   1 |                                         1 |
+-------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

Choose the difference (where I've used 0.0001) appropriately to the values involved.

Collectives™ on Stack Overflow

MySQL floating point rounding error

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related