Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

10 files changed, 3384 insertions, 0 deletions

diff --git a/mysql-test/suite/funcs_1/storedproc/cleanup_sp_tb.inc b/mysql-test/suite/funcs_1/storedproc/cleanup_sp_tb.inc
new file mode 100644
index 00000000..bd41f2dc
--- /dev/null
+++ b/mysql-test/suite/funcs_1/storedproc/cleanup_sp_tb.inc
@@ -0,0 +1,10 @@
+--echo
+--echo --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
+--echo --------------------------------------------------------------------------------
+
+# called both to cleanup possibly existing data before and after the SP tests
+
+--disable_warnings
+DROP DATABASE IF EXISTS db_storedproc;
+DROP DATABASE IF EXISTS db_storedproc_1;
+--enable_warnings
diff --git a/mysql-test/suite/funcs_1/storedproc/load_sp_tb.inc b/mysql-test/suite/funcs_1/storedproc/load_sp_tb.inc
new file mode 100644
index 00000000..6f23a559
--- /dev/null
+++ b/mysql-test/suite/funcs_1/storedproc/load_sp_tb.inc
@@ -0,0 +1,104 @@
+--echo
+--echo --source suite/funcs_1/storedproc/load_sp_tb.inc
+--echo --------------------------------------------------------------------------------
+
+# ==============================================================================
+#
+#  This load script can be called multiple times inside a test script because it
+#  first cleans up all objects that will be created.
+#  Therefore the same script is used as it will be used at the end of a test.
+#
+# ==============================================================================
+
+--disable_abort_on_error
+--enable_query_log
+
+# use the same .inc to cleanup before and after the test
+--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
+
+CREATE DATABASE db_storedproc;
+CREATE DATABASE db_storedproc_1;
+
+USE db_storedproc;
+
+--replace_result $engine_type <engine_to_be_tested>
+eval
+create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
+engine = $engine_type;
+--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
+eval
+load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t4.txt' into table t1;
+
+--replace_result $engine_type <engine_to_be_tested>
+eval
+create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
+engine = $engine_type;
+--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
+eval
+load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t4.txt' into table t2;
+
+--replace_result $engine_type <engine_to_be_tested>
+eval
+create table t3(f1 char(20),f2 char(20),f3 integer) engine = $engine_type;
+--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
+eval
+load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t3.txt' into table t3;
+
+--replace_result $engine_type <engine_to_be_tested>
+eval
+create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
+engine = $engine_type;
+--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
+eval
+load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t4.txt' into table t4;
+
+USE db_storedproc_1;
+
+--replace_result $engine_type <engine_to_be_tested>
+eval
+create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
+engine = $engine_type;
+--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
+eval
+load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t4.txt' into table t6;
+
+USE db_storedproc;
+
+--replace_result $engine_type <engine_to_be_tested>
+eval
+create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
+engine = $engine_type;
+--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
+eval
+load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t7.txt' ignore into table t7;
+
+--replace_result $engine_type <engine_to_be_tested>
+eval
+create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
+engine = $engine_type;
+--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
+eval
+load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t7.txt' ignore into table t8;
+
+--replace_result $engine_type <engine_to_be_tested>
+eval
+create table t9(f1 int, f2 char(25), f3 int) engine = $engine_type;
+--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
+eval
+load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t9.txt' into table t9;
+
+--replace_result $engine_type <engine_to_be_tested>
+eval
+create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
+engine = $engine_type;
+--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
+eval
+load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t4.txt' into table t10;
+
+--replace_result $engine_type <engine_to_be_tested>
+eval
+create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
+engine = $engine_type;
+--replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
+eval
+load data infile '$MYSQLTEST_VARDIR/std_data/funcs_1/t4.txt' into table t11;
diff --git a/mysql-test/suite/funcs_1/storedproc/param_check.inc b/mysql-test/suite/funcs_1/storedproc/param_check.inc
new file mode 100644
index 00000000..203187f9
--- /dev/null
+++ b/mysql-test/suite/funcs_1/storedproc/param_check.inc
@@ -0,0 +1,50 @@
+# suite/funcs_1/storedproc/param_check.inc
+#
+# Auxiliary routine to be sourced by
+# suite/funcs_1/t/storedproc.test
+#
+# Purpose:
+#    The assignment of float values to objects of type DECIMAL causes
+#    conversions and in some cases an adjustment of the value to
+#    a border of the value range.
+#    Try to reveal that function and procedure parameters get a similar
+#    mangling of the value like columns.
+#    
+# Variables to be set before sourcing this routine
+#    $test_value  - value to be checked
+#
+# Created:
+# 2008-08-27 mleich
+#
+# Modified:
+# 2008-11-17 pcrews
+# added --disable / --enable_warning statements to minimize differences
+# between platforms (Bug#40177 Test funcs_1.storedproc failing on Pushbuild)
+#
+# TODO:  (After 5.1 GA)
+# 1)  Examine reordering statements in this file to minimize the number of
+#     --disable / --enable_warning statements.  Unsure if performance gains
+#     warrant the working time
+# 2)  We could probably add a comparison of the # of warnings before the
+#     assignment of @v1_proc and @v1_func to the # of warnings after assignment
+#     The difference of these values should be zero
+# Refer to Bug#40177 - http://bugs.mysql.com/bug.php?id=40177 for notes as well
+ 
+eval UPDATE t1_aux SET f1 = NULL;
+# Enforce that all user variables have the same data type and initial value.
+SELECT f1,f1,f1,f1 INTO @v1_tab,@v1_proc,@v2_proc,@v1_func FROM t1_aux;
+
+--disable_warnings
+eval UPDATE t1_aux SET f1 = $test_value;
+--enable_warnings
+SELECT f1 INTO @v1_tab FROM t1_aux;
+--disable_warnings
+eval CALL sproc_1($test_value, @v1_proc);
+eval SET @v1_func = func_1($test_value);
+--enable_warnings
+
+if (`SELECT @v1_tab <> @v1_proc OR @v1_tab <> @v2_proc OR @v1_tab <> @v1_func`)
+{
+   --echo Error: @v1_tab, @v1_proc, @v2_proc, @v1_func are not all equal
+   SELECT @v1_tab, @v1_proc, @v2_proc, @v1_func;
+}
diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_02.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_02.inc
new file mode 100644
index 00000000..2717303d
--- /dev/null
+++ b/mysql-test/suite/funcs_1/storedproc/storedproc_02.inc
@@ -0,0 +1,1666 @@
+#### suite/funcs_1/storedproc/storedproc_02.inc
+#
+--source suite/funcs_1/storedproc/load_sp_tb.inc
+
+# ==============================================================================
+# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
+#
+# 3.1.2 Syntax checks for the stored procedure-specific programming statements
+#       BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:
+#
+#-  1. Ensure that all subclauses that should be supported are supported.
+#-  2. Ensure that all subclauses that should not be supported are disallowed
+#      with an appropriate error message.
+#-  3. Ensure that all supported subclauses are supported only in the
+#      correct order.
+#-  4. Ensure that an appropriate error message is returned if a subclause is
+#      out-of-order in a stored procedure definition.
+#-  5. Ensure that all subclauses that are defined to be mandatory are indeed
+#      required to be mandatory by the MySQL server and tools.
+#-  6. Ensure that any subclauses that are defined to be optional are indeed
+#      treated as optional by the MySQL server and tools.
+#-  7. Ensure that every BEGIN statement is coupled with a terminating
+#      END statement.
+##  8. Ensure that the scope of each BEGIN/END compound statement within a
+#      stored procedure definition is properly applied.
+#-  9. Ensure that the labels enclosing each BEGIN/END compound statement
+#      must match.
+#- 10. Ensure that it is possible to put a beginning label at the start of
+#      a BEGIN/END compound statement without also requiring an ending label
+#      at the end of the same statement.
+#- 11. Ensure that it is not possible to put an ending label at the end of
+#      a BEGIN/END compound statement without also requiring a matching
+#      beginning label at the start of the same statement.
+#- 12. Ensure that every beginning label must end with a colon (:).
+#- 13. Ensure that every beginning label with the same scope must be unique.
+#- 14. Ensure that the variables, cursors, conditions, and handlers declared
+#      for a stored procedure (with the DECLARE statement) may only be
+#      properly defined.
+#- 15. Ensure that the variables, cursors, conditions, and handlers declared for
+#      a stored procedure (with the DECLARE statement) may only be defined in
+#      the correct order.
+#- 16. Ensure that every possible type of variable -- utilizing every data type
+#      definition supported by the MySQL server in combination with both no
+#      DEFAULT subclause and with DEFAULT subclauses that set the variable’s
+#      default value to a range of appropriate values -- may be declared for
+#      a stored procedure.
+#- 17. Ensure that the DECLARE statement can declare multiple variables both
+#      separately and all at once from a variable list.
+#- 18. Ensure that invalid variable declarations are rejected, with an
+#      appropriate error message.
+#- 19. Ensure that every possible type of cursor may be declared for a
+#      stored procedure.
+#- 20. Ensure that invalid cursor declarations are rejected, with an appropriate
+#      error message.
+#- 21. Ensure that every possible type of condition may be declared for
+#      a stored procedure.
+# -22. Ensure that invalid condition declarations are rejected, with an
+#      appropriate error message.
+#- 23. Ensure that every possible type of handler may be declared for a
+#      stored procedure.
+#- 24. Ensure that invalid handler declarations are rejected, with an
+#      appropriate error message.
+#- 25. Ensure that the scope of every variable, cursor, condition, and handler
+#      declared for a stored procedure (with the DECLARE statement) is
+#      properly applied.
+## 26. Ensure that the initial value of every variable declared for a stored
+#      procedure is either NULL or its DEFAULT value, as appropriate.
+#- 27. Ensure that the SET statement can assign a value to every local variable
+#      declared within a stored procedure’s definition, as well as to every
+#      appropriate global server variable.
+#- 28. Ensure that the SET statement can assign values to variables either
+#      separately or to multiple variables in a list.
+#- 29. Ensure that the SET statement may assign only those values to a variable
+#      that are appropriate for that variable’s data type definition.
+## 30. Ensure that, when a stored procedure is called/executed, every variable
+#      always uses the correct value: either the value with which it is
+#      initialized or the value to which it is subsequently SET or otherwise
+#      assigned, as appropriate.
+## 31. Ensure that the SELECT ... INTO statement properly assigns values to the
+#      variables in its variable list.
+## 32. Ensure that a SELECT ... INTO statement that retrieves multiple rows is
+#      rejected, with an appropriate error message.
+## 33. Ensure that a SELECT ... INTO statement that retrieves too many columns
+#      for the number of variables in its variable list is rejected, with an
+#      appropriate error message.
+## 34. Ensure that a SELECT ... INTO statement that retrieves too few columns
+#      for the number of variables in its variable list is rejected, with an
+#      appropriate error message.
+#- 35. Ensure that a SELECT ... INTO statement that retrieves column values
+#      with inappropriate data types for the matching variables in its variable
+#      list is rejected, with an appropriate error message.
+#- 36. Ensure that the DECLARE ... CONDITION FOR statement can declare a
+#      properly-named condition for every possible SQLSTATE and MySQL-specific
+#      error code.
+#- 37. Ensure that no two conditions declared with the same scope may have the
+#      same condition name.
+## 38. Ensure that the scope of every condition declared is properly applied.
+#- 39. Ensure that every SQLSTATE value declared with a DECLARE ... CONDITION
+#      FOR statement is a character string that is 5 characters long.
+#- 40. Ensure that the DECLARE ... CONDITION FOR statement cannot declare a
+#      condition for an invalid SQLSTATE.
+#- 41. Ensure that the DECLARE ... CONDITION FOR statement cannot declare a
+#      condition for the “successful completion SQLSTATE: “00000“.
+#- 42. Ensure that the DECLARE ... HANDLER FOR statement can declare a CONTINUE,
+#      EXIT, and UNDO handler for every condition declared (with a DECLARE ...
+#      CONDITION FOR statement), within the scope of the handler, for a stored
+#      procedure, as well as for every possible SQLSTATE and MySQL-specific
+#      error code, as well as for the predefined conditions SQLWARNING,
+#      NOT FOUND, and SQLEXCEPTION.
+## 43. Ensure that the DECLARE ... HANDLER FOR statement can not declare any
+#      handler for a condition declared outside of the scope of the handler.
+## 44. Ensure that the DECLARE ... HANDLER FOR statement cannot declare a
+#      handler for any invalid, or undeclared, condition.
+## 45. Ensure that the scope of every handler declared is properly applied.
+#- 46. Ensure that, within the same scope, no two handlers may be declared for
+#      the same condition.
+#- 47. Ensure that every SQLSTATE value declared with a DECLARE ... HANDLER FOR
+#      statement is a character string that is 5 characters long.
+#- 48. Ensure that the DECLARE ... HANDLER FOR statement cannot declare a
+#      condition for an invalid SQLSTATE.
+#- 49. Ensure that the DECLARE ... HANDLER FOR statement cannot declare a
+#      condition for the “successful completion SQLSTATE: “00000“.
+## 50. Ensure that a CONTINUE handler allows the execution of the stored
+#      procedure to continue once the handler statement has completed its
+#      own execution (that is, once the handler action statement has been
+#      executed).
+## 51. Ensure that an EXIT handler causes the execution of the stored procedure
+#      to terminate, within its scope, once the handler action statement has
+#      been executed.
+## 52. Ensure that an EXIT handler does not cause the execution of the stored
+#      procedure to terminate outside of its scope.
+#- 53. Ensure that a handler condition of SQLWARNING takes the same action as
+#      a handler condition defined with an SQLSTATE that begins with “01“.
+## 54. Ensure that a handler with a condition defined with an SQLSTATE that
+#      begins with “01“ is always exactly equivalent in action to a
+#      handler with an SQLWARNING condition.
+#- 55. Ensure that a handler condition of NOT FOUND takes the same action as a
+#      handler condition defined with an SQLSTATE that begins with “02“.
+## 56. Ensure that a handler with a condition defined with an SQLSTATE that
+#      begins with “02“ is always exactly equivalent in action to a
+#      handler with a NOT FOUND condition.
+#- 57. Ensure that a handler condition of SQLEXCEPTION takes the same action
+#      as a handler condition defined with an SQLSTATE that begins with
+#      anything other that “01“ or “02“.
+## 58. Ensure that a handler with a condition defined with an SQLSTATE that
+#      begins with anything other that “01“ or “02“ is always
+#      exactly equivalent in action to a handler with an SQLEXCEPTION condition.
+#- 59. Ensure that no two cursors in a stored procedure can have the same name.
+#- 60. Ensure that a cursor declaration may not include a SELECT ... INTO
+#      statement.
+#- 61. Ensure that a cursor declaration that includes an ORDER BY clause may
+#      not be an updatable cursor.
+#- 62. Ensure that OPEN <cursor name> fails unless a cursor with the same name
+#      has already been declared.
+#- 63. Ensure that OPEN <cursor name> fails if the same cursor is currently
+#      already open.
+#- 64. Ensure that FETCH <cursor name> fails unless a cursor with the same name
+#      is already open.
+## 65. Ensure that FETCH <cursor name> returns the first row of the cursor’s
+#      result set the first time FETCH is executed, that it returns each
+#      subsequent row of the cursor’s result set each of the subsequent
+#      times FETCH is executed, and that it returns a NOT FOUND warning if it
+#      is executed after the last row of the cursor’s result set has already
+#      been fetched.
+#- 66. Ensure that FETCH <cursor name> fails with an appropriate error message
+#      if it is executed before the cursor has been opened.
+#- 67. Ensure that FETCH <cursor name> fails with an appropriate error message
+#      if it is executed after the cursor has been closed.
+## 68. Ensure that FETCH <cursor name> fails with an appropriate error message
+#      if the number of columns to be fetched does not match the number of
+#      variables specified by the FETCH statement.
+#- 69. Ensure that FETCH <cursor name> fails with an appropriate error message
+#      if the data type of the column values being fetched are not appropriate
+#      for the matching FETCH variables to which the data is being assigned.
+#- 70. Ensure that CLOSE <cursor name> fails unless a cursor with the same name
+#      is already open.
+#- 71. Ensure that all cursors are closed when a transaction terminates with
+#      a COMMIT statement.
+#- 72. Ensure that all cursors are closed when a transaction terminates with
+#      a ROLLBACK statement.
+#- 73. Ensure that the result set of a cursor that has been closed is not
+#      longer available to the FETCH statement.
+#- 74. Ensure that every cursor declared within a compound statement is closed
+#      when that compound statement ends.
+## 75. Ensure that, for nested compound statements, a cursor that was declared
+#      and opened during an outer level of the statement is not closed when an
+#      inner level of a compound statement ends.
+## 76. Ensure that all cursors operate asensitively, so that there is no
+#      concurrency conflict between cursors operating on the same, or similar,
+#      sets of results during execution of one or more stored procedures.
+# 77.  Ensure that multiple cursors, nested within multiple compound statements
+#      within a stored procedure, always act correctly and return the
+#      expected result.
+#
+# ==============================================================================
+let $message= Section 3.1.2 - Syntax checks for the stored procedure-specific
+programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE:;
+--source include/show_msg80.inc
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.8:;
+--source include/show_msg.inc
+let $message=
+Ensure that the scope of each BEGIN/END compound statement within a stored
+procedure definition is properly applied;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+
+delimiter //;
+SET STATEMENT sql_mode = '' FOR
+CREATE PROCEDURE sp1( )
+begin_label: BEGIN
+   declare x char DEFAULT 'x';
+   declare y char DEFAULT 'y';
+   set x = '1';
+   set y = '2';
+   label1: BEGIN
+      declare x char DEFAULT 'X';
+      declare y char DEFAULT 'Y';
+      SELECT f1, f2 into x, y from t2 limit 1;
+      SELECT '1.1', x, y;
+      label2: BEGIN
+         declare x char default 'a';
+         declare y char default 'b';
+         label3: BEGIN
+            declare x char default 'c';
+            declare y char default 'd';
+            label4: BEGIN
+               declare x char default 'e';
+               declare y char default 'f';
+               label5: BEGIN
+                  declare x char default 'g';
+                  declare y char default 'h';
+                  SELECT 5, x, y;
+               END label5;
+               SELECT 4, x, y;
+            END label4;
+            SELECT 3, x, y;
+         END label3;
+         SELECT 2, x, y;
+      END label2;
+   END label1;
+   set @v1 = x;
+   set @v2 = y;
+   SELECT '1.2', @v1, @v2;
+END begin_label//
+delimiter ;//
+
+CALL sp1();
+
+#cleanup
+DROP PROCEDURE IF EXISTS sp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.26:;
+--source include/show_msg.inc
+let $message=
+Ensure that the initial value of every variable declared for a stored procedure
+is either NULL or its DEFAULT value, as appropriate.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+
+set @v1=0;
+set @v2=0;
+
+delimiter //;
+CREATE PROCEDURE sp1( )
+BEGIN
+   declare x1 char default 'x';
+   declare y1 char;
+   declare x2 tinytext default 'tinytext';
+   declare y2 tinytext;
+   declare x3 datetime default '2005-10-03 12:13:14';
+   declare y3 datetime;
+   declare x4 float default 1.2;
+   declare y4 float;
+   declare x5 blob default 'b';
+   declare y5 blob;
+   declare x6 smallint default 127;
+   declare y6 smallint;
+   SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6;
+END//
+delimiter ;//
+
+CALL sp1();
+
+# cleanup
+DROP PROCEDURE sp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.30:;
+--source include/show_msg.inc
+let $message=
+Ensure that, when a stored procedure is called/executed, every variable always
+uses the correct value: either the value with which it is initialized or the
+value to which it is subsequently SET or otherwise assigned, as appropriate.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT )
+BEGIN
+    declare x integer;
+    declare y integer default 1;
+    set @x = x;
+    set @y = y;
+    set @z = 234;
+    SELECT f1, f2 into @x, @y from t2 where f1='a`' and f2='a`' limit 1;
+    SELECT @x, @y, @z, invar;
+    BEGIN
+      set @x = 2;
+      SELECT @x, @y, @z;
+      SET outvar = @x * invar + @z * @f;
+      SET invar = outvar;
+      BEGIN
+        set @y = null, @z = 'abcd';
+        SELECT @x, @y, @z;
+      END;
+    END;
+END//
+delimiter ;//
+
+SET @invar  = 100;
+SET @outvar = @invar;
+SET @f      = 10;
+
+SELECT @x, @y, @z, @invar, @outvar;
+
+CALL sp1( @invar, @outvar );
+
+SELECT @x, @y, @z, @invar, @outvar;
+
+# cleanup
+DROP PROCEDURE sp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.31:;
+--source include/show_msg.inc
+let $message=
+Ensure that the SELECT ... INTO statement properly assigns values to the
+variables in its variable list.;
+--source include/show_msg80.inc
+# also tested in a lot of other testcases
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp1( )
+BEGIN
+   declare x integer; declare y integer;
+   set @x=x;
+   set @y=y;
+   SELECT f4, f3 into @x, @y from t2 where f4=-5000 and f3='1000-01-01' limit 1;
+   SELECT @x, @y;
+END//
+delimiter ;//
+
+CALL sp1();
+
+# cleanup 3.1.2.31
+DROP PROCEDURE sp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.32:;
+--source include/show_msg.inc
+let $message=
+Ensure that a SELECT ... INTO statement that retrieves multiple rows is
+rejected, with an appropriate error message.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp1( )
+BEGIN
+   declare x integer; declare y integer;
+   set @x=x;
+   set @y=y;
+   SELECT f4, f3 into @x, @y from t2;
+END//
+delimiter ;//
+
+# Error: SQLSTATE: 42000 (ER_TOO_MANY_ROWS)
+#        Message: Result consisted of more than one row
+--error ER_TOO_MANY_ROWS
+CALL sp1();
+
+# cleanup 3.1.2.32
+DROP PROCEDURE sp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.33:;
+--source include/show_msg.inc
+let $message=
+Ensure that a SELECT ... INTO statement that retrieves too many columns for the
+number of variables in its variable list is rejected, with an appropriate error
+message.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp1( )
+BEGIN
+    declare x integer; declare y integer;
+    set @x=x;
+    set @y=y;
+    SELECT f4, f3, f2, f1 into @x, @y from t2;
+END//
+delimiter ;//
+
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
+CALL sp1();
+
+# cleanup 3.1.2.33
+DROP PROCEDURE sp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.34:;
+--source include/show_msg.inc
+let $message=
+Ensure that a SELECT ... INTO statement that retrieves too few columns for the
+number of variables in its variable list is rejected, with an appropriate error
+message.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp1( )
+BEGIN
+    declare x integer; declare y integer; declare z integer;
+    set @x=x;
+    set @y=y;
+    set @z=z;
+    SELECT f4 into @x, @y, @z from t2;
+END//
+delimiter ;//
+
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
+CALL sp1();
+
+# cleanup 3.1.2.34
+DROP PROCEDURE sp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.38:;
+--source include/show_msg.inc
+let $message=
+Ensure that the scope of every condition declared is properly applied.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS h1;
+DROP TABLE IF EXISTS res_t1;
+--enable_warnings
+
+create table res_t1(w char unique, x char);
+
+insert into res_t1 values('a', 'b');
+
+# Error: SQLSTATE: 20000 (ER_SP_CASE_NOT_FOUND)
+#        Message: Case not found for CASE statement
+# Error: SQLSTATE: 23000 (ER_DUP_KEY)
+#        Message: Can't write; duplicate key in table '%s'
+
+delimiter //;
+CREATE PROCEDURE h1 ()
+BEGIN
+   declare x1, x2, x3, x4, x5, x6 int default 0;
+   SELECT '-1-', x1, x2, x3, x4, x5, x6;
+   BEGIN
+      declare condname condition for sqlstate '23000';
+      declare continue handler for condname set x5 = 1;
+      set x6 = 0;
+      insert into res_t1 values ('a', 'b');
+      set x6 = 1;
+      SELECT '-2-', x1, x2, x3, x4, x5, x6;
+   END;
+   begin1_label: BEGIN
+      BEGIN
+         declare condname condition for sqlstate '20000';
+         declare continue handler for condname set x1 = 1;
+         set x2 = 0;
+         case x2
+            when 1 then set x2=10;
+            when 2 then set x2=11;
+         END case;
+         set x2 = 1;
+         SELECT '-3-', x1, x2, x3, x4, x5, x6;
+         begin2_label: BEGIN
+            BEGIN
+               declare condname condition for sqlstate '23000';
+               declare exit handler for condname set x3 = 1;
+               set x4= 1;
+               SELECT '-4a', x1, x2, x3, x4, x5, x6;
+               insert into res_t1 values ('a', 'b');
+               set x4= 2;
+               SELECT '-4b', x1, x2, x3, x4, x5, x6;
+            END;
+            SELECT '-5-', x1, x2, x3, x4, x5, x6;
+         END begin2_label;
+         SELECT '-6-', x1, x2, x3, x4, x5, x6;
+      END;
+      SELECT '-7-', x1, x2, x3, x4, x5, x6;
+   END begin1_label;
+   SELECT 'END', x1, x2, x3, x4, x5, x6;
+END//
+delimiter ;//
+
+CALL h1();
+
+# and a 2nd test
+--disable_warnings
+DROP TABLE IF EXISTS tnull;
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+
+CREATE TABLE tnull(f1 int);
+
+delimiter //;
+CREATE PROCEDURE sp1()
+BEGIN
+    declare cond1 condition for sqlstate '42S02';
+    declare continue handler for cond1 set @var2 = 1;
+    BEGIN
+      declare cond1 condition for sqlstate '23000';
+      declare continue handler for cond1 set @var2 = 1;
+    END;
+          insert into tnull values(1);
+END//
+delimiter ;//
+
+CALL sp1();
+
+# cleanup 3.1.2.38
+DROP PROCEDURE h1;
+drop table res_t1;
+DROP PROCEDURE sp1;
+DROP TABLE tnull;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.43:;
+--source include/show_msg.inc
+let $message=
+Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler
+for a condition declared outside of the scope of the handler.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS h1;
+DROP PROCEDURE IF EXISTS h2;
+drop table IF EXISTS res_t1;
+--enable_warnings
+
+create table res_t1(w char unique, x char);
+insert into res_t1 values ('a', 'b');
+
+delimiter //;
+--error ER_SP_COND_MISMATCH
+CREATE PROCEDURE h1 ()
+BEGIN
+   declare x1, x2, x3, x4, x5, x6 int default 0;
+   BEGIN
+      declare cond_1 condition for sqlstate '23000';
+      declare continue handler for cond_1 set x5 = 1;
+      BEGIN
+         declare cond_2 condition for sqlstate '20000';
+         declare continue handler for cond_1 set x1 = 1;
+         BEGIN
+            declare continue handler for cond_2 set x3 = 1;
+            set x2 = 1;
+         END;
+         set x6 = 0;
+      END;
+      BEGIN
+         declare continue handler for cond_1 set x1 = 1;
+         BEGIN
+            declare continue handler for cond_2 set x3 = 1;
+            set x2 = 1;
+         END;
+         set x6 = 0;
+      END;
+   END;
+   SELECT x1, x2, x3, x4, x5, x6;
+END//
+
+CREATE PROCEDURE h2 ()
+BEGIN
+   declare x1, x2, x3, x4, x5, x6 int default 0;
+   BEGIN
+      declare condname condition for sqlstate '23000';
+      declare continue handler for condname set x5 = 1;
+      BEGIN
+         declare condname condition for sqlstate '20000';
+         declare continue handler for condname set x1 = 1;
+         BEGIN
+            declare condname condition for sqlstate '42000';
+            declare continue handler for condname set x3 = 1;
+            set x6 = 0;
+            insert into res_t1 values ('a', 'b');
+            set x6 = 1;
+            set x4= 0;
+            CALL sp1();
+            set x4= 1;
+            set x2 = 0;
+            case x2
+               when 1 then set x2=10;
+               when 2 then set x2=11;
+            END case;
+            set x2 = 1;
+         END;
+         set x2 = 0;
+         case x2
+            when 1 then set x2=10;
+            when 2 then set x2=11;
+         END case;
+         set x2 = 1;
+         set x6 = 0;
+         insert into res_t1 values ('a', 'b');
+         set x6 = 1;
+      END;
+   END;
+   SELECT x1, x2, x3, x4, x5, x6;
+END//
+delimiter ;//
+
+CALL h2();
+SELECT * FROM res_t1;
+
+# cleanup 3.1.2.43
+DROP PROCEDURE h2;
+drop table res_t1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.44:;
+--source include/show_msg.inc
+let $message=
+Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for
+any invalid, or undeclared, condition.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS h1;
+--enable_warnings
+
+delimiter //;
+# Error: SQLSTATE: 42000 (ER_SP_COND_MISMATCH)
+#        Message: Undefined CONDITION: %s
+--error ER_SP_COND_MISMATCH
+CREATE PROCEDURE h1 ()
+BEGIN
+   declare x1, x2, x3, x4, x5, x6 int default 0;
+   BEGIN
+      declare condname1 condition for sqlstate '23000';
+      BEGIN
+         declare condname2 condition for sqlstate '20000';
+         declare continue handler for condname1 set x3 = 1;
+         declare continue handler for condname2 set x1 = 1;
+      END;
+   END;
+   BEGIN
+      declare condname3 condition for sqlstate '42000';
+      declare continue handler for condname1 set x3 = 1;
+      declare continue handler for condname2 set x5 = 1;
+      declare continue handler for condname3 set x1 = 1;
+   END;
+END//
+
+# Error: SQLSTATE: 42000 (ER_PARSE_ERROR)
+#        Message: %s near '%s' at line %d
+--error ER_PARSE_ERROR
+CREATE PROCEDURE h1 ()
+BEGIN
+   DECLARE x1 INT DEFAULT 0;
+   BEGIN
+      DECLARE condname1 CONDITION CHECK SQLSTATE '23000';
+   END;
+   DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
+END//
+
+# Error: SQLSTATE: 42000 (ER_SP_BAD_SQLSTATE)
+#        Message: Bad SQLSTATE: '%s'
+--error ER_SP_BAD_SQLSTATE
+CREATE PROCEDURE h1 ()
+BEGIN
+   DECLARE x1 INT DEFAULT 0;
+   BEGIN
+      DECLARE condname1 CONDITION FOR SQLSTATE 'qwert';
+   END;
+   DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1;
+END//
+delimiter ;//
+
+# cleanup 3.1.2.44
+#DROP PROCEDURE h1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.45 + 3.1.2.50:;
+--source include/show_msg.inc
+let $message=
+45. Ensure that the scope of every handler declared is properly applied.
+50. Ensure that a CONTINUE handler allows the execution of the stored procedure
+.   to continue once the handler statement has completed its own execution (that
+.   is, once the handler action statement has been executed).;
+--source include/show_msg80.inc
+
+# RefMan: For an EXIT handler, execution of the current BEGIN...END compound
+#         statement is terminated.
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p1undo;
+DROP PROCEDURE IF EXISTS h1;
+DROP PROCEDURE IF EXISTS sp1;
+drop table IF EXISTS res_t1;
+--enable_warnings
+
+--echo ==> 'UNDO' is still not supported.
+delimiter //;
+--error ER_PARSE_ERROR
+create procedure p1undo ()
+begin
+   declare undo handler for sqlexception select '1';
+   select * from tqq;
+   SELECT 'end of 1';
+end;//
+
+create procedure p1 ()
+begin
+   declare exit handler for sqlexception select 'exit handler 1';
+   begin
+      declare exit handler for sqlexception select 'exit handler 2';
+      begin
+         declare continue handler for sqlexception select 'continue handler 3';
+         drop table if exists tqq;
+         select * from tqq;
+         SELECT 'end of BEGIN/END 3';
+      end;
+      drop table if exists tqq;
+      select * from tqq;
+      SELECT 'end of BEGIN/END 2';
+   end;
+   select * from tqq;
+   SELECT 'end of BEGIN/END 1';
+end;//
+
+call p1()//
+delimiter ;//
+
+create table res_t1(w char unique, x char);
+insert into res_t1 values ('a', 'b');
+
+delimiter //;
+CREATE PROCEDURE h1 ()
+BEGIN
+   declare x1, x2, x3, x4, x5, x6 int default 0;
+   BEGIN
+      declare continue handler for sqlstate '23000' set x5 = 1;
+      insert into res_t1 values ('a', 'b');
+      set x6 = 1;
+   END;
+   begin1_label: BEGIN
+      BEGIN
+         declare continue handler for sqlstate '23000' set x1 = 1;
+         insert into res_t1 values ('a', 'b');
+         set x2 = 1;
+         begin2_label: BEGIN
+            BEGIN
+               declare exit handler for sqlstate '23000' set x3 = 1;
+               set x4= 1;
+               insert into res_t1 values ('a', 'b');
+               set x4= 0;
+            END;
+         END begin2_label;
+      END;
+   END begin1_label;
+   SELECT x1, x2, x3, x4, x5, x6;
+END//
+delimiter ;//
+
+CALL h1();
+
+--echo This will fail, SQLSTATE 00000 is not allowed
+--ERROR ER_SP_BAD_SQLSTATE
+delimiter //;
+CREATE PROCEDURE sp1()
+   begin1_label:BEGIN
+      declare exit handler for sqlstate '00000' set @var1 = 5;
+      set @var2 = 6;
+      begin2_label:BEGIN
+         declare continue handler for sqlstate '00000' set @var3 = 7;
+         set @var4 = 8;
+         SELECT @var3, @var4;
+      END begin2_label;
+      SELECT @var1, @var2;
+   END begin1_label//
+delimiter ;//
+
+--echo Verify SP wasn't created
+--ERROR ER_SP_DOES_NOT_EXIST
+CALL sp1();
+
+# cleanup 3.1.2.45+50
+DROP PROCEDURE p1;
+DROP PROCEDURE h1;
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+DROP TABLE res_t1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.50:;
+--source include/show_msg.inc
+
+# Testcase: Ensure that a continue handler allows the execution of the stored procedure
+#            to continue once the handler statement has completed its own execution
+#            (that is, once the handler action statement has been executed).
+
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+DROP PROCEDURE IF EXISTS sp2;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp1 (x int, y int)
+BEGIN
+    set @y=0;
+END//
+delimiter ;//
+
+delimiter //;
+CREATE PROCEDURE sp2 ()
+BEGIN
+   declare continue handler for sqlstate '42000' set @x2 = 1;
+   set @x=1;
+   SELECT @x2;
+   CALL sp1(1);
+   set @x=2;
+   SELECT @x2, @x;
+END//
+delimiter ;//
+
+CALL sp2();
+
+# cleanup
+DROP PROCEDURE sp1;
+DROP PROCEDURE sp2;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.2.2.51:;
+--source include/show_msg.inc
+let $message=
+Ensure that an EXIT handler causes the execution of the stored procedure to
+terminate, within its scope, once the handler action statement has been
+executed.;
+--source include/show_msg80.inc
+# also tested in 3.1.2.45
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+DROP PROCEDURE IF EXISTS sp2;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp1 (x int, y int)
+BEGIN
+    set @x=0;
+END//
+delimiter ;//
+
+delimiter //;
+CREATE PROCEDURE sp2 ()
+BEGIN
+   declare exit handler for sqlstate '42000' set @x2 = 1;
+   set @x2=0;
+   set @x=1;
+   SELECT '-1-', @x2, @x;
+   CALL sp1(1);
+   SELECT '-2-', @x2, @x;
+   set @x=2;
+END//
+delimiter ;//
+
+# Error: SQLSTATE: 42000 (ER_SP_WRONG_NO_OF_ARGS)
+#        Message: Incorrect number of arguments for %s %s; expected %u, got %u
+--error ER_SP_WRONG_NO_OF_ARGS
+CALL sp1(1);
+CALL sp2();
+SELECT '-3-', @x2, @x;
+
+# cleanup 3.1.2.51
+DROP PROCEDURE sp1;
+DROP PROCEDURE sp2;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.52:;
+--source include/show_msg.inc
+let $message=
+Ensure that an EXIT handler does not cause the execution of the stored procedure
+to terminate outside of its scope.;
+--source include/show_msg80.inc
+# tested also above in
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+DROP PROCEDURE IF EXISTS sp2;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp1 (x int, y int)
+BEGIN
+    set @x=0;
+END//
+delimiter ;//
+
+delimiter //;
+CREATE PROCEDURE sp2()
+BEGIN
+   declare continue handler for sqlstate '42000' set @x2 = 2;
+   set @x2 = 1;
+   set @x =20;
+   SELECT '-1-', @x2, @x;
+   BEGIN
+      declare exit handler for sqlstate '42000' set @x2 = 11;
+      SELECT '-2-', @x2, @x;
+      CALL sp1(1);
+      SELECT '-3a', @x2, @x;
+      set @x=21;
+      SELECT '-3b', @x2, @x;
+   END;
+   set @x=22;
+   SELECT '-4-', @x2, @x;
+END//
+delimiter ;//
+
+CALL sp2();
+
+# cleanup 3.1.2.52
+DROP PROCEDURE sp1;
+DROP PROCEDURE sp2;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.54:;
+--source include/show_msg.inc
+let $message=
+Ensure that a handler with a condition defined with an SQLSTATE that begins with
+“01“ is always exactly equivalent in action to a handler with an SQLWARNING
+condition.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp0;
+DROP PROCEDURE IF EXISTS sp1;
+DROP PROCEDURE IF EXISTS sp2;
+DROP PROCEDURE IF EXISTS sp3;
+DROP PROCEDURE IF EXISTS sp4;
+DROP TABLE IF EXISTS temp;
+--enable_warnings
+
+CREATE TABLE temp( f1 CHAR, f2 CHAR);
+
+delimiter //;
+# 0 - without handler
+SET STATEMENT sql_mode = '' FOR
+CREATE PROCEDURE sp0()
+BEGIN
+   set @done=0;
+   set @x=0;
+   insert into temp values('xxx', 'yy');
+   set @x=1;
+END//
+
+# 1st one with SQLSTATE + CONTINUE
+SET STATEMENT sql_mode = '' FOR
+CREATE PROCEDURE sp1()
+BEGIN
+   declare continue handler for sqlstate '01000' set @done = 1;
+   set @done=0;
+   set @x=0;
+   insert into temp values('xxx', 'yy');
+   set @x=1;
+END//
+
+# 2nd one with SQLWARNING + CONTINUE
+SET STATEMENT sql_mode = '' FOR
+CREATE PROCEDURE sp2()
+BEGIN
+   declare continue handler for sqlwarning set @done = 1;
+   set @done=0;
+   set @x=0;
+   insert into temp values('xxx', 'yy');
+   set @x=1;
+END//
+
+# 3 with SQLSTATE + EXIT
+SET STATEMENT sql_mode = '' FOR
+CREATE PROCEDURE sp3()
+BEGIN
+   declare exit handler for sqlstate '01000' set @done = 1;
+   set @done=0;
+   set @x=0;
+   insert into temp values('xxx', 'yy');
+   set @x=1;
+END//
+
+# 4 with SQLWARNING + EXIT
+SET STATEMENT sql_mode = '' FOR
+CREATE PROCEDURE sp4()
+BEGIN
+   declare exit handler for sqlwarning set @done = 1;
+   set @done=0;
+   set @x=0;
+   insert into temp values('xxx', 'yy');
+   set @x=1;
+END//
+delimiter ;//
+
+INSERT INTO temp VALUES('0', NULL);
+CALL sp0();
+SELECT @done, @x;
+
+INSERT INTO temp VALUES('1', NULL);
+CALL sp1();
+SELECT @done, @x;
+
+INSERT INTO temp VALUES('2', NULL);
+CALL sp2();
+SELECT @done, @x;
+
+INSERT INTO temp VALUES('3', NULL);
+CALL sp3();
+SELECT @done, @x;
+
+INSERT INTO temp VALUES('4', NULL);
+CALL sp4();
+SELECT @done, @x;
+
+SELECT * FROM temp;
+
+# cleanup 3.1.2.54
+DROP PROCEDURE sp1;
+DROP PROCEDURE sp2;
+DROP PROCEDURE sp3;
+DROP PROCEDURE sp4;
+DROP TABLE temp;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.56:;
+--source include/show_msg.inc
+let $message=
+Ensure that a handler with a condition defined with an SQLSTATE that begins with
+“02“ is always exactly equivalent in action to a handler with a NOT FOUND
+condition.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp0;
+DROP PROCEDURE IF EXISTS sp1;
+DROP PROCEDURE IF EXISTS sp2;
+DROP PROCEDURE IF EXISTS sp3;
+DROP PROCEDURE IF EXISTS sp4;
+--enable_warnings
+
+delimiter //;
+# 0 - wihtout handler
+CREATE PROCEDURE sp0()
+BEGIN
+   DECLARE f1_value CHAR(20);
+   DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
+   SET @done = 0;
+   SET @x = 0;
+   OPEN cur1;
+   FETCH cur1 INTO f1_value;
+   SET @x = 1;
+   FETCH cur1 INTO f1_value;
+   SET @x = 2;
+   CLOSE cur1;
+END//
+
+# 1st one with SQLSTATE + CONTINUE
+CREATE PROCEDURE sp1()
+BEGIN
+   DECLARE f1_value CHAR(20);
+   DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
+   declare continue handler for sqlstate '02000' set @done = 1;
+   SET @done = 0;
+   SET @x = 0;
+   OPEN cur1;
+   FETCH cur1 INTO f1_value;
+   SET @x = 1;
+   FETCH cur1 INTO f1_value;
+   SET @x = 2;
+   CLOSE cur1;
+END//
+
+# 2nd one with NOT FOUND + CONTINUE
+CREATE PROCEDURE sp2()
+BEGIN
+   DECLARE f1_value CHAR(20);
+   DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
+   declare continue handler for not found set @done = 1;
+   SET @done = 0;
+   SET @x = 0;
+   OPEN cur1;
+   FETCH cur1 INTO f1_value;
+   SET @x = 1;
+   FETCH cur1 INTO f1_value;
+   SET @x = 2;
+   CLOSE cur1;
+END//
+
+# 3 with SQLSTATE + EXIT
+CREATE PROCEDURE sp3()
+BEGIN
+   DECLARE f1_value CHAR(20);
+   DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
+   declare exit handler for sqlstate '02000' set @done = 1;
+   SET @done = 0;
+   SET @x = 0;
+   OPEN cur1;
+   FETCH cur1 INTO f1_value;
+   SET @x = 1;
+   FETCH cur1 INTO f1_value;
+   SET @x = 2;
+   CLOSE cur1;
+END//
+
+# 4 with NOT FOUND + EXIT
+CREATE PROCEDURE sp4()
+BEGIN
+   DECLARE f1_value CHAR(20);
+   DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
+   declare exit handler for not found set @done = 1;
+   SET @done = 0;
+   SET @x = 0;
+   OPEN cur1;
+   FETCH cur1 INTO f1_value;
+   SET @x = 1;
+   FETCH cur1 INTO f1_value;
+   SET @x = 2;
+   CLOSE cur1;
+END//
+delimiter ;//
+
+--error ER_SP_FETCH_NO_DATA
+CALL sp0();
+SELECT @done, @x;
+
+CALL sp1();
+SELECT @done, @x;
+
+CALL sp2();
+SELECT @done, @x;
+
+CALL sp3();
+SELECT @done, @x;
+
+CALL sp4();
+SELECT @done, @x;
+
+# cleanup 3.1.2.56
+DROP PROCEDURE sp0;
+DROP PROCEDURE sp1;
+DROP PROCEDURE sp2;
+DROP PROCEDURE sp3;
+DROP PROCEDURE sp4;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.58:;
+--source include/show_msg.inc
+let $message=
+Ensure that a handler with a condition defined with an SQLSTATE that begins with
+anything other that “01“ or “02“ is always exactly equivalent in action to a
+handler with an SQLEXCEPTION condition.;
+--source include/show_msg80.inc
+
+# Error: SQLSTATE: 20000 (ER_SP_CASE_NOT_FOUND)
+#        Message: Case not found for CASE statement
+# Error: SQLSTATE: 21000 (ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT)
+#        Message: The used SELECT statements have a different number of columns
+# Error: SQLSTATE: 24000 (ER_SP_CURSOR_NOT_OPEN)
+#        Message: Cursor is not open
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp0;
+DROP PROCEDURE IF EXISTS sp1;
+DROP PROCEDURE IF EXISTS sp2;
+DROP PROCEDURE IF EXISTS sp3;
+DROP PROCEDURE IF EXISTS sp4;
+--enable_warnings
+
+delimiter //;
+# 0 - without handler
+CREATE PROCEDURE sp0()
+BEGIN
+   DECLARE f1_value CHAR(20);
+   DECLARE cv INT DEFAULT 0;
+   DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
+   SET @x = 1;
+   CASE cv
+      WHEN 2 THEN SET @x = 2;
+      WHEN 3 THEN SET @x = 3;
+   END case;
+   SET @x = 4;
+   SELECT f1, f2 FROM t2
+   UNION
+   SELECT f1, f2,3 FROM t2;
+   SET @x = 5;
+   FETCH cur1 INTO f1_value;
+   SET @x = 6;
+END//
+
+# 1 - SQLSTATEs - CONTINUE
+CREATE PROCEDURE sp1()
+BEGIN
+   DECLARE f1_value CHAR(20);
+   DECLARE cv INT DEFAULT 0;
+   DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
+   DECLARE continue HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
+   DECLARE continue HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
+   DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
+   SET @x = 1;
+   CASE cv
+      WHEN 2 THEN SET @x = 2;
+      WHEN 3 THEN SET @x = 3;
+   END case;
+   SET @x = 4;
+   SELECT f1, f2 FROM t2
+   UNION
+   SELECT f1, f2,3 FROM t2;
+   SET @x = 5;
+   FETCH cur1 INTO f1_value;
+   SET @x = 6;
+END//
+
+# 2 - SQLEXCEPTION matches 2 of 3 conditions - CONTINUE
+CREATE PROCEDURE sp2()
+BEGIN
+   DECLARE f1_value CHAR(20);
+   DECLARE cv INT DEFAULT 0;
+   DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
+   DECLARE continue HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
+   DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
+   SET @x = 1;
+   CASE cv
+      WHEN 2 THEN SET @x = 2;
+      WHEN 3 THEN SET @x = 3;
+   END case;
+   SET @x = 4;
+   SELECT f1, f2 FROM t2
+   UNION
+   SELECT f1, f2,3 FROM t2;
+   SET @x = 5;
+   FETCH cur1 INTO f1_value;
+   SET @x = 6;
+END//
+
+# 3 - SQLSTATEs - EXIT
+CREATE PROCEDURE sp3()
+BEGIN
+   DECLARE f1_value CHAR(20);
+   DECLARE cv INT DEFAULT 0;
+   DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
+   DECLARE EXIT HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE';
+   DECLARE EXIT HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE';
+   DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
+   SET @x = 1;
+   CASE cv
+      WHEN 2 THEN SET @x = 2;
+      WHEN 3 THEN SET @x = 3;
+   END case;
+   SET @x = 4;
+   SELECT f1, f2 FROM t2
+   UNION
+   SELECT f1, f2,3 FROM t2;
+   SET @x = 5;
+   FETCH cur1 INTO f1_value;
+   SET @x = 6;
+END//
+
+# 4 - SQLEXCEPTION matches 2 of 3 conditions - EXIT
+CREATE PROCEDURE sp4()
+BEGIN
+   DECLARE f1_value CHAR(20);
+   DECLARE cv INT DEFAULT 0;
+   DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1;
+   DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE';
+   DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE';
+   SET @x = 1;
+   CASE cv
+      WHEN 2 THEN SET @x = 2;
+      WHEN 3 THEN SET @x = 3;
+   END case;
+   SET @x = 4;
+   SELECT f1, f2 FROM t2
+   UNION
+   SELECT f1, f2,3 FROM t2;
+   SET @x = 5;
+   FETCH cur1 INTO f1_value;
+   SET @x = 6;
+   CLOSE cur1;
+END//
+delimiter ;//
+
+CALL sp0();
+SELECT '-0-', @x;
+
+CALL sp1();
+SELECT '-1-', @x;
+
+CALL sp2();
+SELECT '-2-', @x;
+
+CALL sp3();
+SELECT '-3-', @x;
+
+CALL sp4();
+SELECT '-4-', @x;
+
+# cleanup 3.1.2.58
+DROP PROCEDURE sp0;
+DROP PROCEDURE sp1;
+DROP PROCEDURE sp2;
+DROP PROCEDURE sp3;
+DROP PROCEDURE sp4;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.65:;
+--source include/show_msg.inc
+let $message=
+Ensure that FETCH <cursor name> returns the first row of the cursor_s result set
+the first time FETCH is executed, that it returns each subsequent row of the
+cursor_s result set each of the subsequent times FETCH is executed, and that it
+returns a NOT FOUND warning if it is executed after the last row of the cursor_s
+result set has already been fetched.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+DROP TABLE IF EXISTS temp;
+--enable_warnings
+
+CREATE TABLE temp(
+   cnt INT,
+   f1 CHAR(20),
+   f2 CHAR(20),
+   f3 INT,
+   f4 CHAR(20),
+   f5 INT);
+
+INSERT INTO temp VALUES(0, 'onip', 'abc', 8760, 'xyz', 10);
+
+# NOT used: declare continue handler for sqlstate '02000' set proceed=0;
+# --> warning is shown when procedure is executed.
+delimiter //;
+CREATE PROCEDURE sp1( )
+BEGIN
+   declare proceed int default 1;
+   declare count integer default 1;
+   declare f1_value char(20);
+   declare f2_value char(20);
+   declare f5_value char(20);
+   declare f4_value integer;
+   declare f6_value integer;
+   declare cur1 cursor for SELECT f1, f2, f4, f5, f6 from t2
+                where f4 >=-5000 order by f4 limit 3;
+   open cur1;
+   while proceed do
+      SELECT count AS 'loop';
+      fetch cur1 into f1_value, f2_value, f4_value, f5_value, f6_value;
+      insert into temp values (count, f1_value, f2_value, f4_value, f5_value, f6_value);
+      set count = count + 1;
+   END while;
+END//
+delimiter ;//
+
+--error ER_SP_FETCH_NO_DATA
+CALL sp1();
+
+SELECT * FROM temp;
+
+# cleanup 3.1.2.65
+DROP TABLE temp;
+DROP PROCEDURE sp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.68:;
+--source include/show_msg.inc
+let $message=
+Ensure that FETCH <cursor name> fails with an appropriate error message if the
+number of columns to be fetched does not match the number of variables specified
+by the FETCH statement.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+DROP PROCEDURE IF EXISTS sp2;
+--enable_warnings
+
+delimiter //;
+--echo --> not enough columns in FETCH statement
+CREATE PROCEDURE sp1( )
+BEGIN
+   declare newf1 char(20);
+   declare cur1 cursor for SELECT f1, f2 from t2 limit 10;
+   declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
+   BEGIN
+      open cur1;
+      fetch cur1 into newf1;
+      SELECT newf1;
+      close cur1;
+   END;
+END//
+
+--echo --> too many columns in FETCH statement
+CREATE PROCEDURE sp2( )
+BEGIN
+   declare newf1 char(20);
+   declare newf2 char(20);
+   declare cur1 cursor for SELECT f1 from t2 limit 10;
+   declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS '';
+   BEGIN
+      open cur1;
+      fetch cur1 into newf1, newf2;
+      SELECT newf1, newf2;
+      close cur1;
+   END;
+END//
+delimiter ;//
+
+--echo --> not enough columns in FETCH statement
+--error ER_SP_WRONG_NO_OF_FETCH_ARGS
+CALL sp1();
+
+--echo --> too many columns in FETCH statement
+--error ER_SP_WRONG_NO_OF_FETCH_ARGS
+CALL sp2();
+
+# cleanup 3.1.2.68
+DROP PROCEDURE sp1;
+DROP PROCEDURE sp2;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.75:;
+--source include/show_msg.inc
+let $message=
+Ensure that, for nested compound statements, a cursor that was declared and
+opened during an outer level of the statement is not closed when an inner level
+of a compound statement ends.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS temp1;
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+
+create table temp1( f0 char(20), f1 char(20), f2 char(20), f3 int, f4 char(20) );
+
+# Error: SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
+#        Message: No data to FETCH
+
+SELECT f1, f2, f4, f5 from t2 order by f4;
+
+delimiter //;
+CREATE PROCEDURE sp1( )
+BEGIN
+   declare count integer;
+   declare from0 char(20);
+   declare newf1 char(20);
+   declare newf2 char(20);
+   declare newf5 char(20);
+   declare newf4 integer;
+   declare cur1 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5;
+   declare cur2 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5;
+   open cur1;
+   open cur2;
+   BEGIN
+      declare continue handler for sqlstate '02000' set count = 1;
+      fetch cur1 into newf1, newf2, newf4, newf5;
+      SELECT '-1-', count, newf1, newf2, newf4, newf5;
+      insert into temp1 values ('cur1_out', newf1, newf2, newf4, newf5);
+      set count = 4;
+      BEGIN
+         while count > 0 do
+            fetch cur1 into newf1, newf2, newf4, newf5;
+            SELECT '-2-', count, newf1, newf2, newf4, newf5;
+            set count = count - 1;
+         END while;
+         SELECT '-3-', count, newf1, newf2, newf4, newf4;
+      END;
+      BEGIN
+         fetch cur1 into newf1, newf2, newf4, newf5;
+         SELECT '-4-', newf1, newf2, newf4, newf5;
+         insert into temp1 values ('cur1_in', newf1, newf2, newf4, newf5);
+      END;
+      fetch cur2 into newf1, newf2, newf4, newf5;
+      SELECT '-5-', newf1, newf2, newf4, newf5;
+      insert into temp1 values ('cur2', newf1, newf2, newf4, newf5);
+      close cur1;
+   END;
+   fetch cur2 into newf1, newf2, newf4, newf5;
+   SELECT '-6-', newf1, newf2, newf4, newf5;
+   close cur2;
+END//
+delimiter ;//
+
+CALL sp1();
+
+SELECT * from temp1;
+
+# cleanup 3.1.2.75
+DROP PROCEDURE sp1;
+drop table temp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.2.76:;
+--source include/show_msg.inc
+let $message=
+Ensure that all cursors operate asensitively, so that there is no concurrency
+conflict between cursors operating on the same, or similar, sets of results
+during execution of one or more stored procedures.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+drop table IF EXISTS temp1;
+drop table IF EXISTS temp2;
+--enable_warnings
+
+create table temp1( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
+create table temp2( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer );
+
+delimiter //;
+CREATE PROCEDURE sp_inner( )
+BEGIN
+   declare proceed int default 1;
+   declare i_count integer default 20;
+   declare i_newf1 char(20);
+   declare i_newf2 char(20);
+   declare i_newf3 date;
+   declare i_newf4 integer;
+   declare i_newf11 char(20);
+   declare i_newf12 char(20);
+   declare i_newf13 date;
+   declare i_newf14 integer;
+   declare cur1 cursor for SELECT f1, f2, f3, f4 from t2
+                where f4>=-5000 order by f4 limit 4;
+   declare cur2 cursor for SELECT f1, f2, f3, f4 from t2
+                where f4>=-5000 order by f4 limit 3;
+   declare continue handler for sqlstate '02000' set proceed=0;
+   open cur1;
+   open cur2;
+   set i_count = 10;
+   while proceed do
+      fetch cur1 into i_newf1, i_newf2, i_newf3, i_newf4;
+      IF proceed THEN
+         insert into temp1 values ('sp_inner', i_count, i_newf1, i_newf2, i_newf3, i_newf4);
+         fetch cur2 into i_newf11, i_newf12, i_newf13, i_newf14;
+         IF proceed THEN
+            insert into temp2 values ('sp_inner', i_count, i_newf11, i_newf12, i_newf13, i_newf14);
+         END IF;
+      END IF;
+      set i_count = i_count - 1;
+   END while;
+   close cur1;
+   close cur2;
+END//
+
+CREATE PROCEDURE sp_outer( )
+BEGIN
+   DECLARE proceed INT DEFAULT 1;
+   DECLARE o_count INTEGER DEFAULT 20;
+   DECLARE o_newf1 CHAR(20);
+   DECLARE o_newf2 CHAR(20);
+   DECLARE o_newf3 DATE;
+   DECLARE o_newf4 INTEGER;
+   DECLARE o_newf11 CHAR(20);
+   DECLARE o_newf12 CHAR(20);
+   DECLARE o_newf13 DATE;
+   DECLARE o_newf14 INTEGER;
+   DECLARE cur1 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2
+                WHERE f4>=-5000 ORDER BY f4 LIMIT 5;
+   DECLARE cur2 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2
+                WHERE f4>=-5000 ORDER BY f4 LIMIT 5;
+   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET proceed=0;
+   OPEN cur1;
+   OPEN cur2;
+   SET o_count = 1;
+   WHILE proceed DO
+      FETCH cur1 INTO o_newf1, o_newf2, o_newf3, o_newf4;
+      IF proceed THEN
+         INSERT INTO temp1 VALUES ('_sp_out_', o_count, o_newf1, o_newf2, o_newf3, o_newf4);
+         CALL sp_inner();
+         FETCH cur2 INTO o_newf11, o_newf12, o_newf13, o_newf14;
+         IF proceed THEN
+            INSERT INTO temp2 VALUES ('_sp_out_', o_count, o_newf11, o_newf12, o_newf13, o_newf14);
+         END IF;
+      END IF;
+      SET o_count = o_count + 1;
+   END WHILE;
+   CLOSE cur1;
+   CLOSE cur2;
+END//
+delimiter ;//
+
+CALL sp_outer();
+
+SELECT * FROM temp1;
+SELECT * FROM temp2;
+
+# cleanup 3.1.2.75
+DROP PROCEDURE sp_outer;
+DROP PROCEDURE sp_inner;
+DROP TABLE temp1;
+DROP TABLE temp2;
+
+
+# ==============================================================================
+# USE the same .inc to cleanup before and after the test
+--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
+
+# ==============================================================================
+--echo
+--echo .                               +++ END OF SCRIPT +++
+--echo --------------------------------------------------------------------------------
+# ==============================================================================
diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_03.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_03.inc
new file mode 100644
index 00000000..01810390
--- /dev/null
+++ b/mysql-test/suite/funcs_1/storedproc/storedproc_03.inc
@@ -0,0 +1,496 @@
+#### suite/funcs_1/storedproc/storedproc_03.inc
+#
+--source suite/funcs_1/storedproc/load_sp_tb.inc
+
+# ==============================================================================
+# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
+#
+# 3.1.3 Syntax checks for the stored procedure-specific flow control statements
+#       IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:
+#
+#-  1. Ensure that all subclauses that should be supported are supported.
+#-  2. Ensure that all subclauses that should not be supported are disallowed
+#      with an appropriate error message.
+#-  3. Ensure that all supported subclauses are supported only in the
+#      correct order.
+#-  4. Ensure that an appropriate error message is returned if a subclause is
+#      out-of-order in a stored procedure definition.
+#-  5. Ensure that all subclauses that are defined to be mandatory are indeed
+#      required to be mandatory by the MySQL server and tools.
+#-  6. Ensure that any subclauses that are defined to be optional are indeed
+#      treated as optional by the MySQL server and tools.
+##  7. Ensure that the IF statement acts correctly for all variants, including
+#      cases where statements are nested.
+##  8. Ensure that the CASE statement acts correctly for all variants,
+#      including cases where statements are nested.
+##  9. Ensure that the LOOP statement acts correctly for all variants,
+#      including cases where statements are nested.
+#- 10. Ensure that the labels enclosing each LOOP statement must match.
+#- 11. Ensure that it is possible to put a beginning label at the start of
+#      a LOOP statement without also requiring an ending label at the end of
+#      the same statement.
+#- 12. Ensure that it is not possible to put an ending label at the end of
+#      a LOOP statement without also requiring a matching beginning label
+#      at the start of the same statement.
+#- 13. Ensure that every beginning label must end with a colon (:).
+#- 14. Ensure that every beginning label with the same scope must be unique.
+## 15. Ensure that the LEAVE statement acts correctly for all variants,
+#      including cases where statements are nested.
+## 16. Ensure that the ITERATE statement acts correctly for all variants,
+#      including cases where statements are nested.
+#- 17. Ensure that the ITERATE statement fails, with an appropriate error
+#      message, if it appears in any context other than within LOOP, REPEAT,
+#      or WHILE statements.
+## 18. Ensure that the REPEAT statement acts correctly for all variants,
+#      including cases where statements are nested.
+#- 19. Ensure that the labels enclosing each REPEAT statement must match.
+#- 20. Ensure that it is possible to put a beginning label at the start of
+#      a REPEAT statement without also requiring an ending label at the end
+#      of the same statement.
+#- 21. Ensure that it is not possible to put an ending label at the end of
+#      a REPEAT statement without also requiring a matching beginning label
+#      at the start of the same statement.
+#- 22. Ensure that every beginning label must end with a colon (:).
+#- 23. Ensure that every beginning label with the same scope must be unique.
+## 24. Ensure that the WHILE statement acts correctly for all variants,
+#      including cases where statements are nested.
+#- 25. Ensure that the labels enclosing each WHILE statement must match.
+#- 26. Ensure that it is possible to put a beginning label at the start of
+#      a WHILE statement without also requiring an ending label at the end
+#      of the same statement.
+#- 27. Ensure that it is not possible to put an ending label at the end of
+#      a WHILE statement without also requiring a matching beginning label
+#      at the start of the same statement.
+#- 28. Ensure that every beginning label must end with a colon (:).
+#- 29. Ensure that every beginning label with the same scope must be unique.
+## 30. Ensure that multiple cases of all possible combinations of the control
+#      flow statements, nested within multiple compound statements within
+#      a stored procedure, always act correctly and return the expected result.
+#
+# ==============================================================================
+let $message= Section 3.1.3 - Syntax checks for the stored procedure-specific flow
+control statements IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE:;
+--source include/show_msg80.inc
+
+#FIXME # 3.1.3: enhance syntax checks with very complicated checks
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.3.7:;
+--source include/show_msg.inc
+let $message=
+Ensure that the IF statement acts correctly for all variants, including cases
+where statements are nested.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
+DROP PROCEDURE IF EXISTS sp9;
+--enable_warnings
+
+CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
+
+delimiter //;
+CREATE PROCEDURE sp9( action char(20), subaction char(20) )
+BEGIN
+   if action = 'action' then
+      if subaction = 'subaction' then
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction' , 1);
+      else
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 2);
+      END if;
+   else
+      if subaction = 'subaction'
+      then
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction' , 3);
+      elseif subaction = 'subaction1'
+      then
+         BEGIN
+            insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values ('none', 'subaction1', 4);
+         END;
+      else
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 5);
+      END if;
+   END if;
+END//
+delimiter ;//
+
+CALL sp9( 'action', 'subaction' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=1;
+
+CALL sp9( 'temp', 'subaction' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=3;
+
+CALL sp9( 'temp', 'subaction1' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=4;
+
+CALL sp9( 'action', 'temp' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=2;
+
+CALL sp9( 'temp', 'temp' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742 where f3=5;
+
+# cleanup 3.1.3.7
+DROP PROCEDURE sp9;
+DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.3.8.:;
+--source include/show_msg.inc
+let $message=
+Ensure that the CASE statement acts correctly for all variants, including cases
+where statements are nested.;
+--source include/show_msg80.inc
+
+--disable_warnings
+drop table IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
+DROP PROCEDURE IF EXISTS sp10;
+--enable_warnings
+
+create table res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 char(20), f2 varchar(20), f3 smallint);
+
+delimiter //;
+CREATE PROCEDURE sp10( action char(20), subaction char(20) )
+BEGIN
+   case action
+   when 'action' then
+      case
+      when subaction = 'subaction_1' then
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 1);
+      when subaction = 'subaction_2' then
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'subaction_2' , 2);
+      else
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'action', 'none' , 3);
+      END case;
+    else
+      case
+      when subaction = 'subaction_1' then
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_1' , 4);
+      when subaction = 'subaction_2' then
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'subaction_2' , 5);
+      else
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'none', 'none' , 6);
+      END case;
+   END case;
+END//
+delimiter ;//
+
+CALL sp10( 'action', 'subaction_1' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+CALL sp10( 'action', 'subaction_2' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+CALL sp10( 'temp', 'subaction_1' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+CALL sp10( 'temp', 'subaction_2' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+CALL sp10( 'action', 'temp' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+delete from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+CALL sp10( 'temp', 'temp' );
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+# cleanup 3.1.3.8
+DROP PROCEDURE sp10;
+DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.3.9 + 3.1.3.15:;
+--source include/show_msg.inc
+let $message=
+09. Ensure that the LOOP statement acts correctly for all variants, including
+.   cases where statements are nested.
+15. Ensure that the LEAVE statement acts correctly for all variants, including
+.   cases where statements are nested.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
+DROP PROCEDURE IF EXISTS sp11;
+--enable_warnings
+
+CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
+
+delimiter //;
+CREATE PROCEDURE sp11( )
+BEGIN
+   declare count1 integer default 1;
+   declare count2 integer default 1;
+   label1: loop
+      if count2 > 3 then leave label1;
+      END if;
+      set count1 = 1;
+      label2: loop
+         if count1 > 4 then leave label2;
+         END if;
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
+         set count1 = count1 + 1;
+         iterate label2;
+      END loop label2;
+      set count2 = count2 + 1;
+      iterate label1;
+   END loop label1;
+END//
+delimiter ;//
+
+CALL sp11();
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+# cleanup 3.1.3.9
+DROP PROCEDURE sp11;
+DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.3.16:;
+--source include/show_msg.inc
+let $message=
+Ensure that the ITERATE statement acts correctly for all variants, including
+cases where statements are nested.
+(tests for this testcase are also included in other testcases);
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp31316;
+--enable_warnings
+
+delimiter //;
+
+# wrong label at iterate
+# Error: SQLSTATE: 42000 (ER_SP_LILABEL_MISMATCH)
+#        Message: %s with no matching label: %s
+--error ER_SP_LILABEL_MISMATCH
+CREATE PROCEDURE sp31316( )
+BEGIN
+   declare count1 integer default 1;
+   declare count2 integer default 1;
+   label1: loop
+      if count2 > 3 then leave label1;
+      END if;
+      set count1 = 1;
+      label2: loop
+         if count1 > 4 then leave label2;
+         END if;
+         insert into temp values( count1, count2);
+         set count1 = count1 + 1;
+         iterate label3;
+      END loop label2;
+      set count2 = count2 + 1;
+      iterate label1;
+   END loop label1;
+END//
+delimiter ;//
+
+# cleanup 3.1.3.16
+#DROP PROCEDURE sp31316;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.3.18:;
+--source include/show_msg.inc
+let $message=
+Ensure that the REPEAT statement acts correctly for all variants, including
+cases where statements are nested.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp17;
+DROP TABLE IF EXISTS res_t3_itisalongname_1381742_itsaverylongname_1381742;
+--enable_warnings
+
+CREATE TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742( f1 CHAR(20), f2 VARCHAR(20), f3 SMALLINT);
+
+delimiter //;
+CREATE PROCEDURE sp17( )
+BEGIN
+   declare count1 integer default 1;
+   declare count2 integer default 1;
+   repeat
+      set count1 = count1 + 1;
+      set count2 = 1;
+      label1: repeat
+         set count2 = count2 + 1;
+         insert into res_t3_itisalongname_1381742_itsaverylongname_1381742 values( 'xyz' , 'pqr', count1);
+         until count2 > 3
+      END repeat label1;
+      until count1 > 3
+   END repeat;
+END//
+delimiter ;//
+
+CALL sp17();
+SELECT * from res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+# cleanup 3.1.3.18
+DROP PROCEDURE sp17;
+DROP TABLE res_t3_itisalongname_1381742_itsaverylongname_1381742;
+
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.3.24:;
+--source include/show_msg.inc
+let $message=
+Ensure that the WHILE statement acts correctly for all variants, including cases
+where statements are nested.;
+--source include/show_msg80.inc
+
+--disable_warnings
+drop table IF EXISTS res_t21;
+DROP PROCEDURE IF EXISTS sp21;
+--enable_warnings
+
+create table res_t21(name text(10), surname blob(20), age_averylongfieldname_averylongname_1234569 smallint);
+insert into res_t21 values('ashwin', 'mokadam', 25);
+
+delimiter //;
+CREATE PROCEDURE sp21( )
+BEGIN
+   declare count1 integer default 0;
+   declare count2 integer default 0;
+   while count1 < 3 do
+      BEGIN
+         declare ithisissamevariablename int default 100;
+         SELECT ithisissamevariablename;
+         BEGIN
+            declare ithisissamevariablename int default 200;
+            SELECT ithisissamevariablename;
+         END;
+         set count2 = 0;
+         label1: while count2 < 3 do
+            BEGIN
+               declare count1 integer default 7;
+               set count2 = count2 + 1;
+               insert into res_t21 values( 'xyz' , 'pqr', count2);
+               label2: while count1 < 10 do
+                  set count1 = count1 + 1;
+                  insert into res_t21 values( 'xyz' , 'pqr', count1);
+               END while label2;
+            END;
+         END while label1;
+         set count1 = count1 + 1;
+      END;
+   END while;
+END//
+delimiter ;//
+
+CALL sp21();
+SELECT * from res_t21;
+
+# cleanup 3.1.3.
+DROP PROCEDURE sp21;
+drop table res_t21;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.3.30:;
+--source include/show_msg.inc
+let $message=
+Ensure that multiple cases of all possible combinations of the control flow
+statements, nested within multiple compound statements within a stored
+procedure, always act correctly and return the expected result.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS res_tbl;
+DROP PROCEDURE IF EXISTS sp31330;
+--enable_warnings
+
+create table res_tbl (f1 int, f2 text, f3 blob, f4 date,
+      f5 set('one', 'two', 'three', 'four', 'five') default 'one');
+
+delimiter //;
+#FIXME: can be enhanced more and more ...
+CREATE PROCEDURE sp31330 (path int)
+BEGIN
+   declare count int default 1;
+   declare var1 text;
+   declare var2 blob;
+   declare var3 date;
+   declare var4 set('one', 'two', 'three', 'four', 'five') DEFAULT 'five';
+   case
+      when path=1 then
+         set var3 = '2000-11-09';
+         set var1 = 'flowing through case 1';
+         label1: loop
+            if count > 5 then
+               if var4=1000 then
+                  set var2 = 'exiting out of case 1 - invalid SET';
+               END if;
+               if var4='two' then
+                  set var2 = 'exiting out of case 1';
+               END if;
+               insert into res_tbl values (1, var1, var2, var3, (count-2));
+               leave label1;
+            elseif count = 5 then
+               set count= count + 2;
+               set var4='two';
+               iterate label1;
+            else
+               set count= count + 1;
+            END if;
+            set var4='one';
+         END loop label1;
+      when path=2 then
+         set var3 = '1989-11-09';
+         set var1 = 'flowing through case 2';
+         set @count3=0;
+         label2: repeat
+            set count=count + 1;
+            set @count2=1;
+            while @count2 <= 5 do
+               set @count2 = @count2 + 1;
+            END while;
+            SELECT @count2;
+            set @count3=@count3 + @count2;
+            until count > 5
+         END repeat label2;
+         set var2 = 'exiting out of case 2';
+         set var4 = count-3;
+         SELECT @count3;
+         insert into res_tbl values (2, var1, var2, var3, var4);
+      ELSE BEGIN
+         set @error_opt='undefined path specified';
+         SELECT @error_opt;
+      END;
+   END case;
+END//
+delimiter ;//
+
+# Error: SQLSTATE: 42000 (ER_SP_WRONG_NO_OF_ARGS)
+#        Message: Incorrect number of arguments for %s %s; expected %u, got %u
+--error ER_SP_WRONG_NO_OF_ARGS
+CALL sp31330();
+
+CALL sp31330(1);
+SELECT * from res_tbl;
+
+CALL sp31330(2);
+SELECT * from res_tbl;
+
+CALL sp31330(4);
+
+# cleanup 3.1.3.30
+DROP PROCEDURE sp31330;
+drop table res_tbl;
+
+
+
+# ==============================================================================
+# USE the same .inc to cleanup before and after the test
+--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
+
+# ==============================================================================
+--echo
+--echo .                               +++ END OF SCRIPT +++
+--echo --------------------------------------------------------------------------------
+# ==============================================================================
diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc
new file mode 100644
index 00000000..c4f8496f
--- /dev/null
+++ b/mysql-test/suite/funcs_1/storedproc/storedproc_06.inc
@@ -0,0 +1,461 @@
+#### suite/funcs_1/storedproc/storedproc_06.inc
+
+# This test cannot be used for the embedded server because we check here
+# privileges.
+--source include/not_embedded.inc
+
+set GLOBAL sql_mode="";
+
+--source suite/funcs_1/storedproc/load_sp_tb.inc
+
+# ==============================================================================
+# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
+#
+# 3.1.6 Privilege checks:
+#
+#  1. Ensure that no user may create a stored procedure without the
+#     GRANT CREATE ROUTINE privilege.
+#  2. Ensure that root always has the GRANT CREATE ROUTINE privilege.
+#  3. Ensure that a user with the GRANT CREATE ROUTINE privilege can always
+#     create both a procedure and a function, on any appropriate database.
+#  4. Ensure that the default security provision of a stored procedure is
+#     SQL SECURITY DEFINER.
+#  5. Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
+#     called/executed by any user, using only the privileges (including
+#     database access privileges) associated with the user who created
+#     the stored procedure.
+#  6. Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
+#     called/executed by any user, using only the privileges (including
+#     database access privileges) associated with the user executing
+#     the stored procedure.
+#
+# ==============================================================================
+let $message= Section 3.1.6 - Privilege Checks:;
+--source include/show_msg80.inc
+
+
+connection default;
+USE db_storedproc_1;
+--source suite/funcs_1/include/show_connection.inc
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.6.1:
+              -----------------
+Ensure that no user may create a stored procedure without the GRANT CREATE
+ROUTINE privilege.;
+--source include/show_msg80.inc
+
+create user 'user_1'@'localhost';
+
+grant all on db_storedproc_1.* to 'user_1'@'localhost';
+revoke create routine on db_storedproc_1.* from 'user_1'@'localhost';
+flush privileges;
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+--enable_warnings
+
+connect (user1a, localhost, user_1, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+
+USE db_storedproc_1;
+
+delimiter //;
+--error ER_DBACCESS_DENIED_ERROR
+CREATE PROCEDURE sp1(v1 char(20))
+BEGIN
+    SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
+END//
+delimiter ;//
+
+disconnect user1a;
+
+# add privilege again and check
+connection default;
+USE db_storedproc_1;
+--source suite/funcs_1/include/show_connection.inc
+
+GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
+connect (user1b, localhost, user_1, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+
+USE db_storedproc_1;
+
+delimiter //;
+CREATE PROCEDURE sp1(v1 char(20))
+BEGIN
+    SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
+END//
+delimiter ;//
+disconnect user1b;
+
+# cleanup
+connection default;
+USE db_storedproc_1;
+--source suite/funcs_1/include/show_connection.inc
+
+DROP USER 'user_1'@'localhost';
+DROP PROCEDURE sp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.6.2:
+              -----------------
+Ensure that root always has the GRANT CREATE ROUTINE privilege.
+(checked by other testscases);
+--source include/show_msg80.inc
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.6.3:
+              -----------------
+Ensure that a user with the GRANT CREATE ROUTINE privilege can always create
+both a procedure and a function, on any appropriate database.
+--source include/show_msg80.inc
+
+
+create user 'user_1'@'localhost';
+
+grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
+flush privileges;
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS db_storedproc_1.sp3;
+DROP FUNCTION IF EXISTS db_storedproc_1.fn1;
+--enable_warnings
+
+#  disconnect default;
+connect (user2, localhost, user_1, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+
+delimiter //;
+CREATE PROCEDURE sp3(v1 char(20))
+BEGIN
+   SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
+END//
+delimiter ;//
+
+delimiter //;
+CREATE FUNCTION fn1(v1 int) returns int
+BEGIN
+   return v1;
+END//
+delimiter ;//
+
+disconnect user2;
+
+# cleanup
+connection default;
+USE db_storedproc_1;
+--source suite/funcs_1/include/show_connection.inc
+
+drop user 'user_1'@'localhost';
+DROP PROCEDURE sp3;
+DROP FUNCTION fn1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.6.4:
+              -----------------
+Ensure that the default security provision of a stored procedure is SQL SECURITY
+DEFINER.;
+--source include/show_msg80.inc
+
+CREATE USER 'user_1'@'localhost';
+
+grant update on db_storedproc_1.t6 to 'user_1'@'localhost';
+grant execute on db_storedproc_1.* to 'user_1'@'localhost';
+flush privileges;
+
+USE db_storedproc_1;
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp4;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp4(v1 char(20))
+BEGIN
+   SELECT * from db_storedproc_1.t6 where t6.f2= 'xyz';
+END//
+delimiter ;//
+
+#disconnect default;
+
+connect (user3, localhost, user_1, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+
+USE db_storedproc_1;
+CALL sp4('a');
+
+--vertical_results
+SELECT SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
+       ROUTINE_BODY, ROUTINE_DEFINITION, IS_DETERMINISTIC,
+       SQL_DATA_ACCESS, SECURITY_TYPE, SQL_MODE, ROUTINE_COMMENT
+FROM information_schema.routines
+ WHERE routine_schema LIKE 'db_sto%';
+--horizontal_results
+
+disconnect user3;
+
+# cleanup
+connection default;
+--source suite/funcs_1/include/show_connection.inc
+DROP PROCEDURE sp4;
+DROP USER 'user_1'@'localhost';
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.6.5:
+              -----------------
+Ensure that a stored procedure defined with SQL SECURITY DEFINER can be
+called/executed by any user, using only the privileges (including database
+access privileges) associated with the user who created the stored procedure.;
+--source include/show_msg80.inc
+
+USE db_storedproc_1;
+CREATE TABLE t3165 ( c1 char(20), c2 char(20), c3 date);
+INSERT INTO t3165 VALUES ('inserted', 'outside of SP', NULL);
+
+# creates procedures
+create user 'user_1'@'localhost';
+
+#executes procedure
+create user 'user_2'@'localhost';
+
+grant create routine on db_storedproc_1.* to 'user_1'@'localhost';
+grant SELECT on db_storedproc_1.* to 'user_2'@'localhost';
+grant execute on db_storedproc_1.* to 'user_2'@'localhost';
+flush privileges;
+
+connect (user5_1, localhost, user_1, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+
+delimiter //;
+CREATE PROCEDURE sp5_s_i () sql security definer
+BEGIN
+   SELECT * from db_storedproc_1.t3165;
+   insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_s_i', 1000);
+END//
+
+CREATE PROCEDURE sp5_sel () sql security definer
+BEGIN
+   SELECT * from db_storedproc_1.t3165;
+END//
+
+CREATE PROCEDURE sp5_ins () sql security definer
+BEGIN
+   insert into db_storedproc_1.t3165 values ('inserted', 'from sp5_ins', 1000);
+END//
+delimiter ;//
+
+disconnect user5_1;
+
+connect (user5_2, localhost, user_2, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_s_i();
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_ins();
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_sel();
+
+# now 'add' INSERT to DEFINER
+connection default;
+--source suite/funcs_1/include/show_connection.inc
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_sel();
+grant insert on db_storedproc_1.* to 'user_1'@'localhost';
+flush privileges;
+
+connection user5_2;
+--source suite/funcs_1/include/show_connection.inc
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_s_i();
+CALL sp5_ins();
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_sel();
+
+# now 'add' SELECT to DEFINER
+connection default;
+--source suite/funcs_1/include/show_connection.inc
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_sel();
+grant SELECT on db_storedproc_1.* to 'user_1'@'localhost';
+#grant execute on db_storedproc_1.* to 'user_2'@'localhost';
+flush privileges;
+
+connection user5_2;
+--source suite/funcs_1/include/show_connection.inc
+CALL sp5_s_i();
+CALL sp5_ins();
+CALL sp5_sel();
+
+# now revoke INSERT FROM DEFINER
+connection default;
+--source suite/funcs_1/include/show_connection.inc
+REVOKE INSERT on db_storedproc_1.* from 'user_1'@'localhost';
+flush privileges;
+
+connection user5_2;
+--source suite/funcs_1/include/show_connection.inc
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_s_i();
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_ins();
+CALL sp5_sel();
+
+# now revoke SELECT FROM DEFINER
+connection default;
+--source suite/funcs_1/include/show_connection.inc
+REVOKE SELECT on db_storedproc_1.* from 'user_1'@'localhost';
+flush privileges;
+
+connection user5_2;
+--source suite/funcs_1/include/show_connection.inc
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_s_i();
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_ins();
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp5_sel();
+
+# cleanup
+disconnect user5_2;
+connection default;
+--source suite/funcs_1/include/show_connection.inc
+
+DROP PROCEDURE sp5_s_i;
+DROP PROCEDURE sp5_sel;
+DROP PROCEDURE sp5_ins;
+DROP TABLE t3165;
+DROP USER 'user_1'@'localhost';
+DROP USER 'user_2'@'localhost';
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.6.6:
+              -----------------
+Ensure that a stored procedure defined with SQL SECURITY INVOKER can be
+called/executed by any user, using only the privileges (including database
+access privileges) associated with the user executing the stored procedure.;
+--source include/show_msg80.inc
+
+USE db_storedproc_1;
+CREATE TABLE t3166 ( c1 char(30) );
+INSERT INTO db_storedproc_1.t3166 VALUES ('inserted outside SP');
+
+# DEFINER
+create user 'user_1'@'localhost';
+
+# INVOKER
+create user 'user_2'@'localhost';
+
+GRANT CREATE ROUTINE ON db_storedproc_1.* TO 'user_1'@'localhost';
+GRANT SELECT  ON db_storedproc_1.* TO 'user_2'@'localhost';
+GRANT EXECUTE ON db_storedproc_1.* TO 'user_2'@'localhost';
+FLUSH PRIVILEGES;
+
+connect (user6_1, localhost, user_1, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+
+delimiter //;
+CREATE PROCEDURE sp3166_s_i () SQL SECURITY INVOKER
+BEGIN
+   SELECT * from db_storedproc_1.t3166;
+   insert into db_storedproc_1.t3166 values ('inserted from sp3166_s_i');
+END//
+
+CREATE PROCEDURE sp3166_sel () SQL SECURITY INVOKER
+BEGIN
+   SELECT * from db_storedproc_1.t3166;
+END//
+
+CREATE PROCEDURE sp3166_ins () SQL SECURITY INVOKER
+BEGIN
+   insert into db_storedproc_1.t3166 values ('inserted from sp3166_ins');
+END//
+delimiter ;//
+
+disconnect user6_1;
+
+connect (user6_2, localhost, user_2, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp3166_s_i();
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp3166_ins();
+CALL sp3166_sel();
+
+# now 'add' INSERT to INVOKER
+connection default;
+--source suite/funcs_1/include/show_connection.inc
+CALL sp3166_sel();
+GRANT INSERT  ON db_storedproc_1.* TO 'user_2'@'localhost';
+FLUSH PRIVILEGES;
+disconnect user6_2;
+
+connect (user6_3, localhost, user_2, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+CALL sp3166_s_i();
+CALL sp3166_ins();
+CALL sp3166_sel();
+disconnect user6_3;
+
+# now 'remove' SELECT from INVOKER
+connection default;
+--source suite/funcs_1/include/show_connection.inc
+CALL sp3166_sel();
+REVOKE SELECT ON db_storedproc_1.* FROM 'user_2'@'localhost';
+FLUSH PRIVILEGES;
+
+connect (user6_4, localhost, user_2, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp3166_s_i();
+CALL sp3166_ins();
+--error ER_TABLEACCESS_DENIED_ERROR
+CALL sp3166_sel();
+disconnect user6_4;
+
+# now 'remove' EXECUTE FROM INVOKER
+connection default;
+CALL sp3166_s_i();
+--source suite/funcs_1/include/show_connection.inc
+REVOKE EXECUTE on db_storedproc_1.* FROM 'user_2'@'localhost';
+FLUSH PRIVILEGES;
+
+connect (user6_5, localhost, user_2, , db_storedproc_1);
+--source suite/funcs_1/include/show_connection.inc
+--error ER_PROCACCESS_DENIED_ERROR
+CALL sp3166_s_i();
+--error ER_PROCACCESS_DENIED_ERROR
+CALL sp3166_ins();
+--error ER_PROCACCESS_DENIED_ERROR
+CALL sp3166_sel();
+disconnect user6_5;
+
+# cleanup
+connection default;
+--source suite/funcs_1/include/show_connection.inc
+
+DROP PROCEDURE sp3166_s_i;
+DROP PROCEDURE sp3166_sel;
+DROP PROCEDURE sp3166_ins;
+DROP TABLE t3166;
+DROP USER 'user_1'@'localhost';
+DROP USER 'user_2'@'localhost';
+
+
+# ==============================================================================
+# USE the same .inc to cleanup before and after the test
+--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
+
+set GLOBAL sql_mode=default;
+# ==============================================================================
+--echo
+--echo .                               +++ END OF SCRIPT +++
+--echo --------------------------------------------------------------------------------
+# ==============================================================================
diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_07.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_07.inc
new file mode 100644
index 00000000..ec8bd08c
--- /dev/null
+++ b/mysql-test/suite/funcs_1/storedproc/storedproc_07.inc
@@ -0,0 +1,119 @@
+#### suite/funcs_1/storedproc/storedproc_07.inc
+#
+--source include/default_charset.inc
+--source suite/funcs_1/storedproc/load_sp_tb.inc
+
+# ==============================================================================
+# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
+#
+# 3.1.7 SQL mode checks:
+#
+#  1. Ensure that the sql_mode setting in effect at the time a stored procedure is created is the same setting under which the stored procedure runs when it is called/executed.
+#  2. Ensure that if the sql_mode setting is changed when a stored procedure is run, that the original setting is restored as soon as the stored procedure execution is complete.
+#
+# ==============================================================================
+let $message= Section 3.1.7 - SQL mode checks:;
+--source include/show_msg80.inc
+
+USE db_storedproc;
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.7.1:
+              -----------------
+Ensure that the sql_mode setting in effect at the time a stored procedure is
+created is the same setting under which the stored procedure runs when it is
+called/executed.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+DROP TABLE IF EXISTS temp_tbl;
+DROP TABLE IF EXISTS result;
+--enable_warnings
+
+CREATE TABLE temp_tbl (f1 tinyint);
+CREATE TABLE result (f1 text(200), f2 char(20));
+
+set @@sql_mode='traditional';
+SHOW VARIABLES LIKE 'sql_mode';
+
+delimiter //;
+CREATE PROCEDURE sp1()
+BEGIN
+    declare a tinyint;
+    declare count_ int default 1;
+    declare continue handler for sqlstate '22003' set count_=1000;
+    SHOW VARIABLES LIKE 'sql_mode';
+    SELECT @@sql_mode into @cur_val_sql_mode;
+    insert into temp_tbl values (1000);
+    if count_ = 1000 THEN
+       INSERT INTO result VALUES (@cur_val_sql_mode, 'value restored');
+    ELSE
+       INSERT INTO result VALUES (@cur_val_sql_mode, 'value not restored');
+    END if;
+END//
+delimiter ;//
+
+SHOW CREATE PROCEDURE sp1;
+
+set @@sql_mode='';
+SHOW VARIABLES LIKE 'sql_mode';
+
+CALL sp1();
+SELECT * from result;
+
+SHOW VARIABLES LIKE 'sql_mode';
+SELECT @@sql_mode;
+
+# cleanup
+SET @@sql_mode='TRADITIONAL';
+DROP PROCEDURE sp1;
+DROP TABLE temp_tbl;
+DROP TABLE result;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.7.2:
+              -----------------
+Ensure that if the sql_mode setting is changed when a stored procedure is run,
+that the original setting is restored as soon as the stored procedure execution
+is complete.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp2;
+--enable_warnings
+
+--echo ... show initial value
+SHOW VARIABLES LIKE 'sql_mode';
+
+delimiter //;
+CREATE PROCEDURE sp2()
+BEGIN
+   SET @@sql_mode='MAXDB';
+   SHOW VARIABLES LIKE 'sql_mode';
+END//
+delimiter ;//
+
+SHOW CREATE PROCEDURE sp2;
+--echo ... show value prior calling procedure
+SHOW VARIABLES LIKE 'sql_mode';
+
+--echo ... call procedure that changes sql_mode
+CALL sp2();
+
+--echo ... check whether old value is re-set
+SHOW VARIABLES LIKE 'sql_mode';
+
+# cleanup
+DROP PROCEDURE sp2;
+
+
+# ==============================================================================
+# USE the same .inc to cleanup before and after the test
+--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
+
+# ==============================================================================
+let $message= .                               +++ END OF SCRIPT +++;
+--source include/show_msg80.inc
+# ==============================================================================
diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_08.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_08.inc
new file mode 100644
index 00000000..9f0304a3
--- /dev/null
+++ b/mysql-test/suite/funcs_1/storedproc/storedproc_08.inc
@@ -0,0 +1,119 @@
+#### suite/funcs_1/storedproc/storedproc_08.inc
+#
+--source include/default_charset.inc
+--source suite/funcs_1/storedproc/load_sp_tb.inc
+
+# ==============================================================================
+# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
+#
+# 3.1.8 SHOW statement checks:
+#
+#-  1. Ensure that the definition of a procedure is properly recorded and displayed when a SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS statement is executed.
+#-  2. Ensure that the definition of a procedure is not displayed when a SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS statement is executed.
+#-  3. Ensure that an attempt to execute SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS, with the name of a non-existent procedure, fails with an appropriate error message.
+#-  4. Ensure that an attempt to execute SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS, with the name of a function, fails with an appropriate error message.
+#-  5. Ensure that the definition of a function is properly recorded and displayed when a SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS statement is executed.
+#-  6. Ensure that the definition of a function is not displayed when a SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS statement is executed.
+#-  7. Ensure that an attempt to execute SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS, with the name of a non-existent function, fails with an appropriate error message.
+#-  8. Ensure that an attempt to execute SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS, with the name of a procedure, fails with an appropriate error message.
+##  9. Ensure that all stored procedure changes made with ALTER PROCEDURE or ALTER FUNCTION are properly recorded and displayed when a SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS statement (respectively) is executed.
+#- 10. Ensure that, when a stored procedure is dropped, its definition no longer appears when a SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS statement (respectively) is executed.
+#
+# ==============================================================================
+let $message= Section 3.1.8 - SHOW statement checks:;
+--source include/show_msg80.inc
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.8.9:;
+--source include/show_msg.inc
+let $message=
+Ensure that all stored procedure changes made with ALTER PROCEDURE or ALTER
+FUNCTION are properly recorded and displayed when a SHOW CREATE PROCEDURE or
+SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE
+FUNCTION STATUS statement (respectively) is executed.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP FUNCTION IF EXISTS fn_1;
+DROP FUNCTION IF EXISTS fn_2;
+DROP PROCEDURE IF EXISTS sp_1;
+DROP PROCEDURE IF EXISTS sp_2;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp_1 (i1 int)
+BEGIN
+    set @x=i1;
+END//
+
+CREATE PROCEDURE sp_2 (i1 int) SQL SECURITY INVOKER COMMENT 'created with INVOKER'
+BEGIN
+    set @x=i1;
+END//
+
+CREATE FUNCTION fn_1 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) returns year
+BEGIN
+   set @x=i1;
+   set @y=@x;
+   return i4;
+END//
+
+CREATE FUNCTION fn_2 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real)
+   RETURNS YEAR
+   SQL SECURITY INVOKER
+   COMMENT 'created with INVOKER'
+BEGIN
+   set @x=i1;
+   set @y=@x;
+   return i4;
+END//
+delimiter ;//
+
+--source suite/funcs_1/storedproc/storedproc_08_show.inc
+
+let $message= ... now change some stuff:;
+--source include/show_msg.inc
+# some of the changes might be 'strange' here, but they are used here ONLY to
+# check that the changes are recorded / showd correct
+ALTER PROCEDURE sp_1 SQL SECURITY INVOKER;
+ALTER PROCEDURE sp_1 COMMENT 'new comment, SP changed to INVOKER';
+
+ALTER PROCEDURE sp_2 SQL SECURITY DEFINER;
+ALTER PROCEDURE sp_2 DROP COMMENT;
+ALTER PROCEDURE sp_2 COMMENT 'SP changed to DEFINER';
+ALTER PROCEDURE sp_2 READS SQL DATA;
+
+ALTER FUNCTION  fn_1 SQL SECURITY INVOKER;
+ALTER FUNCTION  fn_1 COMMENT 'new comment, FN changed to INVOKER';
+ALTER FUNCTION  fn_1 NO SQL;
+
+ALTER FUNCTION  fn_2 SQL SECURITY DEFINER;
+ALTER FUNCTION  fn_2 COMMENT 'FN changed to DEFINER';
+ALTER FUNCTION  fn_2 MODIFIES SQL DATA;
+
+--source suite/funcs_1/storedproc/storedproc_08_show.inc
+
+
+let $message= ... change back to default and check result:;
+--source include/show_msg.inc
+ALTER FUNCTION  fn_2 CONTAINS SQL;
+--source suite/funcs_1/storedproc/storedproc_08_show.inc
+
+
+# cleanup 3.1.8.9
+let $message= ... cleanup;
+--source include/show_msg.inc
+DROP FUNCTION fn_1;
+DROP FUNCTION fn_2;
+DROP PROCEDURE sp_1;
+
+
+# ==============================================================================
+# USE the same .inc to cleanup before and after the test
+--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
+
+# ==============================================================================
+let $message= .                               +++ END OF SCRIPT +++;
+--source include/show_msg80.inc
+# ==============================================================================
diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_08_show.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_08_show.inc
new file mode 100644
index 00000000..e6eb2fa0
--- /dev/null
+++ b/mysql-test/suite/funcs_1/storedproc/storedproc_08_show.inc
@@ -0,0 +1,29 @@
+#### suite/funcs_1/storedproc/storedproc_08_show.inc
+#
+#  used from .../storedproc_08.inc to show all created / altered routines
+
+--echo
+--echo ... now check what is stored:
+--echo -----------------------------
+
+--vertical_results
+
+--replace_column 24 <modified> 25 <created>
+SELECT * FROM information_schema.routines where routine_schema = 'db_storedproc';
+
+
+SHOW CREATE FUNCTION  fn_1;
+SHOW CREATE FUNCTION  fn_2;
+#SHOW CREATE FUNCTION  fn_2b;
+
+SHOW CREATE PROCEDURE sp_1;
+SHOW CREATE PROCEDURE sp_2;
+
+
+--replace_column 5 <modified> 6 <created>
+SHOW FUNCTION STATUS LIKE 'fn_%';
+
+--replace_column 5 <modified> 6 <created>
+SHOW PROCEDURE STATUS LIKE 'sp_%';
+
+--horizontal_results
diff --git a/mysql-test/suite/funcs_1/storedproc/storedproc_10.inc b/mysql-test/suite/funcs_1/storedproc/storedproc_10.inc
new file mode 100644
index 00000000..db8f45d0
--- /dev/null
+++ b/mysql-test/suite/funcs_1/storedproc/storedproc_10.inc
@@ -0,0 +1,330 @@
+#### suite/funcs_1/storedproc/storedproc_10.inc
+
+# This test cannot be used for the embedded server because we check here
+# privilgeges.
+--source include/not_embedded.inc
+
+--source suite/funcs_1/storedproc/load_sp_tb.inc
+
+# ==============================================================================
+# (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
+#
+# 3.1.10 CALL checks:
+#
+## 1. Ensure that a properly defined procedure can always be called, assuming
+#     the appropriate privileges exist.
+#- 2. Ensure that a procedure cannot be called if the appropriate privileges
+#     do not exist.
+## 3. Ensure that a function can never be called.
+## 4. Ensure that a properly defined function can always be executed, assuming
+#     the appropriate privileges exist.
+#- 5. Ensure that a function cannot be executed if the appropriate privileges
+#     do not exist.
+## 6. Ensure that a procedure can never be executed.
+## 7. Ensure that the ROW_COUNT() SQL function always returns the correct
+#     number of rows affected by the execution of a stored procedure.
+## 8. Ensure that the mysql_affected_rows() C API function always returns
+#     the correct number of rows affected by the execution of a
+#     stored procedure.
+#
+# ==============================================================================
+let $message= Section 3.1.10 - CALL checks:;
+--source include/show_msg80.inc
+
+
+USE db_storedproc;
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.10.2 + 3.1.10.5:;
+--source include/show_msg.inc
+let $message=
+2. Ensure that a procedure cannot be called if the appropriate privileges do not
+   exist.
+5. Ensure that a function cannot be executed if the appropriate privileges do
+   not exist.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp31102;
+DROP FUNCTION  IF EXISTS fn31105;
+--enable_warnings
+
+# DEFINER
+create user 'user_1'@'localhost';
+# INVOKER
+create user 'user_2'@'localhost';
+
+GRANT CREATE ROUTINE ON db_storedproc.* TO 'user_1'@'localhost';
+GRANT SELECT         ON db_storedproc.* TO 'user_2'@'localhost';
+FLUSH PRIVILEGES;
+
+connect (user2_1, localhost, user_1, , db_storedproc);
+--source suite/funcs_1/include/show_connection.inc
+
+delimiter //;
+CREATE PROCEDURE sp31102 () SQL SECURITY INVOKER
+BEGIN
+   SELECT * FROM db_storedproc.t1 WHERE f4=-5000 LIMIT 1;
+END//
+delimiter ;//
+
+delimiter //;
+CREATE FUNCTION fn31105(n INT) RETURNS INT
+  BEGIN
+  DECLARE res INT;
+  SET res = n * n;
+  RETURN res;
+END//
+delimiter ;//
+
+disconnect user2_1;
+
+connect (user2_2, localhost, user_2, , db_storedproc);
+--source suite/funcs_1/include/show_connection.inc
+
+# no privileges exist
+--error ER_PROCACCESS_DENIED_ERROR
+CALL sp31102();
+SELECT fn31105( 9 );
+
+# now 'add' EXECUTE to INVOKER
+connection default;
+USE db_storedproc;
+--source suite/funcs_1/include/show_connection.inc
+# root can execute ...
+CALL sp31102();
+SELECT fn31105( 9 );
+GRANT EXECUTE ON db_storedproc.* TO 'user_2'@'localhost';
+FLUSH PRIVILEGES;
+disconnect user2_2;
+
+# new connection
+connect (user2_3, localhost, user_2, , db_storedproc);
+--source suite/funcs_1/include/show_connection.inc
+CALL sp31102();
+SELECT fn31105( 9 );
+disconnect user2_3;
+
+# now 'remove' SELECT from INVOKER
+connection default;
+USE db_storedproc;
+--source suite/funcs_1/include/show_connection.inc
+REVOKE EXECUTE ON db_storedproc.* FROM 'user_2'@'localhost';
+FLUSH PRIVILEGES;
+
+# root can still execute
+CALL sp31102();
+SELECT fn31105( 9 );
+
+connect (user2_4, localhost, user_2, , db_storedproc);
+--source suite/funcs_1/include/show_connection.inc
+CALL sp31102();
+SELECT fn31105( 9 );
+disconnect user2_4;
+
+# cleanup
+connection default;
+USE db_storedproc;
+
+--source suite/funcs_1/include/show_connection.inc
+DROP PROCEDURE sp31102;
+DROP FUNCTION  fn31105;
+DROP USER 'user_1'@'localhost';
+DROP USER 'user_2'@'localhost';
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.10.3:;
+--source include/show_msg.inc
+let $message=
+Ensure that a function can never be called.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP FUNCTION IF EXISTS fn1;
+--enable_warnings
+
+delimiter //;
+CREATE FUNCTION fn1(a int) returns int
+BEGIN
+    set @b = 0.9 * a;
+    return @b;
+END//
+delimiter ;//
+
+--error ER_SP_DOES_NOT_EXIST
+CALL fn1();
+
+# cleanup
+DROP FUNCTION fn1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.10.6:;
+--source include/show_msg.inc
+let $message=
+Ensure that a procedure can never be executed.;
+--source include/show_msg80.inc
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp1;
+DROP FUNCTION IF EXISTS sp1;
+--enable_warnings
+
+delimiter //;
+CREATE PROCEDURE sp1()
+BEGIN
+    SELECT * from t10;
+END//
+delimiter ;//
+
+--error ER_SP_DOES_NOT_EXIST
+  SELECT sp1();
+
+# cleanup
+DROP PROCEDURE sp1;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.10.7:;
+--source include/show_msg.inc
+let $message=
+Ensure that the ROW_COUNT() SQL function always returns the correct number of
+rows affected by the execution of a stored procedure.;
+--source include/show_msg80.inc
+# Note(mleich): Information taken from a comments in
+#     Bug#21818 Return value of ROW_COUNT() is incorrect for
+#               ALTER TABLE, LOAD DATA
+#     ROW_COUNT() is -1 following any statement which is not DELETE, INSERT
+#     or UPDATE.
+#     Also, after a CALL statement, ROW_COUNT() will return the value of the
+#     last statement in the stored procedure.
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS sp_ins_1;
+DROP PROCEDURE IF EXISTS sp_ins_3;
+DROP PROCEDURE IF EXISTS sp_upd;
+DROP PROCEDURE IF EXISTS sp_ins_upd;
+DROP PROCEDURE IF EXISTS sp_del;
+DROP PROCEDURE IF EXISTS sp_with_rowcount;
+--enable_warnings
+
+CREATE TABLE temp(f1 CHAR(20),f2 CHAR(25),f3 DATE,f4 INT,f5 CHAR(25),f6 INT);
+INSERT INTO temp SELECT * FROM t10;
+
+delimiter //;
+CREATE PROCEDURE sp_ins_1()
+BEGIN
+  INSERT INTO temp VALUES ('abc', 'abc', '20051003', 100, 'uvw', 1000);
+END//
+
+CREATE PROCEDURE sp_ins_3()
+BEGIN
+  INSERT INTO temp VALUES  ('abc', 'xyz', '19490523',   100, 'uvw', 1000);
+  INSERT INTO temp VALUES  ('abc', 'xyz', '1989-11-09', 100, 'uvw', 1000);
+  INSERT INTO temp VALUES  ('abc', 'xyz', '2005-10-24', 100, 'uvw', 1000);
+END//
+
+CREATE PROCEDURE sp_upd()
+BEGIN
+  UPDATE temp SET temp.f1 = 'updated' WHERE temp.f1 ='abc';
+END//
+
+CREATE PROCEDURE sp_ins_upd()
+BEGIN
+   BEGIN
+      INSERT INTO temp VALUES  ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000);
+      INSERT INTO temp VALUES  ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000);
+      INSERT INTO temp VALUES  ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000);
+      INSERT INTO temp VALUES  ('qwe', 'abc', '2005-11-07', 100, 'uvw', 1000);
+   END;
+   SELECT COUNT( f1 ), f1 FROM temp GROUP BY f1;
+   UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f1 ='qwe' AND temp.f2 = 'abc';
+END//
+
+CREATE PROCEDURE sp_del()
+BEGIN
+  DELETE FROM temp WHERE temp.f1 ='qwe' OR temp.f1 = 'updated_2';
+END//
+
+CREATE PROCEDURE sp_with_rowcount()
+BEGIN
+   BEGIN
+      INSERT INTO temp VALUES  ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000),
+                               ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000),
+                               ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000),
+                               ('qwe', 'xyz', '2005-11-07', 100, 'uvw', 1000);
+   END;
+   SELECT row_count() AS 'row_count() after insert';
+   SELECT row_count() AS 'row_count() after select row_count()';
+   SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
+   UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f2 = 'abc';
+   SELECT row_count() AS 'row_count() after update';
+   SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
+   DELETE FROM temp WHERE temp.f1 = 'updated_2';
+   SELECT row_count() AS 'row_count() after delete';
+END//
+delimiter ;//
+
+--disable_ps2_protocol
+CALL sp_ins_1();
+SELECT row_count();
+--sorted_result
+SELECT * FROM temp;
+
+CALL sp_ins_3();
+SELECT row_count();
+--sorted_result
+SELECT * FROM temp;
+
+CALL sp_upd();
+SELECT row_count();
+--sorted_result
+SELECT * FROM temp;
+
+CALL sp_ins_upd();
+SELECT row_count();
+--sorted_result
+SELECT * FROM temp;
+
+CALL sp_del();
+SELECT row_count();
+--sorted_result
+SELECT * FROM temp;
+
+DELETE FROM temp;
+CALL sp_with_rowcount();
+SELECT row_count();
+--sorted_result
+SELECT * FROM temp;
+--enable_ps2_protocol
+
+# cleanup
+DROP PROCEDURE sp_ins_1;
+DROP PROCEDURE sp_ins_3;
+DROP PROCEDURE sp_upd;
+DROP PROCEDURE sp_ins_upd;
+DROP PROCEDURE sp_del;
+DROP PROCEDURE sp_with_rowcount;
+DROP TABLE temp;
+
+
+# ------------------------------------------------------------------------------
+let $message= Testcase 3.1.10.8:;
+--source include/show_msg.inc
+let $message=
+Ensure that the mysql_affected_rows() C API function always returns the correct
+number of rows affected by the execution of a stored procedure.;
+--source include/show_msg80.inc
+
+#FIXME: 3.1.10.8: to be added later.
+
+# ==============================================================================
+# USE the same .inc to cleanup before and after the test
+--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
+
+# ==============================================================================
+--echo
+--echo .                               +++ END OF SCRIPT +++
+--echo --------------------------------------------------------------------------------
+# ==============================================================================