Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

ANSI_NULLS Expand / Collapse
Author
Message
Posted Thursday, April 8, 2010 11:13 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
Hugo Kornelis (4/8/2010)
RBarryYoung (4/7/2010)
Paul:

Now I'm curious to know how you did on mine...? (here: http://www.sqlservercentral.com/questions/T-SQL/63632/) Its still got one of the lowest correct percentages that I know of.

That was a great question indeed, as is this one from Paul. And though I got Paul's question right (bit of luck!), I'll admit that I was in the majority that replied wrong to RBarry's question.

But one of the lowest correct percentages that you know of? I don't think so!
http://www.sqlservercentral.com/questions/Design+and+Theory/64698/ - 37%
http://www.sqlservercentral.com/questions/SQL+Server+2008/63588/ - 35%
http://www.sqlservercentral.com/questions/Design+and+Theory/64304/ - 20%
http://www.sqlservercentral.com/questions/Administration/67278/ - 16%
http://www.sqlservercentral.com/questions/Administration/67277/ - 15%

And I've got two new questions lined up; they are scheduled for April 20th and May 6th. I don't think they're quite as hard as the last three from this list, though.

Ouch! Me too, except I only got the 35% one right. And I'm concerned that the main thing that I may have learned from these was to seriously avoid IGNORE_DUP_KEY.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #899816
Posted Thursday, April 8, 2010 11:30 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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).


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 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 ...


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #899830
Posted Thursday, April 8, 2010 11:33 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:17 PM
Points: 17,815, Visits: 15,744
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).


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 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?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #899835
Posted Thursday, April 8, 2010 12:35 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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).


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 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...)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #899887
Posted Thursday, April 8, 2010 12:39 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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).


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 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 ...

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)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #899894
Posted Thursday, April 8, 2010 1:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 6,043, Visits: 8,324
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
Post #899953
Posted Thursday, April 8, 2010 1:31 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:17 PM
Points: 17,815, Visits: 15,744
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
Post #899958
Posted Thursday, April 8, 2010 5:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:12 AM
Points: 7,791, Visits: 9,545
RBarryYoung (4/8/2010)

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 ...

Not just you, me too. I can understand (and do share) that prickliness. When some holier-than-thou purist starts ranting against NULLs (often at the same time as claiming to be a follower of Codd, which is just bloody ridiculous) and telling people that they shouldn't need a name for bridge tables (or MM tables or whatever you want to call them) because all tables are equal I begin to see red, so I thought your comment let David Portas off far too lightly the other day.

I also find it a bit irritating that MS's SQL team don't bite the bullet and provide full support for the IEEE floating point standard; I know it isn't trivial (probably needs a NOT NaN constraint on a column which is to be used in an index, with errors caused for trying to put a NaN there just as there are for trying to put NULL in a column with a NOT NULL constraint) but it really would make that part of arithmetic a bit cleaner. Another good thing about it is that it would probably annoy the relational purists - effectively we'd be adding a domain-specific NULL, which might to the final end to any hope of getting a "pure" NULL-free relational model accepted. But MS's failure to provide that is a small thing compared to what the discontinuation of NULL would do.


Tom
Post #900074
Posted Thursday, April 8, 2010 5:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:12 AM
Points: 7,791, Visits: 9,545
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
Post #900079
Posted Thursday, April 8, 2010 10:05 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
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)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #900156
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse