Negative (-) is an operator and will affect the order of operations.
7May 18, 2020 by Kenneth Fisher
An interesting question came to my attention the other day.
A strange operation problem in SQL Server: -100/-100*10 = 0
Basically the poster showed the following:
- 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.
Interesting right? These are integers so there aren’t any decimal results so I’m going to switch them to decimals to make it easier for me to see what’s going on.
- If you execute SELECT -100.0/-100.0*10.0 the result is 0.1.
- If you execute SELECT (-100.0/-100.0)*10.0 the result is 10.
- If you execute SELECT -100.0/(-100.0*10.0) the result is 0.1.
- If you execute SELECT 100.0/100.0*10.0 the result is 10.
Still strange results but I much prefer the 0.1 instead of the 0. Next I’m going to group them a bit.
SELECT (-100.0/-100.0)*10.0; SELECT 100.0/100.0*10.0;
The result of both of these is exactly what you would expect right? That’s because order of operations goes from left to right unless there are parentheses. In both cases the 100.0/100.0 and the -100.0/-100.0 are executed first with a result of 1.0 times 10.0 returns 10.0.
So how about this one
SELECT -100.0/(-100.0*10.0)
Still what you would expect. -100.0*10.0 runs first (it’s in parentheses) and returns -1000.0, then comes the -100.0/-1000.0 for the end result of 0.1.
Last but certainly not least our problem child.
SELECT -100.0/-100.0*10.0
In the absence of parenthesis and nothing but multiplication and division it should go left to right. Right? But that would give us -100.0/-100.0 returning 1.0 and finally 1.0*10.0 giving us 10.0. But that’s not what we are getting. Somehow we are ending up with 0.1. Well, unfortunately in SQL Server the – is considered an operator with lower precedence than multiplication and division. Which means that the code actually translates to
SELECT -(100.0/-(100.0*10.0))
Which could cause some really interesting calculation errors.
Fortunately most of the time we use variables and/or columns and end up with
DECLARE @var1 int = -100, @var2 int = -100, @var3 int = 10; SELECT @var1 / @var2 * @var3;
And since the negative values are contained in the variables/columns it executes exactly the way you would expect. (Returns 10).
All of that said, if you are using T-SQL for a lot of math you really need to be aware of this and be careful in what you are doing.
SQL Studies 
We got the unary minus and unary plus operators from PL/1 and Ada, which were hot languages at the time we were doing the early SQL standards. PL/1 had a very complicated grammar which had to be defined with a thing called VDL (Viennese definition language) and a correct parse required a left to right scan, then a right to left scan, and finally another left to right scan. Blech!
Blech may be the best possible way this could be described 😀
Thanks for sharing and….really surprising!! :O However… I hope that it’s a bug and that it would be fixed sooner or later, and not a desired behaviour!
I’ve just tried it on SQL SERVER 2016 SP2 CU2 and it goes exactly like you’ve written :(((
Nope, I doubt it’s a bug. If you look at Joe’s comment it looks like it was created that way deliberately. Again, it’s not something you are going to see too often since most people don’t plug hard coded negative numbers into calculations but it’s certainly something to be aware of.
[…] Kenneth Fisher takes us through a weird problem: […]
This is the most important post I’ve seen in a while, because it has completely upended how I visualize the unary minus operator in SQL. I always assumed two things. First, that -100 was a literal in and of itself (not that it was unary-minus followed by the literal 100). Second, that the unary minus had higher precedence than multiplication and division (i.e. when writing -@var) because the other languages I have used have this precedence, so I mistakenly assumed the case was true in T-SQL
Given all of this, I am now firmly of the opinion that the best way to use unary minus is to surround it with parentheses, like so:
SELECT (-100.0)/(-100.0)*10.0;
Furthermore, one should add a comment to the effect that the parentheses around the unary minus are to ensure operator precedence follows standard logic.
Yup. And thanks 🙂 Do remember that this doesn’t come up all that often because it only really matters with literals and not when the value is stored in a variable or a field.
My 16yo pointed out that when he was learning negatives one of the things he was taught is that -100 is technically 0-100 which I thought was an interesting way to view it.