Divide by Zero Causing Error transaction to fail with Arthimetic Abort and ANSI WARNINGS FALSE after upgrade

Question

This is a strange issue..

We are receiving Divide by zero errors that are causing SQL Server to terminate transaction and crashing the test website.

Arithmetic Abort is set to FALSE along with ANSI warnings and all the other defaults... No code change has happened so it is not explicitly set in the code either.

This is only happening on most of the databases on the server that contain very similar data and the same metadata, but not all.

This has started happening after I performed a in place upgrade of our 2008 test server to 2012. (the issue occurs in both compatibility modes)

If I explicitly turn ARITHMETICABORT to false inside the query window it runs fine... something seems to be ignoring the database wide settings. this happens from within management studio or from code

here is the first piece of code we detected it in, for reference, but I think this is not related to the code

    SELECT  sm.MarkupId, sm.WebSiteCode, sm.CurrencyCode, sm.OfferTitle, sm.Priority, sm.MarkupPercent, sm.MarkupDownAmount, sm.Cumulative,
            sm.AllowAdditionalDiscounts, sm.StartDate, sm.EndDate, sm.DayOfWeek, sm.StartTime, sm.EndTime, sm.DepartureStartDate, sm.DepartureEndDate,
            ISNULL(s.name, '') AS Supplier, ISNULL(s.Supplierid, '') AS SupplierId, ISNULL(sb.CommissionPercent, 0) AS CommissionPercent, eg.Title AS EstabGroup,
            d.DomainName, sm.DurationFrom, sm.DurationTo, sm.BookingTotalFrom, sm.BookingTotalTo,
            ActualCommission = CASE WHEN ISNULL(sb.CommissionPercent, 0) = 0 THEN CAST(( ( sm.MarkupPercent - 100 ) / sm.MarkupPercent ) * 100 AS DECIMAL(18, 2))
                                    ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - ( 100.0 - sm.MarkupPercent ), 2)
                               END, sm.BoardTypeId, BoardTitle = ISNULL(b.Title, '')
    FROM    SupplierMarkup_tbl sm
            INNER JOIN Domain_tbl d ON d.DomainId = sm.DomainId
            LEFT JOIN Supplier_tbl s ON sm.SupplierId = s.SupplierId
            LEFT JOIN acomEstabGroup_tbl eg ON sm.EstabGroupId = eg.EstabGroupId
            LEFT JOIN SupplierBookedItemType_tbl sb ON sb.SupplierId = s.SupplierId
                                                       AND sb.WebsiteCode = sm.WebSiteCode
                                                       AND sb.BookedItemTypeId = 17
            LEFT JOIN acomBoardType_tbl b ON b.BoardTypeId = sm.BoardTypeId
    WHERE   1 = 1
    UNION
    SELECT  CASE WHEN cm.IsExtranet = 1
                      AND cm.GrossAdjustment = 1 THEN -2
                 WHEN cm.IsExtranet = 1
                      AND cm.NetAdjustment = 1 THEN -1
                 ELSE 0
            END AS MarkupId, '' AS WebSiteCode, '' AS CurrencyCode, mo.Title AS OfferTitle, -1 AS Priority, cm.MarkupPercent * 100 AS MarkupPercent,
            0 AS MarkupDownAmount, cm.Cumulative, cm.AllowAdditionalDiscounts, cm.StartDate, cm.EndDate, cm.DayOfWeek, cm.StartTime, cm.EndTime,
            cm.CheckInStartDate AS DepartureStartDate, cm.CheckInEndDate AS DepartureEndDate, ISNULL(s.name, '') AS Supplier, ISNULL(s.Supplierid, '') AS SupplierId,
            0 AS CommissionPercent, '' AS EstabGroup, d.DomainName, cm.DurationFrom, cm.DurationTo, cm.BookingTotalFrom, cm.BookingTotalTo, ActualCommission = 0,
            cm.BoardTypeId, BoardTitle = ISNULL(b.Title, '')
    FROM    conContractEstabMarkup_tbl cm
            INNER JOIN Domain_tbl d ON d.DomainId = cm.DomainId
            LEFT JOIN Supplier_tbl s ON s.SupplierId = 97
            LEFT JOIN acomBoardType_tbl b ON b.BoardTypeId = cm.BoardTypeId
            LEFT JOIN dbo.MarkupOffer_tbl mo ON mo.MarkupOfferId = cm.MarkupOfferId
    WHERE   cm.EstabId IS NULL
            AND cm.Deleted = 0
    ORDER BY Priority, Supplier

---

This is not strictly an answer as I cant tell why this would happen, but I have found the root cause. Hopefully someone can tell me why this behavior happens or if it is a bug ?

The error happens when the 2nd statement after the UNION doesn't return any rows.

not seen one like this in a while !

Answer 1

The problem is in the expression:

ActualCommission = 
    CASE 
        WHEN ISNULL(sb.CommissionPercent, 0) = 0
        THEN CAST(((sm.MarkupPercent - 100) / sm.MarkupPercent) * 100 AS decimal(18, 2))
        ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - (100.0 - sm.MarkupPercent), 2)
    END

A divide-by-zero error could occur for any row where CommissionPercent = 0 and MarkupPercent = 0. Whether the error actually occurs in practice depends on the order of evaluation at runtime, which in turn depends on the shape of the execution plan and the detailed behaviours of the individual plan operators.

For example, some plan shapes might physically eliminate the problematic rows before the expression is evaluated. Others might defer evaluation of the expression until the result is required by another operator later in the plan. There are many possibilities and subtleties here, leading to the apparently 'strange' behaviours you observe.

In general, SQL Server makes no guarantees about the order of evaluation of scalar expressions or the number of times a particular expression might be evaluated at runtime. The exception to this is the CASE statement, which does provide guarantees about the order of evaluation in most circumstances. From the documentation:

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.

The way to protect against the error is to modify the CASE expression so the divide-by-zero is explicitly handled using an earlier WHEN clause. How you choose to account for rows with CommissionPercent = 0 and MarkupPercent = 0 is something only have the information to decide about. That said, the behaviour with ARITHABORT set to OFF is to return a NULL. The following change to the CASE statement produces that result with ARITHABORT set to the highly-recommended ON:

ActualCommission = 
    CASE
        -- New
        WHEN (sb.CommissionPercent = 0 OR sb.CommissionPercent IS NULL) AND sm.MarkupPercent = 0
        THEN NULL

        WHEN ISNULL(sb.CommissionPercent, 0) = 0
        THEN CAST(((sm.MarkupPercent - 100) / sm.MarkupPercent) * 100 AS decimal(18, 2))
        ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - (100.0 - sm.MarkupPercent), 2)
    END

Explicitly accounting for the possibility of a divide-by-zero error is very much better than relying on the ARITHABORT setting. That link contains the following statement:

You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.

ARITHABORT is required to be ON for many newer engine features (e.g. indexes on computed columns and indexed views). Most client libraries explicitly set ARITHABORT to ON when connecting, which overrides any database- or server- level setting.

My strong recommendation is to always use the recommended SET settings and code defensively against error conditions.

SET ANSI_NULLS,
    ANSI_PADDING,
    ANSI_WARNINGS,
    ARITHABORT,
    CONCAT_NULL_YIELDS_NULL,
    QUOTED_IDENTIFIER
    ON;  

SET NUMERIC_ROUNDABORT OFF;