Seemingly incorrect Operator Precedence

Question

I'm having some difficulty wrapping my head around some operator precedence, where I'm expecting all of the queries below to fail; this is based on the documentation below where Division would be evaluated before Comparison operators.

Microsoft documentation for t-sql Operator Precedence: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-2017

Sample data and queries

CREATE TABLE #orders(
    price INT
)
GO 

INSERT INTO #orders VALUES 
(1)
,(2)
,(3)
,(4)
,(5)
,(6)
,(7)
,(8)
,(9)
,(10)
,(0)  

GO

/*
the following two queries work
*/
SELECT * 
FROM   #orders 
WHERE  2 / price > 0 
       AND price > 0; 

SELECT * 
FROM   #orders 
WHERE  price > 0 
       AND 2 / price > 0; 

/*
these don't work - getting divide by zero error
*/
SELECT * 
FROM   #orders 
WHERE  2 / price > 0 
       AND price IN ( 1, 2, 3, 4, 
                      5, 6, 7, 8, 
                      9, 10 ); 

SELECT * 
FROM   #orders 
WHERE  price IN ( 1, 2, 3, 4, 
                  5, 6, 7, 8, 
                  9, 10 ) 
       AND 2 / price > 0; 

Shared Execution plan for one of the working queries: https://www.brentozar.com/pastetheplan/?id=HJFH3JWB7

Shared (estimated) Execution plan for one of the non-working queries: https://www.brentozar.com/pastetheplan/?id=r1Ds2yWSQ

Any help in understanding this behavior would be greatly appreciated!

Thanks,

Eli

Answer 1

Good question. I think that perhaps the language on the documentation page that you shared is a touch imprecise, which is probably contributing to your confusion. Here's a quote (emphasis mine):

When a complex expression has multiple operators, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value.

The strong implication here is that precedence and order of execution are essentially the same thing, but they're not. Eric Lippert explains this better than I ever could (emphasis in the original).

Precedence rules describe how an underparenthesized expression should be parenthesized when the expression mixes different kinds of operators. For example, multiplication is of higher precedence than addition, so 2 + 3 x 4 is equivalent to 2 + (3 x 4), not (2 + 3) x 4.

Associativity rules describe how an underparenthesized expression should be parenthesized when the expression has a bunch of the same kind of operator. For example, addition is associative from left to right, so a + b + c is equivalent to (a + b) + c, not a + (b + c). In ordinary arithmetic, these two expressions always give the same result; in computer arithmetic, they do not necessarily. (As an exercise can you find values for a, b, c such that (a + b) + c is unequal to a + (b + c) in C#?)

Now the confusing one.

Order of evaluation rules describe the order in which each operand in an expression is evaluated. The parentheses just describe how the results are grouped together; “do the parentheses first” is not a rule of C#. Rather, the rule in C# is “evaluate each subexpression strictly left to right”.

Read the whole thing. Eric uses C# and C++ as his examples but his comments apply a lot more generally than that. As he suggests, if you think of operator precedence rules as controlling how the components of an expression are grouped rather than the order in which they're executed, the behavior you're seeing makes a lot more sense. Here's a simplified version of one of the queries that you understandably expected to fail, but which actually succeeded:

declare @t table (x int);
insert @t values (0);

select * from @t where 2 / x > 0 and x > 0;

SQL Server's operator precedence rules simply mean that the above query is equivalent to:

-- Same query as above with operator precedence shown explicitly.
select * from @t where ((2 / x) > 0) and (x > 0);

The interesting thing about AND in this context is that if you know one of its operands is false, then you know that the entire expression is false without even evaluating the other operand. Some programming languages specify the order in which the operand evaluation will occur. For instance, the && operator in C# evaluates its left-hand operand first, and if it's false, it does not evaluate its right-hand operand at all. But the AND operator in T-SQL makes no claims one way or the other; it is up to the optimizer to choose.

There have been a lot of articles devoted to exploring this behavior in detail; for instance, here is a pretty detailed one. Does this help at all?

Answer 2

I don't think this has to do with the operator precedence but what order the optimizer chooses to evaluate the where clause.

In the first two, the rows must be filtered where price > 0 first preventing the error.

Since the second two use IN, the division must be occurring first to limit the rows, and then the IN filter is evaluated. This can be prevented by adding an index... i.e. make ID a primary key, which creates a clustered index, and you won't get the error since the optimizer can use the index and filter the rows before the division, or choose to.

The predicate should change from price > 0 AND 2 / price > 0 to 2/price > 0