Negative (-) is an operator and will affect the order of operations.

7

May 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.

7 thoughts on “Negative (-) is an operator and will affect the order of operations.

  1. Joe Celko says:

    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!

  2. Elena D. says:

    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.

  3. […] Kenneth Fisher takes us through a weird problem: […]

  4. tovodeverett says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,116 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: