Когда MIN(DATE) != MIN(DATE)?

На написание этого поста меня вдохновил мой друг Грег Янгблад, который показал мне на прошлой неделе одну интересную загадку в MySQL.

У него был запущен Percona Server 5.5.21 с примерно следующей структурой таблиц:

CREATE TABLE foo (  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  uid INT UNSIGNED NOT NULL,  update_time DATETIME NOT NULL,  ....  INDEX `uid` (uid, update_time),  INDEX `bar` (some_other_columns)  .... ) ENGINE=InnoDB; 

Когда он выполнил следующий запрос:

SELECT MIN(update_time) FROM foo WHERE update_time IS NOT NULL AND update_time <> '0000-00-00 00:00:00'; 

Результат вернул 2012-06-22 10:28:16. Однако, когда он выполнил слегка другой запрос:

SELECT MIN(t.update_time) FROM (SELECT uid, MIN(update_time) AS "update_time" FROM foo WHERE update_time IS NOT NULL AND update_time <> '0000-00-00 00:00:00' GROUP BY 1) t; 

Ответ, который вернулся, был совсем другим: 2011-08-22 11:27:27. Это правильный ответ, дата не из 2012 года. Мы придумали несколько идей как можно вычислить причину, и одним из предложений было заставить MySQL использовать другой индекс. Представьте наше удивление когда мы пробовали FORCE INDEX на bar или IGNORE INDEX(kid) и получали совершенно другой ответ: 2012-06-21 20:36:35.
Итак, когда мы попробовали «очевидный» вариант:

SELECT update_time FROM foo ORDER BY update_time ORDER BY update_time LIMIT 3; 

И результат?

0024-06-22 01:34:25 2011-08-22 11:27:27 2011-08-23 11:31:40 

Ага! Кривые данные! Мы предположили, что функция MIN() производила какую-то конверсию в unix_timestamp/integer или ctime; и действительно, выполнение SELECT MIN (UNIX_TIMESTAMP (update_time)) …. возвращает ноль, в то время как использование CAST() или CONVERT() на поле update_time сначала (так, что оно явно будет рассматриваться как строка) возвращает результат 0024-06-22.

Документация к MySQL 5.5 заявляет, что поддерживаемый диапазон дат в типе DATETIME составляет от 1000-01-01 00:00:00 до 9999-12-31 23:59:59. В продолжении говорится, что «Для DATE и DATETIME, „поддерживаемый“ означает то, что значения меньше могут работать, но гарантии нет». Выходило, что приложение занесло в базу нечетный datetime, и благодаря тому, что значение прошло проверку формата и попало в базу данных. Упс…

Что же мы из этого поняли?

* К сожалению, задание sql_mode здесь вам не поможет. Хотя 0024-06-21 в техническом плане находится вне поддерживаемого диапазона для DATE/DATETIME, ни TRADITIONAL, STRICT_ALL_TABLES, или STRICT_TRANS_TABLES не выкидывает даже warning. Баг это или фича? Выбор за вами.
* Когда документация говорит что “нет гарантии, что это будет работать”, лучше не рискуйте.
* Даже если кусочек данных подходит под ожидаемый формат, это не значит, что он является правильным значением. Так что, возможно, вот самый важный урок из всего этого: Всегда, всегда проверяйте диапазон введенных данных! Одной проверки формата недостаточно.
И финальный момент — у меня получилось повторить ту же ситуацию с Percona Server 5.5.25a, но в моем случае, это поведение было еще страннее. Я создал таблицу со схожей структурой:

CREATE TABLE `foo` (   `i` int(11) NOT NULL AUTO_INCREMENT,   `update_date` datetime NOT NULL,   PRIMARY KEY (`i`),   KEY `i` (`i`,`update_date`) ) ENGINE=InnoDB 

и затем вставил несколько фиктивных строк с датой 0024-06-21. Затем я сбросил в эту таблицу несколько тысяч случайных datetime с помощью простого скрипта Perl. На первый взгляд, все выглядело так, словно у меня не получится повторить свою ситуацию — выполнение “SELECT MIN(update_time) FROM foo” для моей таблицы давало правильный ответ (1058-11-06 00:00:00), который был минимальной датой с большим значением, чем минимально поддерживаемое значение 1000-01-01 00:00:00. Но когда я решил переместить фиктивные строки изменением их PK, то случилось вот это:

До перемещения: (root@localhost) [test]> select * from foo order by update_date limit 5; +-------+---------------------+                                          | i     | update_date         |                                          +-------+---------------------+                                          |     1 | 0024-06-21 10:35:55 |                                          |     2 | 0024-06-21 10:35:55 |                                          |     3 | 0024-06-21 10:35:55 |                                          |     4 | 0024-06-21 10:35:55 |                                          |  1159 | 1058-11-06 00:00:00 |  +-------+---------------------+  (root@localhost) [test]> select min(update_date) from foo; +---------------------+                                   | min(update_date)    |                                   +---------------------+                                   | 1058-11-06 00:00:00 |                                   +---------------------+  (root@localhost) [test]> update foo SET i=i+100000 where i<5; Query OK, 4 rows affected (0.00 sec)                         Rows matched: 4  Changed: 4  Warnings: 0  

После перемещения: (root@localhost) [test]> select min(update_date) from foo; +---------------------+                                   | min(update_date)    |                                   +---------------------+                                   | 2024-06-21 10:35:55 |                                   +---------------------+  (root@localhost) [test]> select update_date FROM foo order by update_date LIMIT 5; +---------------------+ | update_date         | +---------------------+ | 0024-06-21 10:35:55 | | 0024-06-21 10:35:55 | | 0024-06-21 10:35:55 | | 0024-06-21 10:35:55 | | 1058-11-06 00:00:00 | +---------------------+ 

Очень странно. С одной стороны, выглядит так, словно MySQL использует фильтрацию дат для дат из двух цифр (это объясняет 2024-06-21), с другой стороны, это никак не повлияло на изменение данных DATETIME, и сейчас определенно возвращает неправильный ответ.

Вывод: всегда проверяйте вводимые данные!

Оригинальная статья: Ernie Souhrada. When is MIN(DATE) != MIN(DATE)?