MySQL Bugs: #29223: declare cursor c for SHOW .....

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

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.

Thank you for the bug report.

Do nothing in the code, add test coverage in 5.2, document in 5.2 manual.

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.

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.

Pushed into 5.1.23-beta

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.

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.