Привет!
Я привык выполнять свою работу добросовестно и перед написанием этого поста параноидально проверил несколько раз, насколько подмеченное мной является действительно багом (а не последствиями бессонной ночи перед компьютером), а также попытался найти что-либо похожее в интернетах. In vain. Verloren. Тщетно.
Итак, если интересно, добро пожаловать под кат, чтобы увидеть несложный архитектурный элемент, на котором некорректно срабатывает добавление первой записи в составной VIEW.
Что курил автор
Сначала вкратце о том, зачем подобное архитектурное решение понадобилось.
Не разглашая деталей (соглашение о неразглашении конфиденциальной информации, все дела :)), скажу, что в работе над нынешним проектом мне требуется для трёх классов объектов (a,b,c) реализовать следующие отношения:
c к a — ∞ к 1,
c к b — ∞ к 0..1.
Таким образом, каждый объект c имеет отношение к одному объекту a, а также может иметь отношение к одному объекту b или не иметь отношения к объектам b вовсе.
Велотренажёр для фрилансера
Данный фрагмент БД был спроектирован следующим образом:
+ таблица, перечисляющая все объекты класса a (для простоты пусть их единственный параметр кроме айдишника — название);
+ таблица, перечисляющая все объекты класса b (та же петрушка);
+ таблица, перечисляющая все объекты класса c (кроме айдишника имеет параметры: название, айдишник объекта класса a (must!), айдишник объекта класса b (необязательный));
+ представление, содержащее все объекты класса c с названиями связанных с ними объектов классов a и b (из соображений безопасности (можно выдавать права на VIEW, не затрагивая саму таблицу), для переноса части логики верификации целостности данных из php в MySQL, а также чтобы не таскать в php-коде JOIN-ы) с WITH CASCADED CHECK OPTION.
Месье знает толк в козлятах
Чтобы обеспечить изменяемость представления, я должен был обойтись исключительно INNER JOIN'ами (LEFT OUTER JOIN запрещает изменяемость представления), но с другой стороны необходимо было также отобразить в представлении даже те объекты класса c, которые не имеют отношения к объектам класса b.
Для этого я применил следующий трюк: пусть айдишник связанного объекта класса b может принимать также нулевое значение ('0'), что означает отсутствие связанного объекта класса b; пусть также таблица объектов класса b содержит нулевую запись (с нулевым айдишником), соответствующую отсутствию объекта класса b (дадим ему имя 'N/A').
И вот этот трюк в сочетании с WITH CASCADED CHECK OPTION даёт нештатное поведение оператора INSERT, применённого к представлению объектов класса c.
Как научить оператор INSERT плохому
Приведу модельные запросы к БД, которые воссоздают ситуацию:
CREATE TABLE `a`(`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(255) DEFAULT NULL) ENGINE='InnoDB' CHARSET='utf8' COLLATE='utf8_general_ci';
INSERT INTO `a`(`name`) VALUES('test_a');
CREATE TABLE `b`(`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(255) DEFAULT NULL) ENGINE='InnoDB' CHARSET='utf8' COLLATE='utf8_general_ci';
SET SESSION `SQL_MODE`='NO_AUTO_VALUE_ON_ZERO';
INSERT INTO `b`(`id`,`name`) VALUES('0','N/A');
INSERT INTO `b`(`id`,`name`) VALUES('1','test_b');
SET SESSION `SQL_MODE`='';
CREATE TABLE `c`(`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(255) NOT NULL,`a` INT NOT NULL,`b` INT DEFAULT '0',FOREIGN KEY(`a`) REFERENCES `a`(`id`),FOREIGN KEY(`b`) REFERENCES `b`(`id`)) ENGINE='InnoDB' CHARSET='utf8' COLLATE='utf8_general_ci';
CREATE VIEW `C` AS SELECT `t1`.`id` `id`,`t1`.`name` `name`,`t2`.`name` `a`,`t3`.`name` `b`,`t1`.`a` `a_id`,`t1`.`b` `b_id` FROM `c` `t1` JOIN `a` `t2` ON(`t1`.`a`=`t2`.`id`) JOIN `b` `t3` ON(`t1`.`b`=`t3`.`id`) WITH CASCADED CHECK OPTION;
SELECT `id` FROM `a`;
SELECT `id` FROM `b`;
mysql> SELECT `id` FROM `a`;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.01 sec)
mysql> SELECT `id` FROM `b`;
+----+
| id |
+----+
| 0 |
| 1 |
+----+
2 rows in set (0.00 sec)
Всё как и должно быть, не так ли?
А теперь попробуем просто вставить первую запись без связанного объекта b в представление C.
mysql> INSERT INTO `C`(`a_id`,`name`) VALUES('1','test_c');
ERROR 1369 (HY000): CHECK OPTION failed 'test.C'
mysql>
Обескураживает? Не знаю, как Вас, но меня — да.
Ладно. Попробуем разобраться.
Осуществим абсолютно идентичный запрос напрямую к таблице c, после чего выведем на экран содержимое представления C.
mysql> INSERT INTO `c`(`a`,`name`) VALUES('1','test_c');
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM `C`;
+----+--------+--------+------+------+------+
| id | name | a | b | a_id | b_id |
+----+--------+--------+------+------+------+
| 1 | test_c | test_a | N/A | 1 | 0 |
+----+--------+--------+------+------+------+
1 row in set (0.00 sec)
mysql>
Обескураживает? Не знаю, как Вас, но меня — очень.
Я не могу объяснить подобное поведение иначе как словом «баг».
Тем более что, если теперь привести таблицу c к изначальному виду, записи будут добавляться через представление C «на ура».
mysql> DELETE FROM `c`; ALTER TABLE `c` AUTO_INCREMENT=1; INSERT INTO `C`(`a_id`,`name`) VALUES('1','test_c'); SELECT * FROM `C`;
Query OK, 1 row affected (0.05 sec)
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.00 sec)
+----+--------+--------+------+------+------+
| id | name | a | b | a_id | b_id |
+----+--------+--------+------+------+------+
| 1 | test_c | test_a | N/A | 1 | 0 |
+----+--------+--------+------+------+------+
1 row in set (0.01 sec)
mysql>
Выводы?
Nuff said. Я думаю научиться писать багрепорты в Сообщество (Linux-community или MySQL-community — ещё вопрос: я не видел ещё MySQL 5.6: возможно, там нет этого бага), если Хабровчане одобрят сей плод полуночного задротства и полуденной графомании. (Попиарюсь немного: этой ночью я уже получил первый одобренный pull request на гитхабе.)
Postscriptum
Сказанное выше изначально относилось к MySQL версии 5.1 (да-да, к сожалению, пока что работа на площадке с MySQL 5.1 — неизбежное условие), но затем я попробовал всё то же самое на своей печатной машинке с MySQL 5.5.35 (testing релиз из официальных репозиториев Debian) и увидел всё те же обескураживающие результаты.
Автор: EnderTeszla
