Po zveřejnění o vlastnostech psaní v PostgreSQL, úplně první komentář se týkal potíží při práci s reálnými čísly. Rozhodl jsem se rychle podívat na kód SQL dotazů, které mám k dispozici, abych zjistil, jak často používají typ REAL. Ukazuje se, že se používá poměrně často a vývojáři ne vždy chápou nebezpečí, která za tím stojí. A to přesto, že o vlastnostech ukládání reálných čísel do paměti počítače a o práci s nimi je na internetu i na Habré poměrně dost dobrých článků. Proto se v tomto článku pokusím aplikovat takové funkce na PostgreSQL a pokusím se rychle podívat na problémy s nimi spojené, aby bylo pro vývojáře SQL dotazů snazší se jim vyhnout.
V dokumentaci PostgreSQL se stručně uvádí: „Řízení takových chyb a jejich šíření během výpočtu je předmětem celého oboru matematiky a informatiky a není zde zahrnuto“ (přičemž moudře čtenáře odkazuje na standard IEEE 754). Jaké druhy chyb jsou zde myšleny? Pojďme si je probrat popořadě a brzy bude jasné, proč jsem zase vzal pero.
Vezměme si například jednoduchý požadavek:
********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 строка)
V důsledku toho neuvidíme nic zvláštního – dostaneme očekávaných 0.1. Ale teď to srovnejme s 0.1:
********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)
Ne rovné! Jaké zázraky! Ale dále, více. Někdo řekne, já vím, že REAL se špatně chová se zlomky, tak tam dám celá čísla a s nimi bude určitě všechno v pořádku. Dobře, vrhneme číslo 123 456 789 na REAL:
********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 строка)
A ukázalo se, že jsou to ještě 3! To je vše, databáze konečně zapomněla počítat! Nebo něco špatně chápeme? Pojďme na to přijít.
Nejprve si připomeňme materiál. Jak víte, každé desetinné číslo lze rozšířit na mocniny deseti. Číslo 123.456 se tedy bude rovnat 1*102 + 2*101 + 3*100 + 4*10-1 + 5*10-2 + 6*10-3. Počítač ale pracuje s čísly v binárním tvaru, proto je třeba je znázornit ve formě rozšíření v mocninách dvou. Proto je číslo 5.625 binárně reprezentováno jako 101.101 a bude se rovnat 1*22 + 0*21 + 1*20 + 1*2-1 + 0*2-2 + 1*2-3. A pokud kladné mocniny dvou dávají vždy celá desetinná čísla (1, 2, 4, 8, 16 atd.), pak se zápornými je vše složitější (0.5, 0.25, 0.125, 0,0625 atd.). Problém je v tom Ne každé desetinné číslo může být reprezentováno jako konečný binární zlomek. Naše notoricky známá 0.1 ve formě binárního zlomku se tedy jeví jako periodická hodnota 0.0(0011). V důsledku toho se konečná hodnota tohoto čísla v paměti počítače bude lišit v závislosti na bitové hloubce.
Nyní je čas si připomenout, jak se reálná čísla ukládají do paměti počítače. Obecně řečeno, reálné číslo se skládá ze tří hlavních částí – znaménka, mantisy a exponentu. Znaménko může být plus nebo mínus, takže je mu přidělen jeden bit. Ale počet bitů mantisy a exponentu je určen skutečným typem. Takže pro typ REAL je délka mantisy 23 bitů (jeden bit rovný 1 je implicitně přidán k začátku mantisy a výsledek je 24) a exponent je 8 bitů. Celková velikost je 32 bitů nebo 4 bajty. A pro typ DOUBLE PRECISION bude délka mantisy 52 bitů a exponent bude 11 bitů, celkem tedy 64 bitů, tedy 8 bajtů. PostgreSQL nepodporuje vyšší přesnost pro čísla s plovoucí desetinnou čárkou.
Zabalme naše desetinné číslo 0.1 do typu REAL i DOUBLE PRECISION. Protože znaménko a hodnota exponentu jsou stejné, zaměříme se na mantisu (záměrně vynechávám nesamozřejmé funkce ukládání hodnot exponentu a nulové reálné hodnoty, protože komplikují pochopení a odvádějí pozornost od podstaty problému, v případě zájmu viz standard IEEE 754). co dostaneme? V horním řádku uvedu „mantisu“ pro typ REAL (s přihlédnutím k zaokrouhlení posledního bitu o 1 na nejbližší reprezentovatelné číslo, jinak to bude 0.099999...), a na spodním řádku - pro typ DOUBLE PRECISION:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Je zřejmé, že jde o dvě zcela odlišná čísla! Při porovnávání tedy bude první číslo doplněno nulami a bude tedy větší než druhé (s přihlédnutím k zaokrouhlování - to, které je vyznačeno tučně). To vysvětluje nejednoznačnost z našich příkladů. Ve druhém příkladu je explicitně specifikované číslo 0.1 přetypováno na typ DOUBLE PRECISION a poté je porovnáno s číslem typu REAL. Oba jsou redukovány na stejný typ a máme přesně to, co vidíme výše. Upravme dotaz tak, aby vše zapadlo na své místo:
********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)
A skutečně, provedením dvojité redukce čísla 0.1 na SKUTEČNOU a DVOJNÁSOBNOU PŘESNOST dostaneme odpověď na hádanku:
********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 строка)
To také vysvětluje třetí příklad výše. Číslo 123 456 789 je jednoduché není možné vměstnat mantisu do 24 bitů (23 explicitních + 1 implicitní). Maximální celé číslo, které se vejde do 24 bitů, je 224-1 = 16 777 215. Naše číslo 123 456 789 je proto zaokrouhleno na nejbližší reprezentativní 123 456 792. Změnou typu na DOUBLE PRECISION již tento scénář nevidíme:
********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 строка)
To je vše. Ukazuje se, že neexistují žádné zázraky. Vše popsané je ale dobrým důvodem k zamyšlení, jak moc typ REAL opravdu potřebujete. Asi největší výhodou jeho použití je rychlost výpočtů se známou ztrátou přesnosti. Byl by to ale univerzální scénář, který by ospravedlnil tak časté používání tohoto typu? Nemysli.
Zdroj: www.habr.com