CREATE TABLE `books` (
`id` int(16) NOT NULL AUTO_INCREMENT,
`library_id` int(16) NOT NULL DEFAULT 0,
`wings_id` int(12) NOT NULL DEFAULT 0,
`scheduled_for_removal` int(1) DEFAULT 0,
PRIMARY KEY (`id`),
KEY `library_idx` (`library_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `books` VALUES (32625,8663,707,0),(32624,8663,505,1);
CREATE TABLE `wings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`department_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `wings` VALUES (505,11745),(707,11768);
SELECT wings.id as wing_id, wings.department_id FROM wings WHERE wings.id IN ( SELECT books.wings_id FROM books WHERE books.library_id = 8663 AND books.scheduled_for_removal=0 ) ORDER BY wings.id;
The result should be (707, 11768), but it results in (505, 11745).
Changing the engine for the books table to InnoDB, resolves the problem. Removing the library_idx on books also resolves the problem.
I also tested on mysql 5.5.57, 5.6.37, 5.7.19, and mariadb 5.5.57, 10.0.32 and 10.1.28, where the problem does not occur.
I've added `script.sql` containing the sql from above, and a wrapper script `reproduce.sh` to set up a docker environment for testing.