Bug #46283 COUNT() returning bad values when LIMIT 1 clause added to query
Submitted: 18 Jul 2009 15:22
Reporter: Patrick Crews Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0, 5.1, 5.4, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: bad result, count, limit

[18 Jul 2009 15:22] Patrick Crews
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.
[18 Jul 2009 15:24] Patrick Crews
This was also seen with BIT_XOR.
Suspect this is a problem with LIMIT rather than a particular aggregate function.
[18 Jul 2013 11:22] Hartmut Holzgraefe
Looks like a duplicate of http://bugs.mysql.com/bug.php?id=46217 ?