Does MySQL return invalid data?

Question

Recently, there has been a problem in the Django community with testing MySQL (using MyISAM).

Here's the django ticket: http://code.djangoproject.com/ticket/14661

One of the core developers of Django came up with this test, and many of us were able to reproduce it. Anyone guess what we are doing here? Is this just a mistake in MySQL or am I missing something?

Here's the test code and queries:

DROP TABLE IF EXISTS `testapp_tag`; CREATE TABLE `testapp_tag` ( `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `name` varchar(10) NOT NULL, `parent_id` integer ); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t1", NULL); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t2", 1); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t3", 1); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t4", 3); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t5", 3); SELECT `testapp_tag`.`id`, `testapp_tag`.`name`, `testapp_tag`.`parent_id` FROM `testapp_tag` WHERE NOT ((`testapp_tag`.`id` IN (SELECT U0.`id` FROM `testapp_tag` U0 LEFT OUTER JOIN `testapp_tag` U1 ON (U0.`id` = U1.`parent_id`) WHERE U1.`id` IS NULL) AND `testapp_tag`.`id` IS NOT NULL)) ORDER BY `testapp_tag`.`name` ASC; SELECT `testapp_tag`.`id`, `testapp_tag`.`name`, `testapp_tag`.`parent_id` FROM `testapp_tag` WHERE NOT ((`testapp_tag`.`id` IN (SELECT U0.`id` FROM `testapp_tag` U0 LEFT OUTER JOIN `testapp_tag` U1 ON (U0.`id` = U1.`parent_id`) WHERE U1.`id` IS NULL) AND `testapp_tag`.`id` IS NOT NULL)) ORDER BY `testapp_tag`.`name` ASC; 

Here's the conclusion:

 mysql> SELECT `testapp_tag`.`id`, `testapp_tag`.`name`, `testapp_tag`.`parent_id` FROM `testapp_tag` WHERE NOT ((`testapp_tag` .`id` IN (SELECT U0.`id` FROM `testapp_tag` U0 LEFT OUTER JOIN `testapp_tag` U1 ON (U0.`id` = U1.`parent_id`) WHERE U1.`id` IS NULL) AND `testapp_tag`.`id` IS NOT NULL)) ORDER BY `testapp_tag`.`name` ASC; +----+------+-----------+ | id | name | parent_id | +----+------+-----------+ | 1 | t1 | NULL | | 3 | t3 | 1 | | 5 | t5 | 3 | +----+------+-----------+ 3 rows in set (0.00 sec) mysql> SELECT `testapp_tag`.`id`, `testapp_tag`.`name`, `testapp_tag`.`parent_id` FROM `testapp_tag` WHERE NOT ((`testapp_tag` .`id` IN (SELECT U0.`id` FROM `testapp_tag` U0 LEFT OUTER JOIN `testapp_tag` U1 ON (U0.`id` = U1.`parent_id`) WHERE U1.`id` IS NULL) AND `testapp_tag`.`id` IS NOT NULL)) ORDER BY `testapp_tag`.`name` ASC; +----+------+-----------+ | id | name | parent_id | +----+------+-----------+ | 1 | t1 | NULL | | 3 | t3 | 1 | +----+------+-----------+ 2 rows in set (0.01 sec) 

Answer 1

This form works reliably:

 SELECT T.`id`, T.`name`, T.`parent_id` FROM `testapp_tag` T WHERE NOT (T.`id` IN ( SELECT U0.`id` FROM `testapp_tag` U0 LEFT OUTER JOIN `testapp_tag` U1 ON (U0.`id` = U1.`parent_id`) WHERE U1.`id` IS NULL)) ORDER BY T.`name` ASC; 

The optional filter component NOT + IN + seems to issue MySQL. This is definitely a mistake.

The test in NOT () looks for 2 parts. If the first part is true, the second cannot be true, regardless of whether the field can be zero or not. This is a redundant sentence, which seems to be the cause of the error.

By accepting the response from the ScrumMeister response, I confirm that the error is due to some kind of caching against the last inserted identifier in AUTO_INCREMENT.

 DROP TABLE IF EXISTS `testapp_tag`; CREATE TABLE `testapp_tag` ( `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `name` varchar(10) NOT NULL, `parent_id` integer ); start transaction; INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t1", NULL); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t2", 1); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t3", 1); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t4", 3); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t5", 3); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t6", 3); INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t7", 3); commit; delete from testapp_tag where id = 6; ####### explain extended SELECT T.`id`, T.`name`, T.`parent_id` FROM `testapp_tag` T WHERE NOT (T.`id` IN ( SELECT U0.`id` FROM `testapp_tag` U0 LEFT OUTER JOIN `testapp_tag` U1 ON (U0.`id` = U1.`parent_id`) WHERE U1.`id` IS NULL) AND T.`id` IS NOT NULL) ORDER BY T.`name` ASC; show warnings; 

Produces this plan

 select `test`.`t`.`id` AS `id`,`test`.`t`.`name` AS `name`,`test`.`t`.`parent_id` AS `parent_id` from `test`.`testapp_tag` `T` where ((not(<in_optimizer>(`test`.`t`.`id`, <exists>(select 1 AS `Not_used` from `test`.`testapp_tag` `U0` left join `test`.`testapp_tag` `U1` on((`test`.`u1`.`parent_id` = `test`.`u0`.`id`)) where (isnull(`test`.`u1`.`id`) and (<cache>(`test`.`t`.`id`) = `test`.`u0`.`id`)))))) **or (`test`.`t`.`id` = 7)**) order by `test`.`t`.`name` 

If the insert stops at t6 and the deletion is also t6, the error is masked because the sentence added is equal to or (test.t.id = 6), which we have already deleted in the line marked #### ###

Answer 2

It seems very interesting and looks like an error in the MySql query optimizer.

If you run this instead of a simple choice:

 EXPLAIN EXTENDED SELECT `testapp_tag`.`id`, ....; SHOW WARNINGS; EXPLAIN EXTENDED SELECT `testapp_tag`.`id`, ...; SHOW WARNINGS; 

Then, comparing the output with EXPLAIN EXTENDED warnings, you can see that for the first time the optimizer adds to the selection:

 or (`test`.`testapp_tag`.`id` = 5) 

Also note that removing AND testapp_tag.id IS NOT NULL from WHERE , which does nothing, since the field is marked NOT NULL , seems to fix the problem.