Po publikacji na temat typizacji w PostgreSQL, pierwszy komentarz dotyczył trudności w pracy z liczbami rzeczywistymi. Postanowiłem szybko przejrzeć dostępny mi kod zapytań SQL, aby zobaczyć, jak często typ REAL jest w nich używany. Okazało się, że jest on używany dość często, a deweloperzy nie zawsze rozumieją związane z nim zagrożenia. I to pomimo że w Internecie oraz na Habrze można znaleźć sporo dobrych artykułów o zasadach przechowywania liczb rzeczywistych w pamięci maszyny i pracy z nimi. Dlatego w tym artykule postaram się zastosować te zasady do PostgreSQL i postaram się „na palcach” omówić związane z nimi problemy, aby ułatwić deweloperom zapytań SQL ich unikanie.
Dokumentacja PostgreSQL zawiera zwięzłe stwierdzenie: „Zarządzanie takimi błędami i ich propagacja podczas obliczeń jest przedmiotem całego działu matematyki i informatyki i nie będzie tutaj omawiane” (z rozsądkiem odsyłając czytelnika do standardu IEEE 754). Jakie błędy są tutaj na myśli? Omówmy je po kolei, a szybko stanie się jasne, dlaczego znów wziąłem się za pióro.
Weźmy na przykład proste zapytanie:
********* ZAPYTANIE *********
SELECT 0.1::REAL;
****************************
float4
--------
0.1
(1 wiersz)
W rezultacie nie zobaczymy nic szczególnego – otrzymamy oczekiwaną wartość 0.1. Ale teraz porównajmy ją z 0.1:
********* ZAPYTANIE *********
SELECT 0.1::REAL = 0.1;
****************************
?kolumna?
----------
f
(1 wiersz)
Nie są równe! Co za cuda! Ale to nie koniec. Ktoś powie, że wie, że REAL źle działa z ułamkami, więc będzie wprowadzał liczby całkowite, z nimi na pewno wszystko będzie dobrze. Dobrze, przekształćmy liczbę 123 456 789 do typu REAL:
********* ZAPYTANIE *********
SELECT 123456789::REAL::INT;
****************************
int4
-----------
123456792
(1 wiersz)
I wyszło więcej o 3! Cała baza ostatecznie zapomniała liczyć! Czy może czegoś nie rozumiemy? Zobaczmy.
Na początek przypomnijmy sobie podstawy. Jak wiadomo, każdą liczbę dziesiętną można przedstawić w postaci potęg dziesięciu. Tak więc liczba 123.456 będzie równa 1*10² + 2*10¹ + 3*10⁰ + 4*10⁻¹ + 5*10⁻² + 6*10⁻³. Jednak komputer operuje liczbami w systemie binarnym, dlatego musimy je przedstawić jako sumę potęg dwójki. Dlatego liczba 5.625 w postaci binarnej to 101.101 i będzie równa 1*2² + 0*2¹ + 1*2⁰ + 1*2⁻¹ + 0*2⁻² + 1*2⁻³. I podczas gdy dodatnie potęgi dwójki zawsze dają całkowite liczby dziesiętne (1, 2, 4, 8, 16 itd.), to z potęgami ujemnymi sprawa jest bardziej skomplikowana (0.5, 0.25, 0.125, 0.0625 itd.). Problem polega na tym, że nie każdą dziesiętną ułamkową można przedstawić w formie skończonego ułamka binarnego. Tak więc, nasze nieszczęsne 0.1 jako ułamek binarny przyjmuje postać okresową 0.0(0011). W związku z tym końcowa wartość tej liczby w pamięci maszyny będzie się zmieniać w zależności od rozmiaru bitów.
Teraz nadszedł czas, aby przypomnieć sobie, jak liczby rzeczywiste są przechowywane w pamięci komputera. Mówiąc ogólnie, liczba rzeczywista składa się z trzech głównych części – znaku, mantysy i wykładnika. Znak może być albo plus, albo minus, dlatego zajmuje jeden bit. Natomiast liczba bitów mantysy i wykładnika jest określona przez typ liczby rzeczywistej. Dla typu REAL długość mantysy wynosi 23 bity (jeden bit, równy 1, jest implicitnie dodawany na początku mantysy, co daje 24), a wykładnika – 8 bitów. W sumie daje to 32 bity, czyli 4 bajty. Dla typu DOUBLE PRECISION długość mantysy wynosi już 52 bity, a wykładnika – 11 bitów, co daje łącznie 64 bity, czyli 8 bajtów. PostgreSQL nie obsługuje większej precyzji dla liczb zmiennoprzecinkowych.
Zaprojektujmy naszą liczbę 0.1 w postaci dziesiętnej w obu typach – REAL i DOUBLE PRECISION. Ponieważ znak i wartość wykładnika są takie same, skupmy się na mantysie (świadomie pomijam mniej oczywiste cechy przechowywania wartości wykładnika i zerowych liczb rzeczywistych, ponieważ mogą utrudniać zrozumienie i odwracać uwagę od sedna problemu; jeśli chcesz, zajrzyj do standardu IEEE 754). Co otrzymamy? W górnej linii podam „mantysę” dla typu REAL (uwzględniając zaokrąglenie ostatniego bitu do 1 do najbliższej reprezentowalnej liczby, w przeciwnym razie otrzymamy 0.099999…), a w dolnej – dla typu DOUBLE PRECISION:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Oczywiście, że to dwie zupełnie różne liczby! Dlatego przy porównaniu pierwsza liczba zostanie uzupełniona zerami i w konsekwencji będzie większa od drugiej (z uwzględnieniem zaokrąglenia – pogrubionej jedynki). To wyjaśnia niejednoznaczności w naszych przykładach. W drugim przykładzie wyraźnie wskazana liczba 0.1 zostaje przekształcona do typu DOUBLE PRECISION, po czym porównywana jest z liczbą typu REAL. Oba są przekształcane do jednego typu, a więc mamy dokładnie to, co widzimy powyżej. Zmienimy zapytanie, aby wszystko stanęło na swoim miejscu:
********* ZAPYTANIE *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?kolumna?
----------
t
(1 wiersz)
I rzeczywiście, wykonując podwójne przekształcenie liczby 0.1 do REAL i DOUBLE PRECISION otrzymujemy odpowiedź na zagadkę:
********* ZAPYTANIE *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 wiersz)
To samo dotyczy również trzeciego przykładu z podanych powyżej. Liczba 123 456 789 po prostu nie może być pomieszczona w 24 bitach mantysy (23 jawnych + 1 domyślny). Maksymalna liczba całkowita, którą można umieścić w 24 bitach, to 2^24 - 1 = 16 777 215. Dlatego nasza liczba 123 456 789 jest zaokrąglana do najbliższego reprezentowalnego 123 456 792. Zmieniajac typ na DOUBLE PRECISION, już nie zobaczymy takiego scenariusza:
********* ZAPYTANIE *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 wiersz)
I to wszystko. Okazuje się, że żadnych cudów. Ale wszystko to jest dobrym powodem do zastanowienia się, jak bardzo rzeczywiście potrzebny jest wam typ REAL. Być może największą zaletą jego użycia jest szybkość obliczeń z oczywistą utratą precyzji. Ale czy będzie to uniwersalny scenariusz, który usprawiedliwia tak częste stosowanie tego typu? Nie sądzę.
Źródło: habr.com
