MySQL Bugs: #17009: Stored Procedures: allow cursors on anything that returns a result set.

Bug #17009	Stored Procedures: allow cursors on anything that returns a result set.
Submitted:	1 Feb 2006 15:28	Modified:	3 Feb 2006 15:53
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.18	OS:	Any (any)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
As I see it, there's currently no possibility to work with the result of CHECKSUM TABLE in SQL directly (for example in stored routines or functions). To achieve this, it would be necessary to make it possible to assign the results to a variable.

How to repeat:
n/a

Suggested fix:
I currently see three possible solutions:

1) Allow to assign the results to variables directly, such as in a SELECT ... INTO ... statement (CHECKSUM TABLE ... INTO ...)
2) Add checksum information to the information_schema (probably not the most intuitive way, as it could take a long time to show the results, if the checksum has to be computed on the fly, and users probably expect a quick result on a SELECT on information_schema)
3) Add a function TABLE_CHECKSUM(schema, table)

I think solution 3 would be the most natural way to achieve the desired functionality.

As CHECKSUM TABLE returns a regular result set just like SHOW 
i would assume that CHECKSUM should be allowed in cursor 
declarations the same way that SHOW is allowed

how to repeat:

delimiter //;
CREATE PROCEDURE demo()
BEGIN
   DECLARE a char(100);
   DECLARE b bigint;
   DECLARE c CURSOR FOR checksum table mysql.user;
   OPEN c;
   FETCH cur1 INTO a,b;
END
//
-> ERROR 1322 (42000): Cursor statement must be a SELECT

but 

CREATE PROCEDURE demo()
BEGIN
    DECLARE a char(100);
    DECLARE b char(100);
    DECLARE c CURSOR FOR SHOW VARIABLES;
    OPEN c;
    FETCH c INTO a,b;
END
//
-> Query OK, 0 rows affected (0.18 sec)

Thanks, Hartmut, for accepting the bug and your thoughts.

Just one more thing on the CURSOR solution: I didn't mention it amongst my suggestions as I see another problem with it. To be useful in stored routine context, CHECKSUM TABLE should allow to name a table dynamically. For this we'd need to prepare the statement from a generated string with the table names. And as I see it, it's currently not possible to have a cursor on a prepared statement. (At least I think it wasn't on the first few production releases of 5.0, didn't check it on 5.0.18.)

Regards,
Beat