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 divide-by-zero, 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). 😀
:-DWhat 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 non-asymptotically discontinuous in an uncountably infinite number of ways and in fact in a transfinite-class number of ways that was at least one transfinite-class 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...:w00t:)
[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]