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:

The query:

  • SQL Server Linked Server Example Query
  • SQL Server: What does 1 ++ 2 mean?
  • Failed to update .mdf database because the database is read-only (Windows application)
  • Foreign Key Error on MySQL CREATE TABLE statement (err: 150)
  • SQL - union two tables, each having a few unique columns
  • “IN” clause limitation in Sql Server
  • select 2 as ID1,0 as ID2 into #TEMP
    insert into #TEMP (ID1,ID2)
    values (2,1),(2,2),(2,0)
      case when min(case when ID1=2 and ID2=0 then 0
                         else 1
                    end)=0 then 0
           else sum(log(ID2))
    from #TEMP

    The fix:

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

    • Short Circuit

    • Short-Circuit Evaluation

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

      sum(log(convert(float, ID2)))
    from #TEMP
        -- exclude all records with ID1 = 2 if there are any records with ID1 = 2 and ID2 = 0
        not exists (
            select 1
            from #TEMP NoZeros
                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)
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.