the - A strange operation problem in SQL Server(-100/-100*10=0)




the order of operator precedence is (2)

  • If you execute SELECT -100/-100*10 the result is 0 .
  • If you execute SELECT (-100/-100)*10 the result is 10 .
  • If you execute SELECT -100/(-100*10) the result is 0 .
  • If you execute SELECT 100/100*10 the result is 10 .

BOL states:

When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression.

And

Level   Operators
  1     ~ (Bitwise NOT)
  2     * (Multiplication), / (Division), % (Modulus)
  3     + (Positive), - (Negative), + (Addition), + (Concatenation), - (Subtraction), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)

Is BOL wrong, or am I missing something? It seems the - is throwing the (expected) precedence off.


According to the precedence table, this is the expected behavior. The operator with higher precedence ( / and * ) is evaluated before operator with lower precedence (unary - ). So this:

-100 / -100 * 10

is evaluated as:

-(100 / -(100 * 10))

Note that this behavior is different from most programming languages where unary negation has higher precedence than multiplication and division e.g. VB , JavaScript .


BOL is correct. - has lower precedence than * , so

-A * B

is parsed as

-(A * B)

Multiplication being what it is, you don't typically notice this, except when mixing in the two other binary operators with equal precedence: / and % (and % is rarely used in compound expressions like this). So

C / -A * B

Is parsed as

C / -(A * B)

explaining the results. This is counter-intuitive because in most other languages, unary minus has higher precedence than * and / , but not in T-SQL, and this is documented correctly.

A nice (?) way to illustrate it:

SELECT -1073741824 * 2

produces an arithmetic overflow, because -(1073741824 * 2) produces 2147483648 as an intermediate, which does not fit in an INT , but

SELECT (-1073741824) * 2

produces the expected result -2147483648 , which does.





operator-precedence