Context: Get a report of costs per programmers hours and hour_rateYour 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. 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?"
ALTER TABLE Bugs ADD COLUMN hours FLOAT; ALTER TABLE Accounts ADD COLUMN hourly_rate FLOAT;
Antipattern: Using SQL FLOAT data typeMany 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 // not 1 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 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 FROM Accounts WHERE account_id = 123; #Returns: 59.95 SELECT hourly_rate * 1000000000 FROM Accounts WHERE account_id = 123;--Returns: 59950000762.939 SELECT * FROM Accounts WHERE hourly_rate = 59.95; --Result: empty set; no rows match.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. 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 NumericInstead of FLOAT, use the NUMERIC or DECIMAL SQL data types for fixed-precision fractional numbers. The advantage of NUMERIC and DECIMAL are that they store rational numbers without rounding, as the FLOAT data types do.
ALTER TABLE Bugs ADD COLUMN hours NUMERIC(9,2); ALTER TABLE Accounts ADD COLUMN hourly_rate NUMERIC(9,2); SELECT hourly_rate FROM Accounts WHERE hourly_rate = 59.95; --Returns: 59.95Note: Oracle uses the FLOAT data type to mean an exact scaled numeric, whereas the BINARY_FLOAT data type is an inexact numeric.