A strange operation problem in SQL Server: -100/-100*10 = 0
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