SQL Server 中一个奇怪的操作问题: -100/-100 * 10 = 0

  • 如果执行 SELECT -100/-100*10，结果是 0。
  • 如果执行 SELECT (-100/-100)*10，结果是 10。
  • 如果执行 SELECT -100/(-100*10)，结果是 0。
  • 如果执行 SELECT 100/100*10，结果是 10。

BOL 声明:

当表达式中的两个运算符具有相同的运算符优先级时，将根据它们在表达式中的位置从左到右计算它们。

还有

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

是 BOL 错了，还是我漏掉了什么? 看起来 -抛弃了(预期的)优先级。

7925 次浏览
小开

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.

小开

Notice in the documentation that (perhaps counter-intuitively) the order of precedence for - (Negative) is third.

So you effectively get:

-(100/-(100*10)) = 0

If you place them into variables you won't see this happening, as there is no unary operation that occurs after the multiplication.

So here A and B are the same, whereas C, D, E show the result you are seeing (with E having the complete bracketing)

DECLARE @i1 int, @i2 int, @i3 int;


SELECT @i1 = -100,
@i2 = -100,
@i3 = 10;


SELECT @i1/@i2*@i3      [A],
-100/(-100)*10   [B],
-100/-100*10     [C],
-100/-(100*10)   [D],
-(100/-(100*10)) [E];


A - 10
B - 10
C - 0
D - 0
E - 0

提交答案