I received a support email yesterday for a system that I had written a few years ago. It seems that they were running year-end reporting, and total combined profit numbers were coming out in the quadrillions. It would have been a nice profit to report, but alas, not an accurate one.
When a number is magnitudes larger than what it should be, and there is division involved in a calculation somewhere, then that smells of floating point math issues. In this case, there was a SUM() being performed on a series of numbers, and instead of the result being a true zero, floating point math resulted in a number really, really, really close to zero, but not quite.
Note to the observant reader: If this sum came out to be a true zero, then the division would have been skipped by the CASE logic that is in place.
The worst part about troubleshooting systems that you wrote years before is trying to remember why certain design decisions came to be. In this case, a Net Sales column was stored as a FLOAT data type. If memory serves me correctly, this was the default type that DTS created when the data was migrated over from MSAccess.
Yeah, sure, a better data type in this case would have been the SQL Server DECIMAL type, which is an exact numeric value. But, for the most part, FLOAT had served its purpose quite nicely over the past several years, so I left it alone and wrapped the SUM() operation within a ROUND() function in order to reduce the number of possible side effects from changing the data type altogether.
The funniest thing is that this particular series of numbers were merely pennies in value, and were not even representative of actual sales! These numbers were very minor accounting adjustments made on paper to one account. But the resultant error came out in the quadrillions of dollars!
To demonstrate this particular case, here is a SUM() of those exact numbers in both a FLOAT and a DECIMAL.
Note: This sample uses the new SQL Server 2008 Row Constructor syntax to perform the INSERT as one statement, instead of individual INSERTs for each row.
declare @t table (f float, d decimal(18,2))
INSERT INTO @t VALUES
( 0.05, 0.05),
(-0.02, -0.02),
(-0.03, -0.03)
SELECT SUM(f) AS f,
SUM(d) AS d
FROM @t
Results:
| f |
d |
| 3.46944695195361E-18 |
0.00 |