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 ?

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.