The Order of Operations

Question

The Order of Operations

Viewing 5 posts - 31 through 34 (of 34 total)

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

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

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.

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

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 2

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.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

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: 24800 More actions · Answer 4

Vladan wrote:

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.

<deleted> I skimmed your reply and made a bad reply. Lesson learned - drink coffee THEN read forum posts...

This reply was modified 4 years, 6 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.

andrei_solntsev SSCrazy Points: 2177 More actions · Answer 5

You right, but it's absolutely wrong. Must be considered as a bug