После публикации статьи об особенностях типизации в 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 456 789 к типу REAL:

********* ЗАПРОС *********
SELECT 123456789::REAL::INT;
**************************
   int4   
-----------
123456792
(1 строка)

А оно получилось больше на 3! Все, база окончательно разучилась считать! Или мы чего-то недопонимаем? Давайте разбираться.

Для начала вспомним матчасть. Как известно, любое десятичное число можно разложить по степеням десяти. Так, число 123.456 будет равно 1*10² + 2*10¹ + 3*10⁰ + 4*10^-1 + 5*10^-2 + 6*10^-3. Но компьютер оперирует числами в двоичном виде, следовательно представлять их приходится в виде разложения по степеням двойки. Поэтому число 5.625 в двоичном виде представляется как 101.101 и будет равно 1*2² + 0*2¹ + 1*2⁰ + 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 бита, будет 2²⁴-1 = 16 777 215. Поэтому наше число 123 456 789 округляется до ближайшего представимого 123 456 792. Сменив тип на DOUBLE PRECISION, мы уже не увидим такого сценария:

********* ЗАПРОС *********
SELECT 123456789::DOUBLE PRECISION::INT;
**************************
   int4   
-----------
123456789
(1 строка)

Вот и все. Оказывается, никаких чудес. Но все описанное – хороший повод призадуматься на предмет того, насколько вам действительно нужен тип REAL. Пожалуй, самый большой плюс его использования – быстрота вычислений с заведомо имеющейся потерей точности. Но будет ли это универсальным сценарием, оправдывающим столь частое использование этого типа? Не думаю.

Unreal Features of Real Types, или Будьте осторожны с REAL