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!"

    Helpful Links:
    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.

  • Vladan

    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

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

  • andrei_solntsev

    SSCommitted

    Points: 1695

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

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

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