The Order of Operations

  • trowley

    SSC Veteran

    Points: 298

    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

  • gvoshol 73146

    Hall of Fame

    Points: 3194

    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

    SSC Veteran

    Points: 298

    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: 720963

    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: 5317

    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: 1035

    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: 34270

    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: 23167

    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?

  • brian.f.monson

    SSCommitted

    Points: 1621

    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: 34270

    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: 1035

    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: 71915

    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

    Valued Member

    Points: 72

    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: 997150

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

  • matthew.flower

    Default port

    Points: 1408

    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

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

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