Загадочная ситуация с TIME в MySQL

Прим. перев.: Этот детальный анализ одной, казалось бы, не очень значительной детали в реализации внутри MySQL вызвал закономерные дискуссии о правильности в подходах к разработке известного Open Source-проекта в целом. О том, что же, собственно, выяснил португальский инженер, он повествует в формате, приближенном к детективу…

Многие в 2020 году стали жертвой странного феномена восприятия времени, но некоторые системы управления базами данных манипулируют временем гораздо дольше. Впервые я обратил на это внимание, когда мой друг в одном из своих проектов (Accord — популярный бот для Discord) столкнулся со следующим исключением от коннектора MySQL при использовании с EF Core:

MySqlException: Incorrect TIME value: '960:00:00.000000'

Будучи не слишком сведущим в MySQL (т.к. предпочитаю PostgreSQL по причинам, которые скоро станут очевидными), я на секунду подумал, что неправильным здесь является число часов. Разумно предположить, что значения TIME ограничены 24 часами или что для значений, охватывающих нескольких дней, требуется другой синтаксис — например, 40:00:00:00 будет представлять 40 дней. Но действительность оказалась куда сложнее и запутаннее.

Следующим очевидным шагом стала проверка документации MySQL. Она гласила:

MySQL получает и отображает значения TIME в формате 'hh:mm:ss' (или в формате 'hhh:mm:ss' для больших часовых значений).

Пока все нормально: наше проблемное значение TIME вполне вписывается в этот формат, хотя тот факт, что hh и hhh указаны явным образом, вызывает подозрения (как насчет значений часов, превышающих 999?). Следующее предложение в документации все частично объясняет, попутно стимулируя кучу вопросов типа «Что за...?»:

Значения TIME могут варьироваться от '-838:59:59' до '838:59:59'.

Ну окееей… Какой-то странный диапазон. Наверняка на то есть веская техническая причина. 839 часов — это 34,958(3) дня, и весь диапазон занимает ровно 6040798 секунд. Документация гласит следующее:

MySQL распознает значения TIME в нескольких форматах, некоторые из которых могут включать дробные секунды вплоть до 6 знаков после запятой (микросекунды).

Другими словами, весь интервал занимает 6 040 798 000 000 микросекунд. Опять же, какое-то странное число. Оно далеко от степени двойки (находится между 2⁴² и 2⁴³), так что MySQL, по всей видимости, использует какой-то уникальный внутренний формат представления. Но прежде чем заняться этим вопросом, позвольте мне отметить, насколько этот тип плох.

Это всё, что может предложить MySQL для измерения интервалов времени, при этом весь промежуток лишь чуточку превышает один месяц. Насколько велико это «чуточку»? Как видно, оно даже не кратно целому числу дней.

Хуже того, самый популярный провайдер MySQL в EF Core по умолчанию конвертирует .NET'овский TimeSpan в TIME, несмотря на то, что TimeSpan может содержать интервалы в десятки тысячелетий (в нем используются 64-битные целые числа, а допустимая точность равна 10^-8 с). Сравните это с парой с небольшим месяцев в TIME.

С этой проблемой сталкивались и другие люди, и обсуждение в соответствующем issue содержит отсылку на поведение SQL Server'а: «This mimics the behavior of SQL Server». Я проверил — действительно, тип time у SQL Server'а имеет диапазон от 00:00:00.0000000 до 23:59:59.9999999, что в целом гораздо разумнее, чем странный диапазон TIME.

