bk commit into 5.0 tree (malff:1.2286) BUG#24854

[marc.alff]

Below is the list of changes that have just been committed into a local
5.0 repository of marcsql. When marcsql 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, 2006-12-11 16:59:02-07:00, malff@weblab.(none) +4 -0
  Bug#19194 (Right recursion in parser for CASE causes excessive stack usage,
    limitation)
  Bug#24854 (Mixing Searched Case with Simple Case inside Stored Procedure
    crashes Mysqld)
  
  Implemented code review (19194) comments

  BitKeeper/deleted/.del-sp_stress_case.sh@stripped, 2006-12-11 16:57:06-07:00,
malff@weblab.(none) +0 -0
    Delete: mysql-test/t/sp_stress_case.sh

  mysql-test/r/sp_stress_case.result@stripped, 2006-12-11 16:56:28-07:00, malff@weblab.(none)
+96 -18
    Implemented code review comments : use SQL instead of a shell script to
    generate the code

  mysql-test/t/sp_stress_case.test@stripped, 2006-12-11 16:56:28-07:00, malff@weblab.(none) +84
-25
    Adjusted

  sql/sql_yacc.yy@stripped, 2006-12-11 16:56:28-07:00, malff@weblab.(none) +42 -1
    Added more explicit comments

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	malff
# Host:	weblab.(none)
# Root:	/home/marcsql/TREE/mysql-5.0-19194

--- 1.494/sql/sql_yacc.yy	2006-12-11 16:59:08 -07:00
+++ 1.495/sql/sql_yacc.yy	2006-12-11 16:59:08 -07:00
@@ -100,6 +100,42 @@ void turn_parser_debug_on()
 /**
   Helper action for a case statement (entering the CASE).
   This helper is used for both 'simple' and 'searched' cases.
+  This helper, with the other case_stmt_action_..., is executed when
+  the following SQL code is parsed:
+<pre>
+CREATE PROCEDURE proc_19194_simple(i int)
+BEGIN
+  DECLARE str CHAR(10);
+
+  CASE i
+    WHEN 1 THEN SET str="1";
+    WHEN 2 THEN SET str="2";
+    WHEN 3 THEN SET str="3";
+    ELSE SET str="unknown";
+  END CASE;
+
+  SELECT str;
+END
+</pre>
+  The actions are used to generate the following code:
+<pre>
+SHOW PROCEDURE CODE proc_19194_simple;
+Pos     Instruction
+0       set str@1 NULL
+1       set_case_expr (12) 0 i@0
+2       jump_if_not 5(12) (case_expr@0 = 1)
+3       set str@1 _latin1'1'
+4       jump 12
+5       jump_if_not 8(12) (case_expr@0 = 2)
+6       set str@1 _latin1'2'
+7       jump 12
+8       jump_if_not 11(12) (case_expr@0 = 3)
+9       set str@1 _latin1'3'
+10      jump 12
+11      set str@1 _latin1'unknown'
+12      stmt 0 "SELECT str"
+</pre>
+
   @param lex the parser lex context
 */
 
@@ -110,6 +146,7 @@ void case_stmt_action_case(LEX *lex)
   /*
     BACKPATCH: Creating target label for the jump to
     "case_stmt_action_end_case"
+    (Instruction 12 in the example)
   */
 
   lex->spcont->push_label((char *)"", lex->sphead->instructions());
