Home Dashboard Directory Help
Search

TRY_EXPRESSION by Itzik Ben-Gan


Status: 

Closed
 as Won't Fix Help for as Won't Fix


25
4
Sign in
to vote
Type: Suggestion
ID: 778583
Opened: 2/5/2013 10:07:20 AM
Access Restriction: Public
0
Workaround(s)
view

Description

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...
Details
Sign in to post a comment.
Posted by Microsoft on 2/27/2013 at 12:42 PM
Hello Itzik,

Thanks for your feedback. I am closing this as "won't fix" given the other high priority requests in our pipeline. Not that the feature request remains in our bug database even after closure. So we can look at it in the future if our business priorities change.

--
Umachandar, SQL Programmability Team
Posted by AaronBertrand on 2/5/2013 at 1:05 PM
Up-voted, but just for clarity, in spite of what the documentation might promise, physical evaluation order is not always guaranteed even with CASE expressions. Consider this simple case with aggregates:

DECLARE @i INT = 1;
SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END;

And Itzik I know that you are well aware of this issue (and also reported the one in 649957) but I want to make sure readers aren't given a false sense of security about evaluation order. Paul and I both complained about this from two angles:

http://connect.microsoft.com/SQLServer/feedback/details/690017/case-coalesce-wont-always-evaluate-in-textual-order

http://connect.microsoft.com/SQLServer/feedback/details/691535/aggregates-dont-follow-the-semantics-of-case

These have both been closed as fixed, but it is important to note that only the documentation has been fixed. A remark from Microsoft:

"You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions."
Posted by Itzik Ben-Gan on 2/5/2013 at 11:18 AM
Hi Peso,

If it was possible to get an option to force order of particular expressions it would have been nice. But that's not likely to happen. An option like EXPRESSION_ORDER would force order of all expressions in the query, which could be stronger than the desired effect. I also think it would be hard to implement, given that the optimizer normally decomposes complex expressions and can handle different parts with different operators. And what about parallelism... ? In short, I think that the proposed TRY_EXPRESSION is easier to implement, and hence it's a more realistic request.
Posted by SwePeso on 2/5/2013 at 10:49 AM
If sounds similar to the OPTION (KEEP ORDER) query option hint.
What about a OPTION (EXPRESSION_ORDER) query option hint?
Sign in to post a workaround.