SQLServerCentral » Article Discussions » Article Discussions by Author » Discuss content posted by RBarryYoung » Exponent EngimaInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralWed, 26 Jul 2017 06:43:28 GMT20Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost547995.aspxComments posted to this topic are about the item [B]<A HREF="/questions/TSQL/63632/">Exponent Engima</A>[/B]Thu, 18 Sep 2014 07:50:19 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost898760.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 GMTSQLRNNRRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost898759.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 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost898746.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 GMTSQLRNNRRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost555950.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-245523RE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost555464.aspxThanks, Mohamed. I got the idea for this question because I keep making this mistake myself.Tue, 19 Aug 2008 20:32:50 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost555398.aspxGood question. Makes a good differentiation between the bit Operator XOR and the Math function POWER.Tue, 19 Aug 2008 16:46:56 GMTMohamed-401375RE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost549839.aspxno comment!!Sun, 10 Aug 2008 02:17:16 GMTSorin PetcuRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost549606.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) = 4294967296
So 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_complement
It'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 GMTmtassinRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost549596.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) = 4294967296
So 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 PetcuRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost549312.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 GMTmtassinRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost549283.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 )
Arguments
float_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 PetcuRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost549262.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 )
Arguments
float_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 GMTmtassinRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost549001.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 money
set @x=9.99
set @x=@x+.00489999
Print @x --no change
set @x=9.999
Print @x --duh - 10
It's always about the math.Fri, 08 Aug 2008 04:53:10 GMTJamie-2229RE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548905.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 PetcuRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548817.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-SQLRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548796.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 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548790.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 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548779.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-SQLRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548777.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 bigint
Set @a = 17
Select @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-SQLRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548773.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 bigint
Set @a = 17
Select @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 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548770.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 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548765.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-SQLRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548760.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_brimelowRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548759.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?
and
2) 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 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548755.aspxSo two things crop up here,
1) how do you raise a number to a power in SQL server?
and
2) was the misspelling of enigma deliberate ( the author has used "Engima")
Tim BrimelowThu, 07 Aug 2008 17:28:03 GMTtim_brimelowRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548646.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 GMTmtassinRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548590.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 PetcuRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548554.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 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548552.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 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548541.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 RochezRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548428.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=1547
that discussed 1*1
As for the possible other answer that should be listed: "Bug in documentation"Thu, 07 Aug 2008 09:57:01 GMTYelena VarshalRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548416.aspxAnd then I found this forum post from May!
http://www.sqlservercentral.com/Forums/Topic239436-8-1.aspxThu, 07 Aug 2008 09:43:07 GMTMelvilleRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548414.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 GMTMelvilleRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548387.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? :)
Thanks
Miles...Thu, 07 Aug 2008 09:18:32 GMTMiles NealeRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548371.aspxAlmost:
[code]
DECLARE @a BIGINT
, @b BIGINT
SET @a = 16
SELECT SQRT(POWER(@a,@a) - 17)
[/code]
and this will give an overflow error.Thu, 07 Aug 2008 09:04:05 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548348.aspxso, would this be the proper expression if it were really an exponential question?
DECLARE @a BIGINT
, @b BIGINT
SET @a = 16
SELECT SQRT(POWER(@a,@a - 17))
bcThu, 07 Aug 2008 08:49:14 GMTbc_RE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548338.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 GMTRBarryYoungRE: Exponent Engimahttps://www.sqlservercentral.com/Forums/FindPost548326.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 correct
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.
Still, a nice question. BOL should be updated.Thu, 07 Aug 2008 08:23:44 GMTFesteron