SQL Server Quiz: Understanding behavior April 4th, 2013

Vinod Kumar

I wanted to write a simple post and yet make it interesting for everyone. So here is a neat little trivia and wondering how many want to take a shot at this. Why do I get different results for below queries. What could be the reason?

SELECT 20.0 /2.0 / 5.0 * 3.0

— -16.6666666666666

 

SELECT 20.0 /(-2.0)/ 5.0 * 3.0

— -6.0000000000 

Though the question is simple it is quite intriguing how the engine works behind the scene. So let the ideas flow and any guesses how we can get the same result?

PS: Awesome to see some complete solutions expressed via comments. Thanks to those who took time to write. Understanding the behavior is important and hope we learnt something new here.

Tags: , , , , ,

This entry was posted on Thursday, April 4th, 2013 at 12:13 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


12 Responses to “SQL Server Quiz: Understanding behavior”

  1. Sachin says:

    Hi Vinod I got reference of this site from on of friend.
    In above question there is game of operator precedence only weather me think.
    Preceden as Follows
    1. ()
    2. *
    3. /
    SO Internal interpretation should be as Follows
    1…………..
    SELECT 20.0 / -2.0/ 5.0 * 3.0
    SELECT 20.0 / (-2.0/ 5.0 * 3.0)
    select 20.0/-1.2
    2……………….
    SELECT 20.0/ (-2.0)/ 5.0 * 3.0
    SELECT (20.0/ -2.0)/ 5.0 * 3.0
    SELECT 10/ 5.0 * 3.0

  2. shree says:

    Hello Vinod,

    The difference in the out is because of the Operator presedence used by SQL server to determine the sequence of operation.

    SELECT 20.0 / -2.0 / 5.0 * 3.0
    – -16.6666666666666

    — The Sequence is -2.0 / 5.0 * 3.0 = -1.2
    20 / -1.2 = -16.6666666666666

    SELECT 20.0 / (-2.0) / 5.0 * 3.0
    – -6.0000000000

    — Parentheses are used to override the defined precedence.
    The Sequence is : 20.0 / (-2.0) = -10
    -10 / 5.0 = -2
    -2 * 3.0 = -6.0000000000

    Thanks,

    Shree

  3. Chandru Maruthappan says:

    This Will execute by BODMAS operator precedence

    B->Brackets
    O->Operators
    D->Division
    M->Multiplication
    A->Addition
    S->Subtraction

    SELECT (20.0 / ((-2.0 / 5.0) * 3.0))

    — -16.6666666666666

    SELECT (((20.0 / (-2.0)) / 5.0 )* 3.0)

    — -6.0000000000

  4. sindhu says:

    The reason those two queries gives different results is because of the Operators precendence as both of the
    SELECT statements contains multiple operators the operators are evaluated differently.

    The order of execution of two SELECT statements is as follows

    SELECT 20.0 / -2.0 / 5.0 * 3.0

    SELECT -2.0 / 5.0
    SELECT -2.0 / 5.0 * 3.0
    SELECT 20.0 / -2.0 / 5.0 * 3.0 — Final statement

    SELECT 20.0 / (-2.0) / 5.0 * 3.0

    SELECT 20.0 / (-2.0)
    SELECT 20.0 / (-2.0) / 5.0
    SELECT 20.0 / (-2.0) / 5.0 * 3.0 — Final statement

  5. William Andrus says:

    What I can figure, the ‘-‘ is considered a unary operator, which is taking precedence over the multiplication and division. Which contradicts http://msdn.microsoft.com/en-us/library/ms190276.aspx

  6. SELVA KUMAR S/SIVARAMAN R says:

    1.SELECT 20.0 / -2.0 / 5.0 * 3.0

    – -16.6666666666666

    According to BODMAS/PEMDAS Rules, Operations for Division/Multiplication is to be done from Left To Right. But, In this Scenario, After the Divisor, Subtraction follows it. Hence, whatever the values before the two operators coming continuously in a row is ignored/negated(Or calculated in the Last) and continue with the Left to Right Rule Starts from -2.0(for this scenario).
    Hence -2.0/5.0=-0.4
    -0.4*3.0=-1.2
    Finally, 20/-1.2=-16.666666666

    2.SELECT 20.0 / (-2.0) / 5.0 * 3.0

    – -6.0000000000
    Here (-2.0) represents an Integer. Hence It follows BODMAS/PEMDAS Rule.

    Final Conclusion is: Whenever two operators comes together which ends with + Or – preceded by either / or *, will start the operation from the place where the two operators come together.

  7. Vinod Kumar says:

    Some really cool and complete answers written by some. Keep guessing and keep the responses coming this way. Thanks for taking time.

  8. Brij says:

    The main part of your query lies in:

    Query 1: SELECT 20.0/-2.0/5

    Query 2: SELECT 20/(-2.0)/5

    In Oracle, -2 is the output for both queries.

    In SQL Server they differ, output of first is: -50.0 and second is -2.0

    For query: SELECT 20.0/-2.0/5
    Processing steps:
    1) 20.0 * 5.0 = 100.0
    2) 100.0/-2.0
    3) Output: -2.0

    For query SELECT 20/(-2.0)/5
    Processing steps:
    1) 20.0/(-2.0) = -10
    2) -10/5
    3) Output: -2

  9. Sandip Pani says:

    In SQL server unary operator has lower precedence than *, / .
    due to incorrect operator precedence when the unary minus (-) is used in conjunction with multiplication (*) and division (/) operators.

    This issue is raised to microsoft here
    https://connect.microsoft.com/SQLServer/feedback/details/127183/incorrect-evaluation-of-a-simple-arithmetic-expression-involving-a-fraction-with-a-negative-denominator#details

    I checked in SQL server 2012 and the issue is still exist.

    Work arround is : Add a set of parentheses around negative constants.

  10. Chintak Chhapia says:

    This happens due to Operator Precedence (http://msdn.microsoft.com/en-us/library/ms190276.aspx)

    – (Negative) has less precedence then “*” or “/” . That’s when there is no parenthesis, the expression becomes

    select 20.0/ -(2.0 / 5.0 * 3.0)

    While in case of parenthesis, the expression gets evaluated from left to right as all the operators in expression have same precedence.

    Thanks for providing nice example which emphasis on need to surround values with parenthesis when we use variables and variables can have negative values.

    Again thanks for the nice puzzle. I learn something new today.

  11. SHAHEER says:

    1. Execution will be like this ..

    SELECT (20.0 / – ((2.0 / 5.0) * 3.0))
    1. (2.0 / 5.0) = .4
    2.(0.4 * 3.0)) = 1.2
    3.20/ (1.2) = 16.6666
    4. Negation – = -16.6666

    2. SELECT 20.0 / (-2.0) / 5.0 * 3.0
    left to right
    1. SELECT 20.0 /(-2.0) = -10.00
    2. SELECT -10.00/5.0 = -2.00000000
    3. SELECT -2.00000000*3.0 = -6.000000000

  12. It’s all about the operator precedence AND that fact that when two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression.

    * (Multiply), / (Division), % (Modulo) operators hold the same precedence level. But the subtract operator has a lower precedence. Hence, going by that logic:

    Case #1:
    As SQL Server moves from left to right, it encounters the subtract operator. Hence, the expression after the subtract is evaluated first. The steps therefore are:
    1. SELECT 2.0/5.0 –gives 0.400000 as result
    2. SELECT 0.400000 * 3.0 — gives 1.2000000 as result
    3. Finally, SELECT 20/-1.2000000 — which gives -16.666666666 as result

    Case #2:
    Here, the (-2.0) is treated as a negative value, and therefore, the evaluation steps are:
    1. SELECT 20.0/(-2.0) — gives -10.000000 as result
    2. SELECT (-10.000000)/5.0 — gives -2.000000000 as result
    3. SELECT (-2.000000000) * 3.0 — gives -6.0000000000 as result

Leave a Reply