MySQL Bugs: #8689: found_rows sometimes invalid if next query contains tables

Bug #8689	found_rows sometimes invalid if next query contains tables
Submitted:	22 Feb 2005 15:12	Modified:	19 Aug 2005 1:42
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Any (*)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
After using SQL_CALC_FOUND_ROWS, I tried to look up a value (word for number in various languages) for the result returned by found_rows.

This does not always succeed.

I have created two example, in the first one found_rows() still works in the where clause, but not in having.

In the 2nd example,it fails in the where clause.

How to repeat:
create table num select 0 a, "none" b union select 1,"one" union select 2, "two";

#good
select SQL_CALC_FOUND_ROWS a from num where a = 2 limit 1;
select found_rows() ;
+--------------+
| found_rows() |
+--------------+
|            1 |
+--------------+

#good
select SQL_CALC_FOUND_ROWS a from num where a = 2 limit 1;
select  * from num where found_rows() = a;
+---+-----+
| a | b   |
+---+-----+
| 1 | one |
+---+-----+

#bad
select SQL_CALC_FOUND_ROWS a from num where a = 2 limit 1;
select  * from num having found_rows() = a;
+---+------+
| a | b    |
+---+------+
| 0 | none |
+---+------+

## Example 2

create table num1 (a int, b int); insert into num1 values (0,0),(1,1),(1,1),(2,2),(2,2),(3,3),(3,3),(4,4),(4,4),(5,5), (5,5);

select SQL_CALC_FOUND_ROWS a from num1 where a = 1 limit 1;
 select found_rows();
+--------------+
| found_rows() |
+--------------+
|            2 |
+--------------+
select SQL_CALC_FOUND_ROWS a from num1 where a = 1 limit 1;
select a from num1 where a = found_rows();
+------+
| a    |
+------+
|    0 |
+------+

Suggested fix:
make it work like last_insert_id?
so keep the previous result, during the current query, and only replace at the end of the current query

This is the intended behavior - the result of found_rows() is being
reset when the next query begins execution, and generally may be
updated at any time during execution. The only exception are queries
that have no tables, when found_rows() is set in the end of the query.
This allows us to issue the query "SELECT found_rows();" to retrieve
the rows found by the previous query. If we run "select found_rows();"
second time, we get "1" which is the number of rows the previous
"select found_rows();" returned.

For the particular examples in the bug report there is a very simple
solution that is even more general than what is proposed in the bug
report. The solution is to store the value of the current found_rows()
in a user variable. Then this variable can be used at any time later
on. In addition, this approach allows to remember the fownd_rows() of
arbitrarily old queries.

For example the first problematic query can be solved as:

select SQL_CALC_FOUND_ROWS a from num
where a  = 1 limit 1;

select found_rows() into @prev_found_rows;

select * from num having @prev_found_rows = a;

Perhaps this behavior should be documented better.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).