Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Exponent Engima Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, August 8, 2008 4:53 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, May 4, 2016 6:32 AM Points: 2,938, Visits: 830
 "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. Jamie
Post #549001
 Posted Friday, August 8, 2008 8:43 AM
 Hall of Fame Group: General Forum Members Last Login: Friday, September 16, 2016 12:22 PM Points: 3,849, Visits: 72,507
Post #549262
 Posted Friday, August 8, 2008 8:55 AM
 SSChasing Mays Group: General Forum Members Last Login: Today @ 2:09 AM Points: 633, Visits: 339
Post #549283
 Posted Friday, August 8, 2008 9:18 AM
 Hall of Fame Group: General Forum Members Last Login: Friday, September 16, 2016 12:22 PM Points: 3,849, Visits: 72,507
 Sorin Petcu (8/8/2008)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.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, `SELECT -1 * -1`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. --Mark Tassin MCITP - SQL Server DBAProud member of the Anti-RBAR alliance.For help with Performance click this linkFor tips on how to post your problems
Post #549312
 Posted Friday, August 8, 2008 2:39 PM
 SSChasing Mays Group: General Forum Members Last Login: Today @ 2:09 AM Points: 633, Visits: 339
 mtassin (8/8/2008)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 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! In Theory, theory and practice are the same...In practice, they are not.
Post #549596
 Posted Friday, August 8, 2008 3:03 PM
 Hall of Fame Group: General Forum Members Last Login: Friday, September 16, 2016 12:22 PM Points: 3,849, Visits: 72,507
 Sorin Petcu (8/8/2008)[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!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`select convert(binary,-5)select convert(int,0x0000000000000000000000000000000000000000000000000000FFFFFFFB)select convert(int,convert(binary,-5))` --Mark Tassin MCITP - SQL Server DBAProud member of the Anti-RBAR alliance.For help with Performance click this linkFor tips on how to post your problems
Post #549606
 Posted Sunday, August 10, 2008 2:17 AM
 SSChasing Mays Group: General Forum Members Last Login: Today @ 2:09 AM Points: 633, Visits: 339
 no comment!! In Theory, theory and practice are the same...In practice, they are not.
Post #549839
 Posted Tuesday, August 19, 2008 4:46 PM
 SSC Eights! Group: General Forum Members Last Login: Monday, July 1, 2013 4:39 PM Points: 889, Visits: 97
 Good question. Makes a good differentiation between the bit Operator XOR and the Math function POWER.
Post #555398
 Posted Tuesday, August 19, 2008 8:32 PM
 SSCrazy Eights Group: General Forum Members Last Login: Tuesday, October 25, 2016 7:17 AM Points: 9,298, Visits: 9,517
 Thanks, Mohamed. I got the idea for this question because I keep making this mistake myself. -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #555464
 Posted Wednesday, August 20, 2008 10:22 AM
 SSCertifiable Group: General Forum Members Last Login: Friday, December 2, 2016 8:49 AM Points: 7,030, Visits: 14,716
 mtassin (8/8/2008)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.Mark, your argument assumes that the reader knows 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 should be done:float_expression Is an expression of the type float 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!John
Post #555950

 Permissions