Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Divide by zero Expand / Collapse
Author
Message
Posted Saturday, December 22, 2012 8:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 6,130, Visits: 8,394
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
Post #1399722
Posted Saturday, December 22, 2012 8:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:50 PM
Points: 7,924, Visits: 9,649
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
Post #1399725
Posted Sunday, December 23, 2012 3:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 6,130, Visits: 8,394
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
Post #1399746
Posted Sunday, December 23, 2012 3:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 6,130, Visits: 8,394
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
Post #1399747
Posted Sunday, December 23, 2012 6:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:50 PM
Points: 7,924, Visits: 9,649
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
Post #1399750
Posted Sunday, December 23, 2012 3:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 6,130, Visits: 8,394
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
Post #1399770
Posted Monday, December 24, 2012 2:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, 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!


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1399847
Posted Monday, December 24, 2012 6:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:22 AM
Points: 1,830, Visits: 6,646
That required rather more brain cells than I was expecting to need on Christmas Eve

Glad to see that option 5 really should be correct, I'd started to think I might have completely misunderstood something.

Happy etc!
Post #1399873
Posted Monday, December 24, 2012 9:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:49 AM
Points: 2,620, Visits: 2,467
Toreador (12/24/2012)
That required rather more brain cells than I was expecting to need on Christmas Eve

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!
Post #1399923
Posted Monday, December 24, 2012 9:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1399924
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse