

SSCrazy Eights
Group: General Forum Members
Last Login: Friday, June 12, 2015 8:42 AM
Points: 9,294,
Visits: 9,496





SSCrazy Eights
Group: General Forum Members
Last Login: Friday, June 12, 2015 8:42 AM
Points: 9,294,
Visits: 9,496


Tom.Thomson (4/8/2010)
RBarryYoung (4/7/2010)
Paul White NZ (4/7/2010)
RBarryYoung (4/7/2010) Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.Ooo  I had never tried POWER. Have now, and you're right  though POWER (0, 1) gives me a dividebyzero, rather than a domain error or invalid floating point operation. Yeah, there's just so many reasons that ones wrong, it's hard to pick just one. Almost as bad as POWER(0, 0). What do you want it to do? Do you want it to (a) return 0 so that f(x) = POWER (x,0) has a discontinuity at x = 0 while g(x) = POWER(0,x) is continuous everywhere (ie invert the current situation) or (b) return NaN so that both functions are "undefined" at x=0 (but that requires support for the IEEE standard in SQLS, which hasn't yet happened: see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674) or (c) return a domain error (so that both functions are "undefined" in such a way that no programmer can deal with it and must always test for the special case) or (d) return two different catchable errors from POWER(0,X) (one for x < 0, another for x=0) ?? For me, (b) is the only useful approach, but note that this requires in addition a catchable error to handle attempts to insert NaNs into an index column (since such columns can allow NaN no more than they can allow NULL).
I seem to remember an argument made by one of my professors in college that "Zero to the Zeroth power" could be shown to be nonasymptotically discontinuous in an uncountably infinite number of ways and in fact in a transfiniteclass number of ways that was at least one transfiniteclass higher than that for "0/0". But that's probably too many exceptions to handle in most programs.
Point being (I guess), that even plain old numerical mathematics theory can never be perfectly mapped/applied to real programming, there's always something that has to be swept under the carpet and just smoothed over for practical purposes. It's the main reason that I get so prickly when Relational Purists start moaning about NULLs in SQL. Heck if even basic arithmetic has to swallow DivideByZero in application, then I think that the Relational Model should be able to survive NULLs. But that's jsut me ...
 RBarryYoung, (302)3750451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."




SSCoach
Group: General Forum Members
Last Login: Monday, May 23, 2016 11:31 AM
Points: 19,799,
Visits: 18,071


RBarryYoung (4/8/2010)
Tom.Thomson (4/8/2010)
RBarryYoung (4/7/2010)
Paul White NZ (4/7/2010)
RBarryYoung (4/7/2010) Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.Ooo  I had never tried POWER. Have now, and you're right  though POWER (0, 1) gives me a dividebyzero, rather than a domain error or invalid floating point operation. Yeah, there's just so many reasons that ones wrong, it's hard to pick just one. Almost as bad as POWER(0, 0). What do you want it to do? Do you want it to (a) return 0 so that f(x) = POWER (x,0) has a discontinuity at x = 0 while g(x) = POWER(0,x) is continuous everywhere (ie invert the current situation) or (b) return NaN so that both functions are "undefined" at x=0 (but that requires support for the IEEE standard in SQLS, which hasn't yet happened: see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674) or (c) return a domain error (so that both functions are "undefined" in such a way that no programmer can deal with it and must always test for the special case) or (d) return two different catchable errors from POWER(0,X) (one for x < 0, another for x=0) ?? For me, (b) is the only useful approach, but note that this requires in addition a catchable error to handle attempts to insert NaNs into an index column (since such columns can allow NaN no more than they can allow NULL). I seem to remember an argument made by one of my professors in college that "Zero to the Zeroth power" could be shown to be nonasymptotically discontinuous in an uncountably infinite number of ways and in fact in a transfiniteclass number of ways that was at least one transfiniteclass higher than that for "0/0". But that's probably too many exceptions to handle in most programs. Point being (I guess), that even plain old numerical mathematics theory can never be perfectly mapped/applied to real programming, there's always something that has to be swept under the carpet and just smoothed over for practical purposes. It's the main reason that I get so prickly when Relational Purists start moaning about NULLs in SQL. Heck if even basic arithmetic has to swallow DivideByZero in application, then I think that the Relational Model should be able to survive NULLs. But that's jsut me ...
You raise another quite interesting topic for me. Is 0/0 = 1 or is 0/0 an error?
Jason AKA CirqueDeSQLeil I have given a name to my pain... MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions  Gail Shaw




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, June 12, 2015 8:42 AM
Points: 9,294,
Visits: 9,496


