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.

3 files changed, 43 insertions, 3 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
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index 6bcc14d4e49..9ad658645bd 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -953,7 +953,7 @@ BEGIN
   DECLARE col1, col2, col3, col4, col6 CHAR(255);
   DECLARE default_val VARCHAR(65532);
   DECLARE done INT DEFAULT 0;
-  DECLARE cur1 CURSOR FOR SHOW COLUMNS FROM bug23037;
+  DECLARE cur1 CURSOR FOR SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='bug23037';
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
   OPEN cur1;
   FETCH cur1 INTO col1, col2, col3, col4, default_val, col6;
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index c9145859405..ad516f028e6 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -196,7 +196,7 @@ select f(10)|
 
 drop function f|
 
---error 1322
+--error ER_PARSE_ERROR
 create procedure p()
 begin
   declare c cursor for insert into test.t1 values ("foo", 42);
@@ -2179,6 +2179,27 @@ delimiter ;//
 
 
 #
+# Bug#29223 declare cursor c for SHOW .....
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+--delimiter |
+--error ER_PARSE_ERROR
+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 ;
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings