none
Divide by zero error encountered.

    Question

  • My code is:

    SELECT * FROM 
    (
    	SELECT 20 AS Overdue_Amount, 100 AS Credit_Amount
    	UNION ALL
    	SELECT 0 AS Overdue_Amount, 0 AS Credit_Amount
    ) T
    WHERE Credit_Amount=100
    	OR (Credit_Amount>0 AND Overdue_Amount/Credit_Amount>0.1)
    

    And I got following error:

    Msg 8134, Level 16, State 1, Line 1
    Divide by zero error encountered.

    If I change it like this:

    SELECT * FROM 
    (
    	SELECT 20 AS Overdue_Amount, 100 AS Credit_Amount
    	UNION ALL
    	SELECT 0 AS Overdue_Amount, 0 AS Credit_Amount
    ) T
    WHERE Credit_Amount=100
    	OR (ISNULL(Overdue_Amount/NULLIF(Credit_Amount,0),0)>0.1)
    
    So my quesion is "Why I encountered error when exec first snippet? What happened?"
    Tuesday, September 14, 2010 8:59 AM

Answers

  • interestingly there was an (admittedly old) technet discussion (http://technet.microsoft.com/en-gb/cc678236.aspx) where an MS guy says that short-circuiting is present in SQL Server. 

    "Present" is not a very exact term.

    The SQL language "allow" for short cut. That is a good thing. Imagine:

    WHERE c1 = 23 and c2 = 45

    Also, imagine an index on c1. Now, if SQL were'nt allowed to do short cut (which would be ridiculous), then the index on c1 would be useful since c2 would have to be evaluated even for the rows where c1 is false. This is what I mean by shortcut is *allowed* (amd, again, anything else would be stupid).

    But, shortcut is not *required* since that would make SQL a less declarative language, considering the optimizer whold have few options to run the query. A slightly different example (read closely)

    WHERE c1 = 23 and c2 = 45

    Now we imagine an index on c2 but not on c1. If short cut were required, then SQL Server would have to evaluate c1 = 23 first, and since we have no index on c1 we would have a table scan (even with a perfect index on c2). These kind of things are at the root when it comes to short cut in SQL Server, and "predicate pushing" (like pushing a condition in an outer query to an inner query - which is exactly what you have in your example.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, September 14, 2010 12:16 PM
    Moderator

All replies

  • is this down to the query checking both boolean expressions after the OR? Seems odd as if you write:

     

    select 1 

    where 1=2 or (1=2 and 1/0 > 1)

     

    which is semantically similar to your query, you get no rows returned

    • Edited by bb12321 Tuesday, September 14, 2010 9:13 AM bad code
    Tuesday, September 14, 2010 9:04 AM
  • When you divide any number by 0, you will get divide by zero exception.  In your table T, CreditAmount is 0.

    u r doing, Overdue_Amount/Credit_Amount , so you are dividing with 0, so u r gettign that exception.

    and in the second query, u used nullif(Credit_Amount,0) 

    NULLIf will generate null, if second argument and first argument are equal. In this case, as they both are equal its returning null.

    Then its evaluating OverdueAmount/NULL, which returns null.  

    In otherwords evaluation is like below.

    ISNULL(Overdue_Amount/NULLIF(Credit_Amount,0),0)
    -- As credit_amount is 0, nullif() makes it null
    ISNULL(Overdue_Amount/NULL, 0)
    -- Anyvalue/null is null
    ISNULL(NULL,0)
    --In ISNULL() when the first argument is null, it places second argument
    -> which gives the result 0
    
    Tuesday, September 14, 2010 9:07 AM
  • what version of SQL Server are you running?
    Tuesday, September 14, 2010 9:13 AM
  • Try your query like this. It should work;

    SELECT * FROM 
    (
    	SELECT 20 AS Overdue_Amount, 100 AS Credit_Amount
    	UNION ALL
    	SELECT 0 AS Overdue_Amount, 0 AS Credit_Amount
    ) T
    WHERE Credit_Amount=100	OR (Credit_Amount > 0 AND Overdue_Amount/CASE WHEN ISNULL(Credit_Amount,0) = 0 THEN 1 ELSE Credit_Amount END >0.1)
    
    

    My Blog
    Tuesday, September 14, 2010 9:15 AM
  • in the query fragment:

     

    OR (Credit_Amount>0 AND Overdue_Amount/Credit_Amount>0.1)

    surely if the Credit_amount column isn't greater than 0, then the second part of the expression won't be evaluated. Short circuit evaluation is still in SQL Server as far as I remember.

    Tuesday, September 14, 2010 9:34 AM
  • In difference to other languages, SQL does not have short-cutting. That is, there is no guarantee in which order terms in expressions are evaluated. Thus, you must use other means to avoid division by zero. Your solution with nullif() is the correct one.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Tuesday, September 14, 2010 10:44 AM
  • Thanks for that Erland. I found a good article about this when looking for the information which shows examples of when short-circuiting doesn't occur: http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/

     

    interestingly there was an (admittedly old) technet discussion (http://technet.microsoft.com/en-gb/cc678236.aspx) where an MS guy says that short-circuiting is present in SQL Server. 

    Tuesday, September 14, 2010 10:50 AM
  • No, I believe I read about this problem some time ago. There is no exact shortcut evaluation in T-SQL, so Credit_Amount>0 and Overdue_Amount/Credit_Amount > 0.1 will not 100% filter Credit_Amount > 0 in its evaluation. I remember reading a similar thread here about a year or more ago.

    The simple fix will be

    Credit_Amount > 0  and Overdut_Amount/nullif(Credit_Amount,0) > 0.1

    This way we excluded any possibility on an error.

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, September 14, 2010 12:01 PM
    Moderator
  • interestingly there was an (admittedly old) technet discussion (http://technet.microsoft.com/en-gb/cc678236.aspx) where an MS guy says that short-circuiting is present in SQL Server. 

    "Present" is not a very exact term.

    The SQL language "allow" for short cut. That is a good thing. Imagine:

    WHERE c1 = 23 and c2 = 45

    Also, imagine an index on c1. Now, if SQL were'nt allowed to do short cut (which would be ridiculous), then the index on c1 would be useful since c2 would have to be evaluated even for the rows where c1 is false. This is what I mean by shortcut is *allowed* (amd, again, anything else would be stupid).

    But, shortcut is not *required* since that would make SQL a less declarative language, considering the optimizer whold have few options to run the query. A slightly different example (read closely)

    WHERE c1 = 23 and c2 = 45

    Now we imagine an index on c2 but not on c1. If short cut were required, then SQL Server would have to evaluate c1 = 23 first, and since we have no index on c1 we would have a table scan (even with a perfect index on c2). These kind of things are at the root when it comes to short cut in SQL Server, and "predicate pushing" (like pushing a condition in an outer query to an inner query - which is exactly what you have in your example.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, September 14, 2010 12:16 PM
    Moderator
  • In addition to Tibor's post, consider:

    WHERE EXISTS(SELECT * FROM tbl WHERE tbl.col = outertbl.col) OR @x = 1

    Most likely the query plan will include a filter with a Startup Expression, so that if @x is 1, the subquery will not be executed.

    So SQL can certainly shortcut to optimise, but not in any order that you can rely on.

    And SQL is not unique is this regard, but it works the same in many traditional languages as well. When I learnt Fortran and Pascal as my first languages, we were taught be careful with things like division by 0:

    if x <> 0 then
       if a/x > 1 then

    In Ada they had both: only and or or leaves it up to the optimizer. If you want short-cutting you use and then or or else.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Proposed as answer by Jason-Zhu Wednesday, September 15, 2010 5:03 AM
    Tuesday, September 14, 2010 9:05 PM
  • To add, SQL is an all-at-once operation. This means that all expressions that appear in the same logical phase (e.g., the WHERE filtering phase) are conceptually evaluated at the same point in time. This allows the database engine to determine the order in which it evaluates the expressions, whether to short-circuit, and so on. SQL Server does support a short-circuit, only it won't necessarily evaluate the leftmost expression first. In Jason's first query apparently SQL Server decided to evaluate the expression to the right of the AND (the one with the division) before the one to the left (verifying the value is greater than zero).

    The simple fix is to use a CASE expression (or variants like ISNULL, etc.), which have more rigid physical evaluation order in SQL Server.

    So, instead of:

    WHERE col1 > 0 AND col2 / col1 > 0.1

    Use:

    WHERE CASE WHEN col1 <= 0 THEN 'No' WHEN col2 / col1 > 0.1 THEN 'Yes' ELSE 'No END = 'Yes'

    So according to standard SQL, the fact that Jason's first query fails is not a bug. What's surprising is that in SQL Server the following can fail, even though in this case according to standard SQL such a failure would constitute a bug:

    SELECT *

    FROM (SELECT col1, col2, col3

          FROM T1

          WHERE col1 > 0) AS D

    WHERE col2/col1 > 0.1;

    The reason that this form can still yield a divide-by-zero error is that for optimization reasons SQL Server expands the definition of the table expression and internally interacts with the underlying table directly. In other words, behind the schenes SQL Server rearranges the logic to:

    SELECT col1, col2, col3

    FROM T1

    WHERE col1 > 0

      AND col2/col1 > 0.1;

    And we already know that this form can fail. The bug with the table expression is one of those that are truly by design for the sake of improved optimization. What's important is that when failures can happen, you always have the chance to use a CASE expression.

    BTW, here's another typical example where similar failures can happen:

    WHERE property_type = 'integer'

      AND CAST(property_value AS INT) > 10

    Here the code can fail due to a covrsion error. The fix is similar in the sense that you can use a CASE expression to force evaluation order.

    Cheers


    -- BG
    • Proposed as answer by Jason-Zhu Wednesday, September 15, 2010 5:02 AM
    Wednesday, September 15, 2010 1:40 AM
  • Microsoft SQL Server 2005 - 9.00.4053.00
    Wednesday, September 15, 2010 4:46 AM
  • Got it, thank you very much!

    Wednesday, September 15, 2010 4:56 AM
  • As Ben-Gan later reports, it turns out that the order of operations in "CASE" expressions is NOT guaranteed: when using aggregates order is not guaranteed and possibly also not guaranteed in some non-aggregate cases.  The BOL documentation is still pending update, so I thought it worth resurrecting this to correct the information. 

    Example of division by zero error (i.e. CASE fails to use order of operations):
    DECLARE @TblTest TABLE (N INT);
     INSERT  INTO @TblTest
      VALUES  (1), (1);
     --SELECT N FROM @tblTest;
     SELECT  
            CASE
                WHEN N=1 THEN AVG(1)
                WHEN N=4 THEN AVG (1 / 0)  --N is never 4.
                --note short-circuiting appears to work when next line is substituted for prev line:
                --WHEN N=4 THEN (1/0)
            END AS
      Result 
     FROM  @TblTest
     GROUP BY N;

    See:
    https://connect.microsoft.com/SQLServer/feedback/details/691535/aggregates-dont-follow-the-semantics-of-case 


    • Edited by TechVsLife2 Monday, February 13, 2012 2:20 AM
    Monday, February 13, 2012 2:18 AM