Efter publicering om funktionerna i att skriva i PostgreSQL, den allra första kommentaren handlade om svårigheterna att arbeta med reella siffror. Jag bestämde mig för att ta en snabb titt på koden för SQL-frågorna som är tillgängliga för mig för att se hur ofta de använder REAL-typen. Det visar sig att det används ganska ofta, och utvecklare förstår inte alltid farorna bakom det. Och detta trots att det finns ganska många bra artiklar på Internet och om Habré om funktionerna i att lagra reella tal i datorns minne och om att arbeta med dem. Därför kommer jag i den här artikeln att försöka tillämpa sådana funktioner på PostgreSQL och kommer att försöka ta en snabb titt på problemen som är förknippade med dem, så att det blir lättare för SQL-frågeutvecklare att undvika dem.
PostgreSQL-dokumentationen säger kortfattat: "Hanteringen av sådana fel och deras spridning under beräkning är föremål för en hel gren av matematik och datavetenskap och täcks inte här" (samtidigt som läsaren klokt hänvisar till IEEE 754-standarden). Vilken typ av fel menas här? Låt oss diskutera dem i ordning, så kommer det snart att stå klart varför jag tog upp pennan igen.
Låt oss ta en enkel begäran till exempel:
********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 строка)
Som ett resultat kommer vi inte att se något speciellt – vi får den förväntade 0.1. Men låt oss nu jämföra det med 0.1:
********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)
Inte jämnlikt! Vilka mirakel! Men vidare, mer. Någon kommer att säga, jag vet att REAL beter sig dåligt med bråk, så jag kommer att skriva in heltal där, och allt kommer definitivt att bli bra med dem. Okej, låt oss kasta numret 123 456 789 till REAL:
********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 строка)
Och det blev 3 till! Det var allt, databasen har äntligen glömt hur man räknar! Eller missförstår vi något? Låt oss ta reda på det.
Låt oss först komma ihåg materielen. Som ni vet kan vilket decimaltal som helst utökas till tiopotenser. Så talet 123.456 kommer att vara lika med 1*102 + 2*101 + 3*100 + 4*10-1 + 5*10-2 + 6*10-3. Men datorn arbetar med tal i binär form, därför måste de representeras i form av expansion i två potenser. Därför representeras talet 5.625 i binärt som 101.101 och kommer att vara lika med 1*22 + 0*21 + 1*20 + 1*2-1 + 0*2-2 + 1*2-3. Och om positiva potenser av två alltid ger hela decimaltal (1, 2, 4, 8, 16, etc.), så är allt mer komplicerat med negativa (0.5, 0.25, 0.125, 0,0625, etc.). Problemet är att Inte varje decimal kan representeras som en finit binär bråkdel. Således uppträder vår ökända 0.1 i form av en binär bråkdel som det periodiska värdet 0.0(0011). Följaktligen kommer det slutliga värdet för detta tal i datorns minne att variera beroende på bitdjupet.
Nu är det dags att komma ihåg hur reella tal lagras i datorns minne. Generellt sett består ett reellt tal av tre huvuddelar - tecken, mantissa och exponent. Tecknet kan vara antingen plus eller minus, så en bit tilldelas för det. Men antalet bitar av mantissan och exponenten bestäms av den verkliga typen. Så för den RIKTIGA typen är längden på mantissan 23 bitar (en bit lika med 1 läggs implicit till i början av mantissan, och resultatet är 24), och exponenten är 8 bitar. Totalt är 32 bitar eller 4 byte. Och för typen DUBBEL PRECISION kommer längden på mantissan att vara 52 bitar och exponenten 11 bitar, totalt 64 bitar, eller 8 byte. PostgreSQL stöder inte högre precision för flyttal.
Låt oss packa vårt decimaltal 0.1 i både REAL och DUBBEL PRECISION. Eftersom exponentens tecken och värde är samma, kommer vi att fokusera på mantissan (jag utelämnar medvetet de icke-uppenbara egenskaperna för att lagra exponentens värden och noll verkliga värden, eftersom de komplicerar förståelsen och distraherar från essensen om problemet, om du är intresserad, se IEEE 754-standarden). Vad ska vi få? På den översta raden kommer jag att ge "mantissa" för den REAL typen (med hänsyn till avrundningen av den sista biten med 1 till närmaste representativa nummer, annars blir det 0.099999...), och på den nedersta raden - för typen DUBBEL PRECISION:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Uppenbarligen är det två helt olika siffror! Därför, när man jämför, kommer det första talet att fyllas med nollor och kommer därför att vara större än det andra (med hänsyn till avrundning - den som är markerad i fetstil). Detta förklarar tvetydigheten från våra exempel. I det andra exemplet gjuts det explicit angivna talet 0.1 till typen DUBBEL PRECISION och jämförs sedan med ett nummer av typen REAL. Båda är reducerade till samma typ, och vi har precis vad vi ser ovan. Låt oss ändra frågan så att allt faller på plats:
********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)
Och faktiskt, genom att utföra en dubbelreduktion av talet 0.1 till VERKLIG och DUBBEL PRECISION, får vi svaret på gåtan:
********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 строка)
Detta förklarar också det tredje exemplet ovan. Numret 123 456 789 är enkelt det är omöjligt att passa in mantissan i 24 bitar (23 explicit + 1 underförstått). Det maximala heltal som får plats i 24 bitar är 224-1 = 16 777 215. Därför avrundas vårt nummer 123 456 789 till närmaste representativa 123 456 792. Genom att ändra typen till DUBBEL PRECISION ser vi inte längre detta scenario:
********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 строка)
Det är allt. Det visar sig att det inte finns några mirakel. Men allt som beskrivs är en bra anledning att fundera över hur mycket du verkligen behöver den RIKTIGA typen. Den kanske största fördelen med dess användning är hastigheten på beräkningar med en känd förlust av noggrannhet. Men skulle detta vara ett universellt scenario som skulle motivera så frekvent användning av denna typ? Tänk inte.
Källa: will.com