MySQL Bugs: #62372: Wrong query results with SQL_CALC_FOUND

Bug #62372	Wrong query results with SQL_CALC_FOUND_ROWS, GROUP BY, LIMIT and Using index
Submitted:	7 Sep 2011 11:48	Modified:	11 Feb 2018 14:17
Reporter:	Mateusz Kijowski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.15, 5.5.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
A query with SQL_CALC_FOUND_ROWS, GROUP BY and LIMIT which is satisfied by an index returns incorrect results with certain LIMIT values.

> SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;
+-----+
| kat |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

This value does not exist in the table:

> SELECT `kat` FROM `buggy_sm` WHERE `kat`=0;
Empty set (0.00 sec)

The query returns proper results with different LIMIT value.

> SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 4;
+-----+
| kat |
+-----+
| 101 |
| 102 |
| 201 |
| 324 |
+-----+
4 rows in set (0.00 sec)

Running the first query again yelds another (wrong) result:

> SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;
+-----+
| kat |
+-----+
| 801 |
+-----+
1 row in set (0.00 sec)

On new conenction, the result again is 0.

The LIMIT value affects the query plan:

> EXPLAIN SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;
+----+-------------+----------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | buggy_sm | index | dict          | multi | 15      | NULL |    2 | Using where; Using index |
+----+-------------+----------+-------+---------------+-------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

> EXPLAIN SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 4;
+----+-------------+----------+------+---------------+------+---------+-------+------+----------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra                                        |
+----+-------------+----------+------+---------------+------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | buggy_sm | ref  | dict          | dict | 1       | const |   22 | Using where; Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

But It's possible to get proper results with the first query plan.

How to repeat:
Create a table:

DROP TABLE IF EXISTS `buggy_sm`;
CREATE TABLE `buggy_sm` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `int1` int(11) NOT NULL DEFAULT '0',
  `kat` int(11) NOT NULL DEFAULT '0',
  `date_from` date NOT NULL DEFAULT '0000-00-00',
  `date_to` date NOT NULL DEFAULT '0000-00-00',
  `dict` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `dict` (`dict`),
  KEY `multi` (`kat`,`int1`,`date_from`,`date_to`,`dict`)
) ENGINE=MyISAM AUTO_INCREMENT=32700 DEFAULT CHARSET=latin2;
 
INSERT INTO `buggy_sm` VALUES (1,21,101,'0000-00-00','2006-09-26',0),(79,18,102,'0000-00-00','2006-10-23',0),(101,7153,201,'0000-00-00','2006-09-08',0),(4362,10365,324,'0000-00-00','2007-05-29',0),(19522,892,329,'0000-00-00','2008-11-15',0),(4382,4268,503,'0000-00-00','2006-09-05',1),(5088,728,504,'0000-00-00','2006-09-05',0),(6110,3798,505,'0000-00-00','2008-09-05',0),(7315,6205,506,'0000-00-00','2008-09-05',1),(8394,6732,507,'0000-00-00','2008-09-05',0),(8593,6954,508,'2006-09-13','2008-09-08',0),(8708,9625,509,'2006-09-10','2008-09-05',1),(9179,14811,510,'0000-00-00','2006-09-05',0),(13076,3334,511,'2006-10-02','2008-09-27',1),(13086,15319,512,'2006-09-15','2008-09-10',0),(13216,3479,513,'0000-00-00','2008-09-05',1),(13783,4403,514,'0000-00-00','2006-09-06',1),(29337,11102,516,'0000-00-00','2006-09-09',1),(29857,8606,518,'0000-00-00','2008-09-08',0),(29886,8267,520,'0000-00-00','2006-12-29',0),(13866,2984,601,'0000-00-00','2006-09-08',0),(14082,17253,602,'2006-09-13','2008-09-08',0),(14372,18772,604,'0000-00-00','2006-09-05',0),(14930,8086,605,'0000-00-00','2006-09-06',0),(14974,2842,606,'0000-00-00','2006-09-06',0),(29031,719,610,'0000-00-00','2006-09-11',0),(32699,18916,611,'2006-09-17','2008-09-12',0),(3917,4374,615,'0000-00-00','2006-09-05',0),(32366,3300,633,'2007-02-01','2009-01-27',0),(15054,13146,801,'0000-00-00','2006-09-07',0);

And then issue the statements (in one session):

SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;
SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 4;
SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;

Disconnect and execute the query:

SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;

again to observe that the resul has changed.

The query returns valid result when run without SQL_CALC_FOUND_ROWS:

