Bug #3486 FOUND_ROWS() fails inside stored procedure [and prepared statement]
Submitted: 16 Apr 2004 16:43 Modified: 23 Jun 2004 10:58
Reporter: Karl Niemeier Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 OS:Windows (XP Pro 2002)
Assigned to: Per-Erik Martin CPU Architecture:Any

[16 Apr 2004 16:43] Karl Niemeier 
Description:
A call to FOUND_ROWS() fails inside a stored procedure. 

The documentation is not clear whether found_rows() can only be used when SQL_CALC_FOUND_ROWS is used or not. However, outside of a stored procedure, found_rows() works without SQL_CALC_FOUND_ROWS and inside a stored procedure, found_rows() fails whether you use SQL_CALC_FOUND_ROWS or not.

How to repeat:
delimiter //
drop table if exists t1//
create table t1 (id int)//
insert into t1 values(1)//

drop procedure if exists p1//
create procedure p1(id_parm int, out pout int, out pout2 int)
begin
  select id into pout from t1 where id=id_parm; 
  -- select SQL_CALC_FOUND_ROWS * from t1 where id=id_parm LIMIT 2;
  set pout2=FOUND_ROWS();
end
//

call p1(1, @x, @y)//
select @x, @y//
[17 Jun 2004 18:36] Per-Erik Martin 
This is a problem with prepared statements as well. found_rows() is
calculated at item creation time, not evaluation time.
[23 Jun 2004 10:58] Per-Erik Martin 
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:

This has been fixed in 4.1, and will be propagated to 5.0 in a near future.