MySQL Bugs: #68458: Wrong count from FOUND

Bug #68458	Wrong count from FOUND_ROWS() on MySQL 5.6
Submitted:	21 Feb 2013 17:10	Modified:	1 Mar 2013 17:29
Reporter:	Tomas Forsman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.10	OS:	Any
Assigned to:	Tor Didriksen	CPU Architecture:	Any

Description:
FOUND_ROWS() returns the full table count from the provided table on a query which only has one result.

Also, this differs if the same query is run on MySQL 5.5.29 and MySQL 5.6.10.

How to repeat:
Run the following script on MySQL 5.5.29 and MySQL 5.6.10. The last row 'SELECT FOUND_ROWS()' show '1' on 5.5.29 and '3' and on 5.6.10.

There are two ways to make 5.6.10 show '1', either by commenting the row marked with '# Fix 1' or '# Fix 2'. 

delete from test_resource_value;
delete from test_resource;
drop table test_resource_value;
drop table test_resource;

CREATE TABLE `test_resource` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(191) NOT NULL,
  `fallback_resource_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `k_UNIQUE` (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=0;

CREATE TABLE `test_resource_value` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `resource_id` int(10) unsigned NOT NULL,
  `country_id` int(10) unsigned NOT NULL,
  `value` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c_r_UNIQUE` (`country_id`,`resource_id`), # Fix 1
  KEY `resource_id` (`resource_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0;

INSERT INTO test_resource (`key`) values ("some.key");
INSERT INTO test_resource (`key`) values ("some.other.key");
INSERT INTO test_resource (`key`) values ("yet.another.key");

INSERT INTO test_resource_value (`resource_id`, `country_id`, `value`) VALUES (1, 1, "Some Value");
INSERT INTO test_resource_value (`resource_id`, `country_id`, `value`) VALUES (1, 2, "Some Other Value");
INSERT INTO test_resource_value (`resource_id`, `country_id`, `value`) VALUES (2, 1, "Second Value");
INSERT INTO test_resource_value (`resource_id`, `country_id`, `value`) VALUES (3, 2, "Third Value");

SELECT SQL_CALC_FOUND_ROWS
    * 
FROM
    test_resource r
    LEFT JOIN test_resource_value AS rv1 ON (rv1.country_id=1 and rv1.resource_id=r.id)
WHERE
    r.`key`='some.key' OR rv1.`value`='some.key'
ORDER BY r.`key` # Fix 2
LIMIT 100;

SELECT FOUND_ROWS();

Thank you for the bug report.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

d:\dbs>55c

d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.31 Source distribution

Copyright (c) 2000, 2013, 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 5.5 >use test
Database changed
mysql 5.5 >CREATE TABLE `test_resource` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `key` varchar(191) NOT NULL,
    ->   `fallback_resource_id` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `k_UNIQUE` (`key`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=0;
Query OK, 0 rows affected (0.20 sec)

<CUT>

mysql 5.5 >
mysql 5.5 >SELECT SQL_CALC_FOUND_ROWS
    ->     *
    -> FROM
    ->     test_resource r
    ->     LEFT JOIN test_resource_value AS rv1 ON (rv1.country_id=1 and rv1.resource_id=r.id)
    -> WHERE
    ->     r.`key`='some.key' OR rv1.`value`='some.key'
    -> ORDER BY r.`key` # Fix 2
    -> LIMIT 100;
+----+----------+----------------------+------+-------------+------------+------------+
| id | key      | fallback_resource_id | id   | resource_id | country_id | value      |
+----+----------+----------------------+------+-------------+------------+------------+
|  1 | some.key |                 NULL |    1 |           1 |          1 | Some Value |
+----+----------+----------------------+------+-------------+------------+------------+
1 row in set (0.00 sec)

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

mysql 5.5 >exit
Bye

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> use test
Database changed
mysql> CREATE TABLE `test_resource` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `key` varchar(191) NOT NULL,
    ->   `fallback_resource_id` int(10) unsigned DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `k_UNIQUE` (`key`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=0;
Query OK, 0 rows affected (0.23 sec)

<CUT>

mysql>
mysql> SELECT SQL_CALC_FOUND_ROWS
    ->     *
    -> FROM
    ->     test_resource r
    ->     LEFT JOIN test_resource_value AS rv1 ON (rv1.country_id=1 and rv1.resource_id=r.id)
    -> WHERE
    ->     r.`key`='some.key' OR rv1.`value`='some.key'
    -> ORDER BY r.`key` # Fix 2
    -> LIMIT 100;
+----+----------+----------------------+------+-------------+------------+------------+
| id | key      | fallback_resource_id | id   | resource_id | country_id | value      |
+----+----------+----------------------+------+-------------+------------+------------+
|  1 | some.key |                 NULL |    1 |           1 |          1 | Some Value |
+----+----------+----------------------+------+-------------+------------+------------+
1 row in set (0.00 sec)

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

mysql>

Noted in 5.6.11, 5.7.1 changelogs.

Handling of SQL_CALC_FOUND_ROWS in combination with ORDER BY and 
LIMIT could lead to incorrect results for FOUND_ROWS().

So this was fixed or is there just a note to warn people that it might happen?

Yes, fixed in the upcoming 5.6.11 release.

MySQL server compiled from "5.6.11-log Source distribution" still has this bug. 
After queries using SQL_CALC_FOUND_ROWS, ORDER BY and LIMIT, the FOUND_ROWS() call gives the wrong values.