Expression Evaluation Rules
The order of evaluation of subexpressions is undefined. The inputs of an operator or function are not necessarily evaluated left-to-right or in any other fixed order.
If you can determine the result of an expression by evaluating only some parts of the expression, then other subexpressions might not be evaluated at all. For example, in the following expression:
SELECT true OR somefunc();
somefunc()
would probably not be called at all. The same is true in the following expression:
SELECT somefunc() OR true;
This is not the same as the left-to-right evaluation order that Boolean operators enforce in some programming languages.
Do not use functions with side effects as part of complex expressions, especially in WHERE
and HAVING
clauses, because those clauses are extensively reprocessed when developing an execution plan. Boolean expressions (AND
/OR
/NOT
combinations) in those clauses can be reorganized in any manner that Boolean algebra laws allow.
Use a CASE
construct to force evaluation order. The following example is an untrustworthy way to avoid division by zero in a WHERE
clause:
SELECT ... WHERE x <> 0 AND y/x > 1.5;
The following example shows a trustworthy evaluation order:
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false
END;
This CASE
construct usage defeats optimization attempts; use it only when necessary.