A few years ago, during dev testing, a fellow engineer complained that the total amount paid in the API response is not matching the actual value. However, if we manually add the values in DB, it adds up to the correct amount.
What could have gone wrong?
As we started debugging, we had the following hypotheses:
The error could have crept in during the addition in the application code.
The error could have been in the data type conversion from MySQL to Java.
The error could have been in the MySQL query.
Debugging
When we tried running the hibernate-generated MySQL query through the MySQL command line, we discovered that the value differed from what we would get by manually adding it.
Let's try to understand through examples
We will recommend that you do this exercise as you read along.
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 a 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:
+-------------+----------------+
| 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.
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 162295.98 is stored in the price_float
column. However, due to the limited precision of the FLOAT data type, the actual binary representation of 162295.98 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.
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
Problems with Float: https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html
Floating Point Types: https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html
Storyset: People illustrations by Storyset