MySQL Bugs: #3615: Error using the COUNT function in a stored procedure

Bug #3615	Error using the COUNT function in a stored procedure
Submitted:	30 Apr 2004 19:03	Modified:	21 Jul 2004 15:33
Reporter:	Miguel Solorzano	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.0/5.0.1	OS:	Windows (Windows)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
I was writing a stored procedure to test out the procedure/function
implementation in MySQL and ran into a problem using the COUNT function
inside of a procedure.  Below is the sample stored procedure that will
reproduce the problem.  I believe this example was found in the online
documentation somewhere.

Procedure that counts rows and returns the value:

CREATE PROCEDURE  eco_count(out param1 INT)
BEGIN
  select count(*) into param1 from eco_class_t;
END

When I call the above procedure the first time it works perfect but
unfortunately each subsequent time it reurns the following error:

mysql> call eco_count(@a);                    <----- Works Fine
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;  <---- here is the output
+------+
| @a   |
+------+
| 4    |
+------+
1 row in set (0.00 sec)

mysql> call eco_count(@a);   <------ same exact call returns an error
ERROR 1172 (42000): Result consisted of more than one row
mysql>

I have tried using COUNT(table.row) and had the same problem.

How to repeat:
create table eco_class_t (id int);
insert into eco_class_t values (1),(2),(3), (4);

delimiter //

CREATE PROCEDURE  eco_count(out param1 INT)
BEGIN
  select count(*) into param1 from eco_class_t;
END//

call eco_count(@a)//
select @a//
call eco_count(@a)//

"Me too!" 
 
Well, it might be related. I too have encountered inconsistent results with an aggregate 
function use in a stored procedure. How to repeat: 
 
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.32 sec) 
 
mysql> insert into t values (1),(1); 
Query OK, 2 rows affected (0.00 sec) 
Records: 2  Duplicates: 0  Warnings: 0 
 
mysql> delimiter // 
mysql> create procedure p () select 'x' from t having count(*) = 2; 
    -> // 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p()// 
+---+ 
| x | 
+---+ 
| x | 
+---+ 
1 row in set (0.00 sec) 
 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p()// 
+---+ 
| x | 
+---+ 
| x | 
| x | 
+---+ 
2 rows in set (0.00 sec) 
 
Query OK, 0 rows affected (0.00 sec)

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