Dlaczego TSQL zwraca niepoprawną wartość dla POWER (2., 64.)?

select POWER(2.,64.)zwraca 18446744073709552000zamiast 18446744073709551616. Wygląda na to, że ma tylko 16 cyfr precyzji (zaokrąglenie 17).

Nawet precyzując precyzję select power(cast(2 as numeric(38,0)),cast(64 as numeric(38,0))), nadal zwraca zaokrąglony wynik.

Wydaje się, że jest to dość podstawowa operacja polegająca na tym, że łuszczy się z taką dokładnością przy 16 cyfrach. Najwyższe, jakie może poprawnie obliczyć, to tylko POWER(2.,56.)nie POWER(2.,57.). Co tu się dzieje?

Naprawdę straszne jest to, że select 2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.;faktycznie zwraca odpowiednią wartość. Tyle o zwięzłości.

Z dokumentacji online :

POWER ( float_expression , y )  

Argumenty

float_expression Jest wyrażeniem typu float lub typu, który można niejawnie przekonwertować na float

Implikacja jest taka, że ​​cokolwiek przekażesz jako pierwszy parametr, zostanie domyślnie przekazane do float(53) przed wykonaniem funkcji. Jednak nie zawsze tak jest .

Gdyby tak było, tłumaczyłoby to utratę precyzji:

Konwersja wartości zmiennoprzecinkowych wykorzystujących notację naukową na dziesiętną lub numeryczną jest ograniczona do wartości o dokładności 17 cyfr. Każda wartość z dokładnością większą niż 17 zaokrągleń do zera.

Z drugiej strony dosłowny 2.jest typ numeric…:

DECLARE @foo sql_variant;
SELECT @foo = 2.;
SELECT SQL_VARIANT_PROPERTY(@foo, 'BaseType');
GO

| (Bez nazwy kolumny) |
| : --------------- |
| numeryczny |

dbfiddle tutaj

… A operator mnożenia zwraca typ danych argumentu o wyższym priorytecie .

Wygląda na to, że w 2016 r. (SP1) zachowana jest cała precyzja:

SELECT @@version;
GO

| (Bez nazwy kolumny) |
| : ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------- |
| Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) <br> 28 października 2016 18:17:30 <br> Prawa autorskie (c) Microsoft Corporation <br> Express Edition (64-bit) na Windows Server 2012 R2 Standard 6.3 <X64> (kompilacja 9600:) (Hypervisor) <br> |

SELECT POWER(2.,64.);
GO

| (Bez nazwy kolumny) |
| : ------------------- |
| 18446744073709551616 |

dbfiddle tutaj

… Ale w 2014 r. (SP2) nie są to:

SELECT @@version;
GO

| (Bez nazwy kolumny) |
| : ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------ |
| Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) <br> 17 czerwca 2016 19:14:09 <br> Prawa autorskie (c) Microsoft Corporation <br> Express Edition (64-bit) na Windows NT 6.3 <X64> (kompilacja 9600:) (Hypervisor) <br> |

SELECT POWER(2.,64.);
GO

| (Bez nazwy kolumny) |
| : ------------------- |
| 18446744073709552000 |

dbfiddle tutaj

Wynik 2 ⁶⁴ jest dokładnie reprezentowalny w float(i realpod tym względem).

Problem powstaje, gdy ten dokładny wynik zostanie ponownie przekonwertowany na numeric(typ pierwszego POWERoperandu).

Przed poziom zgodności bazy danych 130 został wprowadzony, SQL Server zaokrągleniu floatdo numericniejawne konwersje do maksymalnie 17 cyfr.

Poniżej poziomu zgodności 130 podczas konwersji zachowana jest jak największa precyzja. Jest to udokumentowane w artykule z bazy wiedzy:

Ulepszenia programu SQL Server 2016 w obsłudze niektórych typów danych i nietypowych operacji

Aby skorzystać z tego w bazie danych Azure SQL, musisz ustawić wartość COMPATIBILITY_LEVEL130:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130;

Testy obciążenia są potrzebne, ponieważ nowe porozumienie nie jest panaceum. Na przykład:

SELECT POWER(10., 38);

... powinien zgłosić błąd, ponieważ nie można zapisać 10 ³⁸numeric (maksymalna dokładność 38). Błąd przepełnienia powoduje zgodność poniżej 120, ale wynik poniżej 130 to:

99999999999999997748809823456034029568 -- (38 digits)

Przy odrobinie matematyki możemy znaleźć obejście. Dla nieparzystych n:

2 ^ n 
= 2 ^ (2k + 1)
= 2 * (2 ^ 2k)
= 2 * (2 ^ k) * (2 ^ k)

Nawet n:

2 ^ n 
= 2 ^ (2k)
= 1 * (2 ^ 2k)
= 1 * (2 ^ k) * (2 ^ k)

Jednym ze sposobów na napisanie tego w T-SQL:

DECLARE @exponent INTEGER = 57;

SELECT (1 + @exponent % 2) * POWER(2., FLOOR(0.5 * @exponent)) * POWER(2., FLOOR(0.5 * @exponent));

Testowany na SQL Server 2008, wynik to 144115188075855872 zamiast 144115188075855870.

Działa to aż do wykładnika 113. Wygląda na to, że NUMERYCZNY (38,0) może przechowywać do 2 ^ 126, więc nie jest całkiem pełny zasięg, ale formuła może być podzielona na więcej części, jeśli to konieczne .

Dla zabawy rekurencyjne rozwiązanie CTE:

with 
  prm (p, e) as           -- parameters, to evaluate: p**e
    (select 2, 64),       -- (2 ** 64)  
  pow (power, exp) as 
    (select cast(p as numeric(30,0)), 
            e
     from prm 
     union all 
     select cast(power * power * (case when exp % 2 = 0 then 1 else p end) 
                 as numeric(30,0)), 
            exp / 2 
     from prm, pow 
     where exp > 1 
    ) 
select power 
from pow 
where exp = 1 ;