Bug #69271 Wrong count from FOUND_ROWS() with ORDER BY
Submitted: 17 May 2013 16:14 Modified: 5 Jun 2013 17:13
Reporter: Leandro Morgado Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.6.x OS:Any
Assigned to: CPU Architecture:Any

[17 May 2013 16:14] Leandro Morgado
Description:
FOUND_ROWS returns the wrong count when the SELECT query includes an ORDER BY.

"In the absence of the SQL_CALC_FOUND_ROWS option in the most recent successful SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement. If the statement includes a LIMIT clause, FOUND_ROWS() returns the number of rows up to the limit. For example, FOUND_ROWS() returns 10 or 60, respectively, if the statement includes LIMIT 10 or LIMIT 50, 10."
  http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_found-rows

I tested on the following:

5.5.28 - working as per the manual 
5.6.9rc - not working
5.6.10 - not working
5.6.11 - not working
5.6.12 - not working
5.6.13 - not working

The results are attached to this bug report.

Depending on version and the values of (LIMIT) offset and rowcount, sometimes the correct value is given.

How to repeat:
Load attached dump file (it has 80 rows) and try:

mysql [localhost] {msandbox} (test) > SELECT VERSION();
+---------------------------------------+
| VERSION()                             |
+---------------------------------------+
| 5.6.11-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)

-- This is correct with LIMIT 50,10 both with and without ORDER BY
mysql [localhost] {msandbox} (test) > SELECT ID, VALUE FROM LOUIS LIMIT 50,10; SELECT FOUND_ROWS();
+-----+----------------------------+
| ID  | VALUE                      |
+-----+----------------------------+
| 207 | DROP CLUSTER               |
| 123 | DROP DEFAULT               |
| 178 | DROP DIMENSION             |
|  90 | DROP FUNCTION              |
| 208 | DROP FUNCTION MAPPING      |
| 124 | DROP INDEX                 |
| 209 | DROP INDEX EXTENSION       |
| 179 | DROP LIBRARY               |
| 210 | DROP MATERIALIZED VIEW     |
| 211 | DROP MATERIALIZED VIEW LOG |
+-----+----------------------------+
10 rows in set (0.00 sec)

+--------------+
| FOUND_ROWS() |
+--------------+
|           60 |
+--------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT ID, VALUE FROM LOUIS ORDER BY ID LIMIT 50,10; SELECT FOUND_ROWS();
+-----+-----------------------+
| ID  | VALUE                 |
+-----+-----------------------+
| 199 | CREATE OPERATOR       |
| 200 | CREATE PACKAGE        |
| 201 | CREATE PACKAGE BODY   |
| 202 | CREATE PROXY_TABLE    |
| 203 | CREATE TYPE BODY      |
| 204 | CREATE TYPE MAPPING   |
| 205 | DESCRIBE              |
| 206 | DROP ALIAS            |
| 207 | DROP CLUSTER          |
| 208 | DROP FUNCTION MAPPING |
+-----+-----------------------+
10 rows in set (0.00 sec)

+--------------+
| FOUND_ROWS() |
+--------------+
|           60 |
+--------------+
1 row in set (0.00 sec)

-- However if LIMIT 10,10 then SELECT with ORDER BY has wrong count
mysql [localhost] {msandbox} (test) > SELECT ID, VALUE FROM LOUIS LIMIT 10,10; SELECT FOUND_ROWS();
+-----+-----------------+
| ID  | VALUE           |
+-----+-----------------+
| 189 | ALTER PACKAGE   |
|  71 | ALTER PROCEDURE |
| 132 | ALTER SEQUENCE  |
|  72 | ALTER TABLE     |
|  73 | ALTER TYPE      |
| 153 | ALTER VIEW      |
| 310 | COLLMOD         |
| 317 | COMPACT         |
| 313 | COUNT           |
| 190 | CREATE ALIAS    |
+-----+-----------------+
10 rows in set (0.00 sec)

+--------------+
| FOUND_ROWS() |
+--------------+
|           20 |
+--------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT ID, VALUE FROM LOUIS ORDER BY ID LIMIT 10,10; SELECT FOUND_ROWS();
+-----+----------------+
| ID  | VALUE          |
+-----+----------------+
|  88 | CREATE VIEW    |
|  90 | DROP FUNCTION  |
|  95 | DROP PROCEDURE |
|  96 | DROP TABLE     |
|  97 | DROP TRIGGER   |
|  98 | DROP TYPE      |
|  99 | DROP VIEW      |
| 119 | CREATE DEFAULT |
| 120 | CREATE RULE    |
| 123 | DROP DEFAULT   |
+-----+----------------+
10 rows in set (0.00 sec)

+--------------+
| FOUND_ROWS() |
+--------------+
|           80 |
+--------------+
1 row in set (0.00 sec)

-- This values of LIMIT does seem to have an impact:
mysql [localhost] {msandbox} (test) > SELECT ID, VALUE FROM LOUIS ORDER BY ID LIMIT 20,9; SELECT FOUND_ROWS();
+-----+-----------------+
| ID  | VALUE           |
+-----+-----------------+
| 124 | DROP INDEX      |
| 125 | DROP RULE       |
| 130 | ALTER INDEX     |
| 132 | ALTER SEQUENCE  |
| 136 | CREATE SEQUENCE |
| 137 | CREATE SYNONYM  |
| 140 | DROP SEQUENCE   |
| 141 | DROP SYNONYM    |
| 153 | ALTER VIEW      |
+-----+-----------------+
9 rows in set (0.00 sec)

+--------------+
| FOUND_ROWS() |
+--------------+
|           80 |
+--------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT ID, VALUE FROM LOUIS ORDER BY ID LIMIT 20,10; SELECT FOUND_ROWS();
+-----+-----------------+
| ID  | VALUE           |
+-----+-----------------+
| 124 | DROP INDEX      |
| 125 | DROP RULE       |
| 130 | ALTER INDEX     |
| 132 | ALTER SEQUENCE  |
| 136 | CREATE SEQUENCE |
| 137 | CREATE SYNONYM  |
| 140 | DROP SEQUENCE   |
| 141 | DROP SYNONYM    |
| 153 | ALTER VIEW      |
| 159 | CREATE TYPE     |
+-----+-----------------+
10 rows in set (0.00 sec)

+--------------+
| FOUND_ROWS() |
+--------------+
|           30 |
+--------------+
1 row in set (0.00 sec)

Suggested fix:
FOUND_ROWS() should always return the correct rowcount across versions. None of the 5.6.x versions showed consistent, correct behaviour.
[17 May 2013 16:17] Leandro Morgado
archive contains results and dump

Attachment: fr_test.tar.gz (application/x-gzip, text), 2.78 KiB.

[18 May 2013 7:40] Shane Bester
related:
http://bugs.mysql.com/bug.php?id=69144
http://bugs.mysql.com/bug.php?id=69119
[5 Jun 2013 17:13] Paul Dubois
Noted in 5.6.13, 5.7.2 changelogs.

In the absence of SQL_CALC_FOUND_ROWS in the preceding query,
FOUND_ROWS() should return the number of rows in the result set, but
this did not always happen if the query contained ORDER BY.