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
The problem is also reproducible on upstream 5.5.27. Have you already filed it at bugs.mysql.com, or are you planning to do so?
(If not, I can do it, I just don't want to produce duplicates).
Hi,
Indeed, there is an extra row in the result set.
Here is a simplified 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'); ,'2013- 01-01 00:00:00'); ,'2013- 01-01 00:00:00');
INSERT INTO `stock` VALUES (2084,'2012-02-01 00:00:00'
INSERT INTO `stock` VALUES (2088,'2012-03-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
The problem is also reproducible on upstream 5.5.27. Have you already filed it at bugs.mysql.com, or are you planning to do so?
(If not, I can do it, I just don't want to produce duplicates).