Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
5.5.27
-
None
Description
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
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-