Understanding the Differences Between FLOAT and DECIMAL Data Types in MySQL: A Case Study on Approximation Errors

A few years ago, during one of the dev testing, a fellow engineer complained that the total amount paid is not matching in API response. However, if we manually add the values in DB, it adds up to the correct amount.

What could have gone wrong?

As we went down the debugging path, we had the following few intuitions:

a. The error could have crept in during adding in application code.

b. The error could have been in data type conversion from mysql to java.

c. The error could have been in MySQL query.

Debugging

When we tried running the hibernate-generated MySQL query through the MySQL command line, we found the value differed from what we would get by manually adding it.

Let’s try to understand through some hands-on example

I will recommend that you do this exercise as you read along the tutorial.

Let’s create a table called “Product” with two columns, “price_float” of type FLOAT and “price_decimal” of type DECIMAL:

CREATE TABLE Product (
  id INT,
  price_float FLOAT(10, 2),
  price_decimal DECIMAL(10, 2)
);

Now, let’s insert a row into the table with a price value of 162295.98:

INSERT INTO Product (id, price_float, price_decimal) VALUES (1, 162295.98, 162295.98);

If we select the values from the table, we can observe the difference:

SELECT price_float, price_decimal FROM Product;

The result would be:

+-------------+----------------+
| price_float | price_decimal  |
+-------------+----------------+
|    162295.98|   162295.98    |
+-------------+----------------+

In this example, notice that both “price_float” and “price_decimal” have the same value of 162295.98. However, when storing the value in the “price_float” column, there can be slight approximation due to the nature of the float data type.

To further illustrate this point, consider the following update:

UPDATE Product SET price_float = price_float + 0.01;
UPDATE Product SET price_decimal = price_decimal + 0.01;

If we select the values again, we will see the difference:

SELECT price_float, price_decimal FROM Product;

The result would be as follow:

+-------------+----------------+
| price_float | price_decimal  |
+-------------+----------------+
|    162295.98|     162295.99  |
+-------------+----------------+

Here, the “price_float” column has remained at 162295.98 due to the floating-point approximation, while the “price_decimal” column, which uses the DECIMAL data type, changed at 162295.99.

Uh! Okay! Some simple English please.

What do we mean by floating point approximation?

The “price_float” column uses the FLOAT data type, which is a floating-point approximation. Floating-point numbers are represented in binary format and have limited precision. The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values. This means that the binary representation may introduce small rounding errors or approximations when storing decimal values.

In the example, the value 0.1 is stored in the “price_float” column. However, due to the limited precision of the FLOAT data type, the actual binary representation of 0.1 may not be exact. When performing calculations or operations involving the FLOAT value, these small approximation errors can accumulate and lead to slightly different results compared to the original decimal value.

On the other hand, the “price_decimal” column uses the DECIMAL data type. DECIMAL allows for exact decimal arithmetic and stores decimal values as strings of decimal digits. It does not suffer from the same approximation issues as the FLOAT data type. Therefore, the value stored in the “price_decimal” column remains unchanged and exact.

Bill Karwin is the author of the book on SQL antipatterns

Suggestions

Be cautious with equality comparisons: Due to the potential for rounding errors, it is generally not recommended to perform exact equality comparisons with float values. Instead, use range-based comparisons or define an acceptable tolerance level for comparisons. Don’t use float for currency.
Use decimal data types for precise calculations: If precise decimal calculations are critical, consider using DECIMAL data types instead of floats. DECIMAL data types store decimal values exactly and allow for precise arithmetic operations without the approximation issues of float types.
Handle conversions carefully: When converting between float and other data types, be cautious about the potential loss of precision. Keep in mind that converting to a lower precision data type may result in data loss or rounding errors.

Reference

People illustrations by Storyset