CODEWITHSUNDEEP
sqlsql-server
Cameron
Cameron

Reputation: 1047

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

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

Upvotes: 10

Views: 37432

Answers (3)

Hemal shah
Hemal shah

Reputation: 11

you can look at the article at the database. Guide As per article if you have negative value passed in LOG() function the you will get

An invalid floating point operation occurred.

An invalid floating point operation occurred.

Upvotes: 0

sudhansu63
sudhansu63

Reputation: 6190

SELECT LOG(0) -> An invalid floating point operation occurred.

You should handle the case for the input of LOG function. As LOG(0) is undefined.

Upvotes: 0

Anthony Faull
Anthony Faull

Reputation: 17987

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.

Upvotes: 11

Related Questions