Evaluation Order

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