InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 01:57:45 GMT20http://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxI too liked the discussion as I reread it today.Involve math, SQL, and a good discussion and you have a good QOD!!:cool:Wed, 07 Apr 2010 10:42:06 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxNice job Jason. This is one tough question, and I know that I'm biased but it's really one of my favorite questions ever. (and I am [i]really[/i] glad that the two mistakes that I made in it did not affect the correct answer, whew!) Even the discussion here has been great.Wed, 07 Apr 2010 10:38:31 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxNice question. I think the math behind the question is revealing for the answer.Good Job on this Barry (p.s. got it correct).Wed, 07 Apr 2010 10:29:56 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]mtassin (8/8/2008)[/b]The Square Root of -1 is i, which is an imaginary/complex number. It cannot be returned as a float data type because it is out of the domain of the float data type.[/quote]Mark, your argument assumes that the reader [i]knows[/i] that the result of passing a negative parameter to the function results in an answer that is out of range or of the wrong data type. For instance, if I typed SELECT SQRT(-23) and I got the error "a domain error occurred", how would I know that this is wasn't because I used an odd number rather than because I used a negative number? I totally agree with you that this behaviour is correct: I'm just saying that I think the range of permissible values should be documented in Books Online. Look at the documentation for the ASIN function, for how it [i]should[/i] be done:[i]float_expression [/i]Is an expression of the type [b]float[/b] or of a type that can be implicitly converted to float, with a value from -1 through 1. Values outside this range return NULL and report a domain error.By the way, I was fooled by the @b thing, just like James. I read "negative" instead of "non-negative" and so I immediately discounted the correct answer!JohnWed, 20 Aug 2008 10:22:42 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxThanks, Mohamed. I got the idea for this question because I keep making this mistake myself.Tue, 19 Aug 2008 20:32:50 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxGood question. Makes a good differentiation between the bit Operator XOR and the Math function POWER.Tue, 19 Aug 2008 16:46:56 GMTMohamed-401375http://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxno comment!!Sun, 10 Aug 2008 02:17:16 GMTSorin Petcuhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]Sorin Petcu (8/8/2008)[/b][hr][quote]I think you don't understand or you don't want to understand.Let me see: how it is represented the number -5 in computer? as binar form as 1111111111111111111111111111111111111111111111111111111111111011 and this number would be reconverted as 18446744073709551611 in decimal format.So, SQRT(-5) would be what? guess what: select SQRT(18446744073709551611) = 4294967296So we are talking only about numbers and not math theory. You said forth and forth about complex numbers. Wrong! Why you want to receive a complex number from a function built in a programming language? Why you want all the time that float number can't be asimilated as complex number? It is nothing to do with complex numbers in a programming language or an engine like sql server. Complex numbers are in math theory. So wake up![/quote]Sorry wrong.-5 in binary is what is below for a 64 bit integer (notice how it's 65 bits long).1111111111111111111111111111111111111111111111111111111111111011 when converted to an unsigned integer is 18446744073709551611, when converted to a signed integer it's -5.Just because you're using Calc to do the conversions... it doesn't apparently handle converting negative numbers to binary and back. Take -5 convert it to binary... convert it back... it should be -5 again.I'm sure you will agree that 18446744073709551611 is not equal to -5.Do you understand how to actually convert binary to decimal? http://en.wikipedia.org/wiki/Two's_complementIt's interesting that you've perhaps pointed out a bug in calc.exe though.. if you use excel and do a DEC2BIN of -5 you get 1111111011 and if you use BIN2DEC to convert it back you'll get the correct answer which is -5.Here's an example in SQL[code]select convert(binary,-5)select convert(int,0x0000000000000000000000000000000000000000000000000000FFFFFFFB)select convert(int,convert(binary,-5))[/code]Fri, 08 Aug 2008 15:03:56 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]mtassin (8/8/2008)[/b][hr]We're not talking about negative numbers, negative numbers are just the input to a function, we're talking about the output of the function. The Square Root of a Negative number (the output of the function) is represented in mathematics as an imaginary (pure) or complex (mixed a +bi) number.The SQRT function used by SQL server returns a float. The Float data type is not able to contain a complex number. Thus the return data type of the SQRT of -1 is out of the domain of the float data type and we get an error message. How the function determines this (either it actually tries to calculate it and gets an error after attempting to find this value, or if it throws the error as soon as it sees the negative I don't know.. and frankly, I don't care).[/quote]I think you don't understand or you don't want to understand.Let me see: how it is represented the number -5 in computer? as binar form as 1111111111111111111111111111111111111111111111111111111111111011 and this number would be reconverted as 18446744073709551611 in decimal format.So, SQRT(-5) would be what? guess what: select SQRT(18446744073709551611) = 4294967296So we are talking only about numbers and not math theory. You said forth and forth about complex numbers. Wrong! Why you want to receive a complex number from a function built in a programming language? Why you want all the time that float number can't be asimilated as complex number? It is nothing to do with complex numbers in a programming language or an engine like sql server. Complex numbers are in math theory. So wake up!Fri, 08 Aug 2008 14:39:03 GMTSorin Petcuhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]Sorin Petcu (8/8/2008)[/b][hr]The Square Root of -1 is i, which is an imaginary/complex number."The computer didn't know nothing about complex numbers. You assume that SQRT function, which is written by a computer programmer, should return i for -1 argument. Why? you wrong here. I could write my SQRT function how my muscles want. It is about programming and not about theory. Negative numbers are represented in computer different way by the way you know from theory. So, please don't mix the math theory with what is inside a computer.[/quote]We're not talking about negative numbers, negative numbers are just the input to a function, we're talking about the output of the function. The Square Root of a Negative number (the output of the function) is represented in mathematics as an imaginary (pure) or complex (mixed a +bi) number.The SQRT function used by SQL server returns a float. The Float data type is not able to contain a complex number. Thus the return data type of the SQRT of -1 is out of the domain of the float data type and we get an error message. How the function determines this (either it actually tries to calculate it and gets an error after attempting to find this value, or if it throws the error as soon as it sees the negative I don't know.. and frankly, I don't care).I do know that -1 * -1 = 1 and that SQRT(1) = +/- 1.If you think that SQRT(-1) is a negative number... tell me what negative number squared yeilds a negative number, because what I was taught in math was that -1*-1 = 1. In fact, [code]SELECT -1 * -1[/code]On my SQL box that returns 1, not -1. So -1 Squared = 1... and SQRT(-1) is a domain error.If you write your SQRT function to behave in a way that violates how mathematics work, then your company wouldn't be able to sell the software. That would be like if I decided that my multiplication function should randomly divide all results by .75 because I feel like it. Yah I could write that function, but nobody would sell it, and if they did, it would be acknowledged as a bug (see Intel chips with broken floating point mathematics circa 1994), in the case of software like SQL server this would result in a hotfix released because if your math functions don't follow the rules of mathematics, then companies don't use them.Fri, 08 Aug 2008 09:18:49 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]mtassin (8/8/2008)[/b][hr][quote][b]Sorin Petcu (8/8/2008)[/b][hr][quote][b]mtassin (8/7/2008)[/b][hr][quote][b]Sorin Petcu (8/7/2008)[/b][hr]Allof you, you have had wrong! I know from school, and everybody knows, that sqrt function did not support negative numbers (except complex numbers). But with this in your mind, you will be wrong in computers world. You should be guided by documentation of language. And this documentation is clear enough: sqrt supports negative float numbers. I know, you will say about ansi sql and other stuff like superior programming languages. But this issue is obviously clear.[/quote]No... because SQRT returns a float. AND THE FLOAT DATATYPE DOES NOT SUPPORT COMPLEX/IMAGINARY NUMBERS. Hence SQRT cannot receive a negative number as an input and a domain error will occur because the result of the SQRT(-1) or SQRT(-.01) is outside of the domain of a FLOAT datatype.[/quote]Again, other guy who make wrong suppositions. Theoretical, from math point of view, "SQRT cannot receive a negative number as an input", but here, we are talking about computer universe. Please, lay down the theory and read carefully the documentation (BOL). On the other side, FLOAT has negative values, also.[/quote]This is nothing to do with theory. Read Books Online about the float data type and tell me where it supports complex numbers. The return data type of SQRT is a float, a float data type cannot contain an imaginary or a complex number.The Square Root of -1 is i, which is an imaginary/complex number. It cannot be returned as a float data type because it is out of the domain of the float data type.It's right there in BOL. [quote][b][u]Books Online[/u][/b]Returns the square root of the given expression.Syntax SQRT ( float_expression ) Argumentsfloat_expression An expression whose type can be implicitly converted to float.[b]Return Value[/b][b]float [/b][/quote]And here is the BOL entry for the float data type.[quote]Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Note: The SQL-92 synonym for real is float(24). Data type Range Storage float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Depends on the value of n real - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 4 Bytes Transact-SQL Syntax Conventions[/quote]Where is i listed as an allowed value for a float data type? If the SQRT function returns a float value, the return value has to be within the allowed values for it. The value of SQRT(-1) is outside of the allowed returnable values for the function.This isn't theory... this is fact.As a much simpler approach, take a calculator and try to take the SQRT of -1... if you have a really neato calculator you'll get a complex number back, if you don't you'll get an error.http://en.wikipedia.org/wiki/Imaginary_number[/quote]Again you have fallen in mistake. Again you assume the fact that "The return data type of SQRT is a float, a float data type cannot contain an imaginary or a complex number.The Square Root of -1 is i, which is an imaginary/complex number."The computer didn't know nothing about complex numbers. You assume that SQRT function, which is written by a computer programmer, should return i for -1 argument. Why? you wrong here. I could write my SQRT function how my muscles want. It is about programming and not about theory. Negative numbers are represented in computer different way by the way you know from theory. So, please don't mix the math theory with what is inside a computer.Fri, 08 Aug 2008 08:55:24 GMTSorin Petcuhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]Sorin Petcu (8/8/2008)[/b][hr][quote][b]mtassin (8/7/2008)[/b][hr][quote][b]Sorin Petcu (8/7/2008)[/b][hr]Allof you, you have had wrong! I know from school, and everybody knows, that sqrt function did not support negative numbers (except complex numbers). But with this in your mind, you will be wrong in computers world. You should be guided by documentation of language. And this documentation is clear enough: sqrt supports negative float numbers. I know, you will say about ansi sql and other stuff like superior programming languages. But this issue is obviously clear.[/quote]No... because SQRT returns a float. AND THE FLOAT DATATYPE DOES NOT SUPPORT COMPLEX/IMAGINARY NUMBERS. Hence SQRT cannot receive a negative number as an input and a domain error will occur because the result of the SQRT(-1) or SQRT(-.01) is outside of the domain of a FLOAT datatype.[/quote]Again, other guy who make wrong suppositions. Theoretical, from math point of view, "SQRT cannot receive a negative number as an input", but here, we are talking about computer universe. Please, lay down the theory and read carrefully the documentation (BOL). On the other side, FLOAT has negative values, also.[/quote]This is nothing to do with theory. Read Books Online about the float data type and tell me where it supports complex numbers. The return data type of SQRT is a float, a float data type cannot contain an imaginary or a complex number.The Square Root of -1 is i, which is an imaginary/complex number. It cannot be returned as a float data type because it is out of the domain of the float data type.It's right there in BOL. [quote][b][u]Books Online[/u][/b]Returns the square root of the given expression.Syntax SQRT ( float_expression ) Argumentsfloat_expression An expression whose type can be implicitly converted to float.[b]Return Value[/b][b]float [/b][/quote]And here is the BOL entry for the float data type.[quote]Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Note: The SQL-92 synonym for real is float(24). Data type Range Storage float - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Depends on the value of n real - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 4 Bytes Transact-SQL Syntax Conventions[/quote]Where is i listed as an allowed value for a float data type? If the SQRT function returns a float value, the return value has to be within the allowed values for it. The value of SQRT(-1) is outside of the allowed returnable values for the function.This isn't theory... this is fact.As a much simpler approach, take a calculator and try to take the SQRT of -1... if you have a really neato calculator you'll get a complex number back, if you don't you'll get an error.http://en.wikipedia.org/wiki/Imaginary_numberFri, 08 Aug 2008 08:43:32 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx"Exclusive or" ouch - I was thinking exponential also. Arguments expressing dismay that mixing numeric types provide incorrect results, hmmm, isn't that a big part of programming? declare@x moneyset @x=9.99set @x=@x+.00489999Print @x --no changeset @x=9.999Print @x --duh - 10It's always about the math.Fri, 08 Aug 2008 04:53:10 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]mtassin (8/7/2008)[/b][hr][quote][b]Sorin Petcu (8/7/2008)[/b][hr]Allof you, you have had wrong! I know from school, and everybody knows, that sqrt function did not support negative numbers (except complex numbers). But with this in your mind, you will be wrong in computers world. You should be guided by documentation of language. And this documentation is clear enough: sqrt supports negative float numbers. I know, you will say about ansi sql and other stuff like superior programming languages. But this issue is obviously clear.[/quote]No... because SQRT returns a float. AND THE FLOAT DATATYPE DOES NOT SUPPORT COMPLEX/IMAGINARY NUMBERS. Hence SQRT cannot receive a negative number as an input and a domain error will occur because the result of the SQRT(-1) or SQRT(-.01) is outside of the domain of a FLOAT datatype.[/quote]Again, other guy who make wrong suppositions. Theoretical, from math point of view, "SQRT cannot receive a negative number as an input", but here, we are talking about computer universe. Please, lay down the theory and read carrefully the documentation (BOL). On the other side, FLOAT has negative values, also.Fri, 08 Aug 2008 01:29:15 GMTSorin Petcuhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxah, that explains now. thanks for picking that up. We sure find mistakes in BOL (how scary to think how often we refer to it!)Thu, 07 Aug 2008 20:16:20 GMTIggy-SQLhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxI checked the 2008 doc and apparently, they are all supposed to be precedence 3: [url]http://technet.microsoft.com/en-us/library/ms190276(SQL.100).aspx[/url].Thu, 07 Aug 2008 19:22:21 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxYeah, I tested a bunch of them, and the simplest answer is that ( ^, |, and & ) are really precedence 3 and ( +, - ) are really precedence 5.Thu, 07 Aug 2008 19:14:10 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxI've tested rbarryyoung's code again with a + instead, and it seems it does the ^ first, then the + sign, which indeed contradicts the BOL.Thu, 07 Aug 2008 18:37:25 GMTIggy-SQLhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]rbarryyoung (8/7/2008)[/b][hr][quote][b]Iggy (8/7/2008)[/b][hr][quote]@a^@a is a bitwise XOR, not an exponent operation. Any value XOR'd with itself is always 0. Subtracting 17 makes it -17, which is a negative number, and an invlaid input for the SQRT() function.[/quote]Actually, the explanation is not correct, even though the answer is.If you remember operator precedence, - comes before ^, so @a^@a - 17 actually translates to @a ^ (@a - 17) or 16 ^ -1 which is -17 (same result but different logic).[/quote]Excellent point. However, this seems to be a [i]true[/i] case of the doc being wrong:Try executing the following:[code]declare @a bigintSet @a = 17Select @a^@a-17 as [QotD], (@a^@a)-17 as [^ first], @a^(@a-17) as [- first][/code]I get the following results:[code]QotD ^ first - first-------------------- -------------------- ---------------------17 -17 17[/code][/quote]excellent test, interesting how the QotD way yields different result to the - first. any thoughts anyone? :cool:Thu, 07 Aug 2008 18:33:59 GMTIggy-SQLhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]Iggy (8/7/2008)[/b][hr][quote]@a^@a is a bitwise XOR, not an exponent operation. Any value XOR'd with itself is always 0. Subtracting 17 makes it -17, which is a negative number, and an invlaid input for the SQRT() function.[/quote]Actually, the explanation is not correct, even though the answer is.If you remember operator precedence, - comes before ^, so @a^@a - 17 actually translates to @a ^ (@a - 17) or 16 ^ -1 which is -17 (same result but different logic).[/quote]Excellent point. However, this seems to be a [i]true[/i] case of the doc being wrong:Try executing the following:[code]declare @a bigintSet @a = 17Select @a^@a-17 as [QotD], (@a^@a)-17 as [^ first], @a^(@a-17) as [- first][/code]I get the following results:[code]QotD ^ first - first-------------------- -------------------- ---------------------17 -17 17[/code]Thu, 07 Aug 2008 18:27:03 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]tim_brimelow (8/7/2008)[/b][hr]And after experimenting with the power function I have found that Power(15,15) comes up with the wrong answer - I get 437893890380859392 and the correct answer is 437893890380859375. I wonder why and it makes me think that perhaps a few other functions in SQL server also generate the wrong answer.Tim Brimelow[/quote]I think that you are hitting the limits of the Float data type (which POWER() uses).Thu, 07 Aug 2008 18:17:19 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote]@a^@a is a bitwise XOR, not an exponent operation. Any value XOR'd with itself is always 0. Subtracting 17 makes it -17, which is a negative number, and an invlaid input for the SQRT() function.[/quote]Actually, the explanation is not correct, even though the answer is.If you remember operator precedence, - comes before ^, so @a^@a - 17 actually translates to @a ^ (@a - 17) or 16 ^ -1 which is -17 (same result but different logic).Thu, 07 Aug 2008 18:09:06 GMTIggy-SQLhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxAnd after experimenting with the power function I have found that Power(15,15) comes up with the wrong answer - I get 437893890380859392 and the correct answer is 437893890380859375. I wonder why and it makes me think that perhaps a few other functions in SQL server also generate the wrong answer.Tim BrimelowThu, 07 Aug 2008 17:52:19 GMTtim_brimelowhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]tim_brimelow (8/7/2008)[/b][hr]So two things crop up here,1) how do you raise a number to a power in SQL server?and2) was the misspelling of enigma deliberate ( the author has used "Engima")[/quote]Ah, now those I can answer:1) POWER(x,y) function.2) Nope. This is the first time that I noticed it. Good catch.Thu, 07 Aug 2008 17:44:56 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxSo two things crop up here,1) how do you raise a number to a power in SQL server?and2) was the misspelling of enigma deliberate ( the author has used "Engima")Tim BrimelowThu, 07 Aug 2008 17:28:03 GMTtim_brimelowhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]Sorin Petcu (8/7/2008)[/b][hr]Allof you, you have had wrong! I know from school, and everybody knows, that sqrt function did not support negative numbers (except complex numbers). But with this in your mind, you will be wrong in computers world. You should be guided by documentation of language. And this documentation is clear enough: sqrt supports negative float numbers. I know, you will say about ansi sql and other stuff like superior programming languages. But this issue is obviously clear.[/quote]No... because SQRT returns a float. AND THE FLOAT DATATYPE DOES NOT SUPPORT COMPLEX/IMAGINARY NUMBERS. Hence SQRT cannot receive a negative number as an input and a domain error will occur because the result of the SQRT(-1) or SQRT(-.01) is outside of the domain of a FLOAT datatype.Thu, 07 Aug 2008 14:04:03 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxAllof you, you have had wrong! I know from school, and everybody knows, that sqrt function did not support negative numbers (except complex numbers). But with this in your mind, you will be wrong in computers world. You should be guided by documentation of language. And this documentation is clear enough: sqrt supports negative float numbers. I know, you will say about ansi sql and other stuff like superior programming languages. But this issue is obviously clear.Thu, 07 Aug 2008 12:46:08 GMTSorin Petcuhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxFolks: For What It's Worth: I sincerely doubt that I would have gotten this question right myself. I am pretty sure of that because I have actually tripped over several times myself in the course of my work. In fact, that is what inspired me to use it (the assumption that "^" is the exponent operator) as the basis for a QotD.Thu, 07 Aug 2008 11:57:07 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]Melville (8/7/2008)[/b][hr]Good question, the bitwise XOR ^ made this a school day for me (always good). Once I took out the -17 it became clear that sqrt (n^n) (n is any integer) returned 0 rather than a positive number; meaning that the -17 caused the error. It also explained why the first answer was wrong.And then it was simple to assume that Sql 2005 didn't like imaginary numbers (which is what I remember calling square roots of negative numbers back in the day).And so the fault can also be achieved by:[code]select sqrt(-17)[/code][/quote]Great job, Melville. One of my goals was to ask a question that could not be answered by just copying the text into a Query window and executing it, but that [i]could[/i] be answered without an encyclopedic knowledge of SQL Server, just using good problem-solving skills. I'm glad to see that that worked for at least some people!Thu, 07 Aug 2008 11:51:55 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]rbarryyoung (8/7/2008)[/b][hr][quote][b]Festeron (8/7/2008)[/b][hr]There are problems with the question, though.Why is @b declared and never used? Where is @b-17? Answer B cannot be correct[/quote]Yikes! You are right, "@b" shouldn't be in there and Answer B should read "@a-17...". Fortunately, it doesn't change the correct answer.[quote]Answer A is not just a distraction, it's wrong to include the "=2^64" because you are deliberately misleading us. A much better A would have been "16^16 is out of range for the bigint datatype' which [in hindsight] is clearly not true.[/quote]Yes it is trying to mislead you by playing on the mistaken assumption that "^" is the exponent operator, just as the Title is. However, that is the whole point of the Question: it [i]is[/i] a mistaken assumption and if you do not figure that out, you will come to the wrong conclusion. And after all, (A) is an [i]incorrect[/i] answer, whether it is incorrect for one reason or two (and both the same reason at that) doesn't really matter.[/quote]I got totally caught with the @b. I actually knew that the '^' was the exclusive or operator and not the EXP operator but I took @b to be the result of @a^@a, in which case the expression evaluates to 0-17 = -17. This just confused me more because there are two correct answers with this assumption and, of course, I picked the wrong one.Thu, 07 Aug 2008 11:39:04 GMTJames Rochezhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxHi,Good Question!!!! For once answered correctly. I would love to see i and other mathematical imaginary features and operations in SQL Server just a little bit more complex than listed in the today's article "Calculating Mathematical Values in SQL Server"http://www.mssqltips.com/tip.asp?tip=1547that discussed 1*1As for the possible other answer that should be listed: "Bug in documentation"Thu, 07 Aug 2008 09:57:01 GMTYelena Varshalhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxAnd then I found this forum post from May!http://www.sqlservercentral.com/Forums/Topic239436-8-1.aspxThu, 07 Aug 2008 09:43:07 GMTMelvillehttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxGood question, the bitwise XOR ^ made this a school day for me (always good). Once I took out the -17 it became clear that sqrt (n^n) (n is any integer) returned 0 rather than a positive number; meaning that the -17 caused the error. It also explained why the first answer was wrong.And then it was simple to assume that Sql 2005 didn't like imaginary numbers (which is what I remember calling square roots of negative numbers back in the day).And so the fault can also be achieved by:[code]select sqrt(-17)[/code]Thu, 07 Aug 2008 09:42:17 GMTMelvillehttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxNo argument on the format, trick or the answer. After one of the earlier questions last week that was a slight bit tricky I looked real close and got it right.You got to be careful to be right. But that is the way business is isn't it? :)ThanksMiles...Thu, 07 Aug 2008 09:18:32 GMTMiles Nealehttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxAlmost:[code]DECLARE @a BIGINT , @b BIGINTSET @a = 16SELECT SQRT(POWER(@a,@a) - 17)[/code]and this will give an overflow error.Thu, 07 Aug 2008 09:04:05 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxso, would this be the proper expression if it were really an exponential question?DECLARE @a BIGINT , @b BIGINTSET @a = 16SELECT SQRT(POWER(@a,@a - 17))bcThu, 07 Aug 2008 08:49:14 GMTbc_http://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]Festeron (8/7/2008)[/b][hr]There are problems with the question, though.Why is @b declared and never used? Where is @b-17? Answer B cannot be correct[/quote]Yikes! You are right, "@b" shouldn't be in there and Answer B should read "@a-17...". Fortunately, it doesn't change the correct answer.[quote]Answer A is not just a distraction, it's wrong to include the "=2^64" because you are deliberately misleading us. A much better A would have been "16^16 is out of range for the bigint datatype' which [in hindsight] is clearly not true.[/quote]Yes it is trying to mislead you by playing on the mistaken assumption that "^" is the exponent operator, just as the Title is. However, that is the whole point of the Question: it [i]is[/i] a mistaken assumption and if you do not figure that out, you will come to the wrong conclusion. And after all, (A) is an [i]incorrect[/i] answer, whether it is incorrect for one reason or two (and both the same reason at that) doesn't really matter.Thu, 07 Aug 2008 08:38:38 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspxI have to admit, I was totally distracted by the caret, and didn't even consider bitwise ops. Nice distraction.There are problems with the question, though.Why is @b declared and never used? Where is @b-17? Answer B cannot be correctAnswer A is not just a distraction, it's wrong to include the "=2^64" because you are deliberately misleading us. A much better A would have been "16^16 is out of range for the bigint datatype' which [in hindsight] is clearly not true.Still, a nice question. BOL should be updated.Thu, 07 Aug 2008 08:23:44 GMTFesteronhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]Sorin Petcu (8/7/2008)[/b][hr][quote][b]rbarryyoung (8/7/2008)[/b][hr]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).[/quote]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+308i 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!Thu, 07 Aug 2008 08:01:11 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic547995-1226-1.aspx[quote][b]Sorin Petcu (8/7/2008)[/b][hr]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+308so, where it says that the argument of SQRT should be not negative!???[/quote]Right. The [i]datatype[/i] 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][b]i[/b][/i] or [i][b]-i[/b][/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).Thu, 07 Aug 2008 07:58:43 GMTRBarryYoung