після публікації про особливості типізації в PostgreSQL, перший коментар був про складності роботи з речовими числами. Я вирішив швидко пробігтися за кодом доступних мені SQL-запитів, щоб подивитися, наскільки часто в них використовується тип REAL. Досить часто використовується, як виявилося, і не завжди розробники розуміють небезпеки, що стоять за ним. І це незважаючи на те, що в Інтернеті та на Хабрі досить багато хороших статей про особливості зберігання речових чисел у машинній пам'яті та про роботу з ними. Тому в цій статті я постараюся застосувати такі особливості до 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 до типу 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…), а в нижній – для типу DOUBLE PRECISION:
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. Змінивши тип на DOUBLE PRECISION, ми вже не побачимо такого
********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
int4
-----------
123456789
(1 строка)
От і все. Виявляється, жодних чудес. Але все описане - хороший привід задуматися щодо того, наскільки вам дійсно потрібен тип REAL. Мабуть, найбільший плюс його використання – швидкість обчислень із явно втратою точності. Але чи буде це універсальним сценарієм, який виправдовує таке часте використання цього типу? Не думаю.
Джерело: habr.com