След публикуване относно характеристиките на писане в PostgreSQL, първият коментар беше за трудностите при работа с реални числа. Реших да разгледам набързо кода на достъпните за мен SQL заявки, за да видя колко често използват типа REAL. Оказва се, че се използва доста често и разработчиците не винаги разбират опасностите зад него. И това въпреки факта, че в интернет и на Habré има доста добри статии за характеристиките на съхраняването на реални числа в компютърната памет и за работата с тях. Затова в тази статия ще се опитам да приложа такива функции към PostgreSQL и ще се опитам да хвърля един бърз поглед върху проблемите, свързани с тях, така че да бъде по-лесно за разработчиците на SQL заявки да ги избегнат.
Документацията на PostgreSQL заявява накратко: „Управлението на такива грешки и тяхното разпространение по време на изчисление е предмет на цял клон на математиката и компютърните науки и не е обхванат тук“ (докато мъдро препраща читателя към стандарта IEEE 754). Какви грешки се имат предвид тук? Нека ги обсъдим по ред и скоро ще стане ясно защо отново хванах писалката.
Да вземем за пример проста заявка:
********* ЗАПРОС *********
SELECT 0.1::REAL;
**************************
float4
--------
0.1
(1 строка)
В резултат на това няма да видим нищо особено – ще получим очакваните 0.1. Но сега нека го сравним с 0.1:
********* ЗАПРОС *********
SELECT 0.1::REAL = 0.1;
**************************
?column?
----------
f
(1 строка)
Не е равно! Какви чудеса! Но по-нататък, още. Някой ще каже, знам, че REAL се държи зле с дроби, така че ще въведа цели числа там и всичко определено ще бъде наред с тях. Добре, нека прехвърлим числото 123 456 789 на REAL:
********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
int4
-----------
123456792
(1 строка)
И се оказаха още 3! Това е всичко, базата данни най-накрая е забравила как да брои! Или нещо не сме разбрали? Нека да го разберем.
Първо, нека си спомним материалите. Както знаете, всяко десетично число може да бъде разширено до степен на десет. И така, числото 123.456 ще бъде равно на 1*102 + 2*101 + 3*100 + 4*10-1 + 5*10-2 + 6*10-3. Но компютърът работи с числа в двоична форма, поради което те трябва да бъдат представени под формата на разширение на степени на две. Следователно числото 5.625 в двоична система е представено като 101.101 и ще бъде равно на 1*22 + 0*21 + 1*20 + 1*2-1 + 0*2-2 + 1*2-3. И ако положителните степени на две винаги дават цели десетични числа (1, 2, 4, 8, 16 и т.н.), то с отрицателните всичко е по-сложно (0.5, 0.25, 0.125, 0,0625 и т.н.). Проблемът е в това Не всяка десетична дроб може да бъде представена като крайна двоична дроб. По този начин нашето прословуто 0.1 под формата на двоична дроб се появява като периодичната стойност 0.0(0011). Следователно крайната стойност на това число в паметта на компютъра ще варира в зависимост от битовата дълбочина.
Сега е моментът да си припомним как реалните числа се съхраняват в компютърната памет. Най-общо казано, реалното число се състои от три основни части - знак, мантиса и показател. Знакът може да бъде плюс или минус, така че за него се отделя един бит. Но броят на битовете на мантисата и степента се определя от реалния тип. И така, за типа REAL дължината на мантисата е 23 бита (един бит, равен на 1, се добавя имплицитно към началото на мантисата и резултатът е 24), а показателят е 8 бита. Общата сума е 32 бита или 4 байта. А за типа DOUBLE PRECISION дължината на мантисата ще бъде 52 бита, а показателят ще бъде 11 бита, за общо 64 бита или 8 байта. PostgreSQL не поддържа по-висока точност за числа с плаваща запетая.
Нека опаковаме нашето десетично число 0.1 в типове REAL и DOUBLE PRECISION. Тъй като знакът и стойността на експонентата са еднакви, ще се съсредоточим върху мантисата (съзнателно пропускам неочевидните характеристики на съхраняване на стойностите на експонентата и нулевите реални стойности, тъй като те усложняват разбирането и отвличат вниманието от същността на проблема, ако се интересувате, вижте стандарта IEEE 754). Какво ще получим? В горния ред ще дам “мантисата” за типа REAL (като се вземе предвид закръгляването на последния бит с 1 до най-близкото представимо число, в противен случай ще бъде 0.099999...), а в долния ред - за тип ДВОЙНА ТОЧНОСТ:
0.000110011001100110011001101
0.00011001100110011001100110011001100110011001100110011001
Очевидно това са две напълно различни числа! Следователно, когато се сравнява, първото число ще бъде допълнено с нули и следователно ще бъде по-голямо от второто (като се вземе предвид закръгляването - отбелязаното с удебелен шрифт). Това обяснява неяснотата от нашите примери. Във втория пример изрично посоченото число 0.1 се преобразува в тип DOUBLE PRECISION и след това се сравнява с число от тип REAL. И двете са сведени до един и същ тип и имаме точно това, което виждаме по-горе. Нека променим заявката, така че всичко да си дойде на мястото:
********* ЗАПРОС *********
SELECT 0.1::REAL > 0.1::DOUBLE PRECISION;
**************************
?column?
----------
t
(1 строка)
И наистина, като извършим двойно редуциране на числото 0.1 до REAL и DOUBLE PRECISION, получаваме отговора на загадката:
********* ЗАПРОС *********
SELECT 0.1::REAL::DOUBLE PRECISION;
**************************
float8
-------------------
0.100000001490116
(1 строка)
Това обяснява и третия пример по-горе. Числото 123 456 789 е просто невъзможно е мантисата да се побере в 24 бита (23 изрични + 1 подразбиращ се). Максималното цяло число, което може да се побере в 24 бита, е 224-1 = 16 777 215. Следователно нашето число 123 456 789 е закръглено до най-близкото представимо 123 456 792. Като променим типа на ДВОЙНА ТОЧНОСТ, вече не виждаме този сценарий:
********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 строка)
Това е всичко. Оказва се, че чудеса няма. Но всичко описано е добра причина да се замислите колко наистина имате нужда от типа REAL. Може би най-голямото предимство на използването му е скоростта на изчисленията с известна загуба на точност. Но дали това е универсален сценарий, който би оправдал толкова честото използване на този тип? Не мисли.
Източник: www.habr.com