TRY_EXPRESSION by Itzik Ben-Gan

Consider complex logical expressions consisting of multiple predicates like the following:

Expression 1: <predicate 1> AND <predicate 2>
Expression 2: <predicate 1> OR <predicate 2>

Relying on the commutative property of the AND and OR operators, and the all-at-once concept in SQL, the optimizer may decide to evaluate the predicates in different order than the written one.
For example, the optimizer may decide to transform the above expressions to the following, respectively:

Expression 1: <predicate 2> AND <predicate 1>
Expression 2: <predicate 2> OR <predicate 1>

Query processing short-circuits predicate evaluation when the final logical result is already known. However, due to the possibility for transformation of the expressions, you can’t predict the order in which the predicates will be evaluated. This can lead to query failures that could have been avoided had the predicates been evaluated in written order.

I’ll use the following sample data to demonstrate the problem:

SET NOCOUNT ON;
USE tempdb;

IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROP TABLE dbo.T1;
GO

CREATE TABLE dbo.T1
(
keycol INT NOT NULL,
val INT NOT NULL
CONSTRAINT PK_T1 PRIMARY KEY(keycol)
);

INSERT INTO dbo.T1 VALUES
(2, 43112609), (3, 0), (5, -1), (7, 2026), (9, 1759);

Consider the following query:

SELECT keycol, val
FROM dbo.T1
WHERE val*1/1 > 0 AND LOG(val) <= 10;

If the predicates were evaluated in written order and a short-circuit was applied, the query would not fail. However, on my system (SQL Server 2012 Enterprise + SP1), the optimizer transformed the expression to (simplified): LOG(val) <= 10 AND val*1/1 > 0. The query fails with the following error:

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

Here’s a similar example that fails with a divide by zero error:

SELECT keycol, val
FROM dbo.T1
WHERE val*1/1 <> 0 AND 43112609/val < 100;

Attempting to use a table expression does not solve the problem since the optimizer unnests the inner query. For example, the following code also fails:

WITH T1_Filtered AS
(
SELECT keycol, val
FROM dbo.T1
WHERE val*1/1 > 0
)
SELECT *
FROM T1_Filtered
WHERE LOG(val) <= 10;

There is a workaround using a CASE expression, which according to the documentation guarantees physical evaluation order:

SELECT keycol, val
FROM dbo.T1
WHERE CASE WHEN val*1/1 > 0 THEN LOG(val) ELSE NULL END <= 10;

However, this solution isn't very elegant.

Press Details (expand) to see proposed solution...