Share via

Evaluation Order

  • Article

A common general SQL question just popped its head up in the Spatial Forum: when presented with a SQL query, in which order are the parts executed?  To be concrete, consider the following table and query:

T:

x
0
1
2 
 SELECT x
FROM T
WHERE x <> 0 AND (8 / x) > 5

What should the result of this query be?  Focus on the fact that we're chancing a divide-by-zero problem with the second clause of the AND.

If this were C, C++, C#, or Java---languages that short-circuit boolean expressions---then the result depends on the order of execution.  Left-to-right seems to be the universal choice, and so the result would be the singleton 1.  We avoid the the divide-by-zero: since 0 fails the first clause, the second is never executed.

Some languages don't short-circuit these expressions, and so they will raise a divide-by-zero.  Pascal is one example.  The order is still left-to-right, but it doesn't really matter since they all get executed.

So what actually happens in SQL?  SQL is that rare language that does short-circuit these expressions, but in which the order is not guaranteed.  As a result, we know this query will either yield a singleton 1 or an error, but we don't know which one.  This is done so that the optimizer can be clever about reordering operations to improve performance, but the result can often be undesirable.

What's a poor developer to do?  All that's possible is to avoid the situation.  Keep in mind that in many cases there isn't an issue, but if the order is important, then the key is usually to use the CASE statement, for which execution order is guaranteed.  This can be a bit clumsy:

 SELECT x
FROM T
WHERE 8 / (CASE WHEN x = 0 THEN null ELSE x END) > 5

This is strange, but it is how it is.

Cheers,

-Isaac

Comments

  • Anonymous
    May 07, 2008
    Is the order guarantee for CASE/WHEN specific to SQL Server, or is it required by the SQL spec?

  • Anonymous
    May 07, 2008
    Does using this type of where clause allow for index usage? ("sargable")

  • Anonymous
    May 12, 2008
    The comment has been removed

  • Anonymous
    May 17, 2008
    The comment has been removed

  • Anonymous
    May 17, 2008
    The comment has been removed