From: Date: October 11 2007 10:38pm
Subject: bk commit into 5.1 tree (davi:1.2594) BUG#29223
List-Archive: http://lists.mysql.com/commits/35411
X-Bug: 29223
Message-Id: <20071011203847.A9C32553F73@moksha.localhost>

Below is the list of changes that have just been committed into a local
5.1 repository of davi. When davi does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-10-11 17:38:40-03:00, davi@stripped +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.

  mysql-test/r/sp-error.result@stripped, 2007-10-11 17:38:36-03:00, davi@stripped +20 -1
    Only allow declaring cursors for SELECT statements to avoid
    possible further confusion/problems.

  mysql-test/t/information_schema.test@stripped, 2007-10-11 17:38:36-03:00, davi@stripped +1 -1
    Only SELECT statements are allowed in cursors.

  mysql-test/t/sp-error.test@stripped, 2007-10-11 17:38:36-03:00, davi@stripped +22 -1
    Add test case for Bug#29223. Non-SELECT statements in cursors now
    yields a parser error.

  sql/sql_yacc.yy@stripped, 2007-10-11 17:38:36-03:00, davi@stripped +3 -15
    Rework DECLARE CURSOR statement to not allow non-SELECT statements.

diff -Nrup a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
--- a/mysql-test/r/sp-error.result	2007-09-10 15:53:15 -03:00
+++ b/mysql-test/r/sp-error.result	2007-10-11 17:38:36 -03:00
@@ -142,7 +142,10 @@ declare c cursor for insert into test.t1
 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` UPG
 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 -Nrup a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
--- a/mysql-test/t/information_schema.test	2007-09-20 06:10:03 -03:00
+++ b/mysql-test/t/information_schema.test	2007-10-11 17:38:36 -03:00
@@ -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 -Nrup a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
--- a/mysql-test/t/sp-error.test	2007-09-10 15:53:15 -03:00
+++ b/mysql-test/t/sp-error.test	2007-10-11 17:38:36 -03:00
@@ -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);
@@ -2177,6 +2177,27 @@ END//
 
 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
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2007-10-07 19:02:45 -03:00
+++ b/sql/sql_yacc.yy	2007-10-11 17:38:36 -03:00
@@ -2540,25 +2540,13 @@ sp_decl:
 sp_cursor_stmt:
           {
             Lex->sphead->reset_lex(YYTHD);
-
-            /*
-             We use statement here just be able to get a better
-              error message. Using 'select' works too, but will then
-             result in a generic "syntax error" if a non-select
-             statement is given.
-            */
           }
-          statement
+          select
           {
             LEX *lex= Lex;
 
-            if (lex->sql_command != SQLCOM_SELECT &&
-               !(sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND))
-            {
-              my_message(ER_SP_BAD_CURSOR_QUERY, ER(ER_SP_BAD_CURSOR_QUERY),
-                         MYF(0));
-              MYSQL_YYABORT;
-            }
+            DBUG_ASSERT(lex->sql_command == SQLCOM_SELECT);
+
             if (lex->result)
             {
               my_message(ER_SP_BAD_CURSOR_SELECT, ER(ER_SP_BAD_CURSOR_SELECT),