I'm getting the error message
Msg 3623, Level 16, State 1, Line 25
An invalid floating point operation occurred.
From SQL Server 11.0.2100 (on an AWS RDS instance) for the following query:
SELECT SUM(LOG(col + 1)) FROM MyTable;
All values for col are within the range -0.1 and 0.1.
If I remove the SUM from the query, it runs fine, which implies the inputs to LOG are correct:
SELECT LOG(col + 1) FROM MyTable;
Also, if I select the above into a temp table, then SUM over the values in the temp table, that also works fine, which implies there's nothing wrong with the SUM:
SELECT LOG(col + 1) thelog INTO #x FROM MyTable;
SELECT SUM(thelog) FROM #x;
DROP TABLE #x;
It's only when I run SUM and LOG together that I get an issue.
Why is this happening? Is it possible that SQL Server is somehow rearranging the +1 to be outside of the LOG, so that the LOG input is out of range?
Note: Putting a CASE WHEN col > -1 inside the query also fixes it, but this shouldn't be required since all values for col + 1 are within range. I'm wondering what the underlying reason for this might be...
Floating point values are notorious for rounding issues because they are stored as binary fractions which often don't have an exact decimal equivalent. I think what's happening here is that one of your values is extremely close to -1 (e.g. -0.99999999999999999999), and when you add 1 it could sometimes be rounded to 0. The LOG function is undefined for an input of 0, so the result is An invalid floating point operation occurred
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With