SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Divide by zero


Divide by zero

Author
Message
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27260 Visits: 12735
Comments posted to this topic are about the item Divide by zero


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39509 Visits: 12889
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

Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27260 Visits: 12735
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27260 Visits: 12735
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:
CASE
WHEN 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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39509 Visits: 12889
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:
CASE
WHEN 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[s] (since until it is evaluated its result[s] is[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

Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27260 Visits: 12735
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
DugyC
DugyC
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2034 Visits: 779
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

_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Toreador
Toreador
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4998 Visits: 8187
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!
Carlo Romagnano
Carlo Romagnano
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10008 Visits: 3465
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!
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111276 Visits: 18624
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search