[PostgreSQL, SQL, Администрирование баз данных] Unreal Features of Real Types, или Будьте осторожны с REAL
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
После публикации статьи об особенностях типизации в 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*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
===========
Похожие новости:
- [SQL, Программирование] OLAP-отчеты. Построение для любой базы на SQL
- [Разработка под iOS, AR и VR] Unreal выпустила приложение для iPhone, которое захватывает мимику лица для Unreal Engine
- [*nix, Python, Разработка на Raspberry Pi, Разработка под Linux] Разработка zond-а для замера скорости интернета
- Для PostgreSQL подготовлено дополнение AGE для хранения данных в форме графа
- [Администрирование баз данных, Микросервисы, Программирование] Использование Camunda для удобной оркестровки на основе REST и Workflow Engine (без Java)
- [Big Data, PostgreSQL, Администрирование баз данных, Хранение данных] Видео @Databases Meetup: Percona, Postgres Pro, Tarantool и MCS
- [Машинное обучение, MySQL, Python] Machine Learning CPython library 'VKF'
- [Python, SQL, Аналитика мобильных приложений, Машинное обучение] Бесплатная Академия Аналитиков Авито для начинающих
- [Машинное обучение, MySQL, Python] CPython библиотека «ВКФ» для машинного обучения
- [MySQL, Oracle] 10 доводов в пользу MySQL
Теги для поиска: #_postgresql, #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_postgresql, #_real, #_double_precision, #_float4, #_float8, #_blog_kompanii_tenzor (
Блог компании Тензор
), #_postgresql, #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 22:21
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
|
После публикации статьи об особенностях типизации в 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*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 =========== Похожие новости:
Блог компании Тензор ), #_postgresql, #_sql, #_administrirovanie_baz_dannyh ( Администрирование баз данных ) |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 22:21
Часовой пояс: UTC + 5