Kontynuując mój najnowszy trend gry z dużymi liczbami , niedawno wygotowałem błąd, na który wpadłem, do następującego kodu:

DECLARE @big_number DECIMAL(38,0) = '1' + REPLICATE(0, 37);

PRINT @big_number + 1;
PRINT @big_number - 1;
PRINT @big_number * 1;
PRINT @big_number / 1;

Dane wyjściowe, które otrzymuję dla tego kodu to:

10000000000000000000000000000000000001
9999999999999999999999999999999999999
10000000000000000000000000000000000000
Msg 8115, Level 16, State 2, Line 6
Arithmetic overflow error converting expression to data type numeric.

Co?

Dlaczego pierwsze 3 operacje miałyby działać, a nie ostatnie? I w jaki sposób może wystąpić błąd przepełnienia arytmetycznego, jeśli @big_numbermożna oczywiście zapisać dane wyjściowe @big_number / 1?

Zrozumienie precyzji i skali w kontekście operacji arytmetycznych

Rozbijmy to i przyjrzyjmy się bliżej szczegółom operatora arytmetycznego dzielenia . Oto, co MSDN ma do powiedzenia na temat typów wyników operatora dzielenia :

Typy wyników

Zwraca typ danych argumentu o wyższym priorytecie. Aby uzyskać więcej informacji, zobacz Pierwszeństwo typu danych (Transact-SQL) .

Jeśli dywidenda liczb całkowitych jest dzielona przez dzielnik liczb całkowitych, wynikiem jest liczba całkowita, która ma ułamkową część wyniku obciętą.

Wiemy, że @big_numberto DECIMAL. Jakiego typu dane używa SQL Server 1? Rzuca to na INT. Możemy to potwierdzić za pomocą SQL_VARIANT_PROPERTY():

SELECT
      SQL_VARIANT_PROPERTY(1, 'BaseType')   AS [BaseType]  -- int
    , SQL_VARIANT_PROPERTY(1, 'Precision')  AS [Precision] -- 10
    , SQL_VARIANT_PROPERTY(1, 'Scale')      AS [Scale]     -- 0
;

W przypadku kopnięć możemy również zastąpić 1oryginalny blok kodu jawnie wpisaną wartością, taką jak DECLARE @one INT = 1;i potwierdzić, że otrzymujemy te same wyniki.

Więc mamy DECIMALi INT. Ponieważ DECIMALma wyższy priorytet niż typ danychINT , wiemy, że dane wyjściowe z naszego podziału zostaną przeniesione na DECIMAL.

Więc gdzie jest problem?

Problem dotyczy skali DECIMALwyjścia. Oto tabela reguł dotyczących tego, jak SQL Server określa dokładność i skalę wyników uzyskanych z operacji arytmetycznych:

Operation                              Result precision                       Result scale *
-------------------------------------------------------------------------------------------------
e1 + e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 - e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 * e2                                p1 + p2 + 1                            s1 + s2
e1 / e2                                p1 - s1 + s2 + max(6, s1 + p2 + 1)     max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2   max(s1, s2) + max(p1-s1, p2-s2)        max(s1, s2)
e1 % e2                                min(p1-s1, p2 -s2) + max( s1,s2 )      max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result 
  precision is greater than 38, the corresponding scale is reduced to prevent the 
  integral part of a result from being truncated.

Oto, co mamy dla zmiennych w tej tabeli:

e1: @big_number, a DECIMAL(38, 0)
-> p1: 38
-> s1: 0

e2: 1, an INT
-> p2: 10
-> s2: 0

e1 / e2
-> Result precision: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 + max(6, 11) = 49
-> Result scale:                    max(6, s1 + p2 + 1) =      max(6, 11) = 11

Zgodnie z komentarzem do gwiazdki w powyższej tabeli maksymalna precyzjaDECIMAL , jaką można uzyskać, wynosi 38 . Dlatego nasza precyzja wyniku zostaje zmniejszona z 49 do 38 i „odpowiednia skala jest zmniejszona, aby zapobiec obcięciu integralnej części wyniku”. Z tego komentarza nie wynika jasno, w jaki sposób skala jest zmniejszana, ale wiemy o tym:

Zgodnie ze wzorem w tabeli minimalna możliwa skala, którą można uzyskać po podzieleniu dwóch DECIMALs, wynosi 6.

W rezultacie otrzymujemy następujące wyniki:

e1 / e2
-> Result precision: 49 -> reduced to 38
-> Result scale:     11 -> reduced to 6  

Note that 6 is the minimum possible scale it can be reduced to. 
It may be between 6 and 11 inclusive.

Jak to wyjaśnia przepełnienie arytmetyczne

Teraz odpowiedź jest oczywista:

Wynik naszej dywizji zostaje obsadzony DECIMAL(38, 6)i DECIMAL(38, 6)nie może utrzymać 10 ³⁷ .

Dzięki temu możemy skonstruować inny podział, który mógłby przez upewniając się, że wynik może zmieścić się w DECIMAL(38, 6):

DECLARE @big_number    DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million   INT           = '1' + REPLICATE(0, 6);

PRINT @big_number / @one_million;

Wynik to:

10000000000000000000000000000000.000000

Zanotuj 6 zer po przecinku. Możemy potwierdzić typ danych Rezultatem jest DECIMAL(38, 6)za pomocą SQL_VARIANT_PROPERTY()jak wyżej:

DECLARE @big_number   DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million  INT           = '1' + REPLICATE(0, 6);

SELECT
      SQL_VARIANT_PROPERTY(@big_number / @one_million, 'BaseType')  AS [BaseType]  -- decimal
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Precision') AS [Precision] -- 38
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Scale')     AS [Scale]     -- 6
;

Niebezpieczne obejście

Jak więc obejść to ograniczenie?

Cóż, to z pewnością zależy od tego, dla czego dokonujesz tych obliczeń. Jednym z rozwiązań, do którego możesz od razu przejść, jest konwersja liczb do FLOATobliczeń, a następnie przekonwertowanie ich z powrotem na DECIMALgotowe.

Może to działać w pewnych okolicznościach, ale należy uważać, aby zrozumieć, jakie są te okoliczności. Jak wszyscy wiemy, konwersja liczb na i z FLOATjest niebezpieczna i może dać nieoczekiwane lub nieprawidłowe wyniki.

W naszym przypadku konwersja 10 ³⁷ na i z FLOATdaje wynik, który jest po prostu zły :

DECLARE @big_number     DECIMAL(38,0)  = '1' + REPLICATE(0, 37);
DECLARE @big_number_f   FLOAT          = CAST(@big_number AS FLOAT);

SELECT
      @big_number                           AS big_number      -- 10^37
    , @big_number_f                         AS big_number_f    -- 10^37
    , CAST(@big_number_f AS DECIMAL(38, 0)) AS big_number_f_d  -- 9999999999999999.5 * 10^21
;

I masz to. Dzielcie się ostrożnie, moje dzieci.