Your boss asks you to produce a report of the cost of programmer time, based on the total work to fix each bug.
You decide to define the new columns as FLOAT, because this data type supports fractional values.
ALTER TABLE Bugs ADD COLUMN hours FLOAT;
ALTER TABLE Accounts ADD COLUMN hourly_rate FLOAT;
The next day, your boss shows up in your office. "I did the calculation by hand for comparison, and your report is inaccurate—slightly, by only a few dollars.
How do you explain this?"
Many programmers naturally use the SQL FLOAT data type everywhere they need fractional numeric data, because they are accustomed
to programming with the float data type.
The FLOAT data type in SQL, like float in most programming languages, encodes a real number in a binary format according to the IEEE 754
Many programmers are not aware of a characteristic of this floatingpoint format: not all values you can describe in decimal can be stored
in binary. Out of necessity, some numbers must be rounded to a value that is very close.
Rational numbers such as one-third (0.333....) cannot be represented in decimal, because you would need to write an infinite number of digits.
The compromise is to use finite precision, choosing a numeric value as close as possible to the original value, for example 0.333
However, this means that the value isn’t exactly the same number we intended.
0.333 + 0.333 + 0.333 = 0.999
Even if we increase the precision, we still can’t add three of these approximations of one-third to get a true value of 1.0
0.333333 + 0.333333 + 0.333333 = 0.999999
You might think this is OK, because you can’t really type a number with infinite digits anyway, but nfortunately is not.
SELECT hourly_rate FROM Accounts WHERE account_id = 123; #Returns: 59.95
But the actual value stored in the FLOAT column may not be exactly this value. If you magnify the value by a billion, you see the discrepancy:
SELECT hourly_rate * 1000000000 FROM Accounts WHERE account_id = 123;--Returns: 59950000762.939
The value is within 1 ten-millionth, which is close enough for many calculations.
However, it’s not close enough for some other kinds of calculations to be accurate.
SELECT * FROM Accounts WHERE hourly_rate = 59.95; --Result: empty set; no rows match.
The cumulative impact of inexact floating-point numbers is even more severe when calculating the aggregate product of a set of numbers
instead of the sum. If you multiply a value of one by 0.999 a thousand times in succession, you get a result of about 0.3677.
Using exact values in financial applications is important.
How to Recognize the Antipattern
Virtually any use of FLOAT, REAL, or DOUBLE PRECISION data types is suspect.
It seems natural to use FLOAT data types in SQL, because it shares its name with a data type found in most programming languages.
Solution: Use Numeric
Instead of FLOAT, use the NUMERIC or DECIMAL SQL data types for fixed-precision fractional numbers.
ALTER TABLE Bugs ADD COLUMN hours NUMERIC(9,2);
ALTER TABLE Accounts ADD COLUMN hourly_rate NUMERIC(9,2);
The advantage of NUMERIC and DECIMAL are that they store rational numbers without rounding, as the FLOAT data types do.
SELECT hourly_rate FROM Accounts WHERE hourly_rate = 59.95; --Returns: 59.95
Oracle uses the FLOAT data type to mean an exact scaled numeric, whereas the BINARY_FLOAT data type is an inexact numeric.