Несколько интересных особенностей MySQL
В не очень далеком прошлом мне пришлось покопаться немного в исходном коде MySQL, и разобраться в некоторых аспектах его работы. В ходе работы лопаткой, и эксперимнтов, я наткнулся на несколько очень интересных особенностей, часть из которых просто забавна, а в случае некоторых бывает очень интересно понять, чем руководствовался программист, который принимал решение сделать именно так.Начнем с такого интересного типа, как ENUM.
mysql> CREATE TABLE enums (a ENUM ('c', 'a', 'b'), b INT, KEY (a)); Query OK, 0 rows affected (0.36 sec)
mysql> INSERT INTO enums VALUES ('a', 1), ('b', 1), ('c', 1); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 Итак, у нас есть таблица, в ней есть два столбца. У первого, a, тип ENUM, у второго, b, INT. В таблице три строки, у всех трех значение b равно 1. Интересно, чему равны минимальный и максимальный элементы в столбце a?
mysql> SELECT MIN (a), MAX (a) FROM enums; ±-------±-------+ | MIN (a) | MAX (a) | ±-------±-------+ | c | b | ±-------±-------+ 1 row in set (0.00 sec) Кажется странным, было бы разумно, если бы самым маленьким был 'a', а самым большим — 'c'.А что если выбрать минимум и максимум только среди тех строк, где b = 1? То есть, среди всех строк?
mysql> SELECT MIN (a), MAX (a) FROM enums WHERE b = 1; ±-------±-------+ | MIN (a) | MAX (a) | ±-------±-------+ | a | c | ±-------±-------+ 1 row in set (0.00 sec) Вот так мы заставили MySQL поменять свое мнение о том, как сравнивать поля в ENUM, просто добавив предикат.Разгадка такого поведения заключается в том, что в первом случае MySQL использует индекс, а во втором нет. Это, конечно, не объясняет, почему MySQL сравнивает ENUMы по разному для сортировки в индексе, и при обычном сравнении.
Второй пример проще и лаконичнее:
mysql> (SELECT * FROM moo LIMIT 1) LIMIT 2; ±-----+ | a | ±-----+ | 1 | | 2 | ±-----+ 2 rows in set (0.00 sec) Когда я показал этот запрос своему коллеге, который занимается разработкой парсера SQL, его вопрос был не «почему этот запрос возвращает две строки», а «как надо написать SQL парсер так, чтобы такой запрос был валидным, без того, чтобы написать правило, специально разрешающее такой запрос».
Интересно, что далеко не любой SELECT в скобках сработает, в частности, UNION в скобках — это синтаксическая ошибка:
mysql> (SELECT * FROM moo UNION ALL SELECT * FROM hru) LIMIT 2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ALL SELECT * FROM hru) LIMIT 2' at line 1 Еще несколько интересных примеров под катом
Вообще, с UNION и LIMIT далеко за примером странного поведения ходить не надо:
mysql> → SELECT 1 FROM moo LIMIT 1 → UNION ALL → SELECT 1 FROM hru LIMIT 1; ±--+ | 1 | ±--+ | 1 | ±--+ 1 row in set (0.00 sec) Внезапно, вернулась только одна строка, хотя обе таблицы не пусты. Потому что второй LIMIT принадлежит всему запросу, а не только правой части UNION.
Тут надо рассказать о такой вещи, как shift-reduce conflict. В современных базах данных с открытым кодом парсер очень часто написан на bison. Такой парсер является так называемым L1-парсером, что значит, что парсер должен понять предназначение очередного токена, посмотрев не далее чем на один токен вперед. Например, в запросе выше смотря на слово LIMIT парсер не может понять, принадлежит этот LIMIT к второму запросу, или ко всему UNION. Когда правила написаны так, что возможны ситуации, при которых понять назначение токена посмотрев только на следующий токен нельзя, это называется shift-reduce conflict. В этом случае парсер будет выбирать решение базируясь на определенном наборе правил. Это очень плохо, потому что это приводит к тому, что вполне нормальные запросы приводят к ошибкам. Что, если я хочу в предыдущем запросе сделать LIMIT и второму SELECT, и UNION?
mysql> SELECT 1 FROM moo → UNION ALL → SELECT 1 FROM hru LIMIT 1 → LIMIT 2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 2' at line 4 Так сделать нельзя, из-за shift-reduce конфликта. Смотря на первый LIMIT парсер еще не знает, что впереди будет второй, и ошибочно полагает, что первый лимит относится ко всему запросу.В PostgreSQL в парсере shift-reduce conflicts нет совсем. Конкретно эта ситуация там разрешена за счет того, что у UNION не может быть LIMIT.В MySQL таких конфликтов больше чем 160. Это поражает воображение, потому что это значит, что есть 160 мест, где парсер может не правильно понять, что от него хотят.
Хороший пример такого конфликта — это соединения. Как известно, в MySQL поддерживаются CROSS JOINs, у которых нет предиката, и INNER JOINs, у которых предикат есть. Вообще говоря, CROSS JOIN и INNER JOIN — это разные вещи, но в MySQL это синонимы. То есть у INNER JOIN может не быть предиката, а у CROSS JOIN он может быть. В частности, это приводит к интересной ошибке:
mysql> SELECT * FROM → moo → INNER JOIN → hru → INNER JOIN → baa → ON hru.a = baa.a → ON moo.a = hru.a →; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON moo.a = hru.a' at line 8 В момент, когда парсер видит первое ON, он еще не знает, что впереди его ждет второе, и сталкивается с выбором: либо это ON для hru и baa, либо hru и baa соединяются без предиката, а текущий ON — это ON для moo и результата соединения hru и baa. Парсер ошибочно выбирает второе, что приводит к совершенно не нужной в этой ситуации ошибке. Если INNER JOIN заменить на LEFT JOIN, для которого варианта без предиката не сущестует, то запрос выполнится:
mysql> SELECT * FROM → moo → LEFT JOIN → hru → LEFT JOIN → baa → ON hru.a = baa.a → ON moo.a = hru.a →; ±-----±-----±-----±-----+ | a | a | b | a | ±-----±-----±-----±-----+ | 1×1 | 1×1 | | 2×2 | 2×2 | ±-----±-----±-----±-----+ 2 rows in set (0.00 sec) Тут самое интересное, это то, что в Bison надо руками указать прямо в коде количество shift-reduce conflicts, иначе код не скомпилируется. То есть в какой-то момент времени один из программистов в MySQL сделал CROSS JOIN и INNER JOIN синонимами, что уже само по себе не имеет смысла, после чего попытался собрать код, и он не собрался с ошибкой компиляции, предупреждающей, что парсер теперь не сможет распарсить определенные запросы. На что тот программист, вместо того, чтобы сделать все правильно, нашел константу, указывающую на количество ошибок в парсере, и увеличил ее.
Хотя если говорить о том, какие интересные решения иногда программисты в MySQL принимают, то лучше всего вспомнить вот эту историю: http://bugs.mysql.com/bug.php? id=27877В ней один из программистов сознательно сделал в collation по умолчанию для utf8 букву 's' равной символу 'ß'. Это очень иронично, потому что единственный язык, в котором это хотя бы отдаленно могло бы иметь смысл — это немецкий, но именно это изменение делает этот collation совершенно не применимым к немецкому языку, потому что теперь строки, которые совершенно не равны друг другу, становятся равны.Это изменение было не только бесполезным, оно еще и сделало процесс перехода с 5.0 на 5.1 для баз данных с utf8 строками на немецком очень болезненным, потому что уникальные индексы внезапно начали содержать повторяющиеся элементы.
Говоря о collations, я еще очень люблю вот такой пример:
Пусть у нас есть таблица с тремя строками с разными collations:
CREATE TABLE strings ( swedish VARCHAR (100) COLLATE utf8_swedish_ci, spanish VARCHAR (100) COLLATE utf8_spanish_ci, bin VARCHAR (100) COLLATE utf8_bin ); Выполним такой запрос:
mysql> SELECT * FROM strings WHERE swedish > bin AND swedish < spanish; ERROR 1267 (HY000): Illegal mix of collations (utf8_swedish_ci,IMPLICIT) and (utf8_spanish_ci,IMPLICIT) for operation '<' MySQL разумно жалуется, что сравнивать swedish и spanish нельзя, потому что непонятно, как их сравнивать.Давайте напишем совершенно идентичный запрос:
mysql> SELECT * FROM strings WHERE swedish BETWEEN bin AND spanish; Empty set (0.00 sec) Внезапно, запрос стал валидным, хотя он по прежднему должен сравнивать swedish и spanish строку. А если я хочу наоборот?
mysql> SELECT * FROM strings WHERE swedish BETWEEN spanish AND bin; ERROR 1270 (HY000): Illegal mix of collations (utf8_swedish_ci, IMPLICIT), (utf8_spanish_ci, IMPLICIT), (utf8_bin, IMPLICIT) for operation 'between' А наоборот нельзя.Если покопаться в коде, то можно понять, что в MySQL BETWEEN реализован совершенно странным образом: если первый или второй парамерт имеют бинарный collation, то все строки будут сравниваться как бинарные, и collation будет проигнорирован. Но если бинарный collation у третьего аргумента, то такая же логика не применяется.
Говоря о том, как странно работают функции в MySQL, завершим эту статью самым красивым примером.
mysql> SELECT LEAST (9, 11); ±-------------+ | LEAST (9, 11) | ±-------------+ | 9 | ±-------------+ 1 row in set (0.00 sec) Тут никаких сюрпризов
mysql> SELECT LEAST (»9»,»11»); ±-----------------+ | LEAST (»9»,»11») | ±-----------------+ | 11 | ±-----------------+ 1 row in set (0.00 sec) Это тоже разумно, строка 11 меньше чем 9. А что будет, если 11 прибавить к 11?
mysql> SELECT LEAST (»9»,»11») + LEAST (»9»,»11»); ±------------------------------------+ | LEAST (»9»,»11») + LEAST (»9»,»11») | ±------------------------------------+ | 18 | ±------------------------------------+ 1 row in set (0.00 sec) Конечно, 18. Получается, функция возвращает разное значение в зависимости от контекста! А можно ли заставить один и тот же LEAST вернуть три разных значения в зависимости от контекста? Оказывается, да
mysql> SELECT LEAST (»9e1»,»110»); ±--------------------+ | LEAST (»9e1»,»110») | ±--------------------+ | 110 | ±--------------------+ 1 row in set (0.00 sec)
mysql> SELECT LEAST (»9e1»,»110») + 0; ±------------------------+ | LEAST (»9e1»,»110») + 0 | ±------------------------+ | 90 | ±------------------------+ 1 row in set (0.00 sec)
mysql> SELECT LEAST (»9e1»,»110») & -1; ±-------------------------+ | LEAST (»9e1»,»110») & -1 | ±-------------------------+ | 9 | ±-------------------------+ 1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS; ±--------±-----±-----------------------------------------+ | Level | Code | Message | ±--------±-----±-----------------------------------------+ | Warning | 1292 | Truncated incorrect INTEGER value: '9e1' | ±--------±-----±-----------------------------------------+ 1 row in set (0.00 sec) Хотя тут надо сказать, что в одном случае мы встретили предупреждение. Но у нас все же получилось заставить один и тот же оператор с одними и теми же аргументами вернуть три разных значения.
Чтобы сделать еще более удивительное открытие, надо познакомиться с функцией NULLIF. Эта функция принимает два аргумента, и возвращает NULL, если они равны, или значение первого аргумента, если они не равны. Отложив в сторону вопрос о том, зачем такая функция вообще существует, давайте посмотрим на результат следующих двух запросов:
mysql> SELECT NULLIF (LEAST (»9»,»11»),»11») + 0; ±-----------------------------------+ | NULLIF (LEAST (»9»,»11»),»11») + 0 | ±-----------------------------------+ | NULL | ±-----------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT NULLIF (LEAST (»9»,»11»),»12») + 0; ±-----------------------------------+ | NULLIF (LEAST (»9»,»11»),»12») + 0 | ±-----------------------------------+ | 9 | ±-----------------------------------+ 1 row in set (0.00 sec) В первом случае мы получили NULL, что говорит о том, что LEAST действительно равен строке »11». Во втором случае в таком же запросе, с такими же типами аргументов, но с другой константой в NULLIF мы получили значение 9! То есть при совершенно одинаковых типах параметров в первом случае LEAST вернул »11», а во втором — 9.Но можно сделать еще лучше:
mysql> SELECT NULLIF (LEAST (»9»,»11»),»9») + 0; ±----------------------------------+ | NULLIF (LEAST (»9»,»11»),»9») + 0 | ±----------------------------------+ | 9 | ±----------------------------------+ 1 row in set (0.00 sec) В этом запросе LEAST вернул что-то отличное от строки »9» (иначе бы NULLIF вернул NULL), однако он в тоже самое время вернул строку »9»! Если посмотреть в код, то это действительно то, что происходит. LEAST выполняется дважды, первый раз сравнивая параметры, как строки, а второй раз — как целые числа.
