On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

### A.5.8. Problems with Floating-Point Comparisons

Note that the following section is relevant primarily to working with `DOUBLE` and `FLOAT` columns, due to the inexact nature of floating point numbers. MySQL performs `DECIMAL` operations with a precision of 64 decimal digits, which should solve most common inaccuracy problems when it comes to `DECIMAL` columns.

Floating-point numbers sometimes cause confusion because they are not stored as exact values inside computer architecture. What you can see on the screen usually is not the exact value of the number. The data types `FLOAT` and `DOUBLE` are such. `DECIMAL` columns store values with exact precision because they are represented as strings.

The following example demonstrates the problem using `DOUBLE`:

```mysql> `CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);`
mysql> `INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),`
-> `(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),`
-> `(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),`
-> `(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),`
-> `(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),`
-> `(6, 0.00, 0.00), (6, -51.40, 0.00);`

mysql> `SELECT i, SUM(d1) AS a, SUM(d2) AS b`
-> `FROM t1 GROUP BY i HAVING a <> b;`

+------+-------+------+
| i    | a     | b    |
+------+-------+------+
|    1 |  21.4 | 21.4 |
|    2 |  76.8 | 76.8 |
|    3 |   7.4 |  7.4 |
|    4 |  15.4 | 15.4 |
|    5 |   7.2 |  7.2 |
|    6 | -51.4 |    0 |
+------+-------+------+
```

The result is correct. Although the first five records look like they shouldn't pass the comparison test (the values of `a` and `b` do not appear to be different), they may do so because the difference between the numbers shows up around the tenth decimal or so, depending on computer architecture.

If columns `d1` and `d2` had been defined as `DECIMAL` rather than `DOUBLE`, the result of the `SELECT` query would have contained only one row — the last one shown above.

 Published under the terms of the GNU General Public License Design by Interspire