0

recently when we have migrated to SQL Server 2012, we have seen a change in how it evaluate the logical expression in the having clause .

I created a simple query that allows you to see the problem :

SELECT sum (0)
HAVING sum (0) > 0 AND 1/sum (0)> 0

The objective is to test the value of a division . Obviously , before any division , it is necessary to check whether the divisor is not equal to 0, that is what I do with sum ( 0 )> 0 . Except that , it generates the following error:

Msg 8134 , Level 16 , State 1 , Line 1 Division by zero.

SQL Server has evaluated all expressions , even if the first is wrong . In our previous SQL Server 2000 version, this worked well. On the other hand , a similar test in the WHERE clause does not generate an error.

The problem can be reproduced with other conditions such as:

select ISNUMERIC(num)
from (select '12J' as num) t
group by num
having ISNUMERIC(num)=1 
and max(cast(num as int)) >0

Which generates : Server : Msg 245, Level 16 , State 1 , Line 1 Failed to convert the varchar value '12J ' to data type int .

Question: is this normal? is there a way to tell it not to evaluate other expressions if the first is false ?

Thank you for your insights !

user3082313
  • 1
  • did you try using `case statements` > – vhadalgi Dec 09 '13 at 10:19
  • I can modify my query to solve every case I could have, but I would like to understand why SQL Server act like this, and if i can change this behaviour. – user3082313 Dec 09 '13 at 10:25
  • It was **never** guaranteed not to evaluate all expressions (either in `HAVING`, in the `WHERE` clause, or anywhere else - you can even have a `WHERE` clause that should eliminate 0s and a divide by zero error from an expression in `SELECT`). That it "worked" before was mere luck. – Damien_The_Unbeliever Dec 09 '13 at 10:29
  • Adding a where clause is not a solution. I can have -1 and 1 in my table, none of them equals to 0, but the sum = 0. And i don't want to change my query, it's only for example, i want to understand why it doesnt work – user3082313 Dec 09 '13 at 10:40
  • 1
    I wasn't suggesting to add a where clause. I was pointing out that SQL Server has always been free to evaluate expressions within a single query in any order it chooses to - even though, frustratingly, that makes it incredibly difficult to guard against things like this divide by zero. The bulletproof way to do it is to insert the results into a temp table/table variable whilst excluding zeros, and then run a separate query from that temp table that then uses the non-zero values for the rest of the query. – Damien_The_Unbeliever Dec 09 '13 at 10:59
  • it seems that it is the optimizer that does not respect the order of the predicates in this version, all queries must be adapted :/ – user3082313 Dec 09 '13 at 13:15
  • The optimizer has **never**, officially, used the order of predicates to do any short circuiting. The fact that it **seemed** to work on 2000. was a *lucky* accident. – Damien_The_Unbeliever Dec 09 '13 at 19:27

0 Answers0