Exponent Engima
-
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] -
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
-
What a nasty question... 😛
Is this standard SQL behaviour or just MS style?
Best Regards,
Chris Büttner
-
Got it right :cool:. Out of interest, what was the "@b bigint" for, to try and confuse people with the @b-17 answer :D?
-
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. -
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
-
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. -
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] -
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] -
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."
-
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] -
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] -
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. -
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] -
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 52 total)
You must be logged in to reply to this topic. Login to reply