How to Short-Circuit SQL Where Clause

Question

I am trying to perform the following query in SQL server:

declare @queryWord as nvarchar(20) = 'asdas'

SELECT  * FROM TABLE_1 
WHERE (ISDATE(@queryWord) = 1) 
AND TABLE_1.INIT_DATE = CONVERT(Date, @queryWord)

This obviously causes an error because 'asdas' cannot be converted to Date. Although, I was expecting a different behavior. That is, because ISDATE(@queryWord) = 1 is false, I was expecting SQL to not check the second condition, but apparently, it does.

I know there are some other ways to perform this query but this is not my question. I wonder if there is some way to do not check the second condition is the first one does not satisfy. I am curious because I thought that SQL already did this.

You can't force a specific order in the evaluation of conditions AFAIK. — juergen d, Oct 17 '13 at 14:19
Possible duplicate: http://stackoverflow.com/questions/381224/sql-server-query-short-circuiting — seekerOfKnowledge, Oct 17 '13 at 14:28
Answer to the question I linked: http://weblogs.sqlteam.com/mladenp/archive/2008/02/25/How-SQL-Server-short-circuits-WHERE-condition-evaluation.aspx — seekerOfKnowledge, Oct 17 '13 at 14:29

RBarryYoung · Accepted Answer · 2013-10-17T14:33:37.240

13

SQL Server does not do short-circuiting (nor should it).

If you need it to not try something under some circumstances, you need to force that in the way that you write your query.

For this query the easiest fix would be to use a CASE expression in your WHERE clause.

declare @queryWord as nvarchar(20) = 'asdas'

SELECT  * FROM TABLE_1 
WHERE TABLE_1.INIT_DATE = (CASE WHEN ISDATE(@queryWord) = 1 
                                THEN CONVERT(Date, @queryWord)
                           ELSE NULL  END)

Off-hand, CASE and query-nesting are the only two supported ways that I can think of to force an order of evaluation for dependent conditions in SQL.

edited Oct 17 '13 at 14:33

answered Oct 17 '13 at 14:25

RBarryYoung

55,398
14
96
137

1

Not really. This would return rows from TABLE_1 where INIT_DATE is null. I know how to perform the query but my question was not this one. – nachovall Oct 17 '13 at 14:33
1

@nachovall Your question was "*is some way to do not check the second condition is the first one does not satisfy?*" I have answered that question. – RBarryYoung Oct 17 '13 at 14:34
1

