MySQL Bugs: #337: Normal SELECT after SELECT SQL_CALC_FOUND_ROWS changes FOUND

Bug #337	Normal SELECT after SELECT SQL_CALC_FOUND_ROWS changes FOUND_ROWS() result
Submitted:	27 Apr 2003 16:54	Modified:	28 Apr 2003 0:54
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.13	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
According to the manual :

FOUND_ROWS() 
Returns the number of rows that the last SELECT SQL_CALC_FOUND_ROWS ... command would have returned, if it had not been restricted with LIMIT. 

However if a SELECT without SQL_CALC_FOUND_ROWS between SELECT SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() is used, the result returned by SELECT FOUND_ROWS() is wrong (ie not the number of rows that the last SELECT SQL_CALC_FOUND_ROWS ... command would have returned)

How to repeat:
CREATE TABLE test1 (a int);
INSERT INTO test1 VALUES (1),(2),(3);
SELECT SQL_CALC_FOUND_ROWS * FROM test1 LIMIT 1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            3 |
+--------------+

=> this is OK

However :

SELECT SQL_CALC_FOUND_ROWS * FROM test1 LIMIT 1;
SELECT * FROM test1 WHERE a=1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

=> This is wrong according to the manuel described behaviour

I have now updated the manual about this.
(FOUND_ROWS() is only valid until the next select)