The Order of Operations

Question

Post reply

The Order of Operations

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

trowley Mr or Mrs. 500 Points: 521 More actions · Answer 1

SQL reads from left to right

Example 16/4/4 is interpreted as (16/4)/4 = 1 instead of 16/(4/4)=16

SQL is using implied multiplication.

Multiplication is interpreted as having higher precedence than division, so that 1 ÷ 2x equals 1 ÷ (2x), not (1 ÷ 2)x.

So -100/-100*10 is being interpreted as (-100/(-100*10))

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

-100/-1000 = 0.1

GaryV Hall of Fame Points: 3307 More actions · Answer 2

trowley wrote:

So -100/-100*10 is being interpreted as (-100/(-100*10))

Then why is 100/100*10 interpreted as (100/100)*10) ?

trowley Mr or Mrs. 500 Points: 521 More actions · Answer 3

The implied multiplication comes into play because of the negative sign.

Think about Algebra

-100/-100*x can be written as -100/-100x

Algebra would solve this problem by multiplying the -100 and x first.

In our case, it is treating the *10 like the x.

-100/-100x = 0.1 where x is equal to 10

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 4

I understand how and why this works, but I do think this is inconsistent with other languages. Quick tests when I wrote this in Python and PowerShell show the answer as 10, not .1.

This is either an implementation bug or a design flaw, IMHO, and the working here should conform to what other languages do.

Note that this works as expected.

DECLARE @a NUMERIC(10,2) = -100.0
, @b NUMERIC(10,2) = -100.0,
@c NUMERIC(10,2) = 10.0

SELECT @a/@b*@c

Since we don't often write explicit unary minus signs, maybe this doesn't matter, but it could. If you change this to set all variables to positive and include the minus in the SELECT, you get 0.1

I wrote this question so that you would think about this and understand where math in SQL could be problematic.

edwardwill SSCertifiable Points: 5848 More actions · Answer 5

It may not be a "bug" but, as you say, it's inconsistent and could therefore trip up the unwary (a bit like using BETWEEN for date comparison, which bit me in the backside a few years ago). Anyway, excellent question!

dale_berta Ten Centuries Points: 1320 More actions · Answer 6

This is apparently a Microsoft thing. Both Oracle and IBM DB2 put +, - , when used for signed numeric values, at highest priority. I couldn't find documentation on what ANSI SQL says about this.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

Without minus signs the expression would evaluate to 10 as division and multiplication are equal in the order of execution. Plus and minus are to follow.

I do understand the explanation and the fact that there are so many opinions in the discussion confirms my opinion that all SQL Server code is written by multiple Microsoft programmers and you never will know what a certain programmer had in mind when writing code. Microsoft programmers have as many different opinions as us in this discussion. So it is better not to leave your logic at the hands of other people, but to explicitly include your expressions in the round brackets (parentheses)

Regards,Yelena Varsha

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 8

I agree that this is a confusing one. Fun little tidbit, changing the - to + yields the same result as the QOTD 0.1000000:

SELECT +100/+100*10.0

To me, this seems very unexpected and seems to be tied to the signs. If you put brackets around the +100 and nothing else, you get a result of 10.0, even though this shouldn't be changing the order of operations. I am really confused why SQL decides that putting signs in means it should change the order it does the operations...

EDIT - I think that it might be how SQL is applying the - and + signs... is it that SQL interprets the above query as:

SELECT +(100/+(100*10.0))

That is, the sign (+ or -) is applied to all operations to the right of the sign?

This reply was modified 4 years, 5 months ago by Mr. Brian Gale.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

brian.f.monson SSCommitted Points: 1821 More actions · Answer 9

Mathew Flower,

Excellent point. In most programming products, the division operator always took precedence over the multiplication. The parser should have recognized that two consecutive operators were present (division followed by minus sign). That should have been sufficient to treat the following number as a negative number (without attempting to do a subtraction operation). The first minus sign was also NOT an arithmetic operation. Therefore the first two numbers were negative. Thus the division operation should have been calculated FIRST. The answer is 10.

This is the thing that really annoys me about Microsoft. They are SO arrogant. Very frequently choosing to veer away from the obvious or the intuitive. No wonder they make out like bandits in forcing so many users of their products in having to take courses to make any sense of their protocols.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 10

Just tried that in Python, in Jupyter Notebooks

print(-100.0/-100.0*10.0)

The output is 10.0

Also tried in Excel entering in the Formula bar

=-100.0/-100.0*10.0

The cell contain the value of 10. Excel is a Microsoft product as well as SQL Server, so I would expect consistency.

Regards,Yelena Varsha

dale_berta Ten Centuries Points: 1320 More actions · Answer 11

Yelena Varshal wrote:

Just tried that in Python, in Jupyter Notebooks
print(-100.0/-100.0*10.0)
The output is 10.0
Also tried in Excel entering in the Formula bar
=-100.0/-100.0*10.0
The cell contain the value of 10. Excel is a Microsoft product as well as SQL Server, so I would expect consistency.

This may be leftover from some really old version of SQL Server, and never changed "for compatibility reasons." My take is give us a switch we can SET, and let us choose.

Stewart "Arturius" Campbell SSC Guru Points: 72622 More actions · Answer 12

I remember this bothering me with Sybase in the mid- 90's and with SQL 6.5 through to today.

something i learned writing COBOL on mainframes was to always, always use parentheses, and that has stuck with me...

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

rory.murchison SSC Enthusiast Points: 109 More actions · Answer 13

I tried the code in a variety of database engines (Db2, Oracle, MySQL, PostgeSQL, Hana, Teradata) and the answer is 10.

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 14

The only reason why I got it right was because it has bitten so many people that work in every company that I've ever worked in including me back in my 6.5 days. So far as I'm concerned, it IS a bug but, if you manually try the same formula on the Windows Calculator, you'll find that SQL Server isn't the only place where this happens except the Windows calculator comes up with an even odder answer. If you paste the formula, it changes the formula to come up with the correct answer. It's why I almost always use parentheses for all but the simplest of formulas.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

matthew.flower Default port Points: 1414 More actions · Answer 15

I think windows calculator gets it right if you use the +/- button for the negative numbers:

-100 ÷ -100 × 10 = 10

If you key in - before the 100s it thinks you have changed your mind about the divide and wanted to subtract instead giving

0 - 100 - 100 × 10 =? -2000