A strange operation problem in SQL Server: -100/-100*10 = 0

Question

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.

Why do you think you have to do with bits, you are working with integers. And integer/integer = integer. So -100/-1000 is 0 — sepupic, Feb 04 '19 at 09:46
Maybe you wanted a decimal as the answer? -100./-100*10, here -100. is decimal and the result is decimal — sepupic, Feb 04 '19 at 09:48
-100/-100*10 = -100/-(100*10) = 0, * (Multiplication) then - (Negative) then / (Division) — cuizizhe, Feb 04 '19 at 09:49
Without any explicit convertions, any constants like 100 are treated as integer. I don't understand what do you expect as the result. The result of division between integers is also integer — sepupic, Feb 04 '19 at 09:51
If we extrapolate this out: `-100/-100*10 = -100/(-100*10) = -100/-1000 = 0` . Why is `-100/-1000=0`? Because you have `int` datatypes and the "actual" value (`0.1`), cannot be represented by a integer value. As a result the decimal points are lost: `0.1->0`. If you need decimal places, you need to use decimals. For example `-100/-100*10.0 = 0.10000000` — Thom A, Feb 04 '19 at 09:56
Because maths, @cuizizhe ... `-100/-1000` is `0.1`. That's how maths works. Why would you expect `-100/-1000` to ever be `10`? Whenever you divide a number by a larger (absolute) number you **always** end up with a number with a smaller (abolsulte) number than you started with. That's basic maths I'm afraid, and how it's always worked for many centuries/millenia. — Thom A, Feb 04 '19 at 10:13
@Larnu, please see my edits, I think OP is asking about the precedence of execution. It's not an illogical question, I think. — HoneyBadger, Feb 04 '19 at 10:17
if that is what they are asking, that makes things more clear, and I'm happy to vote to reopen. — Thom A, Feb 04 '19 at 10:19
OK, I do agree, that `-` does seem to be causing the flow to go "wrong". If you try `-100/(-100)*10` you get the result `10`. it seems that the `/` is being applied against to value `-` in the equation and then the equation `100*10` is being determined. I'm not sure this is an error with BOL, but more that SQL Server isn't behaving as expected. It might be worth raising an issue on [sql-docs](https://github.com/MicrosoftDocs/sql-docs/issues) and seeing what their response is there; perhaps a note could be added to the documentation advising of the "feature". — Thom A, Feb 04 '19 at 10:24
`SELECT -100/(-100)*10` also returns 10. It looks like `-` is treated as the `-` operator which should be applied only after `100*10` is calculated — Panagiotis Kanavos, Feb 04 '19 at 10:25
There are operators from two different levels: 1:) `*`, `/` and 2) `-`. So what is evaluated is SELECT -100/-(100*10), not what OP expects (-100/-100)*10. — Zhorov, Feb 04 '19 at 10:26
@Zhorov that's not the problem here. `-100/-100` has the same precedence as multiplication. — Panagiotis Kanavos, Feb 04 '19 at 10:27
I would actually suggest that this *shouldn't* be the expected behaviour. If you use variables, with the literal values you would expect the same results, but you don't [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d1ef4cf4e537ddb0485d605f1df38ab1). Personally, I recommend this raised as an issue on sql-docs as this is likely u̶n̶intended behaviour and thus needs to be documented. — Thom A, Feb 04 '19 at 10:31
@Larnu I've made the same test case. With variables everything works as expected. — Zhorov, Feb 04 '19 at 10:32
So you're saying that you got `0` as well, @Zhorov? That's not the behaviour I get. — Thom A, Feb 04 '19 at 10:33
`A / -B * C` is `A
B C`. Negate has lower precedence than multiply, per the docs, so the result is `A / -(B * C)`. You can see this more clearly by using floating constants: `12e / -13e * 14e` vs. `12e / (-13e) * 14e` vs `12e / 13e * 14e`.The reason this throws us off is because we generally expect unary minus to become part of the literal, or at least have very high precedence, but that's not how T-SQL works. — Jeroen Mostert, Feb 04 '19 at 10:36
In SQLFiddle every database except SQL Server returns `10`. There's an [unanswered duplicate from 2016](https://stackoverflow.com/questions/20263318/order-precedence-of-multiplication-and-division) too — Panagiotis Kanavos, Feb 04 '19 at 10:45
You can run this query on [Data Stack Exchange](https://data.stackexchange.com/stackoverflow/query/975898/a-strange-operation-problem-in-sql-server--100-10010-0) — aloisdg, Feb 04 '19 at 16:06
The really crazy thing, in my mind, is that `+` and `-` have high precedence than `*` and `/`. `5 + 2 * 10` should be `25`, not `70`. — JDB, Feb 05 '19 at 19:44

Salman A · Answer 1 · 2019-12-30T19:55:30.627

96

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.

edited Dec 30 '19 at 19:55

answered Feb 04 '19 at 11:02

Salman A

262,204
82
430
521

38

Wow, another gem feature in T-SQL :) I guess I have to audit all of my code now to search for bugs. – usr Feb 04 '19 at 14:09
14

