The Order of Operations

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720952

    Comments posted to this topic are about the item The Order of Operations

  • Carlo Romagnano

    SSC-Insane

    Points: 22010

    That's right!

    It's good to know.

    Here's to force the minus precedence:

    SELECT (-100.0)/(-100.0)*10.0

     

  • Toreador

    SSChampion

    Points: 11261

    I don't really understand the explanation. In what was is anything "crazy" about the handling of negatives? Whatever the precedence you end up dividing a negative by a negative, which must always be positive. The only question to decide was whether multiplication or division is executed first.

  • HarryKroek

    SSC Rookie

    Points: 47

    So, this means that a negative number is not considered to be a "number", but a "formula".

     

    In essence: -100.0 does NOT mean to subtract 100, but to represent a negative value.

    In my opinion there is a fundamental difference between "some number minus 10" and "some number plus -10".

     

    So yes, i would call that "crazy".

    It is good to to know this 'bug' in SQL Server.

     

     

    P.S.

    I would expect SQL to execute :

    SELECT (-100.0)/(-100.0)*(10.0)

    Instead, what apparently (according to the explanation) is executed, is:

    SELECT -1*((100.0)/(-1*(100.0*10.0)))

    • This reply was modified 3 months, 2 weeks ago by  HarryKroek.
    • This reply was modified 3 months, 2 weeks ago by  HarryKroek.
  • matthew.flower

    Default port

    Points: 1408

    For many years at least in the UK the school system has been teaching "BODMAS" as the way to solve potentially ambiguous equations. The BBC educational assistance page about it: https://www.bbc.co.uk/bitesize/topics/z69k7ty/articles/z24ctv4

    "From left to right, start with division and multiplication and continue with addition and subtraction."

    -100.0/-100.0*10.0

    The minus signs just indicate negative numbers and with two of them they are always cancelling out. But according the education system the problem should be solved by doing the divide and then the multiply.

    -100.0 / -100.0 = 1.0

    1.0 * 10.0 = 10.0

    I am sure that there is a very good reason to confuse students by having software that doesn't comply with the basic mathematical principles that they have been taught in school.

  • call.copse

    SSCoach

    Points: 17239

    It does seem somewhat crazy to me I must admit. At least it reinforces the maxim that you should always make the precedence you intend, be clearly enforced.

  • kaspencer

    SSCarpal Tunnel

    Points: 4442

    I really really must learn to concentrate more.

    I have done this several times in past questions, now again on this one - clicked on an anwser without properly thinking and then losing the point.

    It isn't that I didn't understand the question - I did, BUT I rushed into it without thinking! Such a pity there's no ROLLBACK on this forum!

    Kenneth Spencer

    (PS.: by the way, HarryK - Steve's explanation is absolutely fine. Any programming language with precedence rules similar to SQL Server - and most are - would give exectly the same result. Is anyone else troubled by it?)

    • This reply was modified 3 months, 2 weeks ago by  kaspencer.
    • This reply was modified 3 months, 2 weeks ago by  kaspencer.

    You never know: reading my book: "All about your computer" might just tell you something you never knew!
    lulu.com/kaspencer

  • edwardwill

    SSCertifiable

    Points: 5317

    I always, always, always parenthesise my operations so that the intent and the outcome are congruent.  It drives other people crazy when they have to code review my work, but tough.

  • gvoshol 73146

    Hall of Fame

    Points: 3193

    Yes, it is crazy.

    So if I have a statement like

    SET @x = -1;

    Does that mean it actually does an arithmetic operation?

  • carl.eaves

    Mr or Mrs. 500

    Points: 512

    Can't have too many parentheses, don't know how many times I have been caught out by software not following the order of precedence.

  • hannes.malan

    SSC Veteran

    Points: 264

    The BOL definitely does not explain it as per the outcome.

    I will call it a bug as well.

  • dale_berta

    Ten Centuries

    Points: 1034

    Toreador wrote:

    I don't really understand the explanation. In what was is anything "crazy" about the handling of negatives? Whatever the precedence you end up dividing a negative by a negative, which must always be positive. The only question to decide was whether multiplication or division is executed first.

    What's "crazy" isn't the final sign. It's that SQL doesn't recognize unary minus. In math, and in every ordinary programming language I've ever encountered, unary operators have higher precedence than anything except parentheses. SQL doesn't recognize unary minus. If it's not a unary minus, SQL sees subtraction. Subtraction occurs after multiplication and division. So SQL treats it as if there's a set of parentheses after the division.

    SELECT -100.0/-(100.0*10.0)

    The minus signs still cancel, as you say, but the interpreted parentheses trump multiplication and division's normal left to right processing, and you wind up with the unexpected result of 0.1.

    If unary minus were a thing, as in math and ordinary programming languages, the unary minus operator would act first, which interprets as:

    SELECT (-100.0)/(-100.0)*10.0

    The minus signs still cancel, but now the multiplication and division are the only operations remaining. They operate left to right, and give us a result of 10.

  • Toreador

    SSChampion

    Points: 11261

    dale_berta wrote:

    What's "crazy" isn't the final sign. It's that SQL doesn't recognize unary minus. In math, and in every ordinary programming language I've ever encountered, unary operators have higher precedence than anything except parentheses. SQL doesn't recognize unary minus. If it's not a unary minus, SQL sees subtraction. Subtraction occurs after multiplication and division. So SQL treats it as if there's a set of parentheses after the division.

     

    I don't believe that's true. SQL isn't treating either of the minus signs as subtraction - apart from anything else, there's nothing specified to subtract from.

    What it's doing is simply to apply the multiplication before the division. BODMAS would lead you to expect the division to occur first

    (-100.0/-100.0)*10.0

    but it is actually doing the multiplication first

    -100.0/(-100.0*10.0)

    The way I was taught, the "DM" in BODMAS were ranked equally, so it works from left to right. My guess is that SQL is working from right to left.

  • gvoshol 73146

    Hall of Fame

    Points: 3193

    It is treating the minus signs as subtraction in precedence order.

    Take them out, and the answer is 10.000...

    Put the first one back in, the answer is -10.000...

    But with both minus signs, the answer is 0.1000...

  • roger.plowman

    SSChampion

    Points: 10243

    Does ANY other computer language yield .1? Probably not. THIS is a serious bug. No matter what they say this kind of issue could cripple a project and probably not even be suspected.

    YAG! (Yet ANOTHER Gotcha)

Viewing 15 posts - 1 through 15 (of 35 total)

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