Bug #29223 declare cursor c for SHOW .....
Submitted: 19 Jun 2007 19:45 Modified: 24 Oct 2007 19:09
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.44/5.1 OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: cursor, show

[19 Jun 2007 19:45] Martin Friebe
Description:
Not really a problem, rather a hidden feature.

http://dev.mysql.com/doc/refman/5.0/en/declare-cursors.html  only describes the ability to declare a cursor for a select statement.

Several show commands are however interenally rewritten as select. They are accepted in cursor declaration.

How to repeat:
drop procedure p1;
DELIMITER |

CREATE PROCEDURE p1()
BEGIN
  DECLARE c char(100);
  DECLARE cur1 CURSOR FOR show tables;

  OPEN cur1;
  FETCH cur1 INTO c;
  select c;
  CLOSE cur1;
END |
DELIMITER ;

call p1();

Suggested fix:
document it? or remove it?

in sql_yacc.yy line 2060 in "sp_cursor_stmt" is a check for SQLCOM_SELECT. This isnt sufficient, as show commands (if a information-schema table exists) are internally rewritten to become a select.
[20 Jun 2007 0:46] Miguel Solorzano
Thank you for the bug report.
[6 Sep 2007 16:46] Konstantin Osipov
Do nothing in the code, add test coverage in 5.2, document in 5.2 manual.
[12 Sep 2007 1:41] Marc Alff
To discuss, but I think we should *not* allow this syntax,
as it opens the door to constructs that are probably not working,
because they are not all implemented reusing internally COM_SELECT,
like for example:

DECLARE CURSOR c FOR SHOW AUTHORS;
DECLARE CURSOR c FOR SHOW CONTRIBUTORS;
DECLARE CURSOR c FOR SHOW PROFILE;
etc etc

Giving end users the expectation that any command using the SHOW keyword should therefore be supported and is a feature is a bad idea IMO.
[11 Oct 2007 20:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35411

ChangeSet@1.2594, 2007-10-11 17:38:40-03:00, davi@moksha.com.br +4 -0
  Bug#29223 declare cursor c for SHOW .....
  
  "DECLARE CURSOR FOR SHOW ..." is a syntax that currently appears to work,
  but is untested for some SHOW commands and does not work for other SHOW
  commands.
  
  Since this is an un-intended feature that leaked as a result of a coding bug
  (in the parser grammar), the correct fix is to fix the grammar to not accept
  this construct.
  
  In other words, "DECLARE CURSOR FOR SHOW <other commands that don't work>"
  is not considered a bug, and we will not implement other features to make all
  the SHOW commands usable inside a cursor just because someone exploited a bug.
[19 Oct 2007 18:52] Bugs System
Pushed into 5.1.23-beta
[24 Oct 2007 19:09] Paul Dubois
Noted in 5.1.23 changelog.

Within a stored routine, it is no longer allowable to declare a
cursor for a SHOW statement. This happened to work in some instances, 
but is no longer supported.
[9 Jan 2009 20:25] Paul Dubois
Updated changelog entry:

Within a stored routine, it is no longer allowable to declare a
cursor for a SHOW or DESCRIBE statement. This happened to work in
some instances, but is no longer supported. In many cases, a
workaround for this change is to use the cursor with a SELECT query
to read from an INFORMATION_SCHEMA table that provides the same
information as the SHOW statement.