oh man. This is even worse than the various PHP [ternary operator bugs](https://stackoverflow.com/q/22633643/995714) https://bugs.php.net/bug.php?id=61915. What sane people think that unary operators must have lower precedence than binary ones? – phuclv Feb 04 '19 at 15:22
1

@phuclv: well, `-` has the same precedence whether it's unary or binary, so it's a form of consistency, you see? I'm actually surprised it "works" as often as it does; there aren't too many expressions where you could actually notice the difference. Which, of course, makes it extra insidious. (Regarding sanity: there are multiple places where the T-SQL parser demonstrably takes shortcuts, whether out of carelessness, laziness or misplaced efficiency concerns, that lead to odd/unintuitive results, this might be one of those. At least this one is actually documented.) – Jeroen Mostert Feb 04 '19 at 15:31
12

The real difference may be whether `-` is considered an operator in `-100`. In some languages, it's part of the syntax of an integer. – Barmar Feb 04 '19 at 16:57
7

So it's a bug in their precedence of unary `-`. – Kevin Feb 04 '19 at 17:47
1

@phuclv there is a classic example of `-3^2` (a mix of unary and binary). A mathematician will tell you that it should be _-9_ i.e. `-(3^2)` (see BODMAS rules), a programmer will tell you that it should be _9_ i.e. `(-3)^2`. – Salman A Feb 04 '19 at 18:03
This is integer division so 100 / -1000 truncates, and that's the last step to get 0 instead of something else? – djechlin Feb 04 '19 at 18:46
@JeroenMostert are there parser bugs? I was under the impression they were using a parser generator. That's why the syntax error message is so generic. It's always "syntax error near ...". – usr Feb 04 '19 at 20:31
`-(100 / -(100 * 10))` -> `-(100 / -(1000))` -> `-(100 / -1000)` -> `-(-0.1)` -> `0.1` = `0`? – 8protons Feb 04 '19 at 21:22
@Salman no, programmers still say that -3^2 = -9 because power has higher precedence in most languages (if they have it) with some weird exceptions like excel – phuclv Feb 04 '19 at 23:55
4

And the winner of counter-intuitive design is ...: Microsoft - once more – rexkogitans Feb 05 '19 at 07:30
@phuclv agreed. The point I was trying to make is that a unary operator does not need to have higher precedence all the time (see -3^2 in vb.net). In JavaScript for example you have `delete window.foo`... it is evaluated as `delete (window.foo)` and not `(delete window).foo` ¯\\_(ツ)_/¯ – Salman A Feb 05 '19 at 08:29
@phuclv: Microsoft just ensures consistency across their products, like [Why does =-x^2+x for x=3 in Excel result in 12](https://superuser.com/questions/1385570/why-does-x2x-for-x-3-in-excel-result-in-12-instead-of-6?noredirect=1&lq=1) – Thomas Weller Feb 05 '19 at 12:15

Jeroen Mostert · Answer 2 · 2019-02-04T11:47:37.330

34

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.

edited Feb 04 '19 at 11:47

answered Feb 04 '19 at 11:11

Jeroen Mostert

27,176
2
52
85

"minus" is binary. Unary `-` is "negative". People who say things like "minus 10" when they mean "negative 10" are being imprecise. – Acccumulation Feb 04 '19 at 18:19
11

@Acccumulation: the imprecision isn't mine. The `-` operator, when applied to a single operand, is called `MINUS` in SQL query plans. Its binary counterpart is called `SUB`. If you like, interpret "unary minus" as shorthand for "the unary operator signified by the minus sign" -- a syntactic rather than a semantic designation. – Jeroen Mostert Feb 04 '19 at 18:33
3

"negative 10" is standard American usage (I believe) but it is not standard in the UK. – Alchymist Feb 05 '19 at 11:11

score 12 · Answer 3 · edited Feb 04 '19 at 14:41

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

A strange operation problem in SQL Server: -100/-100*10 = 0

3 Answers3

Linked