September 7, 2016 at 11:37 am
I guess my point with this matter is that in 22+ years of database development, I personally have never had a need for returning an error message instead of a NULL value when calculating a return for division. Therefore, I have a UDF for division which I require all my ream to use for any division that is performed. It merely returns NULL if the Divisor is 0, otherwise it returns the division. I realize that maybe, in some far and distant universe, some person might actually have a need to return an error instead of performing the calculation for a report or for an INSERT/UPDATE to a table or variable, and appreciates the 2:00 am wake-up call to inform him that his process failed because of division by zero. I would like to think that, for such unusual circumstances, that the developer could merely run a SELECT statement against the value to confirm if it is zero, and if so,then raise a controlled error with intent.
That's my 2 cents!
September 7, 2016 at 4:05 pm
Gregory Hart (9/7/2016)
I guess my point with this matter is that in 22+ years of database development, I personally have never had a need for returning an error message instead of a NULL value when calculating a return for division.
If after 22+ years you have this problem then for 22+ years you allow division by zero in your code.
It only means that for 22+ years you keep doing arithmetics wrong.
And after 22+ years the best you can come is is to blame the tools for doing things right.
What a sad story!
_____________
Code for TallyGenerator
September 7, 2016 at 5:10 pm
OK, ... Pray tell, ... What possible use have you had for a raised error when division by zero occurs?
September 7, 2016 at 6:21 pm
I would agree that in a lot of my t-sql code, I return a null for division by zero.
This is not, however, because division by zero equals NULL.
It doesn't.
I return NULL because I cannot divide by zero, so I cannot return a known value - NULL is "unknown".
It would make my code quicker if division by zero automatically returned a null, but I don't like to mess with ANSI_WARNINGS because of the loss of truncation errors.
If I need to handle things differently when the value is zero (I don't want to return NULL), I end up with a CASE statement such as
CASE cost WHEN 0.00 THEN 100.00 ELSE 100 * ( gross - cost ) / cost END AS MARGIN
It would be nice to have a trace flag or an OPTION to trigger "return null on division by zero" for individual queries - without losing the string truncation error, but it's no biggee.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
September 7, 2016 at 9:53 pm
Gregory Hart (9/7/2016)
OK, ... Pray tell, ... What possible use have you had for a raised error when division by zero occurs?
Same use as for any other type of errors (e.g. Arithmetic overflow, or LOG10 from a negative number, or violation of a unique key, etc.) - pointing out on an error in the code or incorrect data design, which needs to be fixed.
_____________
Code for TallyGenerator
September 7, 2016 at 10:17 pm
mister.magoo (9/7/2016)
If I need to handle things differently when the value is zero (I don't want to return NULL), I end up with a CASE statement such as
CASE cost WHEN 0.00 THEN 100.00 ELSE 100 * ( gross - cost ) / cost END AS MARGIN
You do understand that the result of that case statement is incorrect - right?
_____________
Code for TallyGenerator
September 8, 2016 at 2:22 am
I had a report about this thread. I think it's a valid discussion, and as long as everyone remains respectful, that's fine.
There have been a few people saying NULL is the absence of a value, which isn't what I've learned in database theory. This is a common misconception, partially because of the use of NULL, partially a result of the semantics around how we speak of NULL values.
However, read Gail's initial description. NULL is unknown. x/0 is not unknown. This is a known quantity, and happens to be something not allowed in mathematics.
Whether you want x/0 to return null or an error, you have to handle the value either way. Any calculation looking for a division result that gets a NULL is likely to break as easily as if an error were thrown.
Personally I prefer an error, but perhaps things are handled easier in some cases if a NULL is returned. I wouldn't be upset over such a setting as an option, and you should feel free to post this on Connect and lobby for votes. I suspect you will get few, but I could be wrong.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 8, 2016 at 2:26 am
Sergiy (9/7/2016)
mister.magoo (9/7/2016)
If I need to handle things differently when the value is zero (I don't want to return NULL), I end up with a CASE statement such as
CASE cost WHEN 0.00 THEN 100.00 ELSE 100 * ( gross - cost ) / cost END AS MARGINYou do understand that the result of that case statement is incorrect - right?
Whether it was a "good calculation" wasn't important - the structure was. It's not actual code, thanks 😛
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
September 8, 2016 at 3:48 am
Steve Jones - SSC Editor (9/8/2016)
I had a report about this thread. I think it's a valid discussion, and as long as everyone remains respectful, that's fine.There have been a few people saying NULL is the absence of a value, which isn't what I've learned in database theory. This is a common misconception, partially because of the use of NULL, partially a result of the semantics around how we speak of NULL values.
However, read Gail's initial description. NULL is unknown. x/0 is not unknown. This is a known quantity, and happens to be something not allowed in mathematics.
Whether you want x/0 to return null or an error, you have to handle the value either way. Any calculation looking for a division result that gets a NULL is likely to break as easily as if an error were thrown.
Personally I prefer an error, but perhaps things are handled easier in some cases if a NULL is returned. I wouldn't be upset over such a setting as an option, and you should feel free to post this on Connect and lobby for votes. I suspect you will get few, but I could be wrong.
I think a better option would be to provide support for the IEEE arithmetic standard which would give the values +/- infinity, NaN and also +/-0 the latter would be applicable where otherwise you would get an underflow error.
None of these are NULL although NaN (not a number) is similar.
Where those standards are not applied division by zero is an error in all (AFAIK) programming languages so I see no good reason for SQL behaving differently.
September 8, 2016 at 4:00 am
crmitchell (9/8/2016)
Where those standards are not applied division by zero is an error in all (AFAIK) programming languages so I see no good reason for SQL behaving differently.
Well, if you are processing a big set of data and one row has a division by zero, I certainly would want the option of it not throwing an error (which I have now by circumventing the operation) and being able to complete the task.
As long as your query has division by zero mitigation handling in it, there won't be an error.
But, if you have to add division by zero handling to overcome this problem, why not have a standardised, optional, internal way of returning some kind of value from this disallowed operation, rather than have each programmer write their own version?
For me, the option for x / y to return a null (as with ANSI_WARNINGS off) is better than having everyone use CASE statements or NULLIF() isn't it?
Again, to be sure, I do not agree that x / 0 equals NULL, but pragmatically, I'm fairly sure most people put some form of protection in their divisions and most of those would return a NULL - or am I wrong?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
September 8, 2016 at 6:20 am
mister.magoo (9/8/2016)
Sergiy (9/7/2016)
mister.magoo (9/7/2016)
If I need to handle things differently when the value is zero (I don't want to return NULL), I end up with a CASE statement such as
CASE cost WHEN 0.00 THEN 100.00 ELSE 100 * ( gross - cost ) / cost END AS MARGINYou do understand that the result of that case statement is incorrect - right?
Whether it was a "good calculation" wasn't important - the structure was. It's not actual code, thanks 😛
It is important.
Because, as I said before, every occasion of division by zero indicates an error in code or/and in data design.
Use of zeros instead of nulls - one of typical such errors.
_____________
Code for TallyGenerator
September 8, 2016 at 6:26 am
Personally I prefer an error, but perhaps things are handled easier in some cases if a NULL is returned.
Which NULLIF([somecolumn],0) handles perfectly, if that is the desired behavior.
__________________________________________________
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 8, 2016 at 6:34 am
mister.magoo (9/8/2016)
But, if you have to add division by zero handling to overcome this problem, why not have a standardised, optional, internal way of returning some kind of value from this disallowed operation, rather than have each programmer write their own version?
Because there is and cannot be no standardised interpretation of zero divider.
Those who would writing such a function would have no idea what kind of error was made in each particular case, what kind of design flaw, shortcut in code, whatever else put a zero behind a slash.
In each particular case the output value would be different, depending on the actual meaning of that zero in each particular business case.
Therefore, "each programmer write their own version", according to the business rules in each particular case.
_____________
Code for TallyGenerator
September 8, 2016 at 6:53 am
from: Steve Jones - SSC Editor
There have been a few people saying NULL is the absence of a value, which isn't what I've learned in database theory. This is a common misconception, partially because of the use of NULL, partially a result of the semantics around how we speak of NULL values.
My understanding of databases and mathematics for NULL and infinity is as follows:
Infinity represents a known value, but for which is infinite in size. Size can be either macro or micro. Examples would include the size of the universe or the size of a point. With these references to infinity, there is no number which could be substituted (i.e. ... If you wanted to talk about the size of the universe multiplied by 2, there is no number which could be used for the calculation).
Infinity can also represent a known value that is seemingly impossible to identify the exact value. An example of this would be Pie. It's known to be greater than 3 and less than 4. The difference here is that a number can be used to substitute for it and the accuracy of your results depends upon how many decimal places to carry it to when applying it in a formula.
A NULL value represents the lack of a number. There is no knowledge if it is bigger or smaller than anything. When a row is created and one of the columns is a numeric but the data to populate that column hasn't been input yet, then NULL identifies that the value could be anything or nothing. The only thing that can be said about the value is that it is unknown.
There seem to be 2 question at hand with this forum thread:
1) Is division by zero considered NULL by proper definition
2) Even if #1 is false, wouldn't almost every usage of division by zero be more appropriate to return NULL instead of raising an error.
My opinion on #1 is that division by zero is properly defined by NULL, because the result is not infinitely large nor is it infinitely small nor is it a number where the exact precision can't be determined (like Pie). It is truly an "unknown" value because the operation cannot be performed.
My opinion on #2 is "Heck Yeah!" In databases and in Excel sheets and programming languages and everything else that handles division.
September 8, 2016 at 7:22 am
Gregory Hart (9/8/2016)
from: Steve Jones - SSC Editor
There have been a few people saying NULL is the absence of a value, which isn't what I've learned in database theory. This is a common misconception, partially because of the use of NULL, partially a result of the semantics around how we speak of NULL values.
My understanding of databases and mathematics for NULL and infinity is as follows:
Infinity represents a known value, but for which is infinite in size. Size can be either macro or micro. Examples would include the size of the universe or the size of a point. With these references to infinity, there is no number which could be substituted (i.e. ... If you wanted to talk about the size of the universe multiplied by 2, there is no number which could be used for the calculation).
Infinity can also represent a known value that is seemingly impossible to identify the exact value. An example of this would be Pie. It's known to be greater than 3 and less than 4. The difference here is that a number can be used to substitute for it and the accuracy of your results depends upon how many decimal places to carry it to when applying it in a formula.
A NULL value represents the lack of a number. There is no knowledge if it is bigger or smaller than anything. When a row is created and one of the columns is a numeric but the data to populate that column hasn't been input yet, then NULL identifies that the value could be anything or nothing. The only thing that can be said about the value is that it is unknown.
There seem to be 2 question at hand with this forum thread:
1) Is division by zero considered NULL by proper definition
2) Even if #1 is false, wouldn't almost every usage of division by zero be more appropriate to return NULL instead of raising an error.
My opinion on #1 is that division by zero is properly defined by NULL, because the result is not infinitely large nor is it infinitely small nor is it a number where the exact precision can't be determined (like Pie). It is truly an "unknown" value because the operation cannot be performed.
My opinion on #2 is "Heck Yeah!" In databases and in Excel sheets and programming languages and everything else that handles division.
The bottom line here is that neither side is going to be correct. Discussions about NULL often go south because people have differing views. They frequently end up with people throwing insults around, like on this thread.
I would argue strongly that it should NOT return NULL because it is an invalid mathematical calculation. In many cases the business rule to have it return NULL is perfectly valid. That does not mean that the sql engine should return NULL because it is the easiest for the developer in most situations. Consider this, a NULL value for the denominator or numerator will result in NULL. If dividing by 0 also results in NULL you would have now way of knowing why you got NULL. It could be because either the denominator or numerator is NULL OR it could be because denominator is 0. This leads to ambiguous results which is a criminal wrongdoing for the sql engine to make. Dividing by 0 is not allowed in math and anything other than an error from the sql engine would be a decision by the sql engine that it knows best how to handle an invalid operation. This would be a horribly poor decision. I know Steve suggested creating a connect item for this but I would be shocked if it gained any momentum. That being said I can conceive of times I would want to know that the denominator is 0 and not get NULL although I can't think of a time I have needed this. But knowing the option is there allows me the freedom to decide how I want to deal with dividing by 0 instead of being forced to treat it the same as a NULL in the equation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 64 total)
You must be logged in to reply to this topic. Login to reply