@nachovall Additionally, it is not true that "*This would return rows from TABLE_1 where INIT_DATE is null*". In proper SQL `NULL = NULL` returns ***False***. Only `NULL IS NULL` can return True. – RBarryYoung Oct 17 '13 at 14:37
Yeah, well, maybe my fault. I was looking for this: 'You can't force a specific order in the evaluation of conditions' Thanks – nachovall Oct 17 '13 at 14:38
Regarding "in proper SQL", (1) from TechNet: "When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name." [(see docs at MS TechNet here)](http://technet.microsoft.com/en-us/library/ms188048.aspx) And (2) note that when you right-click in SSMS on "New Stored Procedure", the *starting point* it gives you has "SET ANSI_NULLS ON". If you want to handle NULLs *regardless* of settings, explicitly include a check for IS NULL or IS NOT NULL in the WHERE clause or CASE statement. – Doug_Ivison Oct 17 '13 at 14:48
1

If sql server does not do short circuiting, then how do you explain this: select 1 where 1=0 and 1/0 = 7 Normally, you would expect a divide by 0 error, but the query runs just fine on my sql instance (sql2005). – George Mastros Oct 17 '13 at 14:50
@GMastros Because that's *not* short-circuiting. Short-circuiting is a compiler feature that allows the programmer to control the order of evaluation of conditions and to skip later ones that it does not need. In other words, it's under the control of the programmer. What you are seeing is something that the SQL compiler is allowed to do *when it wants to*. How (and if) it does this is at its discretion, not yours, you cannot control it. – RBarryYoung Oct 17 '13 at 14:56
@GMastros To clarify somewhat, there is a difference between Short-Circuiting as a language feature, and Short-Circuiting as an internal compiler optimization. This question (and discussion) is about the language feature, not the internal optimization. – RBarryYoung Oct 17 '13 at 14:59
@RBarryYoung Thank you for the explanation. That makes perfect sense. – George Mastros Oct 17 '13 at 15:05
@nachovall, I'm not sure this approach is reliable, since `then` part of the `case` can be evaluated before `when` condition is checked. See here ([link](http://stackoverflow.com/q/11898427/2523686)) – i-one Oct 17 '13 at 21:07
Query nesting is not a supported way. The optimiser can rearrange expressions into nested queries. [See example here](http://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors). – Martin Smith Oct 18 '13 at 14:32
1

@i-one - The optimiser does not evaluate the `then` before the `when` in that case. The issue is that the result of the `CASE` is given the datatype of the branch with the highest datatype precedence. Because `money` has higher datatype precedence than `nvarchar` the `else` is evaluated. Then the result of that is cast to money. You see the same error if you do `select case @proptype when 'money' then $1.0 else @val end`. There are occasions [where the short circuiting behaviour seems to fail](http://dba.stackexchange.com/q/12941/3690) but they are more complex than that. – Martin Smith Oct 18 '13 at 14:40
@MartinSmith Yeah, have they acknowledged any of these or the CASE problems as bugs yet? Or are still just ignoring it and saying "*upgrade to 2012 and use TRY_CONVERT*"? At this point the only pre-2012 supported *and working* method is awful: multi-statement functions. – RBarryYoung Oct 18 '13 at 14:41
@MartinSmith Oh, I see, that's the ancient "*CASE has to have a datatype too*" rule. I always forget to check for that one when someone claims that `CASE` doesn't work right. – RBarryYoung Oct 18 '13 at 14:46
1

AFAIK some have been acknowledged as bugs and [others](https://connect.microsoft.com/SQLServer/feedback/details/752873/multistatement-tvf-referenced-in-case-statement-called-unnecessarily) closed as "Won't Fix". `TRY_CONVERT` only works for some classes of queries. Itzik Ben Gan suggested a [`TRY_EXPRESSION`](https://connect.microsoft.com/SQLServer/feedback/details/778583) but that is closed too. Sometimes it is tricky to pin down what [short circuiting even means though](http://dba.stackexchange.com/a/50443/3690) – Martin Smith Oct 18 '13 at 14:57

score 2 · Answer 2 · answered Oct 17 '13 at 14:23

2

I Guess you could do it in 2 passes:

declare @queryWord as nvarchar(20) = 'asdas'


    select
    *
    from
    (
    SELECT  * FROM TABLE_1 
    WHERE (ISDATE(@queryWord) = 1) ) t1
    where t1.INIT_DATE = CONVERT(Date, @queryWord)

So your inner query runs the first test and the outer query the second. In a single query, I don't believe there is any way to force any order of evaluating conditions.

answered Oct 17 '13 at 14:23

Andrew

8,445
3
28
46

1

Would you have preferred a simple "No"? – Andrew Oct 17 '13 at 14:39
:) My fault. That was just an example, I wasn't trying to solve it, but thanks anyway – nachovall Oct 17 '13 at 14:42

score 1 · Answer 3 · answered Oct 17 '13 at 14:26

Why not do a CASE in the WHERE condition?

DECLARE @tester TABLE (
    theDate DATE,
    theValue INT
    )

INSERT INTO @tester VALUES ('2013-10-17', 35)
INSERT INTO @tester VALUES ('2013-10-16', 50)
INSERT INTO @tester VALUES ('2013-10-15', 2)

declare @queryWord as nvarchar(20) = 'asdas'
SELECT  *
FROM @tester
WHERE theDate =
    CASE
        WHEN ISDATE(@queryWord) = 1 THEN CONVERT(Date, @queryWord)
        ELSE theDate
    END

SET @queryWord = '2013-10-17'
SELECT  *
FROM @tester
WHERE theDate =
    CASE
        WHEN ISDATE(@queryWord) = 1 THEN CONVERT(Date, @queryWord)
        ELSE theDate
    END

Your question was to have it check the first condition (that the @queryWord is a valid DATE type) and then proceed to do condition 2 if it is true - this does it. — wergeld, Oct 17 '13 at 14:37

jmoreno · Answer 4 · 2013-10-18T04:06:15.577

There is no defined evaluation order in a SQL statement -- except in the case of case expressions, and even there the order isn't so much defined as the result guaranteed. The conditions in your where clause could theoretically be done in parallel or alternating order.

Case expressions differ not by having a defined order, but by having a guaranteed result. IOW, case when 1=1 then 0 When longrunningfunction() = 1 then 2 end is guaranteed to return zero, but there is no promise not to run the longrunningfunction.

score 1 · Answer 5 · answered Oct 17 '13 at 17:50

It can be "simulated" with a CASE statement. But you have to make the first condition giving a TRUE value to avoid checking of the 2nd condition :

declare @queryWord as nvarchar(20) = 'asdas'

SELECT  * 
FROM TABLE_1
WHERE (CASE 
       WHEN ISDATE(@queryWord) = 0 THEN 0 
       WHEN TABLE_1.INIT_DATE = CONVERT(Date, @queryWord) THEN 1
       ELSE 0 END) = 1

How to Short-Circuit SQL Where Clause

5 Answers5

Linked

Related