На написание этого поста меня вдохновил мой друг Грег Янгблад, который показал мне на прошлой неделе одну интересную загадку в 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)?
Автор: HotWaterMusic
