Post reply

The Order of Operations

  • June 9, 2020 at 2:55 pm

    #3761682

    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

  • June 9, 2020 at 3:01 pm

    #3761684

    trowley wrote:

    So -100/-100*10 is being interpreted as  (-100/(-100*10))

    Then why is 100/100*10 interpreted as  (100/100)*10) ?

  • June 9, 2020 at 3:21 pm

    #3761688

    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

  • June 9, 2020 at 3:48 pm

    #3761704

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • June 9, 2020 at 4:05 pm

    #3761717

    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!

  • June 9, 2020 at 4:28 pm

    #3761723

    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.

  • June 9, 2020 at 4:32 pm

    #3761727

    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

  • June 9, 2020 at 5:39 pm

    #3761755

    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?

    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.

  • June 9, 2020 at 6:43 pm

    #3761773

    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.

  • June 9, 2020 at 7:30 pm

    #3761791

    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

  • June 9, 2020 at 7:53 pm

    #3761793

    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.

  • June 10, 2020 at 7:54 pm

    #3762104

    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”

  • June 10, 2020 at 11:11 pm

    #3762153

    I tried the code in a variety of database engines (Db2, Oracle, MySQL, PostgeSQL, Hana, Teradata) and the answer is 10.

  • June 11, 2020 at 4:28 pm

    #3762353

    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".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 11, 2020 at 4:48 pm

    #3762363

    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 34 total)

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