Divide by zero
-
Comments posted to this topic are about the item Divide by zero
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/ -
Good question.
But the explanation, as it refers to nullif and to view5, is a bit of a mess - it appears to say that view5 will always return null, instead of an error, when the divisor is zero, which is wrong (and conflicts with the answer given, which is right). The thing about nullif (as used in view5) is that it is equivalent to a two-branch case statement, and almost always both branches will be executed in currently supported systems, even if the divisor is zero.
Edit: the current implementation of CASE can (and does) use eager rather than lazy evaluation, which means that it can't be used to eliminate errors by having separate branches for error-producing and safe states; and since nullif is just a shorthand for a case statement, it has the same issue. I regard this as an example of an opportunity for optimisation being used as an excuse to destroy the apparent semantics of the language, which is of course inexcusable.
Tom
-
I have to offer my apologies for this question. I don't know what went wrong. I always check my question, answers and explanation several times before submitting, but I don't keep screenshots - so I cannot check if I really did mess this one up, or if something changed the data I submitted.
The question should of course have read "check THREE answers". And the correct answer options should have been 1, 4, and 5, as explained in the explanation. I'll contact Steve and ask him to correct this as soon as possible.
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/ -
L' Eomot Inversé (12/22/2012)
Good question.But the explanation, as it refers to nullif and to view5, is a bit of a mess - it appears to say that view5 will always return null, instead of an error, when the divisor is zero, which is wrong (and conflicts with the answer given, which is right). The thing about nullif (as used in view5) is that it is equivalent to a two-branch case statement, and almost always both branches will be executed in currently supported systems, even if the divisor is zero.
Edit: the current implementation of CASE can (and does) use eager rather than lazy evaluation, which means that it can't be used to eliminate errors by having separate branches for error-producing and safe states; and since nullif is just a shorthand for a case statement, it has the same issue. I regard this as an example of an opportunity for optimisation being used as an excuse to destroy the apparent semantics of the language, which is of course inexcusable.
As you can see from my previous reply to this topic, I disagree with your comment.
I think you are confusing two things about processing and evaluation order in a CASE expression. Let's look at an example:
CASEWHEN x <> 0 THEN y / x
WHEN (very complicated subquery expression) = 1 THEN (yet another very complicated subquery)
ELSE 42
END
SQL Server guarantees that "y/x" will only be actually computed when "x <> 0" evaluates to True. It also guarantees that "yet another very complicated subquery" will only be actually computed when "x <> 0" does not, and "(very complicated subquery expression) = 1" does evaluate to True.
Some people also expect that SQL Server will not even process (very complicated subquery expression) when "x <> 0" evaluates to true. And while that could have been the case (after all, the result of that subquery evaluation will not be used), this, in fact, is not guaranteed. My suspicion after reading your comment is that you either once read, or once were bitten by this latter behaviour, and then erroneoulsy expanded into no longer trusting SQL Server not to evaluate the THEN if the WHEN does not evaluate to True.
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 (12/23/2012)
I think you are confusing two things about processing and evaluation order in a CASE expression. Let's look at an example:CASEWHEN x <> 0 THEN y / x
WHEN (very complicated subquery expression) = 1 THEN (yet another very complicated subquery)
ELSE 42
END
SQL Server guarantees that "y/x" will only be actually computed when "x <> 0" evaluates to True. It also guarantees that "yet another very complicated subquery" will only be actually computed when "x <> 0" does not, and "(very complicated subquery expression) = 1" does evaluate to True.
Some people also expect that SQL Server will not even process (very complicated subquery expression) when "x <> 0" evaluates to true. And while that could have been the case (after all, the result of that subquery evaluation will not be used), this, in fact, is not guaranteed. My suspicion after reading your comment is that you either once read, or once were bitten by this latter behaviour, and then erroneoulsy expanded into no longer trusting SQL Server not to evaluate the THEN if the WHEN does not evaluate to True.
As to whether the possible results are evaluated, there appears to be no documentation. My memory tells me that I was bitten way back when by a zero divide error in a result branch that should not have been evaluated, but it was a long time ago. Perhaps my memory is playing me false, or perhaps it was conditioned by seeing that "2" in the question. Maybe it was in some other dbms than sql-server - I can't even remember whether my scratch pad variables were decorated with "@" or not; but of course in a dbms with case statements (in addition to case expressions) the execution of code in unwanted branches would be an pretty disastrous but, so that wouldn't have happened in, for example, sql-anywhere. But regardless of whether an error can occur through evaluation of an unneccessary result expression (and I believe it can't, because I know you are a reliable source of information on that sort of thing) my comment about the implementation being broken stands: if T-SQL is going to try some speculative execution for some reason, it must catch errors in that speculative execution internally and only throw them on to the user-visible level if the speculative execution turns out to have been needed, and it doesn't matter a bit whether what is being speculatively executed is a when-expression, and boolean when-expression, or a result branch.
It isn't particularly surprising that some people expect the evaluation of when clauses or boolean clauses to stop at first true, because the text of BoL explicitly states that for a searched case expression (which is what we have here, since the comparison is <> not =) the boolean expressions are evaluated in order. Before SQL 2008 R2 it didn't say that evaluation of these expressions stops when true is met, but that's what most people would expect when it's stated that evaluation is done in order. Up to and including SQL 2008 BoL made no reference to the possibility of a when-boolean-expression being evaluated after the first true. So either the documentation was misleading and should have been changed to say that they may all be evaluated even if a true is met before the last boolean, or the implementation should be changed to stop at the first true.
In 2008 R2 BoL a new remark was introduced into the case page. This begins "The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied." That is plainly false. It then goes on to say "In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input". This must have been intended to say that there are exceptions to the preceding sentence, but since it is essential for an expression to be evaluated before anything receives its result
(since until it is evaluated its resultis[are] unknown) it doesn't actually say that, it's just a pointless tautology. Of course it seems very unlikely that it was intended to mean what it does say (which can be paraphrased as "in some situations we have failed to to build a temporal paradox into the implementation of case statements") - whoever wrote that addition to BoL apparently wasn't very good at writing clear English, and also seems to have been unaware that T-SQL has case expressions but (unlike several other SQL implementations) no case statements. Anyway, the documentation is now (for 2008 R2 and 2012) clearly wrong, not just misleading (worse than before 2008 R2), and definitely needs fixing (although it would be better to fix the implementation - documenting that the code can throw unneeded errors becauses it indulges in unprotected speculative execution would be a step in the right direction but not a proper fix).When it comes to trusting SQL Server (and the things that I associate with it, like ADO) I don't - I've seen too many things broken by a new service pack or a new release, or even by a critical update (and sometimes the change in behaviour was documented, more often not). I've seen the argument that the optimizer, not the semantics, is supreme rather too often to trust it, particularly since in some cases where I felt it was unjustifiable. I do think SQL Server is better than its competition, though - it's what I recommend people to use. But I do tell them to test everything, and have a good try at breaking their code before they believe it will be anything like reliable enough for production.
Tom
-
Hi Tom,
You are right that there are indeed some issues with CASE. And I may not have remembered them all correctly. First, let's skip all older versions of the documentation and move to the SQL Server 2012 version of Books Online:
The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.
(... code fragment snipped ...)
You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.
Badly written, definitely. And not only because the use of the term "statement" instead of "expression". However, I read this as follows:
1. There is some exception to the "evaluation order" rule.
2. To be precise, that exception is that aggregates may be evaluated sooner.
3. Therefore, you can not depend on the order of evaluation when aggregates are involved.
4. (Concluded by my from point 3) Apparently, when aggregates are NOT involved, you CAN still rely on evaluation order.
While hunting for this information, I did find a few Connect items about errors with WHEN (something that's never true) THEN MIN(1/0) or WHEN (something that's always true) THEN 1 ELSE MIN(1/0) - they are the connect items that prompted this clarification to be added to Books Online.
I am very sure that I *also* recall a discussion about evaluation of a WHEN clause that logically was not needed, and Microsoft replying that they do guarantee not computing a THEN or ELSE that's not required, but not maing a similar guarantee about computing the WHEN conditions. But I was unable to find any reference for this. Maybe someone else can?
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/ -
Merde! If only I'd waited a bit longer... however Steve might be on hols with the family already.
Haven't read all posts, just Hugo's first repy to Tom, which confirms my findings.
Having determined 1,4 & 5 were correct in SQL2008, thought maybe this question was over all versions of SQL. So hooked up to a SQL2000 box and tried that, which only allowed 4 & 5. Hence my answers.
Ah well, no hard feelings, I've got my point from this post anyway LOL! 🙂
Wishing both Hugo and Tom, and all other SQL bods out there, a very Merry Christmas and a memorable New Year... hic! :w00t: :hehe:
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font] -
That required rather more brain cells than I was expecting to need on Christmas Eve :hehe:
Glad to see that option 5 really should be correct, I'd started to think I might have completely misunderstood something.
Happy etc!
-
Toreador (12/24/2012)
That required rather more brain cells than I was expecting to need on Christmas Eve :hehe:Glad to see that option 5 really should be correct, I'd started to think I might have completely misunderstood something.
Happy etc!
+1
I answered 1,4 and 5 despite qotd said choose 2!
Please, my point back!
For Steve: is it possible to have a preview of qotd for the author, to check the correctness of the question? Sometime, the final version of the question is different from what the author first published!
-
Thanks to Hugo and Tom for the discussion.
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 -
I was about to argue that 1,4 & 5 should be the correct answer, but read your comment and realized that you've already said I was right - even though I'm flagged as wrong!
Derek
-
SQLRNNR (12/24/2012)
Thanks to Hugo and Tom for the discussion.+1, really enjoyed it. And, couldn't resist posting this response to get my 1,000th point for Christmas! Just really excited about round numbers. :hehe:
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
Nice question and a good discussion indeed!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
I answered 1,4 &5. Please get my point back.:-)
--
Dineshbabu
Desire to learn new things.. -
Hugo Kornelis (12/23/2012)
I have to offer my apologies for this question. I don't know what went wrong. I always check my question, answers and explanation several times before submitting, but I don't keep screenshots - so I cannot check if I really did mess this one up, or if something changed the data I submitted.The question should of course have read "check THREE answers". And the correct answer options should have been 1, 4, and 5, as explained in the explanation. I'll contact Steve and ask him to correct this as soon as possible.
Wheeeww... I was getting worried.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply