September 5, 2016 at 2:17 pm
I guess that maybe I'm missing the point for the definition of NULL. I would conclude that division by zero is in fact the VERY definition of NULL, and such should resolve to that returned value. I most DEFINITELY would not suspect that division by zero would raise an error, thus requiring the programmer to create a specific function to handle such error. So, ... again, I reiterate, ... am I missing out on an understanding of the term "NULL"?
September 5, 2016 at 2:36 pm
You're missing the point, completely.
When referring to NULL within our data sets, we often use phrases such as “the NULL value” or “a value of NULL.” I do it all the time. I see it done all the time. Such phrases are so common that we think little of their use. But inherent in the phrase is the notion of NULL as an actual value. It is not. NULL is a non-value, a nonexistent value. It is not zero. It is not an empty string. A value cannot equal NULL. No two NULL values are equal...
- https://www.simple-talk.com/sql/t-sql-programming/how-to-get-nulls-horribly-wrong-in-sql-server/
Divide by zero error encountered, is just an error message. Mathematically, you cannot do a division by zero.
Igor Micev,My blog: www.igormicev.com
September 5, 2016 at 8:50 pm
Anyway, you at least know now that you're going to get an error when you divide by zero. To get the behavior you want, you can use the NULLIF expression to change the zero to NULL.
declare @X INT = 4
,@Y INT = 0
select @X / NULLIF(@Y,0)
But you really need to improve your understanding of NULL. Search NULL in the box at the upper left and filter it down to just Articles.
A little reading will save you a lot of frustration in the future.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2016 at 12:12 am
To both replies thus far:
I beg to differ. The definition of "NULL" is a lack of a value. The definition of division by zero is a lack of ability to return a value. Hence, division by zero is the very definition of a useful purpose for a NULL. Oracle doesn't even acknowledge proper NULL values. What kind of back-assward logic would force developers to write an exception to something as basic and common as mathematical division to handle what is overtly clear and obvious.
Again, I dissent to anyone that interprets division by zero should not yeild a NULL.
September 6, 2016 at 1:59 am
Gregory Hart (9/6/2016)
I beg to differ. The definition of "NULL" is a lack of a value.
NULL is an unknown value. How many apples are there in the kitchen? NULL (I don't know). It can also be used when there is no value. "What encoding does that word document have?" NULL, audio and video files have encodings, not Word documents.
The result of a divide by zero is not unknown, it's not the lack of a value. It's a mathematically forbidden operation (if divide by zero is allowed, it's possible to prove that 1=2). If you want to play with mathematical limits, dividing by zero gives a result of infinity. It's not NULL, it's an invalid operation, same as taking the logarithm of a negative number
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2016 at 6:29 am
Gregory, you are arguing semantics based on *your* definition of null. We are telling you how SQL works. Further argument is pointless here because they are not going to rewrite it just to please you. You now have a solution to your problem. Please move on.
By the way, like everyone else, I learned that dividing by zero is a mathematical impossibility. It doesn't produce a result. It invalidates the calculation. A drunken math major once proved to me that 0 = 1 by cleverly concealing a divide by zero. Well, perhaps we had both been drinking.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2016 at 6:30 am
if divide by zero is allowed, it's possible to prove that 1=2
The above claim would only work in a logical proof if you identified that NULL= NULL . NULL != NULL, so the above claim is now debunked.
Proof that NULL != NULL
select case when NULL=NULL then 1 else 0 end
-- RETURNS: 0
select case when NULL!=NULL then 1 else 0 end
-- RETURNS: 0
NEXT !!!! Can anyone give me a valid reason why division by zero raises an error instead of returning NULL ?!?! I've been appalled by NULL values for over 22 years of database development. From Oracle's lack of handling them any different than an empty string, thus defeating the entire concept of them, to most every platform raising an error for division by zero. Logic and also user-friendliness would conclude the following 3 facts:
Division by zero should return NULL for every imaginable reason possible. There is most literally one benefit or value to not return anything but an error message, and thus require developers to write code for handling it.Maybe 25 years ago it might have been an issue, but we should have grown up since then?!?!
September 6, 2016 at 6:32 am
Goodbye, Gregory. I'm done feeding trolls for a lifetime.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2016 at 6:40 am
Gregory Hart (9/6/2016)
if divide by zero is allowed, it's possible to prove that 1=2
The above claim would only work in a logical proof if you identified that NULL= NULL . NULL != NULL, so the above claim is now debunked.
So you take a side comment of mine and treat it as if it's the entire post.
Can anyone give me a valid reason why division by zero raises an error instead of returning NULL ?!?!
I did. You just ignored it to focus on an aside in it.
The result of a divide by zero is not unknown, it's not the lack of a value. It's a mathematically forbidden operation. If you want to play with mathematical limits, dividing by zero gives a result of infinity. It's not NULL, it's an invalid operation, same as taking the logarithm of a negative number.
And having spent a fair portion of the last two years working on some heavy number crunching in SQL, divide by zero or other illegal operations (like logarithm of a negative) returning NULL instead of throwing an error would have made the job orders of magnitude harder and added many, many lines of complex NULL checks.
And I'm done too. You don't want an explanation, you want to prove that you're right.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2016 at 7:01 am
Gregory Hart (9/6/2016)
if divide by zero is allowed, it's possible to prove that 1=2
The above claim would only work in a logical proof if you identified that NULL= NULL . NULL != NULL, so the above claim is now debunked.
Proof that NULL != NULL
select case when NULL=NULL then 1 else 0 end
-- RETURNS: 0
select case when NULL!=NULL then 1 else 0 end
-- RETURNS: 0
NEXT !!!! Can anyone give me a valid reason why division by zero raises an error instead of returning NULL ?!?! I've been appalled by NULL values for over 22 years of database development. From Oracle's lack of handling them any different than an empty string, thus defeating the entire concept of them, to most every platform raising an error for division by zero. Logic and also user-friendliness would conclude the following 3 facts:
- If you have a circumstance where there will be some combinations of division by zero, you will have to return SOMETHING, so NULL is seemingly the much more logic option than zero.
- NULL can be seen as infinity of either the the smallest or the larges, but mostly it is a non-number. It's not like is's a number that has a known value and time will eventually fill it, it a non-existent.
- Division by Zero produces an impossible result, ... a non-number.
Division by zero should return NULL for every imaginable reason possible. There is most literally one benefit or value to not return anything but an error message, and thus require developers to write code for handling it.Maybe 25 years ago it might have been an issue, but we should have grown up since then?!?!
Division by Zero produces infinity. Infinity can't be stored in any data type available in SQL Server or any other RDBMS. NULL and infinity are different concepts.
Saying that NULL is a non-number and infinity is a non-number as well to prove that they're the same thing, is like saying that I'm the same as a bug because we're both living things.
September 6, 2016 at 7:01 am
The Dixie Flatline (9/6/2016)
Gregory, you are arguing semantics based on *your* definition of null. We are telling you how SQL works. Further argument is pointless here because they are not going to rewrite it just to please you. You now have a solution to your problem. Please move on.By the way, like everyone else, I learned that dividing by zero is a mathematical impossibility. It doesn't produce a result. It invalidates the calculation. A drunken math major once proved to me that 0 = 1 by cleverly concealing a divide by zero. Well, perhaps we had both been drinking.
Igor Micev,My blog: www.igormicev.com
September 6, 2016 at 7:03 am
Gregory Hart (9/6/2016)
if divide by zero is allowed, it's possible to prove that 1=2
The above claim would only work in a logical proof if you identified that NULL= NULL . NULL != NULL, so the above claim is now debunked.
Proof that NULL != NULL
select case when NULL=NULL then 1 else 0 end
-- RETURNS: 0
select case when NULL!=NULL then 1 else 0 end
-- RETURNS: 0
NEXT !!!! Can anyone give me a valid reason why division by zero raises an error instead of returning NULL ?!?! I've been appalled by NULL values for over 22 years of database development. From Oracle's lack of handling them any different than an empty string, thus defeating the entire concept of them, to most every platform raising an error for division by zero. Logic and also user-friendliness would conclude the following 3 facts:
- If you have a circumstance where there will be some combinations of division by zero, you will have to return SOMETHING, so NULL is seemingly the much more logic option than zero.
- NULL can be seen as infinity of either the the smallest or the larges, but mostly it is a non-number. It's not like is's a number that has a known value and time will eventually fill it, it a non-existent.
- Division by Zero produces an impossible result, ... a non-number.
Division by zero should return NULL for every imaginable reason possible. There is most literally one benefit or value to not return anything but an error message, and thus require developers to write code for handling it.Maybe 25 years ago it might have been an issue, but we should have grown up since then?!?!
READ THIS!
When referring to NULL within our data sets, we often use phrases such as “the NULL value” or “a value of NULL.” I do it all the time. I see it done all the time. Such phrases are so common that we think little of their use. But inherent in the phrase is the notion of NULL as an actual value. It is not. NULL is a non-value, a nonexistent value. It is not zero. It is not an empty string. A value cannot equal NULL. No two NULL values are equal...
- https://www.simple-talk.com/sql/t-sql-programming/how-to-get-nulls-horribly-wrong-in-sql-server/
Igor Micev,My blog: www.igormicev.com
September 6, 2016 at 8:18 am
So that I can best understand the value you have described for the error message instead of returning NULL, please share with me a small block of code where such benefit is appreciated.
Thank you.
September 6, 2016 at 8:28 am
As with many things in mathematics, this isn't necessarily a question of wrong/right. It's more a question of what is useful,elegant, parsimonious, etc. (sort of like treating the equals sign differently in the infamous 1+2+3+4...=-1/12 bit; it's a useful association, even if not what we usually mean by "sum of these numbers equals...").
I do prefer the simplicity of simply disallowing divide by zero, instead of treating it as NULL. The usual argument from looking at division as the inverse of multiplication is enough consideration for me, i.e., if you say x/0=y, then whatever you put in for y, then you would need either for y*0=x, or to define another exception that says that in this one case division is not treated as the inverse of multiplication.
For me it makes more sense to just disallow x/0, so that you keep the "division is the inverse of multiplication" definition, than to say that x/0 is NULL, and that in that case you don't treat division as the inverse of multiplication.
Of course, going back to the usefulness bit, if there were some great practical utility to defining the result of divide by zero as NULL, then by all means use that (and that can be straightforwardly implemented in SQL Server with a NULLIF, as pointed out by previous posters).
While I like the traditional handling of divide by zero, it's perfectly possible to handle it consistently in a number of different ways, again usually for practical reasons (the IEEE floating point standard's method of handling it comes to mind; there it is defined, and exactly in order to solve some practical problems. See https://people.eecs.berkeley.edu/~wkahan/ieee754status/IEEE754.PDF starting at page 10 for a discussion of that).
In the general case, though, I just don't see a compelling reason to use the NULL definition instead of disallowing it.
Cheers!
September 7, 2016 at 7:48 am
I agree with most of the points, even those that seem to contradict. NULL is not a value, it is the absence of information. With regards to the encoding for a Word document, it isn't NULL. It is a known value, which is "None" or "N/A" or even "". Someone once told tell me anyone who knows SQL Server internals would never use an empty string instead of a NULL. But in my view that person didn't understand that there's a significant operational difference.
The division of 0 returns an error because it's not possible to represent infinity. That doesn't mean that in some cases a NULL result would be desirable. It's not uncommon in MDX language to test a possible divisor for a zero value and if so use NULL, eg IIF(Measures.Tickets = 0, Null, Measures.NegativeTickets/Measures.Tickets). This test is done to avoid an error, and in this context the NULL result is appropriate for a multi-dimensional environment. But this case doesn't mean the Null should be returned by default.
//Edited to remove an I and clarify the last sentence in the first paragraph.
Viewing 15 posts - 1 through 15 (of 64 total)
You must be logged in to reply to this topic. Login to reply