Bug #69119 Wrong FOUND_ROWS() on MySQL 5.6.11
Submitted: 2 May 2013 2:51 Modified: 3 Jun 2013 14:26
Reporter: 靖博 今野 Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.6.11 OS:Linux (CentOS 6.4)
Assigned to: Tor Didriksen CPU Architecture:Any
Tags: found_rows, regression
Triage: Needs Triage: D2 (Serious)

[2 May 2013 2:51] 靖博 今野
Description:
FOUND_ROWS() return is wrong count.

The innoDB is OK, but  It does not work on MyISAM.

How to repeat:
mysql> use test;
Database changed
mysql>
mysql> CREATE TABLE test_found_rows (
    ->   pkey char(11) NOT NULL,
    ->   ivalue smallint(6) NOT NULL,
    ->   PRIMARY KEY (`pkey`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    -> ;

INSERT INTO test_found_rows
VALUES
('4548870', -10000),
('4600641', -10000),
('4718998', -10000),
('4737258', -10000),
('4737259', -10000),
('4769408', -10000),
('4806434', -10000),
('4891119', -10000),
('4897306', -10000),
('4982112', -10000),
('5489466', -10000),
('5531955', -10000),
('5837345', -10000),
('6069196', -10000),
('6756422', -10000),
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO test_found_rows
    -> VALUES
    -> ('4548870', -10000),
    -> ('4600641', -10000),
    -> ('4718998', -10000),
    -> ('4737258', -10000),
    -> ('4737259', -10000),
    -> ('4769408', -10000),
    -> ('4806434', -10000),
    -> ('4891119', -10000),
    -> ('4897306', -10000),
    -> ('4982112', -10000),
    -> ('5489466', -10000),
    -> ('5531955', -10000),
    -> ('5837345', -10000),
    -> ('6069196', -10000),
    -> ('6756422', -10000),
    -> ('6839510', -10000),
    -> ('7082263', -10000),
    -> ('7087671', -10000),
    -> ('7352152', -10000)
    -> ;
Query OK, 19 rows affected (0.00 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM test_found_rows;
+---------+--------+
| pkey    | ivalue |
+---------+--------+
| 4548870 | -10000 |
| 4600641 | -10000 |
| 4718998 | -10000 |
| 4737258 | -10000 |
| 4737259 | -10000 |
| 4769408 | -10000 |
| 4806434 | -10000 |
| 4891119 | -10000 |
| 4897306 | -10000 |
| 4982112 | -10000 |
| 5489466 | -10000 |
| 5531955 | -10000 |
| 5837345 | -10000 |
| 6069196 | -10000 |
| 6756422 | -10000 |
| 6839510 | -10000 |
| 7082263 | -10000 |
| 7087671 | -10000 |
| 7352152 | -10000 |
+---------+--------+
19 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
    -> FROM test_found_rows
    -> ORDER BY ivalue DESC, pkey DESC
    -> LIMIT 0,10
    -> ;
+---------+--------+
| pkey    | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.00 sec)

mysql>
mysql>
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)
[3 May 2013 3:27] Piotr Tomasik
I can confirm that I still have FOUND_ROWS Problem on innoDB as well.

Mysql 5.6.11 OSX

Downgraded to 5.5.* and it was ok again
[3 May 2013 11:01] Umesh Shastry
Hello,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[3 May 2013 11:03] Umesh Shastry
######### Affected mysql-5.6.11

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.11-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test_found_rows_myisam (
    ->     pkey char(11) NOT NULL,
    ->     ivalue smallint(6) NOT NULL,
    ->     PRIMARY KEY (`pkey`)
    ->     ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> 
mysql> INSERT INTO test_found_rows_myisam
    -> VALUES
    -> ('4548870', -10000),
    -> ('4600641', -10000),
    -> ('4718998', -10000),
    -> ('4737258', -10000),
    -> ('4737259', -10000),
    -> ('4769408', -10000),
    -> ('4806434', -10000),
    -> ('4891119', -10000),
    -> ('4897306', -10000),
    -> ('4982112', -10000),
    -> ('5489466', -10000),
    -> ('5531955', -10000),
    -> ('5837345', -10000),
    -> ('6069196', -10000),
    -> ('6756422', -10000),
    -> ('6839510', -10000),
    -> ('7082263', -10000),
    -> ('7087671', -10000),
    -> ('7352152', -10000);
Query OK, 19 rows affected (0.13 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
    -> FROM test_found_rows_myisam
    ->  ORDER BY ivalue DESC, pkey DESC
    ->  LIMIT 0,10;
+---------+--------+
| pkey    | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.01 sec)

mysql>  SELECT FOUND_ROWS(); 
+--------------+
| FOUND_ROWS() |
+--------------+
|           10 |
+--------------+
1 row in set (0.06 sec)

mysql> 
mysql> CREATE TABLE test_found_rows_innodb (
    ->      pkey char(11) NOT NULL,
    ->      ivalue smallint(6) NOT NULL,
    ->      PRIMARY KEY (`pkey`)
    ->      ) ENGINE=Innodb DEFAULT CHARSET=utf8;    
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> INSERT INTO test_found_rows_innodb
    ->  VALUES
    ->  ('4548870', -10000),
    ->  ('4600641', -10000),
    ->  ('4718998', -10000),
    ->  ('4737258', -10000),
    ->  ('4737259', -10000),
    ->  ('4769408', -10000),
    ->  ('4806434', -10000),
    ->  ('4891119', -10000),
    ->  ('4897306', -10000),
    ->  ('4982112', -10000),
    ->  ('5489466', -10000),
    ->  ('5531955', -10000),
    ->  ('5837345', -10000),
    ->  ('6069196', -10000),
    ->  ('6756422', -10000),
    ->  ('6839510', -10000),
    ->  ('7082263', -10000),
    ->  ('7087671', -10000),
    ->  ('7352152', -10000);
Query OK, 19 rows affected (0.08 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
    -> FROM test_found_rows_innodb
    ->  ORDER BY ivalue DESC, pkey DESC
    ->  LIMIT 0,10;
+---------+--------+
| pkey    | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.01 sec)

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|           19 |
+--------------+
1 row in set (0.01 sec)

mysql>
[3 May 2013 11:04] Umesh Shastry
######### not affected mysql-5.5.31

[root@ushastry mysql-5.5.31]# bin/mysql -u root -p --port=3306 --protocol=TCP test

Server version: 5.5.31-debug-log Source distribution

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

mysql> CREATE TABLE test_found_rows_myisam (
    ->     pkey char(11) NOT NULL,
    ->     ivalue smallint(6) NOT NULL,
    ->     PRIMARY KEY (`pkey`)
    ->     ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO test_found_rows_myisam
    -> VALUES
    -> ('4548870', -10000),
    -> ('4600641', -10000),
    -> ('4718998', -10000),
    -> ('4737258', -10000),
    -> ('4737259', -10000),
    -> ('4769408', -10000),
    -> ('4806434', -10000),
    -> ('4891119', -10000),
    -> ('4897306', -10000),
    -> ('4982112', -10000),
    -> ('5489466', -10000),
    -> ('5531955', -10000),
    -> ('5837345', -10000),
    -> ('6069196', -10000),
    -> ('6756422', -10000),
    -> ('6839510', -10000),
    -> ('7082263', -10000),
    -> ('7087671', -10000),
    -> ('7352152', -10000);
Query OK, 19 rows affected (0.00 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
    -> FROM test_found_rows_myisam
    ->  ORDER BY ivalue DESC, pkey DESC
    ->  LIMIT 0,10;
+---------+--------+
| pkey    | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.00 sec)

mysql> SELECT FOUND_ROWS(); 
+--------------+
| FOUND_ROWS() |
+--------------+
|           19 |
+--------------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql>  CREATE TABLE test_found_rows_innodb (
    ->      pkey char(11) NOT NULL,
    ->      ivalue smallint(6) NOT NULL,
    ->      PRIMARY KEY (`pkey`)
    ->      ) ENGINE=Innodb DEFAULT CHARSET=utf8; 
Query OK, 0 rows affected (0.06 sec)

mysql>  INSERT INTO test_found_rows_innodb
    ->  VALUES
    ->  ('4548870', -10000),
    ->  ('4600641', -10000),
    ->  ('4718998', -10000),
    ->  ('4737258', -10000),
    ->  ('4737259', -10000),
    ->  ('4769408', -10000),
    ->  ('4806434', -10000),
    ->  ('4891119', -10000),
    ->  ('4897306', -10000),
    ->  ('4982112', -10000),
    ->  ('5489466', -10000),
    ->  ('5531955', -10000),
    ->  ('5837345', -10000),
    ->  ('6069196', -10000),
    ->  ('6756422', -10000),
    ->  ('6839510', -10000),
    ->  ('7082263', -10000),
    ->  ('7087671', -10000),
    ->  ('7352152', -10000);
Query OK, 19 rows affected (0.14 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
    -> FROM test_found_rows_innodb
    ->  ORDER BY ivalue DESC, pkey DESC
    ->  LIMIT 0,10;
+---------+--------+
| pkey    | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.00 sec)

mysql>  SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|           19 |
+--------------+
1 row in set (0.00 sec)

mysql>
[3 May 2013 11:20] Umesh Shastry
## Works in 5.6.10

Server version: 5.6.10-debug-log Source distribution

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

mysql> CREATE TABLE test_found_rows_myisam (
    ->     pkey char(11) NOT NULL,
    ->     ivalue smallint(6) NOT NULL,
    ->     PRIMARY KEY (`pkey`)
    ->     ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> INSERT INTO test_found_rows_myisam
    -> VALUES
    -> ('4548870', -10000),
    -> ('4600641', -10000),
    -> ('4718998', -10000),
    -> ('4737258', -10000),
    -> ('4737259', -10000),
    -> ('4769408', -10000),
    -> ('4806434', -10000),
    -> ('4891119', -10000),
    -> ('4897306', -10000),
    -> ('4982112', -10000),
    -> ('5489466', -10000),
    -> ('5531955', -10000),
    -> ('5837345', -10000),
    -> ('6069196', -10000),
    -> ('6756422', -10000),
    -> ('6839510', -10000),
    -> ('7082263', -10000),
    -> ('7087671', -10000),
    -> ('7352152', -10000);
Query OK, 19 rows affected (0.00 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
    -> FROM test_found_rows_myisam
    ->  ORDER BY ivalue DESC, pkey DESC
    ->  LIMIT 0,10;
+---------+--------+
| pkey    | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.00 sec)

mysql> SELECT FOUND_ROWS(); 
+--------------+
| FOUND_ROWS() |
+--------------+
|           19 |
+--------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test_found_rows_innodb (
    ->      pkey char(11) NOT NULL,
    ->      ivalue smallint(6) NOT NULL,
    ->      PRIMARY KEY (`pkey`)
    ->      ) ENGINE=Innodb DEFAULT CHARSET=utf8;    
Query OK, 0 rows affected (0.06 sec)

mysql>      
mysql>  INSERT INTO test_found_rows_innodb
    ->  VALUES
    ->  ('4548870', -10000),
    ->  ('4600641', -10000),
    ->  ('4718998', -10000),
    ->  ('4737258', -10000),
    ->  ('4737259', -10000),
    ->  ('4769408', -10000),
    ->  ('4806434', -10000),
    ->  ('4891119', -10000),
    ->  ('4897306', -10000),
    ->  ('4982112', -10000),
    ->  ('5489466', -10000),
    ->  ('5531955', -10000),
    ->  ('5837345', -10000),
    ->  ('6069196', -10000),
    ->  ('6756422', -10000),
    ->  ('6839510', -10000),
    ->  ('7082263', -10000),
    ->  ('7087671', -10000),
    ->  ('7352152', -10000);
Query OK, 19 rows affected (0.01 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
    -> FROM test_found_rows_innodb
    ->  ORDER BY ivalue DESC, pkey DESC
    ->  LIMIT 0,10;
+---------+--------+
| pkey    | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.01 sec)

mysql>  SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|           19 |
+--------------+
1 row in set (0.00 sec)

mysql>
[4 May 2013 10:54] Shane Bester
Seems I filed a duplicate by mistake (but my testcase is nicer :)
http://bugs.mysql.com/bug.php?id=69144
[4 May 2013 10:57] Piotr Tomasik
@ Shane Bester

At least you proved that it was also a problem on innodb and not specifically myisam
[8 May 2013 9:05] Tor Didriksen
oops, this is a regression caused by the patch for
Bug#68458 Wrong count from FOUND_ROWS() on MySQL 5.6
[30 May 2013 5:12] Erlend Dahl
Bug#69166 was marked as a duplicate.
[3 Jun 2013 14:26] Paul Dubois
Noted in 5.6.12, 5.7.2 changelogs.

FOUND_ROWS() could return an incorrect value if the preceding query
used filesort.