SQL Server “An invalid floating point operation oc

问题:

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:

The query:

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

The fix:

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.

回答1:

SQL Server does not perform short circuit evaluation (i.e. should not be relied upon). It's a fairly well known problem.

Ref:

• Don’t depend on expression short circuiting in T-SQL (not even with CASE)

• Short Circuit

• Short-Circuit Evaluation

• Is the SQL WHERE clause short-circuit evaluated?

回答2:

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)

---

Original Answer

How about modifying your sum function as shown below?

sum(case ID2 when 0 then null else log(convert(float, ID2)) end)