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
Triage: Needs Triage: D5 (Feature request)

[1 Feb 2006 15:28] Beat Vontobel
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.
[3 Feb 2006 11:41] Hartmut Holzgraefe
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)
[3 Feb 2006 15:53] Beat Vontobel
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