Description:
COUNT() queries are returning inconsistent results when a LIMIT 1 clause is added.
The queries below should be equivalent, but return significantly different values:
SELECT COUNT( OUTR .`int_nokey` ) FROM C OUTR2 JOIN C OUTR ON OUTR2 .`time_key` < OUTR .`datetime_key` ORDER BY OUTR .`pk`
returns 240
while
SELECT COUNT( OUTR .`int_nokey` ) FROM C OUTR2 JOIN C OUTR ON OUTR2 .`time_key` < OUTR .`datetime_key` ORDER BY OUTR .`pk` LIMIT 1
returns 136
This is present in 5.0+
How to repeat:
/*!50400 SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off' */;
/*!50400 SET SESSION optimizer_use_mrr = 'disable' */;
/*!50400 SET SESSION engine_condition_pushdown = 0 */;
/*!50400 SET SESSION join_cache_level = 1 */;
# Begin test case for query 0
--disable_warnings
DROP TABLE IF EXISTS C;
--enable_warnings
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) NOT NULL,
`time_key` time NOT NULL,
`datetime_key` datetime NOT NULL,
PRIMARY KEY (`pk`),
KEY `time_key` (`time_key`),
KEY `datetime_key` (`datetime_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,9,'04:56:51','0000-00-00 00:00:00'),(2,0,'03:03:27','2002-02-09 07:38:13'),(3,8,'07:48:21','2001-05-03 12:08:14'),(4,3,'00:00:00','0000-00-00 00:00:00'),(5,7,'11:58:42','2009-07-28 03:43:30'),(6,0,'03:34:13','2009-08-04 00:00:00'),(7,1,'00:00:00','0000-00-00 00:00:00'),(8,9,'00:00:00','0000-00-00 00:00:00'),(9,0,'00:51:25','2005-08-02 17:16:54'),(10,9,'14:43:13','2002-12-21 00:00:00'),(11,0,'00:39:30','2005-08-15 12:37:35'),(12,5,'00:00:00','0000-00-00 00:00:00'),(13,0,'10:58:36','2006-03-10 00:00:00'),(14,8,'00:00:00','2005-05-16 11:02:36'),(15,8,'14:06:04','2008-11-02 00:00:00'),(16,5,'15:26:21','2006-03-15 00:00:00'),(17,1,'22:23:55','0000-00-00 00:00:00'),(18,7,'00:00:00','0000-00-00 00:00:00'),(19,0,'08:55:47','2008-12-17 20:15:40'),(20,9,'07:56:13','0000-00-00 00:00:00');
SELECT COUNT( OUTR .`int_nokey` )
FROM C OUTR2 JOIN C OUTR ON OUTR2 .`time_key` < OUTR .`datetime_key`
ORDER BY OUTR .`pk` ;
DROP TABLE C;
# End of test case for query 0
# Begin test case for query 1
--disable_warnings
DROP TABLE IF EXISTS C;
--enable_warnings
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`int_nokey` int(11) NOT NULL,
`time_key` time NOT NULL,
`datetime_key` datetime NOT NULL,
PRIMARY KEY (`pk`),
KEY `time_key` (`time_key`),
KEY `datetime_key` (`datetime_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,9,'04:56:51','0000-00-00 00:00:00'),(2,0,'03:03:27','2002-02-09 07:38:13'),(3,8,'07:48:21','2001-05-03 12:08:14'),(4,3,'00:00:00','0000-00-00 00:00:00'),(5,7,'11:58:42','2009-07-28 03:43:30'),(6,0,'03:34:13','2009-08-04 00:00:00'),(7,1,'00:00:00','0000-00-00 00:00:00'),(8,9,'00:00:00','0000-00-00 00:00:00'),(9,0,'00:51:25','2005-08-02 17:16:54'),(10,9,'14:43:13','2002-12-21 00:00:00'),(11,0,'00:39:30','2005-08-15 12:37:35'),(12,5,'00:00:00','0000-00-00 00:00:00'),(13,0,'10:58:36','2006-03-10 00:00:00'),(14,8,'00:00:00','2005-05-16 11:02:36'),(15,8,'14:06:04','2008-11-02 00:00:00'),(16,5,'15:26:21','2006-03-15 00:00:00'),(17,1,'22:23:55','0000-00-00 00:00:00'),(18,7,'00:00:00','0000-00-00 00:00:00'),(19,0,'08:55:47','2008-12-17 20:15:40'),(20,9,'07:56:13','0000-00-00 00:00:00');
SELECT COUNT( OUTR .`int_nokey` )
FROM C OUTR2 JOIN C OUTR ON OUTR2 .`time_key` < OUTR .`datetime_key`
ORDER BY OUTR .`pk`
LIMIT 1 /* TRANSFORM_OUTCOME_SINGLE_ROW */;
DROP TABLE C;
# End of test case for query 1
Suggested fix:
Ensure consistent result sets for equivalent queries.