The Order of Operations

• Jeff Moden

SSC Guru

Points: 997150

Yep... agreed.  But you have to do something "special" for it to work.  I guess I don't see any real reason why SQL Server should be any different except that it doesn't work the way most people would think according to the rules of algebraic hierarchy.

--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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• Jonathan AC Roberts

SSCoach

Points: 17335

Jeff Moden wrote:

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.

The only reason why I got it right is because I pasted the question into SSMS.  It demonstrates it is good practice to put brackets in you formula with SQL Server even if you think you don't really need them and not just for the clarity of the reader.

SSC-Insane

Points: 22072

It seems that it is the two operators following each other "/-" that causes unexpected behavior (not the minus at the beginning).

SELECT -100.0/-100.0*10.0 => 0.1

SELECT (-1*100.0)/-100.0*10.0 =>0.1

SELECT -100.0/(-1*100.0)*10.0 => 10

SELECT (-1*100.0)/(-1*100.0)*10.0 => 10

That was a very helpful question, thanks. One can never be too careful when it comes to things that happen "automatically" (precedence, implicit conversion...), often they happen differently than you think.

• Mr. Brian Gale

SSC-Insane

Points: 23167

It seems that it is the two operators following each other "/-" that causes unexpected behavior (not the minus at the beginning).

SELECT -100.0/-100.0*10.0 => 0.1

SELECT (-1*100.0)/-100.0*10.0 =>0.1

SELECT -100.0/(-1*100.0)*10.0 => 10

SELECT (-1*100.0)/(-1*100.0)*10.0 => 10

That was a very helpful question, thanks. One can never be too careful when it comes to things that happen "automatically" (precedence, implicit conversion...), often they happen differently than you think.