How to fix "domain error" in SQL Server 2005 when using LOG() function to get product of set

Question

I have a inline select statement to calculate the product of the set of values.

Since SQL Server 2005 doesn't have a built in Product aggregate function, I am using LOG/EXP to get it.

My select statement is:

(select exp(sum(log(value))) from table where value > 0)

Unfortunately I keep getting the following error:

Msg 3623, Level 16, State 1, Line 1
A domain error occurred.

I've ensured that none of the values are zero or negative so I'm not really sure why this error is occurring. Does anyone have any ideas?

Answer 1

One of the features of the query planner introduced in SQL 2005 is that, in some circumstances where the table statistics indicate it will be more efficient, the WHERE clause of a statement will be processed after the SELECT clause. (I can't find the Books On-Line reference for this right now).

I suspect this is what is happening here. You either need to exclude the rows where value = 0 before carrying out the calculation - the most reliable way being to store the rows you need in a temporary (#) table - or to modify your query to handle zero internally:

SELECT EXP(SUM(LOG(ISNULL(NULLIF(VALUE,0),1)))) AS result 
FROM [table]

The NULLIF\ISNULL pair I have added to your query substitutes 1 for 0 - I think this will work, but you will need to test it on your data.