Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.5, 10.2.7, 10.2.9, 10.3.1
-
None
-
Official mariadb docker versions
-
10.2.11
Description
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.
Attachments
Issue Links
- duplicates
-
MDEV-13390 Identity server Db Select Statement order by issue.
- Closed
-
MDEV-13704 Nested query does not give same result as subquery and an outer query
- Closed
- is duplicated by
-
MDEV-14071 Wildly wrong result from subquery in WHERE clause
- Closed
- relates to
-
MDEV-13390 Identity server Db Select Statement order by issue.
- Closed