@@ -179,6 +216,7 @@ void case_stmt_action_when(LEX *lex, Ite
   /*
     BACKPATCH: Registering forward jump from
     "case_stmt_action_when" to "case_stmt_action_then"
+    (jump_if_not from instruction 2 to 5, 5 to 8 ... in the example)
   */
 
   sp->push_backpatch(i, ctx->push_label((char *)"", 0));
@@ -203,13 +241,15 @@ void case_stmt_action_then(LEX *lex)
   /*
     BACKPATCH: Resolving forward jump from
     "case_stmt_action_when" to "case_stmt_action_then"
+    (jump_if_not from instruction 2 to 5, 5 to 8 ... in the example)
   */
 
   sp->backpatch(ctx->pop_label());
 
   /*
     BACKPATCH: Registering forward jump from
-    "case_stmt_action_when" to "case_stmt_action_end_case"
+    "case_stmt_action_then" to "case_stmt_action_end_case"
+    (jump from instruction 4 to 12, 7 to 12 ... in the example)
   */
 
   sp->push_backpatch(i, ctx->last_label());
@@ -227,6 +267,7 @@ void case_stmt_action_end_case(LEX *lex,
   /*
     BACKPATCH: Resolving forward jump from
     "case_stmt_action_then" to "case_stmt_action_end_case"
+    (jump from instruction 4 to 12, 7 to 12 ... in the example)
   */
   lex->sphead->backpatch(lex->spcont->pop_label());
 

--- 1.1/mysql-test/r/sp_stress_case.result	2006-12-11 16:59:08 -07:00
+++ 1.2/mysql-test/r/sp_stress_case.result	2006-12-11 16:59:08 -07:00
@@ -1,42 +1,120 @@
-DROP PROCEDURE IF EXISTS bug_19194_a;
-DROP PROCEDURE IF EXISTS bug_19194_b;
-'Silently creating PROCEDURE bug_19194_a'
-'Silently creating PROCEDURE bug_19194_b'
-CALL bug_19194_a(1);
+DROP PROCEDURE IF EXISTS proc_19194_codegen;
+DROP PROCEDURE IF EXISTS bug_19194_simple;
+DROP PROCEDURE IF EXISTS bug_19194_searched;
+CREATE PROCEDURE proc_19194_codegen(
+IN proc_name VARCHAR(50),
+IN count INTEGER,
+IN simple INTEGER,
+OUT body MEDIUMTEXT)
+BEGIN
+DECLARE code MEDIUMTEXT;
+DECLARE i INT DEFAULT 1;
+SET code = concat("CREATE PROCEDURE ", proc_name, "(i INT)\n");
+SET code = concat(code, "BEGIN\n");
+SET code = concat(code, "  DECLARE str CHAR(10);\n");
+IF (simple)
+THEN
+SET code = concat(code, "  CASE i\n");
+ELSE
+SET code = concat(code, "  CASE\n");
+END IF;
+WHILE (i <= count)
+DO
+IF (simple)
+THEN
+SET code = concat(code, "    WHEN ", i, " THEN SET str=\"", i, "\";\n");
+ELSE
+SET code = concat(code, "    WHEN i=", i, " THEN SET str=\"", i, "\";\n");
+END IF;
+SET i = i + 1;
+END WHILE;
+SET code = concat(code, "    ELSE SET str=\"unknown\";\n");
+SET code = concat(code, "  END CASE;\n");
+SET code = concat(code, "  SELECT str;\n");
+SET code = concat(code, "END\n");
+SET body = code;
+END|
+set @body="";
+call proc_19194_codegen("test_simple", 10, 1, @body);
+select @body;
+@body
+CREATE PROCEDURE test_simple(i INT)
+BEGIN
+  DECLARE str CHAR(10);
+  CASE i
+    WHEN 1 THEN SET str="1";
+    WHEN 2 THEN SET str="2";
+    WHEN 3 THEN SET str="3";
+    WHEN 4 THEN SET str="4";
+    WHEN 5 THEN SET str="5";
+    WHEN 6 THEN SET str="6";
+    WHEN 7 THEN SET str="7";
+    WHEN 8 THEN SET str="8";
+    WHEN 9 THEN SET str="9";
+    WHEN 10 THEN SET str="10";
+    ELSE SET str="unknown";
+  END CASE;
+  SELECT str;
+END
+
+call proc_19194_codegen("test_searched", 10, 0, @body);
+select @body;
+@body
+CREATE PROCEDURE test_searched(i INT)
+BEGIN
+  DECLARE str CHAR(10);
+  CASE
+    WHEN i=1 THEN SET str="1";
+    WHEN i=2 THEN SET str="2";
+    WHEN i=3 THEN SET str="3";
+    WHEN i=4 THEN SET str="4";
+    WHEN i=5 THEN SET str="5";
+    WHEN i=6 THEN SET str="6";
+    WHEN i=7 THEN SET str="7";
+    WHEN i=8 THEN SET str="8";
+    WHEN i=9 THEN SET str="9";
+    WHEN i=10 THEN SET str="10";
+    ELSE SET str="unknown";
+  END CASE;
+  SELECT str;
+END
+
+CALL bug_19194_simple(1);
 str
 1
-CALL bug_19194_a(2);
+CALL bug_19194_simple(2);
 str
 2
-CALL bug_19194_a(1000);
+CALL bug_19194_simple(1000);
 str
 1000
-CALL bug_19194_a(4998);
+CALL bug_19194_simple(4998);
 str
 4998
-CALL bug_19194_a(4999);
+CALL bug_19194_simple(4999);
 str
 4999
-CALL bug_19194_a(9999);
+CALL bug_19194_simple(9999);
 str
 unknown
-CALL bug_19194_b(1);
+CALL bug_19194_searched(1);
 str
 1
-CALL bug_19194_b(2);
+CALL bug_19194_searched(2);
 str
 2
-CALL bug_19194_b(1000);
+CALL bug_19194_searched(1000);
 str
 1000
-CALL bug_19194_b(4998);
+CALL bug_19194_searched(4998);
 str
 4998
-CALL bug_19194_b(4999);
+CALL bug_19194_searched(4999);
 str
 4999
-CALL bug_19194_b(9999);
+CALL bug_19194_searched(9999);
 str
 unknown
-DROP PROCEDURE bug_19194_a;
-DROP PROCEDURE bug_19194_b;
+DROP PROCEDURE proc_19194_codegen;
+DROP PROCEDURE bug_19194_simple;
+DROP PROCEDURE bug_19194_searched;

--- 1.1/mysql-test/t/sp_stress_case.test	2006-12-11 16:59:08 -07:00
+++ 1.2/mysql-test/t/sp_stress_case.test	2006-12-11 16:59:08 -07:00
@@ -1,35 +1,94 @@
-
-# The production code tested is not platform specific,
-# but /bin/sh is needed to run the test case.
-
---source include/not_windows.inc
-
 #
 # Bug#19194 (Right recursion in parser for CASE causes excessive stack
 #   usage, limitation)
 #
 
+# This test takes some time (8 min) in debug builds
+# It's provided as a separate file so that the next line can be uncommented
+# later if needed:
+# -- source include/big_test.inc
+
 --disable_warnings
-DROP PROCEDURE IF EXISTS bug_19194_a;
-DROP PROCEDURE IF EXISTS bug_19194_b;
+DROP PROCEDURE IF EXISTS proc_19194_codegen;
+DROP PROCEDURE IF EXISTS bug_19194_simple;
+DROP PROCEDURE IF EXISTS bug_19194_searched;
 --enable_warnings
 
---exec $MYSQL_TEST_DIR/t/sp_stress_case.sh | $MYSQL_TEST 2>&1
-
-CALL bug_19194_a(1);
-CALL bug_19194_a(2);
-CALL bug_19194_a(1000);
-CALL bug_19194_a(4998);
-CALL bug_19194_a(4999);
-CALL bug_19194_a(9999);
-
-CALL bug_19194_b(1);
-CALL bug_19194_b(2);
-CALL bug_19194_b(1000);
-CALL bug_19194_b(4998);
-CALL bug_19194_b(4999);
-CALL bug_19194_b(9999);
+delimiter |;
 
-DROP PROCEDURE bug_19194_a;
-DROP PROCEDURE bug_19194_b;
+CREATE PROCEDURE proc_19194_codegen(
+  IN proc_name VARCHAR(50),
+  IN count INTEGER,
+  IN simple INTEGER,
+  OUT body MEDIUMTEXT)
+BEGIN
+  DECLARE code MEDIUMTEXT;
+  DECLARE i INT DEFAULT 1;
+
+  SET code = concat("CREATE PROCEDURE ", proc_name, "(i INT)\n");
+  SET code = concat(code, "BEGIN\n");
+  SET code = concat(code, "  DECLARE str CHAR(10);\n");
+
+  IF (simple)
+  THEN
+    SET code = concat(code, "  CASE i\n");
+  ELSE
+    SET code = concat(code, "  CASE\n");
+  END IF;
+
+  WHILE (i <= count)
+  DO
+    IF (simple)
+    THEN
+      SET code = concat(code, "    WHEN ", i, " THEN SET str=\"", i, "\";\n");
+    ELSE
+      SET code = concat(code, "    WHEN i=", i, " THEN SET str=\"", i, "\";\n");
+    END IF;
+
+    SET i = i + 1;
+  END WHILE;
+
+  SET code = concat(code, "    ELSE SET str=\"unknown\";\n");
+  SET code = concat(code, "  END CASE;\n");
+  SET code = concat(code, "  SELECT str;\n");
+
+  SET code = concat(code, "END\n");
+
+  SET body = code;
+END|
+
+delimiter ;|
+
+set @body="";
+call proc_19194_codegen("test_simple", 10, 1, @body);
+select @body;
+call proc_19194_codegen("test_searched", 10, 0, @body);
+select @body;
+
+--disable_query_log
+call proc_19194_codegen("bug_19194_simple", 5000, 1, @body);
+let $proc_body = `select @body`;
+eval $proc_body;
+call proc_19194_codegen("bug_19194_searched", 5000, 1, @body);
+let $proc_body = `select @body`;
+eval $proc_body;
+--enable_query_log
+
+CALL bug_19194_simple(1);
+CALL bug_19194_simple(2);
+CALL bug_19194_simple(1000);
+CALL bug_19194_simple(4998);
+CALL bug_19194_simple(4999);
+CALL bug_19194_simple(9999);
+
+CALL bug_19194_searched(1);
+CALL bug_19194_searched(2);
+CALL bug_19194_searched(1000);
+CALL bug_19194_searched(4998);
+CALL bug_19194_searched(4999);
+CALL bug_19194_searched(9999);
+
+DROP PROCEDURE proc_19194_codegen;
+DROP PROCEDURE bug_19194_simple;
+DROP PROCEDURE bug_19194_searched;