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.
