SQL Server “An invalid floating point operation occurred”
This is not really a problem because I solved it. But, I wanted to share something that ate my brain today. So, run this query and check it out:
select 2 as ID1,0 as ID2 into #TEMP insert into #TEMP (ID1,ID2) values (2,1),(2,2),(2,0) select case when min(case when ID1=2 and ID2=0 then 0 else 1 end)=0 then 0 else sum(log(ID2)) end from #TEMP
select case when min(case when ID1=2 and ID2=0 then 0 else 1 end)=0 then 0 else sum(log(case when ID1=2 and ID2<>0 then ID2 else 1 end)) end from #TEMP
My query was larger and more difficult to debug, but what do you say about the plan that MSSQL is making and the fact that it gets it wrong with this query? How can it be modified to work except my little fix that I showed before? I am guessing that computing scalars before the query would make things slow if the scalars are not easy to compute and we compute for all values.
2 Solutions collect form web for “SQL Server “An invalid floating point operation occurred””
SQL Server does not perform short circuit evaluation (i.e. should not be relied upon). It’s a fairly well known problem.
Don’t depend on expression short circuiting in T-SQL (not even with CASE)
Is the SQL WHERE clause short-circuit evaluated?
EDIT: I misunderstood the question with my original answer.
I supposed you could add a where clause, as shown below. Side note: this query might benefit from an index on (ID1, ID2).
select sum(log(convert(float, ID2))) from #TEMP where -- exclude all records with ID1 = 2 if there are any records with ID1 = 2 and ID2 = 0 not exists ( select 1 from #TEMP NoZeros where NoZeros.ID1 = #TEMP.ID1 and NoZeros.ID2 = 0 )
Update: Just in case performance is a concern, I got fairly comparable performance from this query after adding the following indexes:
create index ix_TEMP_ID1_ID2 on #TEMP (ID1, ID2) create index ix_TEMP_ID2 on #TEMP (ID2) include (ID1)
How about modifying your
sum function as shown below?
sum(case ID2 when 0 then null else log(convert(float, ID2)) end)