Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server LOG: "An invalid floating point operation occurred" - even though input is within range

Tags:

sql

sql-server

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...

like image 554
Cameron Avatar asked May 19 '14 17:05

Cameron


1 Answers

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.

like image 179
Anthony Faull Avatar answered Sep 22 '22 14:09

Anthony Faull