Início / dba / Perguntas / 29426
Accepted
DamagedGoods
Asked: 2012-11-29 03:59:58 +0800 CST

Dividir por zero fazendo com que a transação de erro falhe com Arthimetic Abort e ANSI WARNINGS FALSE após a atualização

  • 772

Esta é uma questão estranha..

Estamos recebendo erros de divisão por zero que estão fazendo com que o SQL Server encerre a transação e bloqueie o site de teste.

Abort aritmético é definido como FALSE junto com avisos ANSI e todos os outros padrões... Nenhuma alteração de código ocorreu, portanto, também não está explicitamente definida no código.

Isso está acontecendo apenas na maioria dos bancos de dados no servidor que contêm dados muito semelhantes e os mesmos metadados, mas não em todos.

Isso começou a acontecer depois que realizei uma atualização local de nosso servidor de teste de 2008 para 2012. (o problema ocorre em ambos os modos de compatibilidade)

Se eu transformar ARITHMETICABORT explicitamente em falso dentro da janela de consulta, ele funcionará bem ... algo parece estar ignorando as configurações amplas do banco de dados. isso acontece de dentro do estúdio de gerenciamento ou do código

aqui está o primeiro trecho de código em que o detectamos, para referência, mas acho que isso não está relacionado ao código

    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

Esta não é estritamente uma resposta, pois não sei dizer por que isso aconteceria, mas encontrei a causa raiz. Espero que alguém possa me dizer por que esse comportamento acontece ou se é um bug?

O erro acontece quando a 2ª instrução após UNION não retorna nenhuma linha.

fazia tempo que não via um assim!

sql-server-2012

1 respostas

  1. Best Answer

    O problema está na expressão:

    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

    Um erro de divisão por zero pode ocorrer para qualquer linha em que CommissionPercent = 0e MarkupPercent = 0. Se o erro realmente ocorre na prática depende da ordem de avaliação no tempo de execução, que por sua vez depende da forma do plano de execução e dos comportamentos detalhados dos operadores do plano individual.

    Por exemplo, algumas formas de planta podem eliminar fisicamente as linhas problemáticas antes que a expressão seja avaliada. Outros podem adiar a avaliação da expressão até que o resultado seja solicitado por outro operador posteriormente no plano. Existem muitas possibilidades e sutilezas aqui, levando aos comportamentos aparentemente 'estranhos' que você observa.

    Em geral, o SQL Server não oferece garantias sobre a ordem de avaliação de expressões escalares ou o número de vezes que uma determinada expressão pode ser avaliada em tempo de execução. A exceção a isso é a CASEdeclaração, que fornece garantias sobre a ordem de avaliação na maioria das circunstâncias. Da documentação :

    A instrução CASE avalia suas condições sequencialmente e para com a primeira condição cuja condição é satisfeita. Em algumas situações, uma expressão é avaliada antes que uma instrução CASE receba os resultados da expressão como sua entrada. Erros na avaliação dessas expressões são possíveis. Expressões agregadas que aparecem em argumentos WHEN para uma instrução CASE são avaliadas primeiro e, em seguida, fornecidas para a instrução CASE.

    A maneira de se proteger contra o erro é modificar a CASEexpressão para que a divisão por zero seja tratada explicitamente usando uma WHENcláusula anterior. Como você escolhe contabilizar as linhas CommissionPercent = 0e MarkupPercent = 0é algo que só tem a informação para decidir. Dito isso, o comportamento com ARITHABORTset to OFFé retornar um arquivo NULL. A seguinte alteração na CASEinstrução produz esse resultado com ARITHABORTo valor altamente recomendado 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

    Explicar explicitamente a possibilidade de um erro de divisão por zero é muito melhor do que confiar na configuração ARITHABORT . Esse link contém a seguinte declaração:

    Você deve sempre definir ARITHABORT como ON em suas sessões de logon. Definir ARITHABORT como OFF pode afetar negativamente a otimização da consulta, levando a problemas de desempenho.

    ARITHABORTé necessário ONpara muitos recursos de mecanismo mais recentes (por exemplo, índices em colunas computadas e exibições indexadas). A maioria das bibliotecas de cliente é definida explicitamente ARITHABORT ao ONconectar, o que substitui qualquer configuração no nível do banco de dados ou do servidor.

    Minha forte recomendação é sempre usar as SETconfigurações recomendadas e codificar defensivamente contra condições de erro.

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

SET NUMERIC_ROUNDABORT OFF;
    • 3

relate perguntas