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

в 8:46, , рубрики: mysql, sql, хитрости

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

Источник


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js