Unrecognized MySQL bug: inability to add the first record to the composite VIEW

Hi, Habr!

I used to do my job in good faith and before writing this post I paranoidly checked several times how noticeable I was really a bug (and not the consequences of a sleepless night in front of a computer) , and also tried to find something similar on the Internet. In vain. Verloren. In vain.

So, if you are interested, welcome under the cat to see a simple architectural element on which adding the first record to the composite VIEW incorrectly works.

What the author smoked

First, briefly about why such an architectural solution was needed.
Without disclosing the details (confidentiality agreement, all matters :)), I would say that in working on the current project I need to implement the following relationships for the three classes of objects ( a , b , c ):
c to a - ∞ to 1,
c to b - ∞ to 0..1.
Thus, each object c is related to one object a , and may also be related to one object b or not related to objects b at all.
')

Exercise bike for freelancer

This database fragment was designed as follows:
+ a table listing all objects of class a (for simplicity, let their only parameter except the ID - name);
+ a table listing all objects of class b (the same parsley);
+ a table listing all objects of class c (except for the ID, it has the following parameters: name, ID of an object of class a (must!), ID of an object of class b (optional));
+ a view containing all objects of class c with the names of the associated objects of classes a and b (for security reasons (you can issue rights to VIEW without affecting the table itself), to transfer part of the logic of verifying the integrity of data from php to MySQL, and also to do not drag in php-code JOINs) with WITH CASCADED CHECK OPTION.

Monsieur knows a lot about kids

In order to ensure the variability of the representation, I had to do only INNER JOINs (LEFT OUTER JOIN prohibits the variability of the representation), but on the other hand it was also necessary to display in the view even those objects of class c that are not related to objects of class b .

To do this, I applied the following trick: let the ID of an associated object of class b can also take a zero value ('0'), which means that there is no associated object of class b ; Let also the table of objects of class b contains a zero entry (with a zero id), corresponding to the absence of an object of class b (let's give it the name 'N / A').

And this trick in combination with WITH CASCADED CHECK OPTION gives an abnormal behavior of the INSERT operator applied to the representation of objects of class c .

How to teach the operator INSERT bad

I will give model queries to the database, which recreate the situation:

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) 

Everything is as it should be, isn't it?
And now we try to just insert the first record without the associated object b into the representation C.

 mysql> INSERT INTO `C`(`a_id`,`name`) VALUES('1','test_c'); ERROR 1369 (HY000): CHECK OPTION failed 'test.C' mysql> 

Discouraging? I do not know about you, but me - yes.
Okay. Let's try to figure it out.
We perform an absolutely identical query directly to table c , after which we display the contents of the view 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> 

Discouraging? I do not know about you, but me - very much.
I can not explain this behavior except by the word "bug."
Moreover, if we now bring the table c to its original form, the records will be added through the C representation "with a bang."

 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> 

Findings?

Nuff said. I think to learn how to write bug reports in the Community (Linux-community or MySQL-community is another question: I have not seen MySQL 5.6 yet: perhaps this bug is not there) if Habrovans approve this fruit of midnight zadrotstvo and midday graphomania. (I'll pop a little: this night I already received the first approved pull request on the githaba.)

Postscriptum

The above was originally related to MySQL version 5.1 (yes, yes, unfortunately, so far, work on the site with MySQL 5.1 is an unavoidable condition), but then I tried the same thing on my typewriter MySQL 5.5.35 (testing release from the official Debian repositories) and saw all the same discouraging results.