ANSI_NULLS

  • 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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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[/url]
    Learn Extended Events

  • 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:-D. 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 :crazy: 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

  • 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

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

    [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]

  • Thank you Paul... Good Question..Learn lot from this..

  • Great question and explanation.

    Thanks,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 7 posts - 76 through 82 (of 82 total)

You must be logged in to reply to this topic. Login to reply