CirquedeSQLeil (4/8/2010)
RBarryYoung (4/8/2010)
Tom.Thomson (4/8/2010)
RBarryYoung (4/7/2010)
Paul White NZ (4/7/2010)
RBarryYoung (4/7/2010) Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.Ooo  I had never tried POWER. Have now, and you're right  though POWER (0, 1) gives me a dividebyzero, rather than a domain error or invalid floating point operation. Yeah, there's just so many reasons that ones wrong, it's hard to pick just one. Almost as bad as POWER(0, 0). What do you want it to do? Do you want it to (a) return 0 so that f(x) = POWER (x,0) has a discontinuity at x = 0 while g(x) = POWER(0,x) is continuous everywhere (ie invert the current situation) or (b) return NaN so that both functions are "undefined" at x=0 (but that requires support for the IEEE standard in SQLS, which hasn't yet happened: see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674) or (c) return a domain error (so that both functions are "undefined" in such a way that no programmer can deal with it and must always test for the special case) or (d) return two different catchable errors from POWER(0,X) (one for x < 0, another for x=0) ?? For me, (b) is the only useful approach, but note that this requires in addition a catchable error to handle attempts to insert NaNs into an index column (since such columns can allow NaN no more than they can allow NULL). I seem to remember an argument made by one of my professors in college that "Zero to the Zeroth power" could be shown to be nonasymptotically discontinuous in an uncountably infinite number of ways and in fact in a transfiniteclass number of ways that was at least one transfiniteclass higher than that for "0/0". But that's probably too many exceptions to handle in most programs. Point being (I guess), that even plain old numerical mathematics theory can never be perfectly mapped/applied to real programming, there's always something that has to be swept under the carpet and just smoothed over for practical purposes. It's the main reason that I get so prickly when Relational Purists start moaning about NULLs in SQL. Heck if even basic arithmetic has to swallow DivideByZero in application, then I think that the Relational Model should be able to survive NULLs. But that's jsut me ... You raise another quite interesting topic for me. Is 0/0 = 1 or is 0/0 an error? In arithmetic, it's just "undefined", technically a domain (input) exception, I think. In theoretical mathematics study of such things (Real/Complex Calculus, Real/Complex Analysis, etc.) it's a discontinuity, and a very interesting one at that (and Power(0,0) even more so), because unlike 1/0 you can usually manipulate whatever equation it is embedded in to determine what value it "should" have in that particular case.
(Note: and now I realize/remember that my previous reply to Tom only makes sense when the expression (0 to the 0th power) is a result returned by an equation...)
 RBarryYoung, (302)3750451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, June 12, 2015 8:42 AM
Points: 9,294,
Visits: 9,496


RBarryYoung (4/8/2010)
Tom.Thomson (4/8/2010)
RBarryYoung (4/7/2010)
Paul White NZ (4/7/2010)
RBarryYoung (4/7/2010) Yup, AFAIK SQRT, LOG and maybe POWER are the only things that return this error.Ooo  I had never tried POWER. Have now, and you're right  though POWER (0, 1) gives me a dividebyzero, rather than a domain error or invalid floating point operation. Yeah, there's just so many reasons that ones wrong, it's hard to pick just one. Almost as bad as POWER(0, 0). What do you want it to do? Do you want it to (a) return 0 so that f(x) = POWER (x,0) has a discontinuity at x = 0 while g(x) = POWER(0,x) is continuous everywhere (ie invert the current situation) or (b) return NaN so that both functions are "undefined" at x=0 (but that requires support for the IEEE standard in SQLS, which hasn't yet happened: see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=239674) or (c) return a domain error (so that both functions are "undefined" in such a way that no programmer can deal with it and must always test for the special case) or (d) return two different catchable errors from POWER(0,X) (one for x < 0, another for x=0) ?? For me, (b) is the only useful approach, but note that this requires in addition a catchable error to handle attempts to insert NaNs into an index column (since such columns can allow NaN no more than they can allow NULL). I seem to remember an argument made by one of my professors in college that "Zero to the Zeroth power" could be shown to be nonasymptotically discontinuous in an uncountably infinite number of ways and in fact in a transfiniteclass number of ways that was at least one transfiniteclass higher than that for "0/0". But that's probably too many exceptions to handle in most programs. Point being (I guess), that even plain old numerical mathematics theory can never be perfectly mapped/applied to real programming, there's always something that has to be swept under the carpet and just smoothed over for practical purposes. It's the main reason that I get so prickly when Relational Purists start moaning about NULLs in SQL. Heck if even basic arithmetic has to swallow DivideByZero in application, then I think that the Relational Model should be able to survive NULLs. But that's jsut me ... Now I am remembering, that it only makes sense to talk about (0**0) when it's a result returned from an equation, and only in the context of that equation's form. IIRC, my professor was talking about the function:
Z = X^Y When X=0, and Y=0
It's really nasty...
 RBarryYoung, (302)3750451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."




SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 7,621,
Visits: 10,485


CirquedeSQLeil (4/8/2010)Is 0/0 = 1 or is 0/0 an error? To that question, my answer is a clear and resounding "yes"!
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis




SSCoach
Group: General Forum Members
Last Login: Monday, May 23, 2016 11:31 AM
Points: 19,799,
Visits: 18,071


Hugo Kornelis (4/8/2010)
CirquedeSQLeil (4/8/2010)Is 0/0 = 1 or is 0/0 an error? To that question, my answer is a clear and resounding "yes"!
Glad you liked it.
Jason AKA CirqueDeSQLeil I have given a name to my pain... MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions  Gail Shaw




SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 1:16 PM
Points: 9,407,
Visits: 11,376





SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 1:16 PM
Points: 9,407,
Visits: 11,376


Hugo Kornelis (4/8/2010)
CirquedeSQLeil (4/8/2010)Is 0/0 = 1 or is 0/0 an error? To that question, my answer is a clear and resounding "yes"! And mine is a resounding "sometimes"! Unless it's a resounding "perhaps"?
Of course to see why you have to understand what Barry was getting at with his talk of equations (I tend to talk about functions in that context, but it's the same thing). (If you're not interested in the mathematics of functions over the real plain, read no further).
If you look at the function f(X,Y) = (2X/Y) and ask what its limit is as X and Y approach 0 you can see straight away that what the limit is depends on what path in the plane you follow to get to 0,0 (that's why in arithmetic 0/0 is undefined). If you start from the point X=1 Y=2 and follow a straight line to X=0 Y=0 the value everywhere along the line until you get to X=0 Y=0 the value is 1 everywhere along that line except at 0,0 so the limit at that point when you approach on that line is 1. So we can say that sometimes (for example when you walk down that line) 0/0 should probably be treated as 1. On the other hand, if you start from X=1 Y=0 and follow thre straight line to 0,0 every point produces an error; so if your on that line you had better treat 0/0 as an error. And if you start from X=1, Y=1 and follow a straight line to 0,0 the only value you see is 2. Of course it gets interesting if you don't follow a straight line: for example if you follow a spiral from somewhere to 0,0 you will see the value changing all the time, including very large negative values, very large positive values, everything in between, and errors  and although it changes in a nice regular pattern, there won't be (well, actually, this depends on the sort of spiral it is) a tendency towards some fixed value as you get closer to 0,0 so we can't say there's a limit value there on that path.
We [ex]mathematicians are very lazy, so if the limit was the same on every path we would just say the value is defined and is that limit; but as it's not we say the value is undefined except in the context of a particular function and a particular path.
Tom




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, June 12, 2015 8:42 AM
Points: 9,294,
Visits: 9,496


Tom.Thomson (4/8/2010)
Hugo Kornelis (4/8/2010)
CirquedeSQLeil (4/8/2010)Is 0/0 = 1 or is 0/0 an error? To that question, my answer is a clear and resounding "yes"! And mine is a resounding "sometimes"! Unless it's a resounding "perhaps"? Of course to see why you have to understand what Barry was getting at with his talk of equations (I tend to talk about functions in that context, but it's the same thing). (If you're not interested in the mathematics of functions over the real plain, read no further). If you look at the function f(X,Y) = (2X/Y) and ask what its limit is as X and Y approach 0 you can see straight away that what the limit is depends on what path in the plane you follow to get to 0,0 (that's why in arithmetic 0/0 is undefined). If you start from the point X=1 Y=2 and follow a straight line to X=0 Y=0 the value everywhere along the line until you get to X=0 Y=0 the value is 1 everywhere along that line except at 0,0 so the limit at that point when you approach on that line is 1. So we can say that sometimes (for example when you walk down that line) 0/0 should probably be treated as 1. On the other hand, if you start from X=1 Y=0 and follow thre straight line to 0,0 every point produces an error; so if your on that line you had better treat 0/0 as an error. And if you start from X=1, Y=1 and follow a straight line to 0,0 the only value you see is 2. Of course it gets interesting if you don't follow a straight line: for example if you follow a spiral from somewhere to 0,0 you will see the value changing all the time, including very large negative values, very large positive values, everything in between, and errors  and although it changes in a nice regular pattern, there won't be (well, actually, this depends on the sort of spiral it is) a tendency towards some fixed value as you get closer to 0,0 so we can't say there's a limit value there on that path. We [ex]mathematicians are very lazy, so if the limit was the same on every path we would just say the value is defined and is that limit; but as it's not we say the value is undefined except in the context of a particular function and a particular path. Ouch! You nailed me there, I was actually too lazy to explain all of this ...
 RBarryYoung, (302)3750451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."