Но давайте вернемся к MySQL. Какова причина столь необычного диапазона? В руководстве по устройству MySQL говорится, что в версии 5.6.4 тип TIME изменился и появилась поддержка долей секунд. Для целой части используются три байта. Если эти три байта целиком использовать для кодирования секунд, получается временной промежуток в 2330 с лишним часов — гораздо больше, чем имеющийся максимум в 838 часов (хотя даже он не слишком полезен при конвертации TimeSpan'а).

Это означает, что процесс, которым кодируется время в MySQL, расходует биты впустую — возможно, так сделано ради простоты использования (хотя не уверен, в каких именно обстоятельствах это актуально). Может, это имеет смысл в случае, если СУБД (и представление разработчиков о том, что пользователи будут с ней делать) заточена под работу со строками, и разработчики хотят ускорить представление hh:mm:ss.

Итак, смотрите:

1 бит — знак (1 = неотрицательный, 0 = отрицательный)
1 бит не используется (зарезервирован для будущих расширений)
10 битов — часы (0-838)
6 битов — минуты (0-59)
6 битов — секунды (0-59)
— 24 бита = 3 байта

Это всё объясняет, не так ли? Что ж, давайте посмотрим повнимательнее. 10 бит для часов… и диапазон от нуля до 838. Спешу напомнить, что 2¹⁰=1024, не 838. Интрига набирает обороты…

Конечно же, я не первый человек, который задался этим вопросом (ранее об этом уже спрашивали на StackOverflow). Всё вроде бы излагается в «принятом» там ответе, однако странный выбор 838 часов сначала объясняется «обратной совместимостью с приложениями, которые были написаны довольно давно», и только потом упоминается, что это как-то связано с совместимостью с MySQL 3 — к слову, Windows 98 тогда считалась новинкой, а Linux не исполнилось и 10 лет.

В MySQL 3 тип TIME тоже использовал 3 байта, только делал это совершенно иначе. Один из битов также резервировался для знака, но оставшиеся 23 бита соответствовали целым числам, получаемым следующим образом: часы × 10000 + минуты × 100 + секунды. Другими словами, два младших разряда содержали секунды, следующие два — минуты, а на оставшиеся приходились часы. 2*23 — это 83888608, то есть 838:86:08, поэтому максимальное допустимое значение времени в этом формате — 838:59:59.

Такой формат даже менее удобен, чем текущий, поскольку требует умножения и деления практически для любых операций со временем (за исключением форматирования строк и парсинга — что в очередной раз доказывает, что MySQL уделяет слишком большое внимание строчному IO и не особо заботится о наличии типов, которые были бы удобны для внутренних операций и протоколов, не основанных на строках).

Разработчики MySQL неоднократно могли исправить этот тип, или, по крайней мере, представить альтернативный, свободный от имеющегося ограничения. Со времен MySQL 3 и по сей день тип TIME менялся дважды, но каждый раз странный диапазон оставался неизменным — возможно, это делалось из соображений совместимости.

Я затрудняюсь представить ситуацию, при которой расширение диапазона значения для типа может нарушить совместимость с приложением: разве у типов в MySQL есть определенное поведение при переполнении? Какой нормальный программист будет полагаться на внутренние ограничения типов БД для проверки чего-либо в своем приложении? Если же такой человек найдется, с какой стати он вдруг решит перенести это нелепое ограничение в 838 часов в модель данных своего приложения безо всяких изменений? Честно говоря, я даже не хочу знать ответы на эти вопросы.

Несмотря на пару глобальных трансформаций за историю MySQL, тип TIME по-прежнему остается неудобным и ограниченным. И гвоздем программы здесь, по моему мнению, выступает неиспользуемый бит, «зарезервированный для будущих расширений». Я надеюсь, что в перспективе он будет указывать на старое, «унаследованное» (legacy) значение TIME, и к тому моменту в MySQL и/или MariaDB появится толковый временной тип, такой как INTERVAL в PostgreSQL, имеющий диапазон в ± 178000000 лет и микросекундную точность.

P.S. от переводчика

Читайте также в нашем блоге:

• «Применение оконных функций и CTE в MySQL 8.0 для реализации накопительного итога без хаков»;
• «Обновление MySQL (Percona Server) с 5.7 до 8.0»;
• «Больше разработчиков должны знать это о базах данных».