Bug #1468 FOUND_ROWS() can return incorrect number of rows
Submitted: 2 Oct 2003 14:41 Modified: 10 Dec 2003 19:10
Reporter: Shane Allen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0.15a OS:Linux (debian linux)
Assigned to: Michael Widenius CPU Architecture:Any

[2 Oct 2003 14:41] Shane Allen
Description:
executing a query that returns rows, then found_rows() returns the found rows of that query. If you then execute that same query, but add 'AND 1=2 ' to the where clause, found_rows will return the number of rows that the first query returned, even though 1=2 means that no rows could possibly be returned.

How to repeat:
create database test1234;
\r test1234
create table test (id int, primary key (id));
insert into test values (1), (2), (3), (4), (5);
select SQL_CALC_FOUND_ROWS * from test where id > 3 limit 0, 1;
select FOUND_ROWS();
select SQL_CALC_FOUND_ROWS * from test where id > 3 AND 1=2 limit 0, 1;
select FOUND_ROWS();
select SQL_CALC_FOUND_ROWS * from test where id > 6 limit 0, 1;
select FOUND_ROWS();

my results:

mysql> create database test1234;
Query OK, 1 row affected (0.01 sec)

mysql> \r test1234
Connection id:    122
Current database: test1234

mysql> create table test (id int, primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select SQL_CALC_FOUND_ROWS * from test where id > 3 limit 0, 1;
+----+
| id |
+----+
|  4 |
+----+
1 row in set (0.00 sec)

mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql> select SQL_CALC_FOUND_ROWS * from test where id > 3 AND 1=2 limit 0, 1;
Empty set (0.00 sec)

mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

mysql> select SQL_CALC_FOUND_ROWS * from test where id > 6 limit 0, 1;
Empty set (0.00 sec)

mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            0 |
+--------------+
[2 Oct 2003 17:23] Paul Dubois
I can confirm getting the same results on Mac OS X, 4.0.14
and Red Hat 7, 4.0.16.

I get the *correct* results with 4.1.1 on both systems, however.
[10 Dec 2003 19:10] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix will be in 4.0.17