Evaluation Order

Evaluation Order

  • Comments 5

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

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

  • Does using this type of where clause allow for index usage? ("sargable")

  • Hi,

    Sorry for the delayed response.  Regarding the standard, it seems to me that SQL99 requires this ordering.  In particular, it says that:

    If the <search condition> of some <searched when clause> in a <case specification> is true, then the value of the <case specification> is the value of the <result> of the first (leftmost) <searched when clause> whose <search condition> is true, cast as the declared type of the <case specification>.

    This implies an order (left-to-right) execution in a CASE, and says to me that if a particular WHEN clause returns true, then the corresponding THEN value should be returned, even if a later WHEN or THEN produce an error.

    Regarding index use, I don't believe so.  That said, I don't believe the predicate in my example, (8 / x) > 5, would be answered by the index even without the CASE.

    Cheers,

    -Isaac

  • > This implies an order (left-to-right) execution in a CASE

    It does imply order for sure, but I don't see it requiring short-circuiting - it seems that it is undefined whether that happens or not...

  • I'm really no standards expert, so take this with the appropriate grain of salt, but my reading of it is that it does imply short circuiting, at least semantically.

    It's true that this doesn't imply that each of the clauses is not executed, but it does imply that there can be no effects caused by their execution if they are indeed run.  I.e., unless we debug the execution, the only way we should be able to tell if each clause is executed is that performance may suffer if they are.

    For example, consider "CASE WHEN x = 0 THEN -1 ELSE 1/x END".  If x equals zero, then the standard specifies that the result should be -1.  Either the second clause has to be skipped, or if it is executed and the divide-by-zero occurs, the system has to trap it and prevent it from affecting the results.

    Cheers,

    -Isaac

Page 1 of 1 (5 items)