MySQL Bugs: #1468: FOUND_ROWS() can return incorrect number of rows

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

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 |
+--------------+

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.

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