Invalid floating point operation error when counting logarithm in SQL Server 2008

Question

In Microsoft SQL Server 2008, I have a table, say myTable, containing about 600k rows (actually, it is a result of joining several other tables, but i suppose this is not important). One of its columns, say value is of type numeric(6,2).

The simple query SELECT value FROM myTable ORDER BY value returns of course about 600k numbers, starting with 1.01 (i.e. the lowest) and ending with 70.00 (highest); no NULLs or other values.

Please notice, that all these values are numeric and positive. However, when calling SELECT LOG(value) FROM myTable, i obtain an error message "An invalid floating point operation occurred".

This error always appears after about 3 minutes of the query running. When copying the 600k values to Excel and counting their LN(), there is absolutely no problem.

I have tried converting value to real or float, which did not help at all. Finally I found a workaround: SELECT LOG(CASE WHEN value>0 THEN value ELSE 1 END) FROM myTable. This works. But why, when all the values are positive? I have tried to take the result and compare the logarithms with those counted by Excel - they are all the same (only differences of the order 10^(-15) or smaller occured in some rows, which is almost surely given by different accuracy). That means that the condition in the CASE statement is always true, I suppose.

Does anyone have any idea why this error occurs? Any help appreciated. Thanks.

Answer 1

You can identify the specific value that's causing the prob;

declare @f numeric(6,2), @r float
begin try select
    @f = value, @r = LOG(value) 
from mytable
end try begin catch
    select error_message(),'value=',@f
end catch

Answer 2

You would get this error - "An invalid floating point operation occurred" when you do LOG(0). The value of LOG(zero) is indeterminate in the world of Maths, hence the error.

Cheers.