|
https://bugs.launchpad.net/maria/+bug/1050806
Also reproducible on MySQL 5.5.27 and filed as http://bugs.mysql.com/bug.php?id=66845
The following query
SELECT * FROM (
|
SELECT node_uid, date, mirror_date, @result := 0 AS result
|
FROM stock
|
WHERE date < '2012-12-12 12:12:12'
|
AND node_uid in (2085, 2084)
|
ORDER BY mirror_date ASC
|
) AS calculated_result;
|
on the test data produces 3 rows on 5.5.27 and 2 rows on 5.5.25. The correct result is 2 rows, which is obvious because the third row has node_uid which is neither 2084 nor 2085.
Reproducible with the default optimizer_switch as well as with all OFF values.
Reproducible with MyISAM, Aria, InnoDB.
Reproducible on MariaDB 5.5.27, MySQL 5.5.27, current maria/5.5 revno 3533.
Could not reproduce on MariaDB 5.5.25, MariaDB 5.3 revno 3576, MySQL 5.6.6.
EXPLAIN (with all OFF values in optimizer switch):
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
|
2 DERIVED stock range date date 9 NULL 2 100.00Using where; Using filesort
|
Level Code Message
|
Note 1003 select `calculated_result`.`node_uid` AS `node_uid`,`calculated_result`.`date` AS `date`,`calculated_result`.`mirror_date` AS `mirror_date`,`calculated_result`.`result` AS `result` from (select `test`.`stock`.`node_uid` AS `node_uid`,`test`.`stock`.`date` AS `date`,`test`.`stock`.`mirror_date` AS `mirror_date`,(@result:=0) AS `result` from `test`.`stock` where ((`test`.`stock`.`date` < '2012-12-12 12:12:12') and (`test`.`stock`.`node_uid` in (2085,2084))) order by `test`.`stock`.`mirror_date`) `calculated_result`
|
Test case:
DROP TABLE IF EXISTS `stock`;
|
|
CREATE TABLE `stock` (
|
`node_uid` bigint(20) unsigned DEFAULT NULL,
|
`date` datetime DEFAULT NULL,
|
`mirror_date` datetime DEFAULT NULL,
|
KEY `date` (`date`)
|
) ENGINE=MyISAM;
|
|
INSERT INTO `stock` VALUES (2085,'2012-01-01 00:00:00','2013-01-01 00:00:00');
|
INSERT INTO `stock` VALUES (2084,'2012-02-01 00:00:00','2013-01-01 00:00:00');
|
INSERT INTO `stock` VALUES (2088,'2012-03-01 00:00:00','2013-01-01 00:00:00');
|
|
SELECT * FROM (
|
SELECT node_uid, date, mirror_date, @result := 0 AS result
|
FROM stock
|
WHERE date < '2012-12-12 12:12:12'
|
AND node_uid in (2085, 2084)
|
ORDER BY mirror_date ASC
|
) AS calculated_result;
|
|
Expected result:
node_uid date mirror_date result
|
2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0
|
2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0
|
Actual result:
node_uid date mirror_date result
|
2085 2012-01-01 00:00:00 2013-01-01 00:00:00 0
|
2084 2012-02-01 00:00:00 2013-01-01 00:00:00 0
|
2088 2012-03-01 00:00:00 2013-01-01 00:00:00 0
|
|