> SELECT `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;
+-----+
| kat |
+-----+
| 101 |
| 102 |
+-----+
2 rows in set (0.00 sec)

Confirmed on 5.1, but works properly on 5.0.32 from Debian etch.

Thank you for the bug report. Verified with current mysql-5.5 from bzr on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.17 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS `buggy_sm`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `buggy_sm` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `int1` int(11) NOT NULL DEFAULT '0',
    ->   `kat` int(11) NOT NULL DEFAULT '0',
    ->   `date_from` date NOT NULL DEFAULT '0000-00-00',
    ->   `date_to` date NOT NULL DEFAULT '0000-00-00',
    ->   `dict` tinyint(1) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`),
    ->   KEY `dict` (`dict`),
    ->   KEY `multi` (`kat`,`int1`,`date_from`,`date_to`,`dict`)
    -> ) ENGINE=MyISAM AUTO_INCREMENT=32700 DEFAULT CHARSET=latin2;
Query OK, 0 rows affected (0.24 sec)

mysql> INSERT INTO `buggy_sm` VALUES
    -> (1,21,101,'0000-00-00','2006-09-26',0),
    -> (79,18,102,'0000-00-00','2006-10-23',0),
    -> (101,7153,201,'0000-00-00','2006-09-08',0),
    -> (4362,10365,324,'0000-00-00','2007-05-29',0),
    -> (19522,892,329,'0000-00-00','2008-11-15',0),
    -> (4382,4268,503,'0000-00-00','2006-09-05',1),
    -> (5088,728,504,'0000-00-00','2006-09-05',0),
    -> (6110,3798,505,'0000-00-00','2008-09-05',0),
    -> (7315,6205,506,'0000-00-00','2008-09-05',1),
    -> (8394,6732,507,'0000-00-00','2008-09-05',0),
    -> (8593,6954,508,'2006-09-13','2008-09-08',0),
    -> (8708,9625,509,'2006-09-10','2008-09-05',1),
    -> (9179,14811,510,'0000-00-00','2006-09-05',0),
    -> (13076,3334,511,'2006-10-02','2008-09-27',1),
    -> (13086,15319,512,'2006-09-15','2008-09-10',0),
    -> (13216,3479,513,'0000-00-00','2008-09-05',1),
    -> (13783,4403,514,'0000-00-00','2006-09-06',1),
    -> (29337,11102,516,'0000-00-00','2006-09-09',1),
    -> (29857,8606,518,'0000-00-00','2008-09-08',0),
    -> (29886,8267,520,'0000-00-00','2006-12-29',0),
    -> (13866,2984,601,'0000-00-00','2006-09-08',0),
    -> (14082,17253,602,'2006-09-13','2008-09-08',0),
    -> (14372,18772,604,'0000-00-00','2006-09-05',0),
    -> (14930,8086,605,'0000-00-00','2006-09-06',0),
    -> (14974,2842,606,'0000-00-00','2006-09-06',0),
    -> (29031,719,610,'0000-00-00','2006-09-11',0),
    -> (32699,18916,611,'2006-09-17','2008-09-12',0),
    -> (3917,4374,615,'0000-00-00','2006-09-05',0),
    -> (32366,3300,633,'2007-02-01','2009-01-27',0),
    -> (15054,13146,801,'0000-00-00','2006-09-07',0);
Query OK, 30 rows affected (0.14 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;
+-----+
| kat |
+-----+
| 801 |
+-----+
1 row in set (0.15 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 4;
+-----+
| kat |
+-----+
| 101 |
| 102 |
| 201 |
| 324 |
+-----+
4 rows in set (0.01 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;
+-----+
| kat |
+-----+
| 801 |
+-----+
1 row in set (0.01 sec)

mysql> explain SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;
+----+-------------+----------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | buggy_sm | index | dict          | multi | 15      | NULL |    2 | Using where; Using index |
+----+-------------+----------+-------+---------------+-------+---------+------+------+--------------------------+
1 row in set (0.04 sec)

mysql> explain SELECT SQL_CALC_FOUND_ROWS `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 4;
+----+-------------+----------+------+---------------+------+---------+-------+------+----------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra                                        |
+----+-------------+----------+------+---------------+------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | buggy_sm | ref  | dict          | dict | 1       | const |   22 | Using where; Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 4;
+----+-------------+----------+------+---------------+------+---------+-------+------+----------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra                                        |
+----+-------------+----------+------+---------------+------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | buggy_sm | ref  | dict          | dict | 1       | const |   22 | Using where; Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2; 
+----+-------------+----------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | buggy_sm | index | dict          | multi | 15      | NULL |    2 | Using where; Using index |
+----+-------------+----------+-------+---------------+-------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT `kat` FROM `buggy_sm` WHERE dict=0 GROUP BY `kat` LIMIT 2;+-----+
| kat |
+-----+
| 101 |
| 102 |
+-----+
2 rows in set (0.00 sec)

Not repeatable with version 5.7

Posted by developer:
 
Fixed in 5.6.40 and up.