MySQL и MariaDB развелись достаточно давно, и сейчас их следует уже воспринимать как две разные, хоть и высоко-совместимые СУБД. Если этого не учитывать, можно наткнуться на проблему. Потому что даже во вроде бы простой ситуации с очевидным решением можно наткнуться на различие. Одно такое различие попалось мне сегодня.

На заре становления MySQL старался быть максимально user-friendly, вплоть до lamer-friendly, и позволял то, что строгие диалекты отвергали. В частности, MySQL не был против неполной группировки, когда поле выходного набора не входило в выражение группировки, но и не было аргументом агрегатной функции. Разрешение такой фичи регулировалось флагом ONLY_FULL_GROUP_BY в системной переменной Server SQL Mode - @@sql_mode. Только в 8-й версии этот флаг стал включаться в значение переменной по умолчанию.

Также в 8-й версии были реализованы и оконные функции. Особенностью их является то, что это не агрегатные, а скалярные функции, и выполняются они уже после группировки на полях выходного набора, а не на исходных данных. Соответственно если использовать в выходном наборе оконную функцию от аргумента, не входящего в выражение группировки, или при неявной группировке, при ONLY_FULL_GROUP_BY получим ошибку.

CREATE TABLE test (id INT, x INT);
INSERT INTO test VALUES
(1,1), (1,2), (2,3), (2,4);

Соответственно запрос

SELECT id,
       SUM(x) sum_x,
       SUM(x) OVER (PARTITION BY id) wsum_x
FROM test
GROUP BY id;

будет выполнен при сброшенном флаге и приведёт к ошибке 1055 при установленном.

А вот в MariaDB, по крайней мере для версий 10.3 и старше, как выяснилось, в случае такого комбинирования ошибка - не возникает! Более того, наличие в Server SQL Mode флага ONLY_FULL_GROUP_BY - просто игнорируется!

То ли бага, то ли фича, но логического объяснения такой "особенности" я не нахожу. Придётся просто помнить.

Очередной баг в MySQL.

Нашёл ещё один баг в MySQL. Поскольку Оракл все российские регистрации позакрывал - напишу хоть сюда, что ли...

Баг найден в запросе LOAD XML INFILE. Суть бага - если выполняется загрузка многоуровневого (nested) XML, то после обработки атрибутов нижнего уровня расположенные ниже по тексту атрибуты верхнего уровня не обрабатываются.

Например, имеется такой XML:

<data>
<item col1="1">
  <col2>2</col2>
  <nested col3="3">x</nested>
  <col4>4</col4>
</item>
<item col1="11">
  <col2>22</col2>
  <col4>44</col4>
  <nested col3="33">xx</nested>
</item>
</data>

Выполняем его импорт в таблицу

CREATE TABLE data (
    col1 INT,
    col2 INT,
    col3 INT,
    col4 INT,
	nested VARCHAR(4)
    );

запросом

LOAD XML INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/test.xml' 
INTO TABLE data
ROWS IDENTIFIED BY '<nested>';

и видим, что для первой записи, где атрибут col4 расположен после вложенного атрибута nested, импорт col4 не выполняется. А во второй записи всё нормально.

mysql> SELECT * FROM data;
+------+------+------+------+--------+
| col1 | col2 | col3 | col4 | nested |
+------+------+------+------+--------+
|    1 |    2 |    3 | NULL | x      |
|   11 |   22 |   33 |   44 | xx     |
+------+------+------+------+--------+
2 rows in set (0.00 sec)

В общем, если работаете с импортом XML - будьте осторожны. И лучше импортируйте каждый уровень в отдельную таблицу.

Сегодня получилось узнать "что-нибудь новенького"©. А именно - узнал, когда именно проверяется ссылочная целостность при ссылке на себя.

Создадим таблицу:

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  FOREIGN KEY (id) REFERENCES test (id)
);

Таблица ссылается сама на себя. Более того, поле ссылается само на себя. Практического смысла - ноль, но это позволяет узнать тонкости. Тип данных SERIAL выбран потому, что он создаёт дополнительный вторичный индекс, необходимый для создания внешнего ключа. Для других типов данных индекс надо создавать явно, причём до создания внешнего ключа.

Итак, получится ли вставить в такую таблицу? При вставке сервер должен вставить запись, обновить вторичный индекс, проконтролировать ссылочную целостность. Вопрос в том, в каком порядке он это сделает.

Пробуем.

INSERT INTO test VALUES (1);
SELECT * FROM test;

И видим, что запись успешно вставилась. Это означает, что контроль ссылочной целостности выполняется уже после того, как запись вставлена в таблицу и обновлён индекс. Если бы любой из этих пунктов выполнялся после контроля ссылочной целостности, то контроль бы завершился ошибкой.

Всё это можно увидеть, например, на этом fiddle.

Зачем это может понадобиться? не знаю...

Опечатка в MySQL 8.1 Reference Manual

Читая документацию по процессу апгрейда до версии 8.1, нашёл опечатку.

На странице 2.10.5 Preparing Your Installation for Upgrade имеется пункт 5 с описанием проверки на то, что отсутствуют констрейнты с именами более 64 символов. Приведён текст запроса, который это проверяет. Однако в нём по ошибке вместо таблицы INNODB_FOREIGN написана несуществующая таблица INNODB_SYS_FOREIGN.

Понятно, что опечатка достаточно очевидна, и поправить не проблема. Исправленный текст запроса выглядит так:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
  (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
               INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
   FROM INFORMATION_SCHEMA.INNODB_FOREIGN
   WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

PS. Багтрак недоступен (учётные записи россиян заблокированы, а от анонимусов репорты не принимаются), напишу хоть здесь, чтобы не потерялось (и так уже с десяток багов протерялся/забылся).

MySQL тоже может выдать невменяемое сообщение об ошибке

Вообще MySQL славен тем, что если в SQL-коде встретилась синтаксическая ошибка, он возвращает сообщение об ошибке, которое точно описывает суть проблемы и точно указывает место в коде, где обнаружена ошибка. Но и на старуху бывает проруха..

Неожиданно в достаточно простом коде сообщение об ошибке совершенно не соответствует проблеме:

CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
SELECT * FROM t1 FULL JOIN t2 ON t1.id = t2.id;

Вопреки ожидаемому сообщению о проблеме с FULL JOIN (MySQL в принципе не поддерживает этот тип связывания) сервер неожиданно выдаёт

ERROR 1054 (42S22): Unknown column 't1.id' in 'on clause'

Для опытного программиста выявить источник проблемы не составит труда - да он и не напишет такое. А вот начинающий, или имеющий опыт в другой DBMS, программист может надолго завязнуть в разбирательстве.

Понятно, что эта проблема связана с парсером. FULL является в MySQL ключевым (но не зарезервированным) словом, и используется в некоторых административных запросах (например, в некоторых SHOW).

Увы, исправить это нельзя. Можно только запомнить.