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.


mysql-test/r/sp-error.result:
  Only allow declaring cursors for SELECT statements to avoid
  possible further confusion/problems.
mysql-test/t/information_schema.test:
  Only SELECT statements are allowed in cursors.
mysql-test/t/sp-error.test:
  Add test case for Bug#29223. Non-SELECT statements in cursors now
  yields a parser error.
sql/sql_yacc.yy:
  Rework DECLARE CURSOR statement to not allow non-SELECT statements.

1 files changed, 20 insertions, 1 deletions

diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index bfcd64e89d3..46b8adb85b8 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -142,7 +142,10 @@ declare c cursor for insert into test.t1 values ("foo", 42);
 open c;
 close c;
 end|
-ERROR 42000: Cursor statement must be a SELECT
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into test.t1 values ("foo", 42);
+open c;
+close c;
+end' at line 3
 create procedure p()
 begin
 declare x int;
@@ -1491,3 +1494,19 @@ ALTER DATABASE `#mysql50#upgrade-me` UPGRADE DATA DIRECTORY NAME;
 RETURN 0;
 END//
 ERROR HY000: Can't drop or alter a DATABASE from within another stored routine
+DROP PROCEDURE IF EXISTS p1;
+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|
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHOW TABLES;
+OPEN cur1;
+FETCH cur1 INTO c;
+select c;
+CLOSE cur1;
+END' at line 4