Po publikacji o funkcjach pisania w PostgreSQL, pierwszy komentarz dotyczył trudności w pracy z liczbami rzeczywistymi. Postanowiłem rzucić okiem na kod dostępnych mi zapytań SQL, aby zobaczyć, jak często używają one typu REAL. Okazuje się, że jest on używany dość często, a programiści nie zawsze rozumieją stojące za nim niebezpieczeństwa. I to pomimo faktu, że w Internecie i na Habré jest całkiem sporo dobrych artykułów na temat funkcji przechowywania liczb rzeczywistych w pamięci komputera i pracy z nimi. Dlatego też w tym artykule postaram się zastosować tego typu funkcjonalności w PostgreSQL, a także postaram się pokrótce przyjrzeć problemom z nimi związanym, aby twórcom zapytań SQL łatwiej było ich uniknąć.
Dokumentacja PostgreSQL stwierdza zwięźle: „Zarządzanie takimi błędami i ich propagacją podczas obliczeń jest przedmiotem całej dziedziny matematyki i informatyki i nie jest tutaj omawiane” (mądrze odsyłając czytelnika do standardu IEEE 754). Jakiego rodzaju błędy tu mamy na myśli? Omówmy je po kolei, a wkrótce stanie się jasne, dlaczego ponownie sięgnąłem po pióro.
Weźmy na przykład proste żądanie:
********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 строка)
W rezultacie nie zobaczymy niczego specjalnego – otrzymamy oczekiwane 0.1. Ale teraz porównajmy to z 0.1:
********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)
Nie równe! Cóż za cuda! Ale dalej, więcej. Ktoś powie, wiem, że REAL źle zachowuje się z ułamkami, więc wpiszę tam liczby całkowite i na pewno wszystko będzie z nimi w porządku. OK, rzućmy liczbę 123 456 789 na RZECZYWISTĄ:
********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 строка)
I okazało się, że było ich jeszcze 3! To wszystko, baza danych w końcu zapomniała, jak liczyć! A może czegoś nie rozumiemy? Rozwiążmy to.
Najpierw pamiętajmy o materiale. Jak wiadomo, każdą liczbę dziesiętną można rozwinąć do potęgi dziesięciu. Zatem liczba 123.456 będzie równa 1*102 + 2*101 + 3*100 + 4*10-1 + 5*10-2 + 6*10-3. Ale komputer operuje liczbami w postaci binarnej, dlatego należy je przedstawić w formie rozwinięcia w potęgach dwójki. Dlatego liczba 5.625 w systemie binarnym jest reprezentowana jako 101.101 i będzie równa 1*22 + 0*21 + 1*20 + 1*2-1 + 0*2-2 + 1*2-3. A jeśli dodatnie potęgi dwójki zawsze dają liczby całkowite po przecinku (1, 2, 4, 8, 16 itd.), to w przypadku ujemnych wszystko jest bardziej skomplikowane (0.5, 0.25, 0.125, 0,0625 itd.). Problemem jest Nie każdy ułamek dziesiętny można przedstawić jako skończony ułamek binarny. Zatem nasze notoryczne 0.1 w postaci ułamka binarnego pojawia się jako wartość okresowa 0.0 (0011). W rezultacie ostateczna wartość tej liczby w pamięci komputera będzie się różnić w zależności od głębi bitowej.
Nadszedł czas, aby przypomnieć sobie, w jaki sposób liczby rzeczywiste są przechowywane w pamięci komputera. Ogólnie rzecz biorąc, liczba rzeczywista składa się z trzech głównych części - znaku, mantysy i wykładnika. Znak może być plusem lub minusem, dlatego przydzielany jest dla niego jeden bit. Ale liczba bitów mantysy i wykładnika zależy od rzeczywistego typu. Zatem dla typu REAL długość mantysy wynosi 23 bity (jeden bit równy 1 jest domyślnie dodawany na początku mantysy, a wynikiem jest 24), a wykładnik wynosi 8 bitów. Łącznie jest to 32 bity, czyli 4 bajty. W przypadku typu DOUBLE PRECISION długość mantysy będzie wynosić 52 bity, a wykładnik będzie wynosił 11 bitów, co daje w sumie 64 bity, czyli 8 bajtów. PostgreSQL nie obsługuje większej precyzji dla liczb zmiennoprzecinkowych.
Spakujmy naszą liczbę dziesiętną 0.1 do typów REAL i DOUBLE PRECISION. Ponieważ znak i wartość wykładnika są takie same, skupimy się na mantysie (celowo pomijam nieoczywiste cechy przechowywania wartości wykładnika i zerowych wartości rzeczywistych, ponieważ komplikują one zrozumienie i odwracają uwagę od istoty problemu, jeśli jesteś zainteresowany, zobacz standard IEEE 754). Co otrzymamy? W górnym wierszu podam „mantysę” dla typu REAL (biorąc pod uwagę zaokrąglenie ostatniego bitu o 1 do najbliższej możliwej do przedstawienia liczby, w przeciwnym razie będzie to 0.099999...), a w dolnym wierszu – dla typ PODWÓJNA PRECYZJA:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Oczywiście są to dwie zupełnie różne liczby! Dlatego przy porównywaniu pierwsza liczba zostanie dopełniona zerami, a co za tym idzie, będzie większa od drugiej (uwzględniając zaokrąglenia - ta zaznaczona pogrubioną czcionką). To wyjaśnia niejednoznaczność z naszych przykładów. W drugim przykładzie jawnie określona liczba 0.1 jest rzutowana na typ DOUBLE PRECISION, a następnie porównywana z liczbą typu REAL. Obydwa sprowadzają się do tego samego typu i mamy dokładnie to, co widzimy powyżej. Zmodyfikujmy zapytanie tak, aby wszystko się ułożyło:
********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)
I rzeczywiście, dokonując podwójnej redukcji liczby 0.1 do RZECZYWISTEJ i PODWÓJNEJ PRECYZJI, otrzymujemy odpowiedź na zagadkę:
********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 строка)
Wyjaśnia to również trzeci przykład powyżej. Liczba 123 456 789 jest prosta niemożliwe jest zmieszczenie mantysy w 24 bitach (23 wyraźne + 1 dorozumiane). Maksymalna liczba całkowita, która może zmieścić się w 24 bitach, to 224-1 = 16 777 215. Dlatego nasza liczba 123 456 789 jest zaokrąglana do najbliższej możliwej do przedstawienia liczby 123 456 792. Zmieniając typ na DOUBLE PRECISION, nie widzimy już tego scenariusza:
********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 строка)
To wszystko. Okazuje się, że cudów nie ma. Ale wszystko, co opisano, jest dobrym powodem, aby pomyśleć o tym, jak bardzo potrzebujesz PRAWDZIWEGO typu. Być może największą zaletą jego zastosowania jest szybkość obliczeń przy znanej utracie dokładności. Czy byłby to jednak scenariusz uniwersalny, uzasadniający tak częste stosowanie tego typu rozwiązań? Nie myśl.
Źródło: www.habr.com