Exponent Engima

  • RBarryYoung

    SSC Guru

    Points: 143327

    Comments posted to this topic are about the item Exponent Engima

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • hodgy

    SSCertifiable

    Points: 5735

    Oh, I fell for that one. good question, highlighting an incorrect assumption of mine (I neither use exponents nor bitwise XORs on a regular basis).

    Thanks, Tom

    Life: it twists and turns like a twisty turny thing

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    What a nasty question... 😛

    Is this standard SQL behaviour or just MS style?

    Best Regards,

    Chris Büttner

  • skyline666

    Ten Centuries

    Points: 1099

    Got it right :cool:. Out of interest, what was the "@b bigint" for, to try and confuse people with the @b-17 answer :D?

  • Sorin Petcu

    Hall of Fame

    Points: 3145

    what a glitch!!

    in BOL it says that SQRT ( float_expression )

    and float could be, as definition of float,

    float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

    so, where it says that the argument of SQRT should be not negative!???

    In Theory, theory and practice are the same...In practice, they are not.
  • hodgy

    SSCertifiable

    Points: 5735

    i think it is more a case of sql server probably not being able to handle complex numbers (the square root of a negative number).

    Life: it twists and turns like a twisty turny thing

  • Sorin Petcu

    Hall of Fame

    Points: 3145

    yeah, but the question was from TSQL category. so, it supposed to be on sql issue and not sql server engine issue.

    In Theory, theory and practice are the same...In practice, they are not.
  • RBarryYoung

    SSC Guru

    Points: 143327

    hodgy (8/7/2008)


    Oh, I fell for that one. good question, highlighting an incorrect assumption of mine (I neither use exponents nor bitwise XORs on a regular basis).

    Thanks, Tom

    Thanks Tom. Yeah, hardly anyone does in SQL server, so that was the hidden corner I was aiming for.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung

    SSC Guru

    Points: 143327

    Christian Buettner (8/7/2008)


    What a nasty question... 😛

    Is this standard SQL behaviour or just MS style?

    Standard ANSI SQL. The key is that "^" is NOT the exponent operator as many (including myself) have assumed and as it is in VB. Rather it is the bitwise XOR operator, as in C#, which of course gives completely different results. That is standard ANSI.

    Square Root functions require non-negative inputs in virtually every production language ever, except those that have built-in complex numbers, like Fortan and ADA.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Rudyx - the Doctor

    SSC-Forever

    Points: 43696

    Cool question - I have not had to think about complex numbers, a+bi, since high school !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • RBarryYoung

    SSC Guru

    Points: 143327

    skyline666 (8/7/2008)


    Got it right :cool:. Out of interest, what was the "@b bigint" for, to try and confuse people with the @b-17 answer :D?

    Actually, if was to give versimilitude to the first answer: "16^16 = 2^64 which is out of bigint's range". I figured that folks would see think "Aha! BigInt DOES have 64 bits, but half of the range is negative, so it can actually only go up to 2**63!". And so, thinking that they had detected my trick would go with that.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • mtassin

    SSC-Insane

    Points: 23099

    Sorin Petcu (8/7/2008)


    what a glitch!!

    in BOL it says that SQRT ( float_expression )

    and float could be, as definition of float,

    float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

    so, where it says that the argument of SQRT should be not negative!???

    Becaust SQRT(-1) = i ?

    Somehow I doubt we'll see SQL handle imaginary numbers any time soon.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Sorin Petcu

    Hall of Fame

    Points: 3145

    rbarryyoung (8/7/2008)


    skyline666 (8/7/2008)


    Got it right :cool:. Out of interest, what was the "@b bigint" for, to try and confuse people with the @b-17 answer :D?

    Actually, if was to give versimilitude to the first answer: "16^16 = 2^64 which is out of bigint's range". I figured that folks would see think "Aha! BigInt DOES have 64 bits, but half of the range is negative, so it can actually only go up to 2**63!". And so, thinking that they had detected my trick would go with that.

    Actually, it was not any trick here. Because someone would select 16^16 which returns 0. And this means that the input for sqrt will be a negative number. According to BOL, sqrt should receive negative numbers also (float).

    In Theory, theory and practice are the same...In practice, they are not.
  • RBarryYoung

    SSC Guru

    Points: 143327

    Sorin Petcu (8/7/2008)


    what a glitch!!

    in BOL it says that SQRT ( float_expression )

    and float could be, as definition of float,

    float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

    so, where it says that the argument of SQRT should be not negative!???

    Right. The datatype float is the valid input type for SQRT(), but not all possible values are allowed as input. This is because SQRT(-1) is technically either i or -i, both of which are unexpressable in any native numeric type in SQL. That makes SQL's math "Real" instead of "Complex" and in all Real Math environments, negative numbers are outside of the domain of accpeted input values for SQRT().

    Technically you are right, this is not documented in BOL. However, it is documented in the ANSI SQL specs and well understood as a natural limitation of SQRT() in all Real-based languages (LOG() has similar restricitons).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • mtassin

    SSC-Insane

    Points: 23099

    Sorin Petcu (8/7/2008)


    rbarryyoung (8/7/2008)


    Actually, it was not any trick here. Because someone would select 16^16 which returns 0. And this means that the input for sqrt will be a negative number. According to BOL, sqrt should receive negative numbers also (float).

    Except that it has to return a float. And the float data type is defined as

    a number in the range - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

    i is not in that range. it's complex and falls outside of the non-imaginary number range. Hence SQRT which must return a float will throw a domain error.

    Heck I got it wrong... because I remembered from the days of Pascal I think using ^ as the exponent operator... but once I learned about it being the XOR operator I could accept my wrongness... this is a good tricky question!



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

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

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