Fixes to embedded server to be able to run tests with it

(Needed for "list of pushes" web page and autopush)

29 files changed, 6205 insertions, 200 deletions

diff --git a/mysql-test/t/backup.test b/mysql-test/t/backup.test
index a3339ecce69..053e83528e0 100644
--- a/mysql-test/t/backup.test
+++ b/mysql-test/t/backup.test
@@ -1,3 +1,6 @@
+# This test should work in embedded server after we fix mysqltest
+-- source include/not_embedded.inc
+
 #
 # This test is a bit tricky as we can't use backup table to overwrite an old
 # table
diff --git a/mysql-test/t/binlog.test b/mysql-test/t/binlog.test
index 2310e800df7..42dd4ee6299 100644
--- a/mysql-test/t/binlog.test
+++ b/mysql-test/t/binlog.test
@@ -1,6 +1,7 @@
 #
 # misc binlogging tests that do not require a slave running
 #
+-- source include/not_embedded.inc
 -- source include/have_bdb.inc
 -- source include/have_innodb.inc
 
@@ -18,7 +19,7 @@ begin;
 insert t2 values (5);
 commit;
 # first COMMIT must be Query_log_event, second - Xid_log_event
---replace_result "xid=18" "xid=11"
+--replace_result "xid=19" "xid=12"
 --replace_column 2 # 5 #
 show binlog events from 98;
 drop table t1,t2;
@@ -40,7 +41,7 @@ while ($1)
 --enable_query_log
 commit;
 drop table t1;
---replace_result "xid=29" "xid=18"
+--replace_result "xid=30" "xid=19"
 --replace_column 2 # 5 #
 show binlog events in 'master-bin.000001' from 98;
 --replace_column 2 # 5 #
diff --git a/mysql-test/t/blackhole.test b/mysql-test/t/blackhole.test
index ce26d2447eb..e40b84eb5cd 100644
--- a/mysql-test/t/blackhole.test
+++ b/mysql-test/t/blackhole.test
@@ -2,6 +2,7 @@
 # Simple test for blackhole example
 # Taken from the select test
 #
+-- source include/not_embedded.inc
 -- source include/have_blackhole.inc
 
 --disable_warnings
diff --git a/mysql-test/t/compress.test b/mysql-test/t/compress.test
index 46244edd2a8..3f1892b5dec 100644
--- a/mysql-test/t/compress.test
+++ b/mysql-test/t/compress.test
@@ -1,6 +1,9 @@
 # Turn on compression between the client and server
 # and run a number of tests
 
+# Can't test with embedded server
+-- source include/not_embedded.inc
+
 -- source include/have_compress.inc
 
 connect (comp_con,localhost,root,,,,,COMPRESS);
diff --git a/mysql-test/t/ctype_cp932.test b/mysql-test/t/ctype_cp932.test
index 0c70e264891..688d06c4dde 100644
--- a/mysql-test/t/ctype_cp932.test
+++ b/mysql-test/t/ctype_cp932.test
@@ -398,28 +398,6 @@ DROP TABLE t2;
 DROP TABLE t3;
 DROP TABLE t4;
 
-# Test prepared statement with 0x8300 sequence in parameter while
-# running with cp932 client character set.
-RESET MASTER;
-CREATE TABLE t1(f1 blob);
-PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(?)';
-SET @var1= x'8300';
-# TODO: Note that this doesn't actually test the code which was added for
-# bug#11338 because this syntax for prepared statements causes the PS to
-# be replicated differently than if we executed the PS from C or Java.
-# Using this syntax, variable names are inserted into the binlog instead
-# of values. The real goal of this test is to check the code that was
-# added to Item_param::query_val_str() in order to do hex encoding of
-# PS parameters when the client character set is cp932;
-# Bug#11338 has an example java program which can be used to verify this 
-# code (and I have used it to test the fix) until there is some way to
-# exercise this code from mysql-test-run.
-EXECUTE stmt1 USING @var1;
-SHOW BINLOG EVENTS FROM 98;
-SELECT HEX(f1) FROM t1;
-DROP table t1;
-# end test for bug#11338
-
 SET collation_connection='cp932_japanese_ci';
 -- source include/ctype_filesort.inc
 SET collation_connection='cp932_bin';
diff --git a/mysql-test/t/ctype_cp932_notembedded.test b/mysql-test/t/ctype_cp932_notembedded.test
new file mode 100644
index 00000000000..52e7acc3f01
--- /dev/null
+++ b/mysql-test/t/ctype_cp932_notembedded.test
@@ -0,0 +1,32 @@
+-- source include/not_embedded.inc
+-- source include/have_cp932.inc
+
+--character_set cp932
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+set names cp932;
+set character_set_database = cp932;
+
+# Test prepared statement with 0x8300 sequence in parameter while
+# running with cp932 client character set.
+RESET MASTER;
+CREATE TABLE t1(f1 blob);
+PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(?)';
+SET @var1= x'8300';
+# TODO: Note that this doesn't actually test the code which was added for
+# bug#11338 because this syntax for prepared statements causes the PS to
+# be replicated differently than if we executed the PS from C or Java.
+# Using this syntax, variable names are inserted into the binlog instead
+# of values. The real goal of this test is to check the code that was
+# added to Item_param::query_val_str() in order to do hex encoding of
+# PS parameters when the client character set is cp932;
+# Bug#11338 has an example java program which can be used to verify this 
+# code (and I have used it to test the fix) until there is some way to
+# exercise this code from mysql-test-run.
+EXECUTE stmt1 USING @var1;
+SHOW BINLOG EVENTS FROM 98;
+SELECT HEX(f1) FROM t1;
+DROP table t1;
+# end test for bug#11338
diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test
index ca34cc020f3..5ae757b1fde 100644
--- a/mysql-test/t/delayed.test
+++ b/mysql-test/t/delayed.test
@@ -3,6 +3,9 @@
 # (Can't be tested with purify :( )
 #
 
+# This tests not performed with embedded server
+-- source include/not_embedded.inc
+
 --disable_warnings
 drop table if exists t1;
 --enable_warnings
diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test
index 3fb09df5f2f..a7f1eeaa2cc 100644
--- a/mysql-test/t/handler.test
+++ b/mysql-test/t/handler.test
@@ -2,6 +2,9 @@
 # test of HANDLER ...
 #
 
+# should work in embedded server after mysqltest is fixed
+-- source include/not_embedded.inc
+
 --disable_warnings
 drop table if exists t1,t3,t4,t5;
 --enable_warnings
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index a47e635cf74..64d7d55294d 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -1619,33 +1619,7 @@ connection a;
 checksum table t1;
 drop table t1;
 
-#
-# BUG#11238 - in prelocking mode SELECT .. FOR UPDATE is changed to
-# non-blocking SELECT
-#
-create table t1 (col1 integer primary key, col2 integer) engine=innodb;
-insert t1 values (1,100);
-delimiter |;
-create function f1 () returns integer begin
-declare var1 int;
-select col2 into var1 from t1 where col1=1 for update;
-return var1;
-end|
-delimiter ;|
-start transaction;
-select f1();
-connection b;
-send update t1 set col2=0 where col1=1;
 connection default;
-select * from t1;
-connection a;
-rollback;
-connection b;
-reap;
-rollback;
-connection default;
-drop table t1;
-drop function f1;
 disconnect a;
 disconnect b;
 
@@ -1788,6 +1762,7 @@ commit;
 
 set foreign_key_checks=0;
 create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
+--replace_result $MYSQLTEST_VARDIR . master-data/ ''
 -- error 1005
 create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
 set foreign_key_checks=1;
@@ -1798,6 +1773,7 @@ drop table t2;
 
 set foreign_key_checks=0;
 create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
+--replace_result $MYSQLTEST_VARDIR . master-data/ ''
 -- error 1005
 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
 set foreign_key_checks=1;
@@ -1827,6 +1803,7 @@ drop table t2,t1;
 set foreign_key_checks=0;
 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
 create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
+--replace_result $MYSQLTEST_VARDIR . master-data/ ''
 -- error 1025
 rename table t3 to t1;
 set foreign_key_checks=1;
diff --git a/mysql-test/t/innodb_notembedded.test b/mysql-test/t/innodb_notembedded.test
new file mode 100644
index 00000000000..53332d9fda4
--- /dev/null
+++ b/mysql-test/t/innodb_notembedded.test
@@ -0,0 +1,40 @@
+-- source include/not_embedded.inc
+-- source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+connect (a,localhost,root,,);
+connect (b,localhost,root,,);
+
+
+#
+# BUG#11238 - in prelocking mode SELECT .. FOR UPDATE is changed to
+# non-blocking SELECT
+#
+create table t1 (col1 integer primary key, col2 integer) engine=innodb;
+insert t1 values (1,100);
+delimiter |;
+create function f1 () returns integer begin
+declare var1 int;
+select col2 into var1 from t1 where col1=1 for update;
+return var1;
+end|
+delimiter ;|
+start transaction;
+select f1();
+connection b;
+send update t1 set col2=0 where col1=1;
+connection default;
+select * from t1;
+connection a;
+rollback;
+connection b;
+reap;
+rollback;
+connection default;
+drop table t1;
+drop function f1;
+disconnect a;
+disconnect b;
diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test
index 7a9a1ee3f60..4712e7e8266 100644
--- a/mysql-test/t/mysql.test
+++ b/mysql-test/t/mysql.test
@@ -1,3 +1,5 @@
+# This test should work in embedded server after we fix mysqltest
+-- source include/not_embedded.inc
 #
 # Testing the MySQL command line client(mysql)
 #
diff --git a/mysql-test/t/mysql_client_test.test b/mysql-test/t/mysql_client_test.test
index 4899ef9cecc..b61deeac001 100644
--- a/mysql-test/t/mysql_client_test.test
+++ b/mysql-test/t/mysql_client_test.test
@@ -1,3 +1,6 @@
+# This test should work in embedded server after we fix mysqltest
+-- source include/not_embedded.inc
+
 # We run with different binaries for normal and --embedded-server
 #
 # If this test fails with "command "$MYSQL_CLIENT_TEST" failed",
diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test
index 1bb1cd48f0d..858c704c10e 100644
--- a/mysql-test/t/mysqltest.test
+++ b/mysql-test/t/mysqltest.test
@@ -1,3 +1,5 @@
+# This test should work in embedded server after mysqltest is fixed
+-- source include/not_embedded.inc
 
 # ============================================================================
 #
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 2dca45470cc..d416f34ce45 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -819,29 +819,14 @@ select sql_cache * from t1 where s1=1;
 end;//
 delimiter ;//
 call f1();
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
 call f1();
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
 call f1();
 select sql_cache * from t1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
 insert into t1 values (1);
 select sql_cache * from t1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
 call f1();
 call f1();
 select sql_cache * from t1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
 flush query cache;
 reset query cache;
 flush status;
diff --git a/mysql-test/t/query_cache_notembedded.test b/mysql-test/t/query_cache_notembedded.test
index fd4785ffe95..5e1ab7051e5 100644
--- a/mysql-test/t/query_cache_notembedded.test
+++ b/mysql-test/t/query_cache_notembedded.test
@@ -97,4 +97,88 @@ connection root;
 SELECT * FROM t1;
 drop table t1;
 
+#
+# query in QC from normal execution and SP (BUG#6897)
+# improved to also test BUG#3583 and BUG#12990
+#
+flush query cache;
+reset query cache;
+flush status;
+delimiter //;
+create table t1 (s1 int)//
+create procedure f1 () begin
+select sql_cache * from t1;
+select sql_cache * from t1;
+select sql_cache * from t1;
+end;//
+create procedure f2 () begin
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+end;//
+create procedure f3 () begin
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+end;//
+create procedure f4 () begin
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1 where s1=1;
+end;//
+delimiter ;//
+call f1();
+--replace_result 1 3
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+call f1();
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+call f1();
+select sql_cache * from t1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+insert into t1 values (1);
+select sql_cache * from t1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+call f1();
+call f1();
+select sql_cache * from t1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+flush query cache;
+reset query cache;
+flush status;
+select sql_cache * from t1;
+select sql_cache * from t1 where s1=1;
+call f1();
+call f2();
+call f3();
+call f4();
+call f4();
+call f3();
+call f2();
+select sql_cache * from t1 where s1=1;
+insert into t1 values (2);
+call f1();
+select sql_cache * from t1 where s1=1;
+select sql_cache * from t1;
+call f1();
+call f3();
+call f3();
+call f1();
+
+drop procedure f1;
+drop procedure f2;
+drop procedure f3;
+drop procedure f4;
+drop table t1;
+
+
 set GLOBAL query_cache_size=0;
diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test
index b9008ac34a3..175a5bba6fa 100644
--- a/mysql-test/t/read_only.test
+++ b/mysql-test/t/read_only.test
@@ -1,6 +1,9 @@
 # Test of the READ_ONLY global variable:
 # check that it blocks updates unless they are only on temporary tables.
 
+# should work with embedded server after mysqltest is fixed
+-- source include/not_embedded.inc
+
 --disable_warnings
 DROP TABLE IF EXISTS t1,t2,t3;
 --enable_warnings
diff --git a/mysql-test/t/skip_grants.test b/mysql-test/t/skip_grants.test
index 16b0fbc4d25..a0164cb0759 100644
--- a/mysql-test/t/skip_grants.test
+++ b/mysql-test/t/skip_grants.test
@@ -1,3 +1,6 @@
+# This tests not performed with embedded server
+-- source include/not_embedded.inc
+
 --disable_warnings
 drop table if exists t1,v1;
 drop view if exists t1,v1;
diff --git a/mysql-test/t/sp-destruct.test b/mysql-test/t/sp-destruct.test
index fb368a1e621..4f5f1cdcb9b 100644
--- a/mysql-test/t/sp-destruct.test
+++ b/mysql-test/t/sp-destruct.test
@@ -46,10 +46,13 @@ flush table mysql.proc;
 
 # Thrashing the .frm file
 --system echo 'saljdlfa' > $MYSQLTEST_VARDIR/master-data/mysql/proc.frm
+--replace_result $MYSQLTEST_VARDIR . master-data// ''
 --error ER_NOT_FORM_FILE
 call bug14233();
+--replace_result $MYSQLTEST_VARDIR . master-data// ''
 --error ER_NOT_FORM_FILE
 create view v1 as select bug14233_f();
+--replace_result $MYSQLTEST_VARDIR . master-data// ''
 --error ER_NOT_FORM_FILE
 insert into t1 values (0);
 
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index c4745537de3..4b307de2ad0 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -648,28 +648,6 @@ call bug3294()|
 drop procedure bug3294|
 
 #
-# BUG#6807: Stored procedure crash if CREATE PROCEDURE ... KILL QUERY
-#
---disable_warnings
-drop procedure if exists bug6807|
---enable_warnings
-create procedure bug6807()
-begin
-  declare id int;
-
-  set id = connection_id();
-  kill query id;
-  select 'Not reached';
-end|
-
---error 1317
-call bug6807()|
---error 1317
-call bug6807()|
-
-drop procedure bug6807|
-
-#
 # BUG#876: Stored Procedures: Invalid SQLSTATE is allowed in 
 #          a DECLARE ? HANDLER FOR stmt.
 #
diff --git a/mysql-test/t/sp-threads.test b/mysql-test/t/sp-threads.test
index 70c1efb1f0b..d8a8ce5dae7 100644
--- a/mysql-test/t/sp-threads.test
+++ b/mysql-test/t/sp-threads.test
@@ -1,3 +1,5 @@
+# This test should work in embedded server after mysqltest is fixed
+-- source include/not_embedded.inc
 #
 # Testing stored procedures with multiple connections,
 # except security/privilege tests, they go to sp-security.test
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index a4b99620344..243c1b413b7 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -699,10 +699,11 @@ drop procedure into_test4|
 --disable_warnings
 drop procedure if exists into_outfile|
 --enable_warnings
-create procedure into_outfile(x char(16), y int)
+--replace_result $MYSQLTEST_VARDIR ..
+eval create procedure into_outfile(x char(16), y int)
 begin
   insert into test.t1 values (x, y);
-  select * into outfile "../tmp/spout" from test.t1;
+  select * into outfile "$MYSQLTEST_VARDIR/tmp/spout" from test.t1;
   insert into test.t1 values (concat(x, "2"), y+2);
 end|
 
@@ -715,10 +716,11 @@ drop procedure into_outfile|
 --disable_warnings
 drop procedure if exists into_dumpfile|
 --enable_warnings
-create procedure into_dumpfile(x char(16), y int)
+--replace_result $MYSQLTEST_VARDIR ..
+eval create procedure into_dumpfile(x char(16), y int)
 begin
   insert into test.t1 values (x, y);
-  select * into dumpfile "../tmp/spdump" from test.t1 limit 1;
+  select * into dumpfile "$MYSQLTEST_VARDIR/tmp/spdump" from test.t1 limit 1;
   insert into test.t1 values (concat(x, "2"), y+2);
 end|
 
@@ -1531,7 +1533,7 @@ begin
   end while;
 end|
 show create procedure opp|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
 show procedure status like '%p%'|
 
 # This isn't the fastest way in the world to compute prime numbers, so
@@ -1549,7 +1551,7 @@ select * from t3 where i=45 or i=100 or i=199|
 drop table t3|
 drop procedure opp|
 drop procedure ip|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
 show procedure status like '%p%'|
 
 
@@ -1618,13 +1620,13 @@ drop procedure if exists bar|
 create procedure bar(x char(16), y int)
  comment "111111111111" sql security invoker
  insert into test.t1 values (x, y)|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
 show procedure status like 'bar'|
 alter procedure bar comment "2222222222" sql security definer|
 alter procedure bar comment "3333333333"|
 alter procedure bar|
 show create procedure bar|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
 show procedure status like 'bar'|
 drop procedure bar|
 
@@ -2965,7 +2967,6 @@ begin
   show databases like 'foo';
   show errors;
   show columns from t1;
-  show grants for 'root'@'localhost';
   show keys from t1;
   show open tables like 'foo';
   show privileges;
@@ -2987,20 +2988,6 @@ call bug4902()|
 
 drop procedure bug4902|
 
-# We need separate SP for SHOW PROCESSLIST  since we want use replace_column
---disable_warnings
-drop procedure if exists bug4902_2|
---enable_warnings
-create procedure bug4902_2()
-begin
-  show processlist;
-end|
---replace_column 1 # 6 # 3 localhost
-call bug4902_2()|
---replace_column 1 # 6 # 3 localhost
-call bug4902_2()|
-drop procedure bug4902_2|
-
 #
 # BUG#4904
 #
@@ -3215,44 +3202,6 @@ select @x|
 delete from t1|
 drop procedure bug4941|
 
-
-#
-# BUG#3583: query cache doesn't work for stored procedures
-#
---disable_warnings
-drop procedure if exists bug3583|
---enable_warnings
---disable_warnings
-drop procedure if exists bug3583|
---enable_warnings
-create procedure bug3583()
-begin
-  declare c int;
-
-  select * from t1;
-  select count(*) into c from t1;
-  select c;
-end|
-
-insert into t1 values ("x", 3), ("y", 5)|
-set @x = @@query_cache_size|
-set global query_cache_size = 10*1024*1024|
-
-flush status|
-flush query cache|
-show status like 'Qcache_hits'|
-call bug3583()|
-show status like 'Qcache_hits'|
-call bug3583()|
-call bug3583()|
-show status like 'Qcache_hits'|
-
-set global query_cache_size = @x|
-flush status|
-flush query cache|
-delete from t1|
-drop procedure bug3583|
-
 #
 # BUG#4905: Stored procedure doesn't clear for "Rows affected"
 #
@@ -3562,24 +3511,6 @@ select id, bug5240() from t1|
 drop function bug5240|
 
 #
-# BUG#5278: Stored procedure packets out of order if SET PASSWORD.
-#
---disable_warnings
-drop function if exists bug5278|
---enable_warnings
-create function bug5278 () returns char
-begin
-  SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
-  return 'okay';
-end|
-
---error 1133
-select bug5278()|
---error 1133
-select bug5278()|
-drop function bug5278|
-
-#
 # BUG#7992: rolling back temporary Item tree changes in SP
 #
 --disable_warnings
@@ -5176,24 +5107,6 @@ select bug10100f(5)|
 call bug10100t(5)|
 
 #end of the stack checking
-set @@max_sp_recursion_depth=255|
-set @var=1|
-#disable log because error about stack overrun contains numbers which
-#depend on a system
--- disable_result_log
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100p(255, @var)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pt(1,255)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pv(1,255)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pd(1,255)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pc(1,255)|
--- enable_result_log
-set @@max_sp_recursion_depth=0|
-
 deallocate prepare stmt2|
 
 drop function bug10100f|
diff --git a/mysql-test/t/sp.test.orig b/mysql-test/t/sp.test.orig
new file mode 100644
index 00000000000..a4b99620344
--- /dev/null
+++ b/mysql-test/t/sp.test.orig
@@ -0,0 +1,5716 @@
+#
+# Basic stored PROCEDURE tests
+#
+# Please keep this file free of --error cases and other
+# things that will not run in a single debugged mysqld
+# process (e.g. master-slave things).
+#
+# Test cases for bugs are added at the end. See template there.
+#
+# Tests that require --error go into sp-error.test
+# Tests that require inndb go into sp_trans.test
+# Tests that check privilege and security issues go to sp-security.test.
+# Tests that require multiple connections, except security/privilege tests,
+#   go to sp-thread.
+# Tests that uses 'goto' to into sp-goto.test (currently disabled)
+# Tests that destroys system tables (e.g. mysql.proc) for error testing
+#   go to sp-destruct.
+
+use test;
+
+# Test tables
+#
+# t1 and t2 are reused throughout the file, and dropped at the end.
+# t3 and up are created and dropped when needed.
+#
+--disable_warnings
+drop table if exists t1,t2,t3,t4;
+--enable_warnings
+create table t1 (
+	id   char(16) not null default '',
+        data int not null
+);
+create table t2 (
+	s   char(16),
+        i   int,
+	d   double
+);
+
+
+# Single statement, no params.
+--disable_warnings
+drop procedure if exists foo42;
+--enable_warnings
+create procedure foo42()
+  insert into test.t1 values ("foo", 42);
+
+call foo42();
+select * from t1;
+delete from t1;
+drop procedure foo42;
+
+
+# Single statement, two IN params.
+--disable_warnings
+drop procedure if exists bar;
+--enable_warnings
+create procedure bar(x char(16), y int)
+  insert into test.t1 values (x, y);
+
+call bar("bar", 666);
+select * from t1;
+delete from t1;
+# Don't drop procedure yet...
+
+
+# Now for multiple statements...
+delimiter |;
+
+# Empty statement
+--disable_warnings
+drop procedure if exists empty|
+--enable_warnings
+create procedure empty()
+begin
+end|
+
+call empty()|
+drop procedure empty|
+
+# Scope test. This is legal (warnings might be possible in the future,
+# but for the time being, we just accept it).
+--disable_warnings
+drop procedure if exists scope|
+--enable_warnings
+create procedure scope(a int, b float)
+begin
+  declare b int;
+  declare c float;
+
+  begin
+    declare c int;
+  end;
+end|
+
+drop procedure scope|
+
+# Two statements.
+--disable_warnings
+drop procedure if exists two|
+--enable_warnings
+create procedure two(x1 char(16), x2 char(16), y int)
+begin
+  insert into test.t1 values (x1, y);
+  insert into test.t1 values (x2, y);
+end|
+
+call two("one", "two", 3)|
+select * from t1|
+delete from t1|
+drop procedure two|
+
+
+# Simple test of local variables and SET.
+--disable_warnings
+drop procedure if exists locset|
+--enable_warnings
+create procedure locset(x char(16), y int)
+begin
+  declare z1, z2 int;
+  set z1 = y;
+  set z2 = z1+2;
+  insert into test.t1 values (x, z2);
+end|
+
+call locset("locset", 19)|
+select * from t1|
+delete from t1|
+drop procedure locset|
+
+
+# In some contexts local variables are not recognized
+# (and in some, you have to qualify the identifier).
+--disable_warnings
+drop procedure if exists setcontext|
+--enable_warnings
+create procedure setcontext()
+begin
+  declare data int default 2;
+
+  insert into t1 (id, data) values ("foo", 1);
+  replace t1 set data = data, id = "bar";
+  update t1 set id = "kaka", data = 3 where t1.data = data;
+end|
+
+call setcontext()|
+select * from t1|
+delete from t1|
+drop procedure setcontext|
+
+
+# Set things to null
+create table t3 ( d date, i int, f double, s varchar(32) )|
+
+--disable_warnings
+drop procedure if exists nullset|
+--enable_warnings
+create procedure nullset()
+begin
+  declare ld date;
+  declare li int;
+  declare lf double;
+  declare ls varchar(32);
+
+  set ld = null, li = null, lf = null, ls = null;
+  insert into t3 values (ld, li, lf, ls);
+
+  insert into t3 (i, f, s) values ((ld is null), 1,    "ld is null"),
+                                  ((li is null), 1,    "li is null"),
+				  ((li = 0),     null, "li = 0"),
+				  ((lf is null), 1,    "lf is null"),
+				  ((lf = 0),     null, "lf = 0"),
+				  ((ls is null), 1,    "ls is null");
+end|
+
+call nullset()|
+select * from t3|
+drop table t3|
+drop procedure nullset|
+
+
+# The peculiar (non-standard) mixture of variables types in SET.
+--disable_warnings
+drop procedure if exists mixset|
+--enable_warnings
+create procedure mixset(x char(16), y int)
+begin
+  declare z int;
+
+  set @z = y, z = 666, max_join_size = 100;
+  insert into test.t1 values (x, z);
+end|
+
+call mixset("mixset", 19)|
+show variables like 'max_join_size'|
+select id,data,@z from t1|
+delete from t1|
+drop procedure mixset|
+
+
+# Multiple CALL statements, one with OUT parameter.
+--disable_warnings
+drop procedure if exists zip|
+--enable_warnings
+create procedure zip(x char(16), y int)
+begin
+  declare z int;
+  call zap(y, z);
+  call bar(x, z);
+end|
+
+# SET local variables and OUT parameter.
+--disable_warnings
+drop procedure if exists zap|
+--enable_warnings
+create procedure zap(x int, out y int)
+begin
+  declare z int;
+  set z = x+1, y = z;
+end|
+
+call zip("zip", 99)|
+select * from t1|
+delete from t1|
+drop procedure zip|
+drop procedure bar|
+
+# Top-level OUT parameter
+call zap(7, @zap)|
+select @zap|
+
+drop procedure zap|
+
+
+# "Deep" calls...
+--disable_warnings
+drop procedure if exists c1|
+--enable_warnings
+create procedure c1(x int)
+  call c2("c", x)|
+--disable_warnings
+drop procedure if exists c2|
+--enable_warnings
+create procedure c2(s char(16), x int)
+  call c3(x, s)|
+--disable_warnings
+drop procedure if exists c3|
+--enable_warnings
+create procedure c3(x int, s char(16))
+  call c4("level", x, s)|
+--disable_warnings
+drop procedure if exists c4|
+--enable_warnings
+create procedure c4(l char(8), x int, s char(16))
+  insert into t1 values (concat(l,s), x)|
+
+call c1(42)|
+select * from t1|
+delete from t1|
+drop procedure c1|
+drop procedure c2|
+drop procedure c3|
+drop procedure c4|
+
+# INOUT test
+--disable_warnings
+drop procedure if exists iotest|
+--enable_warnings
+create procedure iotest(x1 char(16), x2 char(16), y int)
+begin
+  call inc2(x2, y);
+  insert into test.t1 values (x1, y);
+end|
+
+--disable_warnings
+drop procedure if exists inc2|
+--enable_warnings
+create procedure inc2(x char(16), y int)
+begin
+  call inc(y);
+  insert into test.t1 values (x, y);
+end|
+
+--disable_warnings
+drop procedure if exists inc|
+--enable_warnings
+create procedure inc(inout io int)
+  set io = io + 1|
+
+call iotest("io1", "io2", 1)|
+select * from t1|
+delete from t1|
+drop procedure iotest|
+drop procedure inc2|
+
+# Propagating top-level @-vars
+--disable_warnings
+drop procedure if exists incr|
+--enable_warnings
+create procedure incr(inout x int)
+  call inc(x)|
+
+# Before
+select @zap|
+call incr(@zap)|
+# After
+select @zap|
+
+drop procedure inc|
+drop procedure incr|
+
+# Call-by-value test
+#  The expected result is:
+#    ("cbv2", 4)
+#    ("cbv1", 4711)
+--disable_warnings
+drop procedure if exists cbv1|
+--enable_warnings
+create procedure cbv1()
+begin
+  declare y int default 3;
+
+  call cbv2(y+1, y);
+  insert into test.t1 values ("cbv1", y);
+end|
+
+--disable_warnings
+drop procedure if exists cbv2|
+--enable_warnings
+create procedure cbv2(y1 int, inout y2 int)
+begin
+  set y2 = 4711;
+  insert into test.t1 values ("cbv2", y1);
+end|
+
+call cbv1()|
+select * from t1|
+delete from t1|
+drop procedure cbv1|
+drop procedure cbv2|
+
+
+# Subselect arguments
+
+insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)|
+
+--disable_warnings
+drop procedure if exists sub1|
+--enable_warnings
+create procedure sub1(id char(16), x int)
+  insert into test.t1 values (id, x)|
+
+--disable_warnings
+drop procedure if exists sub2|
+--enable_warnings
+create procedure sub2(id char(16))
+begin
+  declare x int;
+  set x = (select sum(t.i) from test.t2 t);
+  insert into test.t1 values (id, x);
+end|
+
+--disable_warnings
+drop procedure if exists sub3|
+--enable_warnings
+create function sub3(i int) returns int
+  return i+1|
+
+call sub1("sub1a", (select 7))|
+call sub1("sub1b", (select max(i) from t2))|
+--error ER_OPERAND_COLUMNS
+call sub1("sub1c", (select i,d from t2 limit 1))|
+call sub1("sub1d", (select 1 from (select 1) a))|
+call sub2("sub2")|
+select * from t1|
+select sub3((select max(i) from t2))|
+drop procedure sub1|
+drop procedure sub2|
+drop function sub3|
+delete from t1|
+delete from t2|
+
+# Basic tests of the flow control constructs
+
+# Just test on 'x'...
+--disable_warnings
+drop procedure if exists a0|
+--enable_warnings
+create procedure a0(x int)
+while x do
+  set x = x-1;
+  insert into test.t1 values ("a0", x);
+end while|
+
+call a0(3)|
+select * from t1|
+delete from t1|
+drop procedure a0|
+
+
+# The same, but with a more traditional test.
+--disable_warnings
+drop procedure if exists a|
+--enable_warnings
+create procedure a(x int)
+while x > 0 do
+  set x = x-1;
+  insert into test.t1 values ("a", x);
+end while|
+
+call a(3)|
+select * from t1|
+delete from t1|
+drop procedure a|
+
+
+# REPEAT
+--disable_warnings
+drop procedure if exists b|
+--enable_warnings
+create procedure b(x int)
+repeat
+  insert into test.t1 values (repeat("b",3), x);
+  set x = x-1;
+until x = 0 end repeat|
+
+call b(3)|
+select * from t1|
+delete from t1|
+drop procedure b|
+
+
+# Check that repeat isn't parsed the wrong way
+--disable_warnings
+drop procedure if exists b2|
+--enable_warnings
+create procedure b2(x int)
+repeat(select 1 into outfile 'b2');
+  insert into test.t1 values (repeat("b2",3), x);
+  set x = x-1;
+until x = 0 end repeat|
+
+# We don't actually want to call it.
+drop procedure b2|
+
+
+# Labelled WHILE with ITERATE (pointless really)
+--disable_warnings
+drop procedure if exists c|
+--enable_warnings
+create procedure c(x int)
+hmm: while x > 0 do
+  insert into test.t1 values ("c", x);
+  set x = x-1;
+  iterate hmm;
+  insert into test.t1 values ("x", x);
+end while hmm|
+
+call c(3)|
+select * from t1|
+delete from t1|
+drop procedure c|
+
+
+# Labelled WHILE with LEAVE
+--disable_warnings
+drop procedure if exists d|
+--enable_warnings
+create procedure d(x int)
+hmm: while x > 0 do
+  insert into test.t1 values ("d", x);
+  set x = x-1;
+  leave hmm;
+  insert into test.t1 values ("x", x);
+end while|
+
+call d(3)|
+select * from t1|
+delete from t1|
+drop procedure d|
+
+
+# LOOP, with simple IF statement
+--disable_warnings
+drop procedure if exists e|
+--enable_warnings
+create procedure e(x int)
+foo: loop
+  if x = 0 then
+    leave foo;
+  end if;
+  insert into test.t1 values ("e", x);
+  set x = x-1;
+end loop foo|
+
+call e(3)|
+select * from t1|
+delete from t1|
+drop procedure e|
+
+
+# A full IF statement
+--disable_warnings
+drop procedure if exists f|
+--enable_warnings
+create procedure f(x int)
+if x < 0 then
+  insert into test.t1 values ("f", 0);
+elseif x = 0 then
+  insert into test.t1 values ("f", 1);
+else
+  insert into test.t1 values ("f", 2);
+end if|
+
+call f(-2)|
+call f(0)|
+call f(4)|
+select * from t1|
+delete from t1|
+drop procedure f|
+
+
+# This form of CASE is really just syntactic sugar for IF-ELSEIF-...
+--disable_warnings
+drop procedure if exists g|
+--enable_warnings
+create procedure g(x int)
+case
+when x < 0 then
+  insert into test.t1 values ("g", 0);
+when x = 0 then
+  insert into test.t1 values ("g", 1);
+else
+  insert into test.t1 values ("g", 2);
+end case|
+
+call g(-42)|
+call g(0)|
+call g(1)|
+select * from t1|
+delete from t1|
+drop procedure g|
+
+
+# The "simple CASE"
+--disable_warnings
+drop procedure if exists h|
+--enable_warnings
+create procedure h(x int)
+case x
+when 0 then
+  insert into test.t1 values ("h0", x);
+when 1 then
+  insert into test.t1 values ("h1", x);
+else
+  insert into test.t1 values ("h?", x);
+end case|
+
+call h(0)|
+call h(1)|
+call h(17)|
+select * from t1|
+delete from t1|
+drop procedure h|
+
+
+# It's actually possible to LEAVE a BEGIN-END block
+--disable_warnings
+drop procedure if exists i|
+--enable_warnings
+create procedure i(x int)
+foo:
+begin
+  if x = 0 then
+    leave foo;
+  end if;
+  insert into test.t1 values ("i", x);
+end foo|
+
+call i(0)|
+call i(3)|
+select * from t1|
+delete from t1|
+drop procedure i|
+
+
+# SELECT with one of more result set sent back to the clinet
+insert into t1 values ("foo", 3), ("bar", 19)|
+insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)|
+
+--disable_warnings
+drop procedure if exists sel1|
+--enable_warnings
+create procedure sel1()
+begin
+  select * from t1;
+end|
+
+call sel1()|
+drop procedure sel1|
+
+--disable_warnings
+drop procedure if exists sel2|
+--enable_warnings
+create procedure sel2()
+begin
+  select * from t1;
+  select * from t2;
+end|
+
+call sel2()|
+drop procedure sel2|
+delete from t1|
+delete from t2|
+
+# SELECT INTO local variables
+--disable_warnings
+drop procedure if exists into_test|
+--enable_warnings
+create procedure into_test(x char(16), y int)
+begin
+  insert into test.t1 values (x, y);
+  select id,data into x,y from test.t1 limit 1;
+  insert into test.t1 values (concat(x, "2"), y+2);
+end|
+
+call into_test("into", 100)|
+select * from t1|
+delete from t1|
+drop procedure into_test|
+
+
+# SELECT INTO with a mix of local and global variables
+--disable_warnings
+drop procedure if exists into_tes2|
+--enable_warnings
+create procedure into_test2(x char(16), y int)
+begin
+  insert into test.t1 values (x, y);
+  select id,data into x,@z from test.t1 limit 1;
+  insert into test.t1 values (concat(x, "2"), y+2);
+end|
+
+call into_test2("into", 100)|
+select id,data,@z from t1|
+delete from t1|
+drop procedure into_test2|
+
+
+# SELECT * INTO ... (bug test)
+--disable_warnings
+drop procedure if exists into_test3|
+--enable_warnings
+create procedure into_test3()
+begin
+  declare x char(16);
+  declare y int;
+
+  select * into x,y from test.t1 limit 1;
+  insert into test.t2 values (x, y, 0.0);
+end|
+
+insert into t1 values ("into3", 19)|
+# Two call needed for bug test
+call into_test3()|
+call into_test3()|
+select * from t2|
+delete from t1|
+delete from t2|
+drop procedure into_test3|
+
+
+# SELECT INTO with no data is a warning ("no data", which we will
+# not see normally). When not caught, execution proceeds.
+--disable_warnings
+drop procedure if exists into_test4|
+--enable_warnings
+create procedure into_test4()
+begin
+  declare x int;
+
+  select data into x from test.t1 limit 1;
+  insert into test.t3 values ("into4", x);
+end|
+
+delete from t1|
+create table t3 ( s char(16), d int)|
+call into_test4()|
+select * from t3|
+insert into t1 values ("i4", 77)|
+call into_test4()|
+select * from t3|
+delete from t1|
+drop table t3|
+drop procedure into_test4|
+
+
+# These two (and the two procedures above) caused an assert() to fail in
+# sql_base.cc:lock_tables() at some point.
+--disable_warnings
+drop procedure if exists into_outfile|
+--enable_warnings
+create procedure into_outfile(x char(16), y int)
+begin
+  insert into test.t1 values (x, y);
+  select * into outfile "../tmp/spout" from test.t1;
+  insert into test.t1 values (concat(x, "2"), y+2);
+end|
+
+--system rm -f $MYSQLTEST_VARDIR/tmp/spout
+call into_outfile("ofile", 1)|
+--system rm -f $MYSQLTEST_VARDIR/tmp/spout
+delete from t1|
+drop procedure into_outfile|
+
+--disable_warnings
+drop procedure if exists into_dumpfile|
+--enable_warnings
+create procedure into_dumpfile(x char(16), y int)
+begin
+  insert into test.t1 values (x, y);
+  select * into dumpfile "../tmp/spdump" from test.t1 limit 1;
+  insert into test.t1 values (concat(x, "2"), y+2);
+end|
+
+--system rm -f $MYSQLTEST_VARDIR/tmp/spdump
+call into_dumpfile("dfile", 1)|
+--system rm -f $MYSQLTEST_VARDIR/tmp/spdump
+delete from t1|
+drop procedure into_dumpfile|
+
+--disable_warnings
+drop procedure if exists create_select|
+--enable_warnings
+create procedure create_select(x char(16), y int)
+begin
+  insert into test.t1 values (x, y);
+  create temporary table test.t3 select * from test.t1;
+  insert into test.t3 values (concat(x, "2"), y+2);
+end|
+
+call create_select("cs", 90)|
+select * from t1, t3|
+drop table t3|
+delete from t1|
+drop procedure create_select|
+
+
+# A minimal, constant FUNCTION.
+--disable_warnings
+drop function if exists e|
+--enable_warnings
+create function e() returns double
+  return 2.7182818284590452354|
+
+set @e = e()|
+select e(), @e|
+
+# A minimal function with one argument
+--disable_warnings
+drop function if exists inc|
+--enable_warnings
+create function inc(i int) returns int
+  return i+1|
+
+select inc(1), inc(99), inc(-71)|
+
+# A minimal function with two arguments
+--disable_warnings
+drop function if exists mul|
+--enable_warnings
+create function mul(x int, y int) returns int
+  return x*y|
+
+select mul(1,1), mul(3,5), mul(4711, 666)|
+
+# A minimal string function
+--disable_warnings
+drop function if exists append|
+--enable_warnings
+create function append(s1 char(8), s2 char(8)) returns char(16)
+  return concat(s1, s2)|
+
+select append("foo", "bar")|
+
+# A function with flow control
+--disable_warnings
+drop function if exists fac|
+--enable_warnings
+create function fac(n int unsigned) returns bigint unsigned
+begin
+  declare f bigint unsigned default 1;
+
+  while n > 1 do
+    set f = f * n;
+    set n = n - 1;
+  end while;
+  return f;
+end|
+
+select fac(1), fac(2), fac(5), fac(10)|
+
+# Nested calls
+--disable_warnings
+drop function if exists fun|
+--enable_warnings
+create function fun(d double, i int, u int unsigned) returns double
+  return mul(inc(i), fac(u)) / e()|
+
+select fun(2.3, 3, 5)|
+
+
+# Various function calls in differen statements
+
+insert into t2 values (append("xxx", "yyy"), mul(4,3), e())|
+insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))|
+
+# Disable PS because double's give a bit different values
+--disable_ps_protocol
+select * from t2 where s = append("a", "b")|
+select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)|
+select * from t2 where d = e()|
+select * from t2|
+--enable_ps_protocol
+delete from t2|
+
+drop function e|
+drop function inc|
+drop function mul|
+drop function append|
+drop function fun|
+
+
+#
+# CONDITIONs and HANDLERs
+#
+
+--disable_warnings
+drop procedure if exists hndlr1|
+--enable_warnings
+create procedure hndlr1(val int)
+begin
+  declare x int default 0;
+  declare foo condition for 1136;
+  declare bar condition for sqlstate '42S98';        # Just for testing syntax
+  declare zip condition for sqlstate value '42S99';  # Just for testing syntax
+  declare continue handler for foo set x = 1;
+
+  insert into test.t1 values ("hndlr1", val, 2);  # Too many values
+  if (x) then
+    insert into test.t1 values ("hndlr1", val);   # This instead then
+  end if;
+end|
+
+call hndlr1(42)|
+select * from t1|
+delete from t1|
+drop procedure hndlr1|
+
+--disable_warnings
+drop procedure if exists hndlr2|
+--enable_warnings
+create procedure hndlr2(val int)
+begin
+  declare x int default 0;
+
+  begin
+    declare exit handler for sqlstate '21S01' set x = 1;
+
+    insert into test.t1 values ("hndlr2", val, 2); # Too many values
+  end;
+
+  insert into test.t1 values ("hndlr2", x);
+end|
+
+call hndlr2(42)|
+select * from t1|
+delete from t1|
+drop procedure hndlr2|
+
+
+--disable_warnings
+drop procedure if exists hndlr3|
+--enable_warnings
+create procedure hndlr3(val int)
+begin
+  declare x int default 0;
+  declare continue handler for sqlexception        # Any error
+  begin
+    declare z int;
+
+    set z = 2 * val;
+    set x = 1;
+  end;
+
+  if val < 10 then
+    begin
+      declare y int;
+
+      set y = val + 10;
+      insert into test.t1 values ("hndlr3", y, 2);  # Too many values
+      if x then
+        insert into test.t1 values ("hndlr3", y);
+      end if;
+    end;
+  end if;
+end|
+
+call hndlr3(3)|
+select * from t1|
+delete from t1|
+drop procedure hndlr3|
+
+
+# Variables might be uninitialized when using handlers
+# (Otherwise the compiler can detect if a variable is not set, but
+#  not in this case.)
+create table t3 ( id   char(16), data int )|
+
+--disable_warnings
+drop procedure if exists hndlr4|
+--enable_warnings
+create procedure hndlr4()
+begin
+  declare x int default 0;
+  declare val int;	                           # No default
+  declare continue handler for sqlstate '02000' set x=1;
+
+  select data into val from test.t3 where id='z' limit 1;  # No hits
+
+  insert into test.t3 values ('z', val);
+end|
+
+call hndlr4()|
+select * from t3|
+drop table t3|
+drop procedure hndlr4|
+
+
+#
+# Cursors
+#
+--disable_warnings
+drop procedure if exists cur1|
+--enable_warnings
+create procedure cur1()
+begin
+  declare a char(16);
+  declare b int;
+  declare c double;
+  declare done int default 0;
+  declare c cursor for select * from test.t2;
+  declare continue handler for sqlstate '02000' set done = 1;
+
+  open c;
+  repeat
+    fetch c into a, b, c;
+    if not done then
+       insert into test.t1 values (a, b+c);
+    end if;
+  until done end repeat;
+  close c;
+end|
+
+insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)|
+call cur1()|
+select * from t1|
+drop procedure cur1|
+
+create table t3 ( s char(16), i int )|
+
+--disable_warnings
+drop procedure if exists cur2|
+--enable_warnings
+create procedure cur2()
+begin
+  declare done int default 0;
+  declare c1 cursor for select id,data from test.t1;
+  declare c2 cursor for select i from test.t2;
+  declare continue handler for sqlstate '02000' set done = 1;
+
+  open c1;
+  open c2;
+  repeat
+  begin
+    declare a char(16);
+    declare b,c int;
+
+    fetch from c1 into a, b;
+    fetch next from c2 into c;
+    if not done then
+      if b < c then
+        insert into test.t3 values (a, b);
+      else
+        insert into test.t3 values (a, c);
+      end if;
+    end if;
+  end;
+  until done end repeat;
+  close c1;
+  close c2;
+end|
+
+call cur2()|
+select * from t3|
+delete from t1|
+delete from t2|
+drop table t3|
+drop procedure cur2|
+
+
+# The few characteristics we parse
+--disable_warnings
+drop procedure if exists chistics|
+--enable_warnings
+create procedure chistics()
+    language sql
+    modifies sql data
+    not deterministic
+    sql security definer
+    comment 'Characteristics procedure test'
+  insert into t1 values ("chistics", 1)|
+
+show create procedure chistics|
+# Call it, just to make sure.
+call chistics()|
+select * from t1|
+delete from t1|
+alter procedure chistics sql security invoker|
+show create procedure chistics|
+drop procedure chistics|
+
+--disable_warnings
+drop function if exists chistics|
+--enable_warnings
+create function chistics() returns int
+    language sql
+    deterministic
+    sql security invoker
+    comment 'Characteristics procedure test'
+  return 42|
+
+show create function chistics|
+# Call it, just to make sure.
+select chistics()|
+alter function chistics
+   no sql
+   comment 'Characteristics function test'|
+show create function chistics|
+drop function chistics|
+
+
+# Check mode settings
+insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)|
+
+set @@sql_mode = 'ANSI'|
+delimiter $|
+--disable_warnings
+drop procedure if exists modes$
+--enable_warnings
+create procedure modes(out c1 int, out c2 int)
+begin
+  declare done int default 0;
+  declare x int;
+  declare c cursor for select data from t1;
+  declare continue handler for sqlstate '02000' set done = 1;
+
+  select 1 || 2 into c1;
+  set c2 = 0;
+  open c;
+  repeat
+    fetch c into x;
+    if not done then
+      set c2 = c2 + 1;
+    end if;
+  until done end repeat;
+  close c;
+end$
+delimiter |$
+set @@sql_mode = ''|
+
+set sql_select_limit = 1|
+call modes(@c1, @c2)|
+set sql_select_limit = default|
+
+select @c1, @c2|
+delete from t1|
+drop procedure modes|
+
+
+# Check that dropping a database without routines works.
+# (Dropping with routines is tested in sp-security.test)
+# First an empty db.
+create database sp_db1|
+drop database sp_db1|
+
+# Again, with a table.
+create database sp_db2|
+use sp_db2|
+# Just put something in here...
+create table t3 ( s char(4), t int )|
+insert into t3 values ("abcd", 42), ("dcba", 666)|
+use test|
+drop database sp_db2|
+
+# And yet again, with just a procedure.
+create database sp_db3|
+use sp_db3|
+--disable_warnings
+drop procedure if exists dummy|
+--enable_warnings
+create procedure dummy(out x int)
+  set x = 42|
+use test|
+drop database sp_db3|
+# Check that it's gone
+select type,db,name from mysql.proc where db = 'sp_db3'|
+
+
+# ROW_COUNT() function after a CALL
+# We test the other cases here too, although it's not strictly SP specific
+--disable_warnings
+drop procedure if exists rc|
+--enable_warnings
+create procedure rc()
+begin
+  delete from t1;
+  insert into t1 values ("a", 1), ("b", 2), ("c", 3);
+end|
+
+call rc()|
+select row_count()|
+--disable_ps_protocol
+update t1 set data=42 where id = "b";
+select row_count()|
+--enable_ps_protocol
+delete from t1|
+select row_count()|
+delete from t1|
+select row_count()|
+select * from t1|
+select row_count()|
+drop procedure rc|
+
+
+#
+# Let us test how well new locking scheme works.
+#
+
+# Let us prepare playground
+--disable_warnings
+drop function if exists f0|
+drop function if exists f1|
+drop function if exists f2|
+drop function if exists f3|
+drop function if exists f4|
+drop function if exists f5|
+drop function if exists f6|
+drop function if exists f7|
+drop function if exists f8|
+drop function if exists f9|
+drop function if exists f10|
+drop function if exists f11|
+drop function if exists f12_1|
+drop function if exists f12_2|
+drop view if exists v0|
+drop view if exists v1|
+drop view if exists v2|
+--enable_warnings
+delete from t1|
+delete from t2|
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+
+# Test the simplest function using tables
+create function f1() returns int
+  return (select sum(data) from t1)|
+select f1()|
+# This should work too (and give 2 rows as result)
+select id, f1() from t1|
+
+# Function which uses two instances of table simultaneously
+create function f2() returns int
+  return (select data from t1 where data <= (select sum(data) from t1) limit 1)|
+select f2()|
+select id, f2() from t1|
+
+# Function which uses the same table twice in different queries
+create function f3() returns int
+begin
+  declare n int;
+  declare m int;
+  set n:= (select min(data) from t1);
+  set m:= (select max(data) from t1);
+  return n < m;
+end|
+select f3()|
+select id, f3() from t1|
+
+# Calling two functions using same table
+select f1(), f3()|
+select id, f1(), f3() from t1|
+
+# Function which uses two different tables
+create function f4() returns double 
+  return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
+select f4()|
+select s, f4() from t2|
+
+# Recursive functions which due to this recursion require simultaneous
+# access to several instance of the same table won't work
+create function f5(i int) returns int
+begin
+  if i <= 0 then
+    return 0;
+  elseif i = 1  then
+    return (select count(*) from t1 where data = i);
+  else
+    return (select count(*) + f5( i - 1) from t1 where data = i);
+  end if;
+end|
+select f5(1)|
+# Since currently recursive functions are disallowed ER_SP_NO_RECURSION
+# error will be returned, once we will allow them error about
+# insufficient number of locked tables will be returned instead.
+--error ER_SP_NO_RECURSION
+select f5(2)|
+--error ER_SP_NO_RECURSION
+select f5(3)|
+
+# OTOH this should work 
+create function f6() returns int
+begin
+  declare n int;
+  set n:= f1();
+  return (select count(*) from t1 where data <= f7() and data <= n);
+end|
+create function f7() returns int
+  return (select sum(data) from t1 where data <= f1())|
+select f6()|
+select id, f6() from t1|
+
+#
+# Let us test how new locking work with views
+#
+# The most trivial view
+create view v1 (a) as select f1()|
+select * from v1|
+select id, a from t1, v1|
+select * from v1, v1 as v|
+# A bit more complex construction
+create view v2 (a) as select a*10 from v1|
+select * from v2|
+select id, a from t1, v2|
+select * from v1, v2|
+
+# Nice example where the same view is used on
+# on different expression levels
+create function f8 () returns int
+  return (select count(*) from v2)|
+
+select *, f8() from v1|
+
+# Let us test what will happen if function is missing
+drop function f1|
+--error 1356
+select * from v1|
+
+# And what will happen if we have recursion which involves
+# views and functions ?
+create function f1() returns int
+  return (select sum(data) from t1) + (select sum(data) from v1)|
+--error ER_SP_NO_RECURSION
+select f1()|
+--error ER_SP_NO_RECURSION
+select * from v1|
+--error ER_SP_NO_RECURSION
+select * from v2|
+# Back to the normal cases
+drop function f1|
+create function f1() returns int
+  return (select sum(data) from t1)|
+
+# Let us also test some weird cases where no real tables is used
+create function f0() returns int
+  return (select * from (select 100) as r)|
+select f0()|
+select *, f0() from (select 1) as t|
+create view v0 as select f0()|
+select * from v0|
+select *, f0() from v0|
+
+#
+# Let us test how well prelocking works with explicit LOCK TABLES.
+#
+lock tables t1 read, t1 as t11 read|
+# These should work well
+select f3()|
+select id, f3() from t1 as t11|
+# Degenerate cases work too :)
+select f0()|
+select * from v0|
+select *, f0() from v0, (select 123) as d1|
+# But these should not !
+--error 1100
+select id, f3() from t1|
+--error 1100
+select f4()|
+unlock tables|
+
+# Let us test how LOCK TABLES which implicitly depends on functions
+# works
+lock tables v2 read, mysql.proc read|
+select * from v2|
+select * from v1|
+# These should not work as we have too little instances of tables locked
+--error 1100
+select * from v1, t1|
+--error 1100
+select f4()|
+unlock tables|
+
+# Tests for handling of temporary tables in functions.
+#
+# Unlike for permanent tables we should be able to create, use
+# and drop such tables in functions.
+# 
+# Simplest function using temporary table. It is also test case for bug 
+# #12198 "Temporary table aliasing does not work inside stored functions"
+create function f9() returns int
+begin
+  declare a, b int;
+  drop temporary table if exists t3;
+  create temporary table t3 (id int);
+  insert into t3 values (1), (2), (3);
+  set a:= (select count(*) from t3);
+  set b:= (select count(*) from t3 t3_alias);
+  return a + b;
+end|
+# This will emit warning as t3 was not existing before.
+select f9()|
+select f9() from t1 limit 1|
+
+# Function which uses both temporary and permanent tables.
+create function f10() returns int
+begin
+  drop temporary table if exists t3;
+  create temporary table t3 (id int);
+  insert into t3 select id from t4;
+  return (select count(*) from t3);
+end|
+# Check that we don't ignore completely tables used in function
+--error ER_NO_SUCH_TABLE
+select f10()|
+create table t4 as select 1 as id|
+select f10()|
+
+# Practical cases which we don't handle well (yet)
+#
+# Function which does not work because of well-known and documented
+# limitation of MySQL. We can't use the several instances of the
+# same temporary table in statement.
+create function f11() returns int
+begin
+  drop temporary table if exists t3;
+  create temporary table t3 (id int);
+  insert into t3 values (1), (2), (3);
+  return (select count(*) from t3 as a, t3 as b);
+end|
+--error ER_CANT_REOPEN_TABLE
+select f11()|
+--error ER_CANT_REOPEN_TABLE
+select f11() from t1|
+# We don't handle temporary tables used by nested functions well
+create function f12_1() returns int
+begin
+  drop temporary table if exists t3;
+  create temporary table t3 (id int);
+  insert into t3 values (1), (2), (3);
+  return f12_2();
+end|
+create function f12_2() returns int
+  return (select count(*) from t3)|
+# We need clean start to get error
+drop temporary table t3|
+--error ER_NO_SUCH_TABLE
+select f12_1()|
+--error ER_NO_SUCH_TABLE
+select f12_1() from t1 limit 1|
+
+# Cleanup
+drop function f0|
+drop function f1|
+drop function f2|
+drop function f3|
+drop function f4|
+drop function f5|
+drop function f6|
+drop function f7|
+drop function f8|
+drop function f9|
+drop function f10|
+drop function f11|
+drop function f12_1|
+drop function f12_2|
+drop view v0|
+drop view v1|
+drop view v2|
+delete from t1 |
+delete from t2 |
+drop table t4|
+
+# End of non-bug tests
+
+
+#
+# Some "real" examples
+#
+
+# fac
+
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+create table t3 (n int unsigned not null primary key, f bigint unsigned)|
+
+--disable_warnings
+drop procedure if exists ifac|
+--enable_warnings
+create procedure ifac(n int unsigned)
+begin
+  declare i int unsigned default 1;
+
+  if n > 20 then
+    set n = 20;		# bigint overflow otherwise
+  end if;
+  while i <= n do
+    begin
+      insert into test.t3 values (i, fac(i));
+      set i = i + 1;
+    end;
+  end while;
+end|
+
+call ifac(20)|
+select * from t3|
+drop table t3|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show function status like '%f%'|
+drop procedure ifac|
+drop function fac|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show function status like '%f%'|
+
+
+# primes
+
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+
+create table t3 (
+  i int unsigned not null primary key,
+  p bigint unsigned not null
+)|
+
+insert into t3 values
+ ( 0,   3), ( 1,   5), ( 2,   7), ( 3,  11), ( 4,  13),
+ ( 5,  17), ( 6,  19), ( 7,  23), ( 8,  29), ( 9,  31),
+ (10,  37), (11,  41), (12,  43), (13,  47), (14,  53),
+ (15,  59), (16,  61), (17,  67), (18,  71), (19,  73),
+ (20,  79), (21,  83), (22,  89), (23,  97), (24, 101),
+ (25, 103), (26, 107), (27, 109), (28, 113), (29, 127),
+ (30, 131), (31, 137), (32, 139), (33, 149), (34, 151),
+ (35, 157), (36, 163), (37, 167), (38, 173), (39, 179),
+ (40, 181), (41, 191), (42, 193), (43, 197), (44, 199)|
+
+--disable_warnings
+drop procedure if exists opp|
+--enable_warnings
+create procedure opp(n bigint unsigned, out pp bool)
+begin
+  declare r double;
+  declare b, s bigint unsigned default 0;
+
+  set r = sqrt(n);
+
+ again:
+  loop
+    if s = 45 then
+      set b = b+200, s = 0;
+    else
+      begin
+        declare p bigint unsigned;
+
+        select t.p into p from test.t3 t where t.i = s;
+        if b+p > r then
+          set pp = 1;
+          leave again;
+        end if;
+        if mod(n, b+p) = 0 then
+          set pp = 0;
+          leave again;
+        end if;
+        set s = s+1;
+      end;
+    end if;
+  end loop;
+end|
+
+--disable_warnings
+drop procedure if exists ip|
+--enable_warnings
+create procedure ip(m int unsigned)
+begin
+  declare p bigint unsigned;
+  declare i int unsigned;
+
+  set i=45, p=201;
+
+  while i < m do
+    begin
+      declare pp bool default 0;
+
+      call opp(p, pp);
+      if pp then
+        insert into test.t3 values (i, p);
+        set i = i+1;
+      end if;
+      set p = p+2;
+    end;
+  end while;
+end|
+show create procedure opp|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like '%p%'|
+
+# This isn't the fastest way in the world to compute prime numbers, so
+# don't be too ambitious. ;-)
+call ip(200)|
+# We don't want to select the entire table here, just pick a few
+# examples.
+# The expected result is:
+#    i      p
+#   ---   ----
+#    45    211
+#   100    557
+#   199   1229
+select * from t3 where i=45 or i=100 or i=199|
+drop table t3|
+drop procedure opp|
+drop procedure ip|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like '%p%'|
+
+
+# Fibonacci, for recursion test. (Yet Another Numerical series :)
+#
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+create table t3 ( f bigint unsigned not null )|
+
+# We deliberately do it the awkward way, fetching the last two
+# values from the table, in order to exercise various statements
+# and table accesses at each turn.
+--disable_warnings
+drop procedure if exists fib|
+--enable_warnings
+create procedure fib(n int unsigned)
+begin
+  if n > 1 then
+    begin
+      declare x, y bigint unsigned;
+      declare c cursor for select f from t3 order by f desc limit 2;
+
+      open c;
+      fetch c into y;
+      fetch c into x;
+      close c;
+      insert into t3 values (x+y);
+      call fib(n-1);
+    end;
+  end if;
+end|
+
+# Enable recursion
+set @@max_sp_recursion_depth= 20|
+
+# Minimum test: recursion of 3 levels
+
+insert into t3 values (0), (1)|
+
+call fib(3)|
+
+select * from t3 order by f asc|
+
+delete from t3|
+
+# The original test, 20 levels, ran into memory limits on some machines
+# and builds. Try 10 instead...
+
+insert into t3 values (0), (1)|
+
+call fib(10)|
+
+select * from t3 order by f asc|
+drop table t3|
+drop procedure fib|
+set @@max_sp_recursion_depth= 0|
+
+#
+# Comment & suid
+#
+
+--disable_warnings
+drop procedure if exists bar|
+--enable_warnings
+create procedure bar(x char(16), y int)
+ comment "111111111111" sql security invoker
+ insert into test.t1 values (x, y)|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like 'bar'|
+alter procedure bar comment "2222222222" sql security definer|
+alter procedure bar comment "3333333333"|
+alter procedure bar|
+show create procedure bar|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like 'bar'|
+drop procedure bar|
+
+#
+# rexecution
+#
+--disable_warnings
+drop procedure if exists p1|
+--enable_warnings
+create procedure p1 ()
+  select (select s1 from t3) from t3|
+
+create table t3 (s1 int)|
+
+call p1()|
+insert into t3 values (1)|
+call p1()|
+drop procedure p1|
+drop table t3|
+
+#
+# backticks
+#
+--disable_warnings
+drop function if exists foo|
+--enable_warnings
+create function `foo` () returns int
+  return 5|
+select `foo` ()|
+drop function `foo`|
+
+#
+# Implicit LOCK/UNLOCK TABLES for table access in functions
+#
+
+--disable_warnings
+drop function if exists t1max|
+--enable_warnings
+create function t1max() returns int
+begin
+  declare x int;
+  select max(data) into x from t1;
+  return x;
+end|
+
+insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)|
+select t1max()|
+drop function t1max|
+
+create table t3 (
+  v char(16) not null primary key,
+  c int unsigned not null
+)|
+
+create function getcount(s char(16)) returns int
+begin
+  declare x int;
+
+  select count(*) into x from t3 where v = s;
+  if x = 0 then
+    insert into t3 values (s, 1);
+  else
+    update t3 set c = c+1 where v = s;
+  end if;
+  return x;
+end|
+
+select * from t1 where data = getcount("bar")|
+select * from t3|
+select getcount("zip")|
+select getcount("zip")|
+select * from t3|
+select getcount(id) from t1 where data = 3|
+select getcount(id) from t1 where data = 5|
+select * from t3|
+drop table t3|
+drop function getcount|
+
+
+# Test cases for different combinations of condition handlers in nested
+# begin-end blocks in stored procedures.
+#
+# Note that the standard specifies that the most specific handler should
+# be triggered even if it's an outer handler masked by a less specific
+# handler in an inner block.
+# Note also that '02000' is more specific than NOT FOUND; there might be
+# other '02xxx' states, even if we currently do not issue them in any
+# situation (e.g. '02001').
+#
+# The combinations we test are these:
+#
+#                                         Inner
+#              errcode      sqlstate     not found    sqlwarning   sqlexception
+#  Outer      +------------+------------+------------+------------+------------+
+#errcode      | h_ee (i)   | h_es (o)   | h_en (o)   | h_ew (o)   | h_ex (o)   |
+#sqlstate     | h_se (i)   | h_ss (i)   | h_sn (o)   | h_sw (o)   | h_sx (o)   |
+#not found    | h_ne (i)   | h_ns (i)   | h_nn (i)   |            |            |
+#sqlwarning   | h_we (i)   | h_ws (i)   |            | h_ww (i)   |            |
+#sqlexception | h_xe (i)   | h_xs (i)   |            |            | h_xx (i)   |
+#             +------------+---------------------------------------------------+
+#
+# (i) means that the inner handler is the one that should be invoked,
+# (o) means that the outer handler should be invoked.
+#
+# ('not found', 'sqlwarning' and 'sqlexception' are mutually exclusive, hence
+#  no tests for those combinations.)
+#
+
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists h_ee|
+drop procedure if exists h_es|
+drop procedure if exists h_en|
+drop procedure if exists h_ew|
+drop procedure if exists h_ex|
+drop procedure if exists h_se|
+drop procedure if exists h_ss|
+drop procedure if exists h_sn|
+drop procedure if exists h_sw|
+drop procedure if exists h_sx|
+drop procedure if exists h_ne|
+drop procedure if exists h_ns|
+drop procedure if exists h_nn|
+drop procedure if exists h_we|
+drop procedure if exists h_ws|
+drop procedure if exists h_ww|
+drop procedure if exists h_xe|
+drop procedure if exists h_xs|
+drop procedure if exists h_xx|
+--enable_warnings
+
+# smallint    - to get out of range warnings
+# primary key - to get constraint errors
+create table t3 (a smallint primary key)|
+
+insert into t3 (a) values (1)|
+
+create procedure h_ee()
+    deterministic
+begin
+  declare continue handler for 1062 -- ER_DUP_ENTRY
+    select 'Outer (bad)' as 'h_ee';
+
+  begin
+    declare continue handler for 1062 -- ER_DUP_ENTRY
+        select 'Inner (good)' as 'h_ee';
+
+    insert into t3 values (1);
+  end;
+end|
+
+create procedure h_es()
+    deterministic
+begin
+  declare continue handler for 1062 -- ER_DUP_ENTRY
+    select 'Outer (good)' as 'h_es';
+
+  begin
+    -- integrity constraint violation
+    declare continue handler for sqlstate '23000'
+      select 'Inner (bad)' as 'h_es';
+
+    insert into t3 values (1);
+  end;
+end|
+
+create procedure h_en()
+    deterministic
+begin
+  declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
+    select 'Outer (good)' as 'h_en';
+
+  begin
+    declare x int;
+    declare continue handler for sqlstate '02000' -- no data
+      select 'Inner (bad)' as 'h_en';
+
+    select a into x from t3 where a = 42;
+  end;
+end|
+
+create procedure h_ew()
+    deterministic
+begin
+  declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
+    select 'Outer (good)' as 'h_ew';
+
+  begin
+    declare continue handler for sqlwarning
+      select 'Inner (bad)' as 'h_ew';
+
+    insert into t3 values (123456789012);
+  end;
+  delete from t3;
+  insert into t3 values (1);
+end|
+
+create procedure h_ex()
+    deterministic
+begin
+  declare continue handler for 1062 -- ER_DUP_ENTRY
+    select 'Outer (good)' as 'h_ex';
+
+  begin
+    declare continue handler for sqlexception
+      select 'Inner (bad)' as 'h_ex';
+
+    insert into t3 values (1);
+  end;
+end|
+
+create procedure h_se()
+    deterministic
+begin
+  -- integrity constraint violation
+  declare continue handler for sqlstate '23000' 
+    select 'Outer (bad)' as 'h_se';
+
+  begin
+    declare continue handler for 1062 -- ER_DUP_ENTRY
+      select 'Inner (good)' as 'h_se';
+
+    insert into t3 values (1);
+  end;
+end|
+
+create procedure h_ss()
+    deterministic
+begin
+  -- integrity constraint violation
+  declare continue handler for sqlstate '23000' 
+    select 'Outer (bad)' as 'h_ss';
+
+  begin
+    -- integrity constraint violation
+    declare continue handler for sqlstate '23000' 
+      select 'Inner (good)' as 'h_ss';
+
+    insert into t3 values (1);
+  end;
+end|
+
+create procedure h_sn()
+    deterministic
+begin
+  -- Note: '02000' is more specific than NOT FOUND ;
+  --       there might be other not found states 
+  declare continue handler for sqlstate '02000' -- no data
+    select 'Outer (good)' as 'h_sn';
+
+  begin
+    declare x int;
+    declare continue handler for not found
+      select 'Inner (bad)' as 'h_sn';
+
+    select a into x from t3 where a = 42;
+  end;
+end|
+
+create procedure h_sw()
+    deterministic
+begin
+  -- data exception - numeric value out of range
+  declare continue handler for sqlstate '22003'
+    select 'Outer (good)' as 'h_sw';
+
+  begin
+    declare continue handler for sqlwarning
+      select 'Inner (bad)' as 'h_sw';
+
+    insert into t3 values (123456789012);
+  end;
+  delete from t3;
+  insert into t3 values (1);
+end|
+
+create procedure h_sx()
+    deterministic
+begin
+  -- integrity constraint violation
+  declare continue handler for sqlstate '23000' 
+    select 'Outer (good)' as 'h_sx';
+
+  begin
+    declare continue handler for sqlexception
+      select 'Inner (bad)' as 'h_sx';
+
+    insert into t3 values (1);
+  end;
+end|
+
+create procedure h_ne()
+    deterministic
+begin
+  declare continue handler for not found
+    select 'Outer (bad)' as 'h_ne';
+
+  begin
+    declare x int;
+    declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
+      select 'Inner (good)' as 'h_ne';
+
+    select a into x from t3 where a = 42;
+  end;
+end|
+
+create procedure h_ns()
+    deterministic
+begin
+  declare continue handler for not found
+    select 'Outer (bad)' as 'h_ns';
+
+  begin
+    declare x int;
+    declare continue handler for sqlstate '02000' -- no data
+      select 'Inner (good)' as 'h_ns';
+
+    select a into x from t3 where a = 42;
+  end;
+end|
+
+create procedure h_nn()
+    deterministic
+begin
+  declare continue handler for not found
+    select 'Outer (bad)' as 'h_nn';
+
+  begin
+    declare x int;
+    declare continue handler for not found
+      select 'Inner (good)' as 'h_nn';
+
+    select a into x from t3 where a = 42;
+  end;
+end|
+
+create procedure h_we()
+    deterministic
+begin
+  declare continue handler for sqlwarning
+    select 'Outer (bad)' as 'h_we';
+
+  begin
+    declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
+      select 'Inner (good)' as 'h_we';
+
+    insert into t3 values (123456789012);
+  end;
+  delete from t3;
+  insert into t3 values (1);
+end|
+
+create procedure h_ws()
+    deterministic
+begin
+  declare continue handler for sqlwarning
+    select 'Outer (bad)' as 'h_ws';
+
+  begin
+    -- data exception - numeric value out of range
+    declare continue handler for sqlstate '22003'
+      select 'Inner (good)' as 'h_ws';
+
+    insert into t3 values (123456789012);
+  end;
+  delete from t3;
+  insert into t3 values (1);
+end|
+
+create procedure h_ww()
+    deterministic
+begin
+  declare continue handler for sqlwarning
+    select 'Outer (bad)' as 'h_ww';
+
+  begin
+    declare continue handler for sqlwarning
+      select 'Inner (good)' as 'h_ww';
+
+    insert into t3 values (123456789012);
+  end;
+  delete from t3;
+  insert into t3 values (1);
+end|
+
+create procedure h_xe()
+    deterministic
+begin
+  declare continue handler for sqlexception
+    select 'Outer (bad)' as 'h_xe';
+
+  begin
+    declare continue handler for 1062 -- ER_DUP_ENTRY
+      select 'Inner (good)' as 'h_xe';
+
+    insert into t3 values (1);
+  end;
+end|
+
+create procedure h_xs()
+    deterministic
+begin
+  declare continue handler for sqlexception
+    select 'Outer (bad)' as 'h_xs';
+
+  begin
+    -- integrity constraint violation
+    declare continue handler for sqlstate '23000'
+      select 'Inner (good)' as 'h_xs';
+
+    insert into t3 values (1);
+  end;
+end|
+
+create procedure h_xx()
+    deterministic
+begin
+  declare continue handler for sqlexception
+    select 'Outer (bad)' as 'h_xx';
+
+  begin
+    declare continue handler for sqlexception
+      select 'Inner (good)' as 'h_xx';
+
+    insert into t3 values (1);
+  end;
+end|
+
+call h_ee()|
+call h_es()|
+call h_en()|
+call h_ew()|
+call h_ex()|
+call h_se()|
+call h_ss()|
+call h_sn()|
+call h_sw()|
+call h_sx()|
+call h_ne()|
+call h_ns()|
+call h_nn()|
+call h_we()|
+call h_ws()|
+call h_ww()|
+call h_xe()|
+call h_xs()|
+call h_xx()|
+
+drop table t3|
+drop procedure h_ee|
+drop procedure h_es|
+drop procedure h_en|
+drop procedure h_ew|
+drop procedure h_ex|
+drop procedure h_se|
+drop procedure h_ss|
+drop procedure h_sn|
+drop procedure h_sw|
+drop procedure h_sx|
+drop procedure h_ne|
+drop procedure h_ns|
+drop procedure h_nn|
+drop procedure h_we|
+drop procedure h_ws|
+drop procedure h_ww|
+drop procedure h_xe|
+drop procedure h_xs|
+drop procedure h_xx|
+
+
+#
+# Test cases for old bugs
+#
+
+#
+# BUG#822
+#
+--disable_warnings
+drop procedure if exists bug822|
+--enable_warnings
+create procedure bug822(a_id char(16), a_data int)
+begin
+  declare n int;
+  select count(*) into n from t1 where id = a_id and data = a_data;
+  if n = 0 then
+    insert into t1 (id, data) values (a_id, a_data);
+  end if;
+end|
+
+delete from t1|
+call bug822('foo', 42)|
+call bug822('foo', 42)|
+call bug822('bar', 666)|
+select * from t1|
+delete from t1|
+drop procedure bug822|
+
+#
+# BUG#1495
+#
+--disable_warnings
+drop procedure if exists bug1495|
+--enable_warnings
+create procedure bug1495()
+begin
+  declare x int;
+
+  select data into x from t1 order by id limit 1;
+  if x > 10 then
+    insert into t1 values ("less", x-10);
+  else
+    insert into t1 values ("more", x+10);
+  end if;
+end|
+
+insert into t1 values ('foo', 12)|
+call bug1495()|
+delete from t1 where id='foo'|
+insert into t1 values ('bar', 7)|
+call bug1495()|
+delete from t1 where id='bar'|
+select * from t1|
+delete from t1|
+drop procedure bug1495|
+
+#
+# BUG#1547
+#
+--disable_warnings
+drop procedure if exists bug1547|
+--enable_warnings
+create procedure bug1547(s char(16))
+begin
+  declare x int;
+
+  select data into x from t1 where s = id limit 1;
+  if x > 10 then
+    insert into t1 values ("less", x-10);
+  else
+    insert into t1 values ("more", x+10);
+  end if;
+end|
+
+insert into t1 values ("foo", 12), ("bar", 7)|
+call bug1547("foo")|
+call bug1547("bar")|
+select * from t1|
+delete from t1|
+drop procedure bug1547|
+
+#
+# BUG#1656
+#
+--disable_warnings
+drop table if exists t70|
+--enable_warnings
+create table t70 (s1 int,s2 int)|
+insert into t70 values (1,2)|
+
+--disable_warnings
+drop procedure if exists bug1656|
+--enable_warnings
+create procedure bug1656(out p1 int, out p2 int)
+  select * into p1, p1 from t70|
+
+call bug1656(@1, @2)|
+select @1, @2|
+drop table t70|
+drop procedure bug1656|
+
+#
+# BUG#1862
+#
+create table t3(a int)|
+
+--disable_warnings
+drop procedure if exists bug1862|
+--enable_warnings
+create procedure bug1862()
+begin
+  insert into t3 values(2);    
+  flush tables;
+end|
+
+call bug1862()|
+# the second call caused a segmentation
+call bug1862()|
+select * from t3|
+drop table t3|
+drop procedure bug1862|
+
+#
+# BUG#1874
+#
+--disable_warnings
+drop procedure if exists bug1874|
+--enable_warnings
+create procedure bug1874()
+begin
+  declare x int;
+  declare y double;
+  select max(data) into x from t1;
+  insert into t2 values ("max", x, 0);
+  select min(data) into x from t1;
+  insert into t2 values ("min", x, 0);
+  select sum(data) into x from t1;
+  insert into t2 values ("sum", x, 0);
+  select avg(data) into y from t1;
+  insert into t2 values ("avg", 0, y);
+end|
+
+insert into t1 (data) values (3), (1), (5), (9), (4)|
+call bug1874()|
+select * from t2|
+delete from t1|
+delete from t2|
+drop procedure bug1874|
+
+#
+# BUG#2260
+#
+--disable_warnings
+drop procedure if exists bug2260|
+--enable_warnings
+create procedure bug2260()
+begin
+  declare v1 int;
+  declare c1 cursor for select data from t1;
+  declare continue handler for not found set @x2 = 1;
+
+  open c1;
+  fetch c1 into v1;
+  set @x2 = 2;
+  close c1;
+end|
+
+call bug2260()|
+select @x2|
+drop procedure bug2260|
+
+#
+# BUG#2267 "Lost connect if stored procedure has SHOW FUNCTION STATUS"
+#
+--disable_warnings
+drop procedure if exists bug2267_1|
+--enable_warnings
+create procedure bug2267_1()
+begin
+  show procedure status;
+end|
+
+--disable_warnings
+drop procedure if exists bug2267_2|
+--enable_warnings
+create procedure bug2267_2()
+begin
+  show function status;
+end|
+
+--disable_warnings
+drop procedure if exists bug2267_3|
+--enable_warnings
+create procedure bug2267_3()
+begin
+  show create procedure bug2267_1;
+end|
+
+--disable_warnings
+drop procedure if exists bug2267_4|
+drop function if exists bug2267_4|
+--enable_warnings
+create procedure bug2267_4()
+begin
+  show create function bug2267_4;
+end|
+create function bug2267_4() returns int return 100|
+
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+call bug2267_1()|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+call bug2267_2()|
+call bug2267_3()|
+call bug2267_4()|
+
+drop procedure bug2267_1|
+drop procedure bug2267_2|
+drop procedure bug2267_3|
+drop procedure bug2267_4|
+drop function bug2267_4|
+
+#
+# BUG#2227
+#
+--disable_warnings
+drop procedure if exists bug2227|
+--enable_warnings
+create procedure bug2227(x int)
+begin
+  declare y float default 2.6;
+  declare z char(16) default "zzz";
+
+  select 1.3, x, y, 42, z;
+end|
+
+call bug2227(9)|
+drop procedure bug2227|
+
+#
+# BUG#2614 "Stored procedure with INSERT ... SELECT that does not
+#           contain any tables crashes server"
+#
+--disable_warnings
+drop procedure if exists bug2614|
+--enable_warnings
+create procedure bug2614()
+begin
+  drop table if exists t3;
+  create table t3 (id int default '0' not null);
+  insert into t3 select 12;
+  insert into t3 select * from t3;
+end|
+
+--disable_warnings
+call bug2614()|
+--enable_warnings
+call bug2614()|
+drop table t3|
+drop procedure bug2614|
+
+#
+# BUG#2674
+#
+--disable_warnings
+drop function if exists bug2674|
+--enable_warnings
+create function bug2674() returns int
+  return @@sort_buffer_size|
+
+set @osbs = @@sort_buffer_size|
+set @@sort_buffer_size = 262000|
+select bug2674()|
+drop function bug2674|
+set @@sort_buffer_size = @osbs|
+
+#
+# BUG#3259
+#
+--disable_warnings
+drop procedure if exists bug3259_1 |
+--enable_warnings
+create procedure bug3259_1 () begin end|
+--disable_warnings
+drop procedure if exists BUG3259_2 |
+--enable_warnings
+create procedure BUG3259_2 () begin end|
+--disable_warnings
+drop procedure if exists Bug3259_3 |
+--enable_warnings
+create procedure Bug3259_3 () begin end|
+
+call BUG3259_1()|
+call BUG3259_1()|
+call bug3259_2()|
+call Bug3259_2()|
+call bug3259_3()|
+call bUG3259_3()|
+
+drop procedure bUg3259_1|
+drop procedure BuG3259_2|
+drop procedure BUG3259_3|
+
+#
+# BUG#2772
+#
+--disable_warnings
+drop function if exists bug2772|
+--enable_warnings
+create function bug2772() returns char(10) character set latin2
+  return 'a'|
+
+select bug2772()|
+drop function bug2772|
+
+#
+# BUG#2776
+#
+--disable_warnings
+drop procedure if exists bug2776_1|
+--enable_warnings
+create procedure bug2776_1(out x int)
+begin
+  declare v int;
+
+  set v = default;
+  set x = v;
+end|
+
+--disable_warnings
+drop procedure if exists bug2776_2|
+--enable_warnings
+create procedure bug2776_2(out x int)
+begin
+  declare v int default 42;
+
+  set v = default;
+  set x = v;
+end|
+
+set @x = 1|
+call bug2776_1(@x)|
+select @x|
+call bug2776_2(@x)|
+select @x|
+drop procedure bug2776_1|
+drop procedure bug2776_2|
+
+#
+# BUG#2780
+#
+create table t3 (s1 smallint)|
+
+insert into t3 values (123456789012)|
+
+--disable_warnings
+drop procedure if exists bug2780|
+--enable_warnings
+create procedure bug2780()
+begin
+  declare exit handler for sqlwarning set @x = 1; 
+
+  set @x = 0;
+  insert into t3 values (123456789012);
+  insert into t3 values (0);
+end|
+
+call bug2780()|
+select @x|
+select * from t3|
+
+drop procedure bug2780|
+drop table t3|
+
+#
+# BUG#1863
+#
+create table t3 (content varchar(10) )|
+insert into t3 values ("test1")|
+insert into t3 values ("test2")|
+create table t4 (f1 int, rc int, t3 int)|
+
+--disable_warnings
+drop procedure if exists bug1863|
+--enable_warnings
+create procedure bug1863(in1 int)
+begin 
+
+  declare ind int default 0;
+  declare t1 int;
+  declare t2 int;
+  declare t3 int;
+
+  declare rc int default 0;
+  declare continue handler for 1065 set rc = 1;
+
+  drop temporary table if exists temp_t1;
+  create temporary table temp_t1 (
+    f1 int auto_increment, f2 varchar(20), primary key (f1)
+  );
+
+  insert into temp_t1 (f2) select content from t3;
+
+  select f2 into t3 from temp_t1 where f1 = 10;
+
+  if (rc) then
+       insert into t4 values (1, rc, t3);
+  end if;
+
+  insert into t4 values (2, rc, t3);
+
+end|
+
+call bug1863(10)|
+call bug1863(10)|
+select * from t4|
+
+drop procedure bug1863|
+drop temporary table temp_t1;
+drop table t3, t4|
+
+#
+# BUG#2656
+#
+
+create table t3 ( 
+  OrderID  int not null,
+  MarketID int,
+  primary key (OrderID)
+)|
+
+create table t4 ( 
+  MarketID int not null,
+  Market varchar(60),
+  Status char(1),
+  primary key (MarketID)
+)|
+
+insert t3 (OrderID,MarketID) values (1,1)|
+insert t3 (OrderID,MarketID) values (2,2)|
+insert t4 (MarketID,Market,Status) values (1,"MarketID One","A")|
+insert t4 (MarketID,Market,Status) values (2,"MarketID Two","A")|
+
+--disable_warnings
+drop procedure if exists bug2656_1|
+--enable_warnings
+create procedure bug2656_1()
+begin 
+  select
+    m.Market
+  from  t4 m JOIN t3 o 
+        ON o.MarketID != 1 and o.MarketID = m.MarketID;
+end |
+
+--disable_warnings
+drop procedure if exists bug2656_2|
+--enable_warnings
+create procedure bug2656_2()
+begin 
+  select
+    m.Market
+  from  
+    t4 m, t3 o
+  where       
+    m.MarketID != 1 and m.MarketID = o.MarketID;
+        
+end |
+
+call bug2656_1()|
+call bug2656_1()|
+call bug2656_2()|
+call bug2656_2()|
+drop procedure bug2656_1|
+drop procedure bug2656_2|
+drop table t3, t4|
+
+
+#
+# BUG#3426
+#
+--disable_warnings
+drop procedure if exists bug3426|
+--enable_warnings
+create procedure bug3426(in_time int unsigned, out x int)
+begin
+  if in_time is null then
+    set @stamped_time=10;
+    set x=1;
+  else
+    set @stamped_time=in_time;
+    set x=2;
+  end if;
+end|
+
+call bug3426(1000, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+call bug3426(NULL, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+# Clear SP cache
+alter procedure bug3426 sql security invoker|
+call bug3426(NULL, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+call bug3426(1000, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+
+drop procedure bug3426|
+
+#
+# BUG#3448
+#
+--disable_warnings
+create table t3 (
+  a int primary key, 
+  ach char(1)
+) engine = innodb|
+
+create table t4 (
+  b int  primary key , 
+  bch char(1)
+) engine = innodb|
+--enable_warnings
+
+insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')|
+insert into t4 values (1 , 'bCh1' )|
+
+--disable_warnings
+drop procedure if exists bug3448|
+--enable_warnings
+create procedure bug3448()
+  select * from t3 inner join t4 on t3.a = t4.b|
+
+select * from t3 inner join t4 on t3.a = t4.b|
+call bug3448()|
+call bug3448()|
+
+drop procedure bug3448|
+drop table t3, t4|
+
+
+#
+# BUG#3734
+#
+create table t3 (
+  id int unsigned auto_increment not null primary key,
+  title VARCHAR(200),
+  body text,
+  fulltext (title,body)
+)|
+
+insert into t3 (title,body) values
+  ('MySQL Tutorial','DBMS stands for DataBase ...'),
+  ('How To Use MySQL Well','After you went through a ...'),
+  ('Optimizing MySQL','In this tutorial we will show ...'),
+  ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
+  ('MySQL vs. YourSQL','In the following database comparison ...'),
+  ('MySQL Security','When configured properly, MySQL ...')|
+
+--disable_warnings
+drop procedure if exists bug3734 |
+--enable_warnings
+create procedure bug3734 (param1 varchar(100))
+  select * from t3 where match (title,body) against (param1)|
+
+call bug3734('database')|
+call bug3734('Security')|
+
+drop procedure bug3734|
+drop table t3|
+
+#
+# BUG#3863
+#
+--disable_warnings
+drop procedure if exists bug3863|
+--enable_warnings
+create procedure bug3863()
+begin
+  set @a = 0;
+  while @a < 5 do
+    set @a = @a + 1;
+  end while;
+end|
+
+call bug3863()|
+select @a|
+call bug3863()|
+select @a|
+
+drop procedure bug3863|
+
+#
+# BUG#2460
+#
+
+create table t3 (
+  id int(10) unsigned not null default 0,
+  rid int(10) unsigned not null default 0,
+  msg text not null,
+  primary key (id),
+  unique key rid (rid, id)
+)|
+
+--disable_warnings
+drop procedure if exists bug2460_1|
+--enable_warnings
+create procedure bug2460_1(in v int)
+begin
+    ( select n0.id from t3 as n0 where n0.id = v )
+  union
+    ( select n0.id from t3 as n0, t3 as n1
+        where n0.id = n1.rid and n1.id = v )
+  union
+    ( select n0.id from t3 as n0, t3 as n1, t3 as n2
+        where n0.id = n1.rid and n1.id = n2.rid and n2.id = v );
+end|
+
+call bug2460_1(2)|
+call bug2460_1(2)|
+insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')|
+call bug2460_1(2)|
+call bug2460_1(2)|
+
+--disable_warnings
+drop procedure if exists bug2460_2|
+--enable_warnings
+create procedure bug2460_2()
+begin
+  drop table if exists t3;
+  create temporary table t3 (s1 int);
+  insert into t3 select 1 union select 1;
+end|
+
+call bug2460_2()|
+call bug2460_2()|
+select * from t3|
+
+drop procedure bug2460_1|
+drop procedure bug2460_2|
+drop table t3|
+
+
+#
+# BUG#2564
+#
+set @@sql_mode = ''|
+--disable_warnings
+drop procedure if exists bug2564_1|
+--enable_warnings
+create procedure bug2564_1()
+    comment 'Joe''s procedure'
+  insert into `t1` values ("foo", 1)|
+
+set @@sql_mode = 'ANSI_QUOTES'|
+--disable_warnings
+drop procedure if exists bug2564_2|
+--enable_warnings
+create procedure bug2564_2()
+  insert into "t1" values ('foo', 1)|
+
+delimiter $|
+set @@sql_mode = ''$
+--disable_warnings
+drop function if exists bug2564_3$
+--enable_warnings
+create function bug2564_3(x int, y int) returns int
+  return x || y$
+
+set @@sql_mode = 'ANSI'$
+--disable_warnings
+drop function if exists bug2564_4$
+--enable_warnings
+create function bug2564_4(x int, y int) returns int
+  return x || y$
+delimiter |$
+
+set @@sql_mode = ''|
+show create procedure bug2564_1|
+show create procedure bug2564_2|
+show create function bug2564_3|
+show create function bug2564_4|
+
+drop procedure bug2564_1|
+drop procedure bug2564_2|
+drop function bug2564_3|
+drop function bug2564_4|
+
+#
+# BUG#3132
+#
+--disable_warnings
+drop function if exists bug3132|
+--enable_warnings
+create function bug3132(s char(20)) returns char(50)
+  return concat('Hello, ', s, '!')|
+
+select bug3132('Bob') union all select bug3132('Judy')|
+drop function bug3132|
+
+#
+# BUG#3843
+#
+--disable_warnings
+drop procedure if exists bug3843|
+--enable_warnings
+create procedure bug3843()
+  analyze table t1|
+
+# Testing for packets out of order
+call bug3843()|
+call bug3843()|
+select 1+2|
+
+drop procedure bug3843|
+
+#
+# BUG#3368
+#
+create table t3 ( s1 char(10) )|
+insert into t3 values ('a'), ('b')|
+
+--disable_warnings
+drop procedure if exists bug3368|
+--enable_warnings
+create procedure bug3368(v char(10))
+begin
+  select group_concat(v) from t3;
+end|
+
+call bug3368('x')|
+call bug3368('yz')|
+drop procedure bug3368|
+drop table t3|
+
+#
+# BUG#4579
+#
+create table t3 (f1 int, f2 int)|
+insert into t3 values (1,1)|
+
+--disable_warnings
+drop procedure if exists bug4579_1|
+--enable_warnings
+create procedure bug4579_1 ()
+begin
+  declare sf1 int;
+
+  select f1 into sf1 from t3 where f1=1 and f2=1;
+  update t3 set f2 = f2 + 1 where f1=1 and f2=1;
+  call bug4579_2();
+end|
+
+--disable_warnings
+drop procedure if exists bug4579_2|
+--enable_warnings
+create procedure bug4579_2 ()
+begin
+end|
+
+call bug4579_1()|
+call bug4579_1()|
+call bug4579_1()|
+
+drop procedure bug4579_1|
+drop procedure bug4579_2|
+drop table t3|
+
+#
+# BUG#2773: Function's data type ignored in stored procedures
+#
+--disable_warnings
+drop procedure if exists bug2773|
+--enable_warnings
+
+create function bug2773() returns int return null|
+create table t3 as select bug2773()|
+show create table t3|
+drop table t3|
+drop function bug2773|
+
+#
+# BUG#3788: Stored procedure packet error
+#
+--disable_warnings
+drop procedure if exists bug3788|
+--enable_warnings
+
+create function bug3788() returns date return cast("2005-03-04" as date)|
+select bug3788()|
+drop function bug3788|
+
+create function bug3788() returns binary(1) return 5|
+select bug3788()|
+drop function bug3788|
+ 
+
+#
+# BUG#4726
+#
+create table t3 (f1 int, f2 int, f3 int)|
+insert into t3 values (1,1,1)|
+
+--disable_warnings
+drop procedure if exists bug4726|
+--enable_warnings
+create procedure bug4726()
+begin
+   declare tmp_o_id INT;
+   declare tmp_d_id INT default 1;
+
+   while tmp_d_id <= 2 do
+   begin
+     select f1 into tmp_o_id from t3 where f2=1 and f3=1;
+     set tmp_d_id = tmp_d_id + 1;
+   end;
+   end while;
+end|
+
+call bug4726()|
+call bug4726()|
+call bug4726()|
+
+drop procedure bug4726|
+drop table t3|
+
+#
+# BUG#4318
+#
+
+--disable_parsing # Don't know if HANDLER commands can work with SPs, or at all..
+create table t3 (s1 int)|
+insert into t3 values (3), (4)|
+
+--disable_warnings
+drop procedure if exists bug4318|
+--enable_warnings
+create procedure bug4318()
+  handler t3 read next|
+
+handler t3 open|
+# Expect no results, as tables are closed, but there shouldn't be any errors
+call bug4318()|
+call bug4318()|
+handler t3 close|
+
+drop procedure bug4318|
+drop table t3|
+--enable_parsing
+
+#
+# BUG#4902: Stored procedure with SHOW WARNINGS leads to packet error
+#
+# Added tests for most other show commands we could find too.
+# (Skipping those already tested, and the ones depending on optional handlers.)
+#
+# Note: This will return a large number of results of different formats,
+#       which makes it impossible to filter with --replace_column.
+#       It's possible that some of these are not deterministic across
+#       platforms. If so, just remove the offending command.
+#
+--disable_warnings
+drop procedure if exists bug4902|
+--enable_warnings
+create procedure bug4902()
+begin
+  show charset like 'foo';
+  show collation like 'foo';
+  show column types;
+  show create table t1;
+  show create database test;
+  show databases like 'foo';
+  show errors;
+  show columns from t1;
+  show grants for 'root'@'localhost';
+  show keys from t1;
+  show open tables like 'foo';
+  show privileges;
+  show status like 'foo';
+  show tables like 'foo';
+  show variables like 'foo';
+  show warnings;
+end|
+--disable_parsing
+show binlog events;
+show storage engines;
+show master status;
+show slave hosts;
+show slave status;
+--enable_parsing
+
+call bug4902()|
+call bug4902()|
+
+drop procedure bug4902|
+
+# We need separate SP for SHOW PROCESSLIST  since we want use replace_column
+--disable_warnings
+drop procedure if exists bug4902_2|
+--enable_warnings
+create procedure bug4902_2()
+begin
+  show processlist;
+end|
+--replace_column 1 # 6 # 3 localhost
+call bug4902_2()|
+--replace_column 1 # 6 # 3 localhost
+call bug4902_2()|
+drop procedure bug4902_2|
+
+#
+# BUG#4904
+#
+--disable_warnings
+drop procedure if exists bug4904|
+--enable_warnings
+create procedure bug4904()
+begin
+  declare continue handler for sqlstate 'HY000' begin end;
+
+  create table t2 as select * from t3;
+end|
+
+-- error 1146
+call bug4904()|
+
+drop procedure bug4904|
+
+create table t3 (s1 char character set latin1, s2 char character set latin2)|
+
+--disable_warnings
+drop procedure if exists bug4904|
+--enable_warnings
+create procedure bug4904 ()
+begin
+  declare continue handler for sqlstate 'HY000' begin end;
+
+  select s1 from t3 union select s2 from t3; 
+end|
+
+call bug4904()|
+
+drop procedure bug4904|
+drop table t3|
+
+#
+# BUG#336
+#
+--disable_warnings
+drop procedure if exists bug336|
+--enable_warnings
+create procedure bug336(out y int)
+begin
+  declare x int;
+  set x = (select sum(t.data) from test.t1 t);
+  set y = x;
+end|
+
+insert into t1 values ("a", 2), ("b", 3)|
+call bug336(@y)|
+select @y|
+delete from t1|
+drop procedure bug336|
+
+#
+# BUG#3157
+#
+--disable_warnings
+drop procedure if exists bug3157|
+--enable_warnings
+create procedure bug3157()
+begin
+  if exists(select * from t1) then
+    set @n= @n + 1;
+  end if;
+  if (select count(*) from t1) then
+    set @n= @n + 1;
+  end if;
+end|
+
+set @n = 0|
+insert into t1 values ("a", 1)|
+call bug3157()|
+select @n|
+delete from t1|
+drop procedure bug3157|
+
+#
+# BUG#5251: mysql changes creation time of a procedure/function when altering
+#
+--disable_warnings
+drop procedure if exists bug5251|
+--enable_warnings
+create procedure bug5251()
+begin
+end|
+
+select created into @c1 from mysql.proc
+  where db='test' and name='bug5251'|
+--sleep 2
+alter procedure bug5251 comment 'foobar'|
+select count(*) from mysql.proc
+  where  db='test' and name='bug5251' and created = @c1|
+
+drop procedure bug5251|
+
+#
+# BUG#5279: Stored procedure packets out of order if CHECKSUM TABLE
+#
+--disable_warnings
+drop procedure if exists bug5251|
+--enable_warnings
+create procedure bug5251()
+  checksum table t1|
+
+call bug5251()|
+call bug5251()|
+drop procedure bug5251|
+
+#
+# BUG#5287: Stored procedure crash if leave outside loop
+#
+--disable_warnings
+drop procedure if exists bug5287|
+--enable_warnings
+create procedure bug5287(param1 int)
+label1:
+  begin
+    declare c cursor for select 5;
+
+    loop
+      if param1 >= 0 then
+        leave label1;
+      end if;
+    end loop;
+end|
+call bug5287(1)|
+drop procedure bug5287|
+
+
+#
+# BUG#5307: Stored procedure allows statement after BEGIN ... END
+#
+--disable_warnings
+drop procedure if exists bug5307|
+--enable_warnings
+create procedure bug5307()
+begin
+end; set @x = 3|
+
+call bug5307()|
+select @x|
+drop procedure bug5307|
+
+#
+# BUG#5258: Stored procedure modified date is 0000-00-00
+# (This was a design flaw)
+--disable_warnings
+drop procedure if exists bug5258|
+--enable_warnings
+create procedure bug5258()
+begin
+end|
+
+--disable_warnings
+drop procedure if exists bug5258_aux|
+--enable_warnings
+create procedure bug5258_aux()
+begin
+  declare c, m char(19);
+
+  select created,modified into c,m from mysql.proc where name = 'bug5258';
+  if c = m then
+    select 'Ok';
+  else
+    select c, m;
+  end if;
+end|
+
+call bug5258_aux()|
+
+drop procedure bug5258|
+drop procedure bug5258_aux|
+
+#
+# BUG#4487: Stored procedure connection aborted if uninitialized char
+#
+--disable_warnings
+drop function if exists bug4487|
+--enable_warnings
+create function bug4487() returns char
+begin
+  declare v char;
+  return v;
+end|
+
+select bug4487()|
+drop function bug4487|
+
+
+#
+# BUG#4941: Stored procedure crash fetching null value into variable.
+#
+--disable_warnings
+drop procedure if exists bug4941|
+--enable_warnings
+--disable_warnings
+drop procedure if exists bug4941|
+--enable_warnings
+create procedure bug4941(out x int)
+begin
+  declare c cursor for select i from t2 limit 1;
+  open c;
+  fetch c into x;
+  close c;
+end|
+
+insert into t2 values (null, null, null)|
+set @x = 42|
+call bug4941(@x)|
+select @x|
+delete from t1|
+drop procedure bug4941|
+
+
+#
+# BUG#3583: query cache doesn't work for stored procedures
+#
+--disable_warnings
+drop procedure if exists bug3583|
+--enable_warnings
+--disable_warnings
+drop procedure if exists bug3583|
+--enable_warnings
+create procedure bug3583()
+begin
+  declare c int;
+
+  select * from t1;
+  select count(*) into c from t1;
+  select c;
+end|
+
+insert into t1 values ("x", 3), ("y", 5)|
+set @x = @@query_cache_size|
+set global query_cache_size = 10*1024*1024|
+
+flush status|
+flush query cache|
+show status like 'Qcache_hits'|
+call bug3583()|
+show status like 'Qcache_hits'|
+call bug3583()|
+call bug3583()|
+show status like 'Qcache_hits'|
+
+set global query_cache_size = @x|
+flush status|
+flush query cache|
+delete from t1|
+drop procedure bug3583|
+
+#
+# BUG#4905: Stored procedure doesn't clear for "Rows affected"
+#
+--disable_warnings
+drop procedure if exists bug4905|
+--enable_warnings
+
+create table t3 (s1 int,primary key (s1))|
+
+--disable_warnings
+drop procedure if exists bug4905|
+--enable_warnings
+create procedure bug4905()
+begin
+  declare v int;
+  declare continue handler for sqlstate '23000' set v = 5;
+
+  insert into t3 values (1);
+end|
+
+call bug4905()|
+select row_count()|
+call bug4905()|
+select row_count()|
+call bug4905()|
+select row_count()|
+select * from t3|
+
+drop procedure bug4905|
+drop table t3|
+
+#
+# BUG#6022: Stored procedure shutdown problem with self-calling function.
+#
+
+--disable_parsing # until we implement support for recursive stored functions.
+--disable_warnings
+drop function if exists bug6022|
+--enable_warnings
+
+--disable_warnings
+drop function if exists bug6022|
+--enable_warnings
+create function bug6022(x int) returns int
+begin
+  if x < 0 then
+    return 0;
+  else
+    return bug6022(x-1);
+  end if;
+end|
+
+select bug6022(5)|
+drop function bug6022|
+--enable_parsing
+
+#
+# BUG#6029: Stored procedure specific handlers should have priority
+#
+--disable_warnings
+drop procedure if exists bug6029|
+--enable_warnings
+
+--disable_warnings
+drop procedure if exists bug6029|
+--enable_warnings
+create procedure bug6029()
+begin
+  declare exit handler for 1136  select '1136';
+  declare exit handler for sqlstate '23000'  select 'sqlstate 23000';
+  declare continue handler for sqlexception  select 'sqlexception';
+
+  insert into t3 values (1);
+  insert into t3 values (1,2);
+end|
+ 
+create table t3 (s1 int, primary key (s1))|
+insert into t3 values (1)|
+call bug6029()|
+delete from t3|
+call bug6029()|
+
+drop procedure bug6029|
+drop table t3|
+
+#
+# BUG#8540: Local variable overrides an alias
+#
+--disable_warnings
+drop procedure if exists bug8540|
+--enable_warnings
+
+create procedure bug8540()
+begin
+  declare x int default 1;
+  select x as y, x+0 as z;
+end|
+
+call bug8540()|
+drop procedure bug8540|
+
+#
+# BUG#6642: Stored procedure crash if expression with set function
+#
+create table t3 (s1 int)|
+
+--disable_warnings
+drop procedure if exists bug6642|
+--enable_warnings
+
+create procedure bug6642()
+  select abs(count(s1)) from t3|
+
+call bug6642()|
+call bug6642()|
+drop procedure bug6642|
+
+#
+# BUG#7013: Stored procedure crash if group by ... with rollup
+#
+insert into t3 values (0),(1)|
+--disable_warnings
+drop procedure if exists bug7013|
+--enable_warnings
+create procedure bug7013()
+  select s1,count(s1) from t3 group by s1 with rollup|
+call bug7013()|
+call bug7013()|
+drop procedure bug7013|
+
+#
+# BUG#7743: 'Lost connection to MySQL server during query' on Stored Procedure
+#
+--disable_warnings
+drop table if exists t4|
+--enable_warnings
+create table t4 (
+  a mediumint(8) unsigned not null auto_increment,
+  b smallint(5) unsigned not null,
+  c char(32) not null,
+  primary key  (a)
+) engine=myisam default charset=latin1|
+insert into t4 values (1, 2, 'oneword')|
+insert into t4 values (2, 2, 'anotherword')|
+
+--disable_warnings
+drop procedure if exists bug7743|
+--enable_warnings
+create procedure bug7743 ( searchstring char(28) )
+begin
+  declare var mediumint(8) unsigned;
+  select a into var from t4 where b = 2 and c = binary searchstring limit 1;
+  select var;
+end|
+
+call bug7743("oneword")|
+call bug7743("OneWord")|
+call bug7743("anotherword")|
+call bug7743("AnotherWord")|
+drop procedure bug7743|
+drop table t4|
+
+#
+# BUG#7992: SELECT .. INTO variable .. within Stored Procedure crashes
+#           the server
+#
+delete from t3|
+insert into t3 values(1)|
+drop procedure if exists bug7992_1|
+drop procedure if exists bug7992_2|
+create procedure bug7992_1()
+begin
+  declare i int;
+  select max(s1)+1 into i from t3;
+end|
+create procedure bug7992_2()
+  insert into t3 (s1) select max(t4.s1)+1 from t3 as t4|
+
+call bug7992_1()|
+call bug7992_1()|
+call bug7992_2()|
+call bug7992_2()|
+
+drop procedure bug7992_1|
+drop procedure bug7992_2|
+drop table t3|
+
+#
+# BUG#8116: calling simple stored procedure twice in a row results
+#           in server crash
+#
+create table t3 (  userid bigint(20) not null default 0 )|
+
+--disable_warnings
+drop procedure if exists bug8116|
+--enable_warnings
+create procedure bug8116(in _userid int)
+   select * from t3 where userid = _userid|
+
+call bug8116(42)|
+call bug8116(42)|
+drop procedure bug8116|
+drop table t3|
+
+#
+# BUG#6857: current_time() in STORED PROCEDURES
+#
+--disable_warnings
+drop procedure if exists bug6857|
+--enable_warnings
+create procedure bug6857(counter int)
+begin
+  declare t0, t1 int;
+  declare plus bool default 0;
+
+  set t0 = current_time();
+  while counter > 0 do
+    set counter = counter - 1;
+  end while;
+  set t1 = current_time();
+  if t1 > t0 then
+    set plus = 1;
+  end if;
+  select plus;
+end|
+
+# QQ: This is currently disabled. Not only does it slow down a normal test
+#     run, it makes running with valgrind (or similar tools) extremely
+#     painful.
+# Make sure this takes at least one second on all machines in all builds.
+# 30000 makes it about 3 seconds on an old 1.1GHz linux.
+#call bug6857(300000)|
+
+drop procedure bug6857|
+
+#
+# BUG#8757: Stored Procedures: Scope of Begin and End Statements do not
+#           work properly.
+--disable_warnings
+drop procedure if exists bug8757|
+--enable_warnings
+create procedure bug8757()
+begin
+  declare x int;
+  declare c1 cursor for select data from t1 limit 1;
+
+  begin
+    declare y int;
+    declare c2 cursor for select i from t2 limit 1;
+
+    open c2;
+    fetch c2 into y;
+    close c2;
+    select 2,y;
+  end;
+  open c1;
+  fetch c1 into x;
+  close c1;
+  select 1,x;
+end|
+
+delete from t1|
+delete from t2|
+insert into t1 values ("x", 1)|
+insert into t2 values ("y", 2, 0.0)|
+
+call bug8757()|
+
+delete from t1|
+delete from t2|
+drop procedure bug8757|
+
+
+#
+# BUG#8762: Stored Procedures: Inconsistent behavior
+#           of DROP PROCEDURE IF EXISTS statement.
+--disable_warnings
+drop procedure if exists bug8762|
+--enable_warnings
+# Doesn't exist
+drop procedure if exists bug8762; create procedure bug8762() begin end|
+# Does exist
+drop procedure if exists bug8762; create procedure bug8762() begin end|
+drop procedure bug8762|
+
+
+#
+# BUG#5240: Stored procedure crash if function has cursor declaration
+#
+--disable_warnings
+drop function if exists bug5240|
+--enable_warnings
+create function bug5240 () returns int
+begin
+  declare x int;
+  declare c cursor for select data from t1 limit 1;
+
+  open c;
+  fetch c into x;
+  close c;
+  return x;
+end|
+
+delete from t1|
+insert into t1 values ("answer", 42)|
+select id, bug5240() from t1|
+drop function bug5240|
+
+#
+# BUG#5278: Stored procedure packets out of order if SET PASSWORD.
+#
+--disable_warnings
+drop function if exists bug5278|
+--enable_warnings
+create function bug5278 () returns char
+begin
+  SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
+  return 'okay';
+end|
+
+--error 1133
+select bug5278()|
+--error 1133
+select bug5278()|
+drop function bug5278|
+
+#
+# BUG#7992: rolling back temporary Item tree changes in SP
+#
+--disable_warnings
+drop procedure if exists p1|
+--enable_warnings
+create table t3(id int)|
+insert into t3 values(1)|
+create procedure bug7992()
+begin
+  declare i int;
+  select max(id)+1 into i from t3;
+end|
+
+call bug7992()|
+call bug7992()|
+drop procedure bug7992|
+drop table t3|
+delimiter ;|
+
+#
+# BUG#8849: problem with insert statement with table alias's
+#
+# Rolling back changes to AND/OR structure of ON and WHERE clauses  in SP
+# 
+
+delimiter |;
+create table t3 (
+  lpitnumber int(11) default null,
+  lrecordtype int(11) default null
+)|
+
+create table t4 (
+  lbsiid int(11) not null default '0',
+  ltradingmodeid int(11) not null default '0',
+  ltradingareaid int(11) not null default '0',
+  csellingprice decimal(19,4) default null,
+  primary key  (lbsiid,ltradingmodeid,ltradingareaid)
+)|
+
+create table t5 (
+  lbsiid int(11) not null default '0',
+  ltradingareaid int(11) not null default '0',
+  primary key  (lbsiid,ltradingareaid)
+)|
+
+--disable_warnings
+drop procedure if exists bug8849|
+--enable_warnings
+create procedure bug8849()
+begin
+  insert into t5
+  (
+   t5.lbsiid,
+   t5.ltradingareaid
+  )
+  select distinct t3.lpitnumber, t4.ltradingareaid
+  from
+    t4 join t3 on
+      t3.lpitnumber = t4.lbsiid
+      and t3.lrecordtype = 1
+    left join t4 as price01 on
+      price01.lbsiid = t4.lbsiid and
+      price01.ltradingmodeid = 1 and
+      t4.ltradingareaid = price01.ltradingareaid;
+end|
+
+call bug8849()|
+call bug8849()|
+call bug8849()|
+drop procedure bug8849|
+drop tables t3,t4,t5|
+
+#
+# BUG#8937: Stored Procedure: AVG() works as SUM() in SELECT ... INTO statement
+#
+--disable_warnings
+drop procedure if exists bug8937|
+--enable_warnings
+create procedure bug8937()
+begin
+  declare s,x,y,z int;
+  declare a float;
+
+  select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1;
+  select s,x,y,z;
+  select avg(data) into a from t1;
+  select a;
+end|
+
+delete from t1|
+insert into t1 (data) values (1), (2), (3), (4), (6)|
+call bug8937()|
+
+drop procedure bug8937|
+delete from t1|
+
+
+#
+# BUG#6900: Stored procedure inner handler ignored
+# BUG#9074: STORED PROC: The scope of every handler declared is not
+#                        properly applied
+#
+--disable_warnings
+drop procedure if exists bug6900|
+drop procedure if exists bug9074|
+drop procedure if exists bug6900_9074|
+--enable_warnings
+
+create table t3 (w char unique, x char)|
+insert into t3 values ('a', 'b')|
+
+create procedure bug6900()
+begin
+  declare exit handler for sqlexception select '1';
+
+  begin
+    declare exit handler for sqlexception select '2';
+
+    insert into t3 values ('x', 'y', 'z');
+  end;
+end|
+
+create procedure bug9074()
+begin
+  declare x1, x2, x3, x4, x5, x6 int default 0;
+
+  begin    
+    declare continue handler for sqlstate '23000' set x5 = 1;      
+
+    insert into t3 values ('a', 'b');      
+    set x6 = 1;      
+  end;
+
+ begin1_label:
+  begin
+    declare continue handler for sqlstate '23000' set x1 = 1;      
+
+    insert into t3 values ('a', 'b');      
+    set x2 = 1;      
+				
+   begin2_label:
+    begin  
+      declare exit handler for sqlstate '23000' set x3 = 1;         
+
+      set x4= 1;         
+      insert into t3 values ('a','b');
+      set x4= 0;
+    end begin2_label;
+  end begin1_label;
+
+  select x1, x2, x3, x4, x5, x6;
+end|
+
+create procedure bug6900_9074(z int)
+begin
+  declare exit handler for sqlstate '23000' select '23000';
+
+  begin
+    declare exit handler for sqlexception select 'sqlexception';
+
+    if z = 1 then
+      insert into t3 values ('a', 'b');
+    else
+      insert into t3 values ('x', 'y', 'z');
+    end if;
+  end;
+end|
+
+call bug6900()|
+call bug9074()|
+call bug6900_9074(0)|
+call bug6900_9074(1)|
+
+drop procedure bug6900|
+drop procedure bug9074|
+drop procedure bug6900_9074|
+drop table t3|
+
+
+#
+# BUG#7185: Stored procedure crash if identifier is AVG
+#
+--disable_warnings
+drop procedure if exists avg|
+--enable_warnings
+create procedure avg ()
+begin
+end|
+
+call avg ()|
+drop procedure avg|
+
+
+#
+# BUG#6129: Stored procedure won't display @@sql_mode value
+#
+--disable_warnings
+drop procedure if exists bug6129|
+--enable_warnings
+set @old_mode= @@sql_mode;
+set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO";
+create procedure bug6129()
+  select @@sql_mode|
+call bug6129()|
+set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"|
+call bug6129()|
+set @@sql_mode= "NO_ZERO_IN_DATE"|
+call bug6129()|
+set @@sql_mode=@old_mode;
+
+drop procedure bug6129|
+
+
+#
+# BUG#9856: Stored procedures: crash if handler for sqlexception, not found
+#
+--disable_warnings
+drop procedure if exists bug9856|
+--enable_warnings
+create procedure bug9856()
+begin
+  declare v int;
+  declare c cursor for select data from t1;
+  declare exit handler for sqlexception, not found select '16';
+
+  open c;
+  fetch c into v;
+  select v;
+end|
+
+delete from t1|
+call bug9856()|
+call bug9856()|
+drop procedure bug9856|
+
+
+#
+# BUG##9674: Stored Procs: Using declared vars in algebric operation causes
+#            system crash.
+#
+--disable_warnings
+drop procedure if exists bug9674_1|
+drop procedure if exists bug9674_2|
+--enable_warnings
+create procedure bug9674_1(out arg int)
+begin
+  declare temp_in1 int default 0;
+  declare temp_fl1 int default 0;
+
+  set temp_in1 = 100;
+  set temp_fl1 = temp_in1/10;
+  set arg = temp_fl1;
+end|
+
+create procedure bug9674_2()
+begin
+  declare v int default 100;
+
+  select v/10;
+end|
+
+call bug9674_1(@sptmp)|
+call bug9674_1(@sptmp)|
+select @sptmp|
+call bug9674_2()|
+call bug9674_2()|
+drop procedure bug9674_1|
+drop procedure bug9674_2|
+
+
+#
+# BUG#9598: stored procedure call within stored procedure overwrites IN variable
+#
+--disable_warnings
+drop procedure if exists bug9598_1|
+drop procedure if exists bug9598_2|
+--enable_warnings
+create procedure bug9598_1(in var_1 char(16),
+                           out var_2 integer, out var_3 integer)
+begin
+  set var_2 = 50;
+  set var_3 = 60;
+end|
+
+create procedure bug9598_2(in v1 char(16),
+                           in v2 integer,
+                           in v3 integer,
+                           in v4 integer,
+                           in v5 integer)
+begin
+  select v1,v2,v3,v4,v5;
+  call bug9598_1(v1,@tmp1,@tmp2);
+  select v1,v2,v3,v4,v5;
+end|
+
+call bug9598_2('Test',2,3,4,5)|
+select @tmp1, @tmp2|
+
+drop procedure bug9598_1|
+drop procedure bug9598_2|
+
+
+#
+# BUG#9902: Crash with simple stored function using user defined variables
+#
+--disable_warnings
+drop procedure if exists bug9902|
+--enable_warnings
+create function bug9902() returns int(11)
+begin
+  set @x = @x + 1;
+  return @x;
+end|
+
+set @qcs1 = @@query_cache_size|
+set global query_cache_size = 100000|
+set @x = 1|
+insert into t1 values ("qc", 42)|
+select bug9902() from t1|
+select bug9902() from t1|
+select @x|
+
+set global query_cache_size = @qcs1|
+delete from t1|
+drop function bug9902|
+
+
+#
+# BUG#9102: Stored proccedures: function which returns blob causes crash
+#
+--disable_warnings
+drop function if exists bug9102|
+--enable_warnings
+create function bug9102() returns blob return 'a'|
+select bug9102()|
+drop function bug9102|
+
+
+#
+# BUG#7648: Stored procedure crash when invoking a function that returns a bit
+#
+--disable_warnings
+drop function if exists bug7648|
+--enable_warnings
+create function bug7648() returns bit(8) return 'a'|
+select bug7648()|
+drop function bug7648|
+
+
+#
+# BUG#9775: crash if create function that returns enum or set
+#
+--disable_warnings
+drop function if exists bug9775|
+--enable_warnings
+create function bug9775(v1 char(1)) returns enum('a','b') return v1|
+select bug9775('a'),bug9775('b'),bug9775('c')|
+drop function bug9775|
+create function bug9775(v1 int) returns enum('a','b') return v1|
+select bug9775(1),bug9775(2),bug9775(3)|
+drop function bug9775|
+
+create function bug9775(v1 char(1)) returns set('a','b') return v1|
+select bug9775('a'),bug9775('b'),bug9775('a,b'),bug9775('c')|
+drop function bug9775|
+create function bug9775(v1 int) returns set('a','b') return v1|
+select bug9775(1),bug9775(2),bug9775(3),bug9775(4)|
+drop function bug9775|
+
+
+#
+# BUG#8861: If Return is a YEAR data type, value is not shown in year format
+#
+--disable_warnings
+drop function if exists bug8861|
+--enable_warnings
+create function bug8861(v1 int) returns year return v1|
+select bug8861(05)|
+set @x = bug8861(05)|
+select @x|
+drop function bug8861|
+
+
+#
+# BUG#9004: Inconsistent behaviour of SP re. warnings
+#
+--disable_warnings
+drop procedure if exists bug9004_1|
+drop procedure if exists bug9004_2|
+--enable_warnings
+create procedure bug9004_1(x char(16))
+begin
+  insert into t1 values (x, 42);
+  insert into t1 values (x, 17);
+end|
+create procedure bug9004_2(x char(16))
+  call bug9004_1(x)|
+
+# Truncation warnings expected...
+call bug9004_1('12345678901234567')|
+call bug9004_2('12345678901234567890')|
+
+delete from t1|
+drop procedure bug9004_1|
+drop procedure bug9004_2|
+
+#
+# BUG#7293: Stored procedure crash with soundex
+#
+--disable_warnings
+drop procedure if exists bug7293|
+--enable_warnings
+insert into t1 values ('secret', 0)| 
+create procedure bug7293(p1 varchar(100))
+begin
+  if exists (select id from t1 where soundex(p1)=soundex(id)) then
+    select 'yes';
+  end if;
+end;| 
+call bug7293('secret')| 
+call bug7293 ('secrete')| 
+drop procedure bug7293|
+delete from t1|
+
+
+#
+# BUG#9841: Unexpected read lock when trying to update a view in a
+#           stored procedure
+#
+--disable_warnings
+drop procedure if exists bug9841|
+drop view if exists v1|
+--enable_warnings
+
+create view v1 as select * from t1, t2 where id = s|
+create procedure bug9841 ()
+  update v1 set data = 10| 
+call bug9841()|
+
+drop view v1|
+drop procedure bug9841|
+
+
+#
+# BUG#5963 subqueries in SET/IF
+#
+--disable_warnings
+drop procedure if exists bug5963|
+--enable_warnings
+
+create procedure bug5963_1 () begin declare v int; set v = (select s1 from t3); select v; end;|
+create table t3 (s1 int)|
+insert into t3 values (5)|
+call bug5963_1()|
+call bug5963_1()|
+drop procedure bug5963_1|
+drop table t3|
+
+create procedure bug5963_2 (cfk_value int) 
+begin 
+  if cfk_value in (select cpk from t3) then 
+    set @x = 5; 
+  end if; 
+  end; 
+| 
+create table t3 (cpk int)| 
+insert into t3 values (1)| 
+call bug5963_2(1)|
+call bug5963_2(1)|
+drop procedure bug5963_2|
+drop table t3|
+
+
+#
+# BUG#9559: Functions: Numeric Operations using -ve value gives incorrect
+#           results.
+#
+--disable_warnings
+drop function if exists bug9559|
+--enable_warnings
+create function bug9559()
+  returns int
+begin
+  set @y = -6/2;
+  return @y;
+end|
+
+select bug9559()|
+
+drop function bug9559|
+
+
+#
+# BUG#10961: Stored procedures: crash if select * from dual
+#
+--disable_warnings
+drop procedure if exists bug10961|
+--enable_warnings
+# "select * from dual" results in an error, so the cursor will not open
+create procedure bug10961()
+begin
+  declare v char;
+  declare x int;
+  declare c cursor for select * from dual;
+  declare continue handler for sqlexception select x;
+
+  set x = 1;
+  open c;
+  set x = 2;
+  fetch c into v;
+  set x = 3;
+  close c;
+end|
+
+call bug10961()|
+call bug10961()|
+
+drop procedure bug10961|
+
+#
+# BUG #6866: Second call of a stored procedure using a view with on expressions
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug6866|
+--enable_warnings
+
+DROP VIEW IF EXISTS tv|
+DROP TABLE IF EXISTS tt1,tt2,tt3|
+
+CREATE TABLE tt1 (a1 int, a2 int, a3 int, data varchar(10))|
+CREATE TABLE tt2 (a2 int, data2 varchar(10))|
+CREATE TABLE tt3 (a3 int, data3 varchar(10))|
+
+INSERT INTO tt1 VALUES (1, 1, 4, 'xx')|
+
+INSERT INTO tt2 VALUES (1, 'a')|
+INSERT INTO tt2 VALUES (2, 'b')|
+INSERT INTO tt2 VALUES (3, 'c')|
+
+INSERT INTO tt3 VALUES (4, 'd')|
+INSERT INTO tt3 VALUES (5, 'e')|
+INSERT INTO tt3 VALUES (6, 'f')|
+
+CREATE VIEW tv AS
+SELECT tt1.*, tt2.data2, tt3.data3
+  FROM tt1 INNER JOIN tt2 ON tt1.a2 = tt2.a2
+         LEFT JOIN tt3 ON tt1.a3 = tt3.a3
+    ORDER BY tt1.a1, tt2.a2, tt3.a3|
+
+CREATE PROCEDURE bug6866 (_a1 int)
+BEGIN
+SELECT * FROM tv WHERE a1 = _a1;
+END|
+
+CALL bug6866(1)|
+CALL bug6866(1)|
+CALL bug6866(1)|
+
+DROP PROCEDURE bug6866;
+
+DROP VIEW tv|
+DROP TABLE tt1, tt2, tt3|
+
+#
+# BUG#10136: items cleunup
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug10136|
+--enable_warnings
+create table t3 ( name char(5) not null primary key, val float not null)|
+insert into t3 values ('aaaaa', 1), ('bbbbb', 2), ('ccccc', 3)|
+create procedure bug10136()
+begin
+  declare done int default 3;
+
+  repeat
+    select * from t3;
+    set done = done - 1;
+  until done <= 0 end repeat;
+
+end|
+call bug10136()|
+call bug10136()|
+call bug10136()|
+drop procedure bug10136|
+drop table t3|
+
+#
+# BUG#11529: crash server after use stored procedure
+#
+--disable_warnings
+drop procedure if exists bug11529|
+--enable_warnings
+create procedure bug11529()
+begin
+  declare c cursor for select id, data from t1 where data in (10,13);
+
+  open c;
+  begin
+    declare vid char(16);
+    declare vdata int;
+    declare exit handler for not found begin end;
+
+    while true do
+      fetch c into vid, vdata;
+    end while;
+  end;
+  close c;
+end|
+
+insert into t1 values
+  ('Name1', 10),
+  ('Name2', 11),
+  ('Name3', 12),
+  ('Name4', 13),
+  ('Name5', 14)|
+
+call bug11529()|
+call bug11529()|
+delete from t1|
+drop procedure bug11529|
+
+
+#
+# BUG#6063: Stored procedure labels are subject to restrictions (partial)
+# BUG#7088: Stored procedures: labels won't work if character set is utf8
+#
+--disable_warnings
+drop procedure if exists bug6063|
+drop procedure if exists bug7088_1|
+drop procedure if exists bug7088_2|
+--enable_warnings
+
+--disable_parsing # temporarily disabled until Bar fixes BUG#11986
+create procedure bug6063()
+  lâbel: begin end|
+call bug6063()|
+# QQ Known bug: this will not show the label correctly.
+show create procedure bug6063|
+
+set character set utf8|
+create procedure bug7088_1()
+  label1: begin end label1|
+create procedure bug7088_2()
+  läbel1: begin end|
+call bug7088_1()|
+call bug7088_2()|
+set character set default|
+show create procedure bug7088_1|
+show create procedure bug7088_2|
+
+drop procedure bug6063|
+drop procedure bug7088_1|
+drop procedure bug7088_2|
+--enable_parsing
+
+#
+# BUG#9565: "Wrong locking in stored procedure if a sub-sequent procedure
+#           is called".
+#
+--disable_warnings
+drop procedure if exists bug9565_sub|
+drop procedure if exists bug9565|
+--enable_warnings
+create procedure bug9565_sub()
+begin
+  select * from t1;
+end|
+create procedure bug9565()
+begin
+  insert into t1 values ("one", 1);
+  call bug9565_sub();
+end|
+call bug9565()|
+delete from t1|
+drop procedure bug9565_sub|
+drop procedure bug9565|
+
+
+#
+# BUG#9538: SProc: Creation fails if we try to SET system variable
+#           using @@var_name in proc
+#
+--disable_warnings
+drop procedure if exists bug9538|
+--enable_warnings
+create procedure bug9538()
+  set @@sort_buffer_size = 1000000|
+
+set @x = @@sort_buffer_size|
+set @@sort_buffer_size = 2000000|
+select @@sort_buffer_size|
+call bug9538()|
+select @@sort_buffer_size|
+set @@sort_buffer_size = @x|
+
+drop procedure bug9538|
+
+
+#
+# BUG#8692: Cursor fetch of empty string
+#
+--disable_warnings
+drop procedure if exists bug8692|
+--enable_warnings
+create table t3 (c1 varchar(5), c2 char(5), c3 enum('one','two'), c4 text, c5 blob, c6 char(5), c7 varchar(5))|
+insert into t3 values ('', '', '', '', '', '', NULL)|
+
+create procedure bug8692()
+begin 
+    declare v1 VARCHAR(10); 
+    declare v2 VARCHAR(10); 
+    declare v3 VARCHAR(10); 
+    declare v4 VARCHAR(10); 
+    declare v5 VARCHAR(10); 
+    declare v6 VARCHAR(10); 
+    declare v7 VARCHAR(10); 
+    declare c8692 cursor for select c1,c2,c3,c4,c5,c6,c7 from t3; 
+    open c8692; 
+    fetch c8692 into v1,v2,v3,v4,v5,v6,v7;
+    select v1, v2, v3, v4, v5, v6, v7;
+end|
+
+call bug8692()|
+drop procedure bug8692|
+drop table t3|
+
+#
+# Bug#10055 "Using stored function with information_schema causes empty
+#            result set"
+#
+--disable_warnings
+drop function if exists bug10055|
+--enable_warnings
+create function bug10055(v char(255)) returns char(255) return lower(v)|
+# This select should not crash server and should return all fields in t1
+select t.column_name, bug10055(t.column_name)
+from information_schema.columns as t
+where t.table_schema = 'test' and t.table_name = 't1'|
+drop function bug10055|
+
+#
+# Bug #12297 "SP crashes the server if data inserted inside a lon loop"
+# The test for memleak bug, so actually there is no way to test it
+# from the suite. The test below could be used to check SP memory
+# consumption by passing large input parameter.
+#
+
+--disable_warnings
+drop procedure if exists bug12297|
+--enable_warnings
+
+create procedure bug12297(lim int)
+begin
+  set @x = 0;
+  repeat
+    insert into t1(id,data)
+    values('aa', @x);
+    set @x = @x + 1;
+  until @x >= lim
+  end repeat;
+end|
+
+call bug12297(10)|
+drop procedure bug12297|
+
+#
+# Bug #11247 "Stored procedures: Function calls in long loops leak memory"
+# One more memleak bug test. One could use this test to check that the memory
+# isn't leaking by increasing the input value for p_bug11247.
+#
+
+--disable_warnings
+drop function if exists f_bug11247|
+drop procedure if exists p_bug11247|
+--enable_warnings
+
+create function f_bug11247(param int)
+  returns int
+return param + 1|
+
+create procedure p_bug11247(lim int)
+begin
+  declare v int default 0;
+
+  while v < lim do
+    set v= f_bug11247(v);
+  end while;
+end|
+
+call p_bug11247(10)|
+drop function f_bug11247|
+drop procedure p_bug11247|
+#
+# BUG#12168: "'DECLARE CONTINUE HANDLER FOR NOT FOUND ...' in conditional
+# handled incorrectly"
+#
+--disable_warnings
+drop procedure if exists bug12168|
+drop table if exists t3, t4|
+--enable_warnings
+
+create table t3 (a int)|
+insert into t3 values (1),(2),(3),(4)|
+
+create table t4 (a int)|
+
+create procedure bug12168(arg1 char(1))
+begin
+  declare b, c integer;
+  if arg1 = 'a' then
+    begin
+      declare c1 cursor for select a from t3 where a % 2;
+      declare continue handler for not found set b = 1;
+      set b = 0;
+      open c1;
+      c1_repeat: repeat
+        fetch c1 into c;
+        if (b = 1) then
+          leave c1_repeat;
+        end if;
+
+        insert into t4 values (c);
+        until b = 1
+      end repeat;
+    end;
+  end if;
+  if arg1 = 'b' then
+    begin
+      declare c2 cursor for select a from t3 where not a % 2;
+      declare continue handler for not found set b = 1;
+      set b = 0;
+      open c2;
+      c2_repeat: repeat
+        fetch c2 into c;
+        if (b = 1) then
+          leave c2_repeat;
+        end if;
+
+        insert into t4 values (c);
+        until b = 1
+      end repeat;
+    end;
+  end if;
+end|
+
+call bug12168('a')|
+select * from t4|
+truncate t4|
+call bug12168('b')|
+select * from t4|
+truncate t4|
+call bug12168('a')|
+select * from t4|
+truncate t4|
+call bug12168('b')|
+select * from t4|
+truncate t4|
+drop table t3, t4|
+drop procedure if exists bug12168|
+
+#
+# Bug #11333 "Stored Procedure: Memory blow up on repeated SELECT ... INTO
+# query"
+# One more memleak bug. Use the test to check memory consumption.
+#
+
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug11333|
+--enable_warnings
+
+create table t3 (c1 char(128))|
+
+insert into t3 values 
+  ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')|
+
+
+create procedure bug11333(i int)
+begin
+    declare tmp varchar(128);
+    set @x = 0;
+    repeat
+        select c1 into tmp from t3
+          where c1 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
+        set @x = @x + 1;
+        until @x >= i
+    end repeat;
+end|
+
+call bug11333(10)|
+
+drop procedure bug11333|
+drop table t3|
+
+#
+# BUG#9048: Creating a function with char binary IN parameter fails
+#
+--disable_warnings
+drop function if exists bug9048|
+--enable_warnings
+create function bug9048(f1 char binary) returns char binary
+begin
+  set f1= concat( 'hello', f1 );
+  return f1;
+end|
+drop function bug9048|
+
+# Bug #12849 Stored Procedure: Crash on procedure call with CHAR type
+# 'INOUT' parameter
+#
+
+--disable_warnings
+drop procedure if exists bug12849_1|
+--enable_warnings
+create procedure bug12849_1(inout x char) select x into x|
+set @var='a'|
+call bug12849_1(@var)|
+select @var|
+drop procedure bug12849_1|
+
+--disable_warnings
+drop procedure if exists bug12849_2|
+--enable_warnings
+create procedure bug12849_2(inout foo varchar(15))
+begin
+select concat(foo, foo) INTO foo;
+end|
+set @var='abcd'|
+call bug12849_2(@var)|
+select @var|
+drop procedure bug12849_2|
+
+#
+# BUG#13133: Local variables in stored procedures are not initialized correctly.
+#
+--disable_warnings
+drop procedure if exists bug131333|
+drop function if exists bug131333|
+--enable_warnings
+create procedure bug131333()
+begin
+  begin
+    declare a int;
+
+    select a;
+    set a = 1;
+    select a;
+  end;
+  begin
+    declare b int;
+
+    select b;
+  end;
+end|
+
+create function bug131333()
+  returns int
+begin
+  begin
+    declare a int;
+
+    set a = 1;
+  end;
+  begin
+    declare b int;
+
+    return b;
+  end;
+end|
+
+call bug131333()|
+select bug131333()|
+
+drop procedure bug131333|
+drop function bug131333|
+
+#
+# BUG#12379: PROCEDURE with HANDLER calling FUNCTION with error get
+#            strange result
+#
+--disable_warnings
+drop function if exists bug12379|
+drop procedure if exists bug12379_1|
+drop procedure if exists bug12379_2|
+drop procedure if exists bug12379_3|
+drop table if exists t3|
+--enable_warnings
+
+create table t3 (c1 char(1) primary key not null)|
+
+create function bug12379()
+  returns integer
+begin
+   insert into t3 values('X');
+   insert into t3 values('X');
+   return 0;
+end|
+
+create procedure bug12379_1()
+begin
+   declare exit handler for sqlexception select 42;
+
+   select bug12379();
+END|
+create procedure bug12379_2()
+begin
+   declare exit handler for sqlexception begin end;
+
+   select bug12379();
+end|
+create procedure bug12379_3()
+begin
+   select bug12379();
+end|
+
+--error 1062
+select bug12379()|
+select 1|
+call bug12379_1()|
+select 2|
+call bug12379_2()|
+select 3|
+--error 1062
+call bug12379_3()|
+select 4|
+
+drop function bug12379|
+drop procedure bug12379_1|
+drop procedure bug12379_2|
+drop procedure bug12379_3|
+drop table t3|
+
+#
+# Bug #13124    Stored Procedure using SELECT INTO crashes server
+#
+
+--disable_warnings
+drop procedure if exists bug13124|
+--enable_warnings
+create procedure bug13124()
+begin
+  declare y integer;
+  set @x=y;
+end|
+call bug13124()|
+drop procedure  bug13124|
+
+#
+# Bug #12979  Stored procedures: crash if inout decimal parameter
+#
+
+# check NULL inout parameters processing
+
+--disable_warnings
+drop procedure if exists bug12979_1|
+--enable_warnings
+create procedure bug12979_1(inout d decimal(5)) set d = d / 2|
+set @bug12979_user_var = NULL|
+call bug12979_1(@bug12979_user_var)|
+drop procedure bug12979_1|
+
+# check NULL local variables processing
+
+--disable_warnings
+drop procedure if exists bug12979_2|
+--enable_warnings
+create procedure bug12979_2()
+begin
+declare internal_var decimal(5);
+set internal_var= internal_var / 2;
+select internal_var;
+end|
+call bug12979_2()|
+drop procedure bug12979_2|
+
+
+#
+# BUG#6127: Stored procedure handlers within handlers don't work
+#
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug6127|
+--enable_warnings
+create table t3 (s1 int unique)|
+
+set @sm=@@sql_mode|
+set sql_mode='traditional'|
+
+create procedure bug6127()
+begin
+  declare continue handler for sqlstate '23000'
+    begin
+      declare continue handler for sqlstate '22003'
+        insert into t3 values (0);
+
+      insert into t3 values (1000000000000000);
+    end;
+
+  insert into t3 values (1);
+  insert into t3 values (1);
+end|
+
+call bug6127()|
+select * from t3|
+--error ER_DUP_ENTRY
+call bug6127()|
+select * from t3|
+set sql_mode=@sm|
+drop table t3|
+drop procedure bug6127|
+
+
+#
+# BUG#12589: Assert when creating temp. table from decimal stored procedure
+#            variable
+#
+--disable_warnings
+drop procedure if exists bug12589_1|
+drop procedure if exists bug12589_2|
+drop procedure if exists bug12589_3|
+--enable_warnings
+create procedure bug12589_1()
+begin
+  declare spv1 decimal(3,3);
+  set spv1= 123.456;
+
+  set spv1 = 'test';
+  create temporary table tm1 as select spv1;
+  show create table tm1;
+  drop temporary table tm1;
+end|
+
+create procedure bug12589_2()
+begin
+  declare spv1 decimal(6,3);
+  set spv1= 123.456;
+
+  create temporary table tm1 as select spv1;
+  show create table tm1;
+  drop temporary table tm1;
+end|
+
+create procedure bug12589_3()
+begin
+  declare spv1 decimal(6,3);
+  set spv1= -123.456;
+
+  create temporary table tm1 as select spv1;
+  show create table tm1;
+  drop temporary table tm1;
+end|
+
+# Note: The type of the field will match the value, not the declared
+#       type of the variable. (This is a type checking issue which
+#       might be changed later.)
+
+# Warning expected from "set spv1 = 'test'", the value is set to decimal "0".
+call bug12589_1()|
+# No warnings here
+call bug12589_2()|
+call bug12589_3()|
+drop procedure bug12589_1|
+drop procedure bug12589_2|
+drop procedure bug12589_3|
+
+#
+# BUG#7049: Stored procedure CALL errors are ignored
+#
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug7049_1|
+drop procedure if exists bug7049_2|
+drop procedure if exists bug7049_3|
+drop procedure if exists bug7049_4|
+drop function if exists bug7049_1|
+drop function if exists bug7049_2|
+--enable_warnings
+
+create table t3 ( x int unique )|
+
+create procedure bug7049_1()
+begin
+  insert into t3 values (42);
+  insert into t3 values (42);
+end|
+
+create procedure bug7049_2()
+begin
+  declare exit handler for sqlexception
+    select 'Caught it' as 'Result';
+
+  call bug7049_1();
+  select 'Missed it' as 'Result';
+end|
+
+create procedure bug7049_3()
+  call bug7049_1()|
+
+create procedure bug7049_4()
+begin
+  declare exit handler for sqlexception
+    select 'Caught it' as 'Result';
+
+  call bug7049_3();
+  select 'Missed it' as 'Result';
+end|
+
+create function bug7049_1()
+  returns int
+begin
+  insert into t3 values (42);
+  insert into t3 values (42);
+  return 42;
+end|
+
+create function bug7049_2()
+  returns int
+begin
+  declare x int default 0;
+  declare continue handler for sqlexception
+    set x = 1;
+
+  set x = bug7049_1();
+  return x;
+end|
+
+call bug7049_2()|
+select * from t3|
+delete from t3|
+call bug7049_4()|
+select * from t3|
+select bug7049_2()|
+
+drop table t3|
+drop procedure bug7049_1|
+drop procedure bug7049_2|
+drop procedure bug7049_3|
+drop procedure bug7049_4|
+drop function bug7049_1|
+drop function bug7049_2|
+
+
+#
+# BUG#13941: replace() string fuction behaves badly inside stored procedure
+# (BUG#13914: IFNULL is returning garbage in stored procedure)
+#
+--disable_warnings
+drop function if exists bug13941|
+drop procedure if exists bug13941|
+--enable_warnings
+
+create function bug13941(p_input_str text)
+  returns text
+begin
+  declare p_output_str text;
+
+  set p_output_str = p_input_str;
+
+  set p_output_str = replace(p_output_str, 'xyzzy', 'plugh');
+  set p_output_str = replace(p_output_str, 'test', 'prova');
+  set p_output_str = replace(p_output_str, 'this', 'questo');
+  set p_output_str = replace(p_output_str, ' a ', 'una ');
+  set p_output_str = replace(p_output_str, 'is', '');
+
+  return p_output_str;
+end|
+
+create procedure bug13941(out sout varchar(128))
+begin
+  set sout = 'Local';
+  set sout = ifnull(sout, 'DEF');
+end|
+
+# Note: The bug showed different behaviour in different types of builds,
+#  giving garbage results in some, and seemingly working in others.
+#  Running with valgrind (or purify) is the safe way to check that it's
+#  really working correctly.
+select bug13941('this is a test')|
+call bug13941(@a)|
+select @a|
+
+drop function bug13941|
+drop procedure bug13941|
+
+
+#
+# BUG#13095: Cannot create VIEWs in prepared statements
+#
+
+delimiter ;|
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug13095;
+DROP TABLE IF EXISTS bug13095_t1;
+DROP VIEW IF EXISTS bug13095_v1;
+--enable_warnings
+
+delimiter |;
+
+CREATE PROCEDURE bug13095(tbl_name varchar(32))
+BEGIN
+  SET @str =
+    CONCAT("CREATE TABLE ", tbl_name, "(stuff char(15))");
+  SELECT @str;
+  PREPARE stmt FROM @str;
+  EXECUTE stmt;
+
+  SET @str =
+    CONCAT("INSERT INTO ", tbl_name, " VALUES('row1'),('row2'),('row3')" );
+  SELECT @str;
+  PREPARE stmt FROM @str;
+  EXECUTE stmt;
+
+  SET @str =
+    CONCAT("CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM ", tbl_name);
+  SELECT @str;
+  PREPARE stmt FROM @str;
+  EXECUTE stmt;
+
+  SELECT * FROM bug13095_v1;
+
+  SET @str =
+    "DROP VIEW bug13095_v1";
+  SELECT @str;
+  PREPARE stmt FROM @str;
+  EXECUTE stmt;
+END|
+
+delimiter ;|
+
+CALL bug13095('bug13095_t1');
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug13095;
+DROP VIEW IF EXISTS bug13095_v1;
+DROP TABLE IF EXISTS bug13095_t1;
+--enable_warnings
+
+delimiter |;
+
+#
+# BUG#14210: "Simple query with > operator on large table gives server
+# crash"
+# Check that cursors work in case when HEAP tables are converted to
+# MyISAM
+#
+--disable_warnings
+drop procedure if exists bug14210|
+--enable_warnings
+set @@session.max_heap_table_size=16384|
+select @@session.max_heap_table_size|
+# To trigger the memory corruption the original table must be InnoDB.
+# No harm if it's not, so don't warn if the suite is run with --skip-innodb
+--disable_warnings
+create table t3 (a char(255)) engine=InnoDB|
+--enable_warnings
+create procedure bug14210_fill_table()
+begin
+  declare table_size, max_table_size int default 0;
+  select @@session.max_heap_table_size into max_table_size;
+  delete from t3;
+  insert into t3 (a) values (repeat('a', 255));
+  repeat
+    insert into t3 select a from t3;
+    select count(*)*255 from t3 into table_size;
+  until table_size > max_table_size*2 end repeat;
+end|
+call bug14210_fill_table()|
+drop procedure bug14210_fill_table|
+create table t4 like t3|
+
+create procedure bug14210()
+begin
+  declare a char(255);
+  declare done int default 0;
+  declare c cursor for select * from t3;
+  declare continue handler for sqlstate '02000' set done = 1;
+  open c;
+  repeat
+    fetch c into a;
+    if not done then
+       insert into t4 values (upper(a));
+    end if;
+  until done end repeat;
+  close c;
+end|
+call bug14210()|
+select count(*) from t4|
+
+drop table t3, t4|
+drop procedure bug14210|
+set @@session.max_heap_table_size=default|
+
+
+#
+# BUG#1473: Dumping of stored functions seems to cause corruption in
+#           the function body
+#
+--disable_warnings
+drop function if exists bug14723|
+drop procedure if exists bug14723|
+--enable_warnings
+
+delimiter ;;|
+/*!50003 create function bug14723()
+ returns bigint(20)
+main_loop: begin
+  return 42;
+end */;;
+show create function bug14723;;
+select bug14723();;
+
+/*!50003 create procedure bug14723()
+main_loop: begin
+  select 42;
+end */;;
+show create procedure bug14723;;
+call bug14723();;
+
+delimiter |;;
+
+drop function bug14723|
+drop procedure bug14723|
+
+#
+# Bug#14845 "mysql_stmt_fetch returns MYSQL_NO_DATA when COUNT(*) is 0"
+# Check that when fetching from a cursor, COUNT(*) works properly.
+#
+create procedure bug14845()
+begin
+  declare a char(255);
+  declare done int default 0;
+  declare c cursor for select count(*) from t1 where 1 = 0;
+  declare continue handler for sqlstate '02000' set done = 1;
+  open c;
+  repeat
+    fetch c into a;
+    if not done then
+      select a;
+    end if;
+  until done end repeat;
+  close c;
+end|
+call bug14845()|
+drop procedure bug14845|
+
+#
+# BUG#13549 "Server crash with nested stored procedures".
+# Server should not crash when during execution of stored procedure
+# we have to parse trigger/function definition and this new trigger/
+# function has more local variables declared than invoking stored
+# procedure and last of these variables is used in argument of NOT
+# operator.
+#
+--disable_warnings
+drop procedure if exists bug13549_1|
+drop procedure if exists bug13549_2|
+--enable_warnings
+CREATE PROCEDURE `bug13549_2`()
+begin
+  call bug13549_1();
+end|
+CREATE PROCEDURE `bug13549_1`()
+begin
+  declare done int default 0;
+  set done= not done;
+end|
+CALL bug13549_2()|
+drop procedure bug13549_2|
+drop procedure bug13549_1|
+
+#
+# BUG#10100: function (and stored procedure?) recursivity problem
+#
+--disable_warnings
+drop function if exists bug10100f|
+drop procedure if exists bug10100p|
+drop procedure if exists bug10100t|
+drop procedure if exists bug10100pt|
+drop procedure if exists bug10100pv|
+drop procedure if exists bug10100pd|
+drop procedure if exists bug10100pc|
+--enable_warnings
+# routines with simple recursion
+create function bug10100f(prm int) returns int
+begin
+  if prm > 1 then
+    return prm * bug10100f(prm - 1);
+  end if;
+  return 1;
+end|
+create procedure bug10100p(prm int, inout res int)
+begin
+  set res = res * prm;
+  if prm > 1 then
+    call bug10100p(prm - 1, res);  
+  end if;
+end|
+create procedure bug10100t(prm int)
+begin
+  declare res int;
+  set res = 1;
+  call bug10100p(prm, res);
+  select res;
+end|
+
+# a procedure which use tables and recursion
+create table t3 (a int)|
+insert into t3 values (0)|
+create view v1 as select a from t3;
+create procedure bug10100pt(level int, lim int)
+begin
+  if level < lim then
+    update t3 set a=level;
+    FLUSH TABLES;
+    call bug10100pt(level+1, lim);
+  else
+    select * from t3;
+  end if;
+end|
+# view & recursion
+create procedure bug10100pv(level int, lim int)
+begin
+  if level < lim then
+    update v1 set a=level;
+    FLUSH TABLES;
+    call bug10100pv(level+1, lim);
+  else
+    select * from v1;
+  end if;
+end|
+# dynamic sql & recursion
+prepare stmt2 from "select * from t3;";
+create procedure bug10100pd(level int, lim int)
+begin
+  if level < lim then
+    select level;
+    prepare stmt1 from "update t3 set a=a+2";
+    execute stmt1;
+    FLUSH TABLES;
+    execute stmt1;
+    FLUSH TABLES;
+    execute stmt1;
+    FLUSH TABLES;
+    deallocate prepare stmt1;
+    execute stmt2;
+    select * from t3;
+    call bug10100pd(level+1, lim);
+  else
+    execute stmt2;
+  end if;
+end|
+# cursor & recursion
+create procedure bug10100pc(level int, lim int)
+begin
+  declare lv int;
+  declare c cursor for select a from t3;
+  open c;
+  if level < lim then
+    select level;
+    fetch c into lv;
+    select lv;
+    update t3 set a=level+lv;
+    FLUSH TABLES;
+    call bug10100pc(level+1, lim);
+  else
+    select * from t3;
+  end if;
+  close c;
+end|
+
+set @@max_sp_recursion_depth=4|
+select @@max_sp_recursion_depth|
+-- error ER_SP_NO_RECURSION
+select bug10100f(3)|
+-- error ER_SP_NO_RECURSION
+select bug10100f(6)|
+call bug10100t(5)|
+call bug10100pt(1,5)|
+call bug10100pv(1,5)|
+update t3 set a=1|
+call bug10100pd(1,5)|
+select * from t3|
+update t3 set a=1|
+call bug10100pc(1,5)|
+select * from t3|
+set @@max_sp_recursion_depth=0|
+select @@max_sp_recursion_depth|
+-- error ER_SP_NO_RECURSION
+select bug10100f(5)|
+-- error ER_SP_RECURSION_LIMIT
+call bug10100t(5)|
+
+#end of the stack checking
+set @@max_sp_recursion_depth=255|
+set @var=1|
+#disable log because error about stack overrun contains numbers which
+#depend on a system
+-- disable_result_log
+-- error ER_STACK_OVERRUN_NEED_MORE
+call bug10100p(255, @var)|
+-- error ER_STACK_OVERRUN_NEED_MORE
+call bug10100pt(1,255)|
+-- error ER_STACK_OVERRUN_NEED_MORE
+call bug10100pv(1,255)|
+-- error ER_STACK_OVERRUN_NEED_MORE
+call bug10100pd(1,255)|
+-- error ER_STACK_OVERRUN_NEED_MORE
+call bug10100pc(1,255)|
+-- enable_result_log
+set @@max_sp_recursion_depth=0|
+
+deallocate prepare stmt2|
+
+drop function bug10100f|
+drop procedure bug10100p|
+drop procedure bug10100t|
+drop procedure bug10100pt|
+drop procedure bug10100pv|
+drop procedure bug10100pd|
+drop procedure bug10100pc|
+drop view v1|
+
+#
+# BUG#13729: Stored procedures: packet error after exception handled
+#
+--disable_warnings
+drop procedure if exists bug13729|
+drop table if exists t3|
+--enable_warnings
+
+create table t3 (s1 int, primary key (s1))|
+
+insert into t3 values (1),(2)|
+
+create procedure bug13729()
+begin
+  declare continue handler for sqlexception select 55;
+
+  update t3 set s1 = 1;
+end|
+
+call bug13729()|
+# Used to cause Packets out of order
+select * from t3|
+
+drop procedure bug13729|
+drop table t3|
+
+#
+# BUG#14643: Stored Procedure: Continuing after failed var. initialization
+#            crashes server.
+#
+--disable_warnings
+drop procedure if exists bug14643_1|
+drop procedure if exists bug14643_2|
+--enable_warnings
+
+create procedure bug14643_1()
+begin
+  declare continue handler for sqlexception select 'boo' as 'Handler';
+
+  begin
+    declare v int default undefined_var;
+
+    if v = 1 then
+      select 1;
+    else
+      select v, isnull(v);
+    end if;
+  end;
+end|
+
+create procedure bug14643_2()
+begin
+  declare continue handler for sqlexception select 'boo' as 'Handler';
+
+  case undefined_var
+  when 1 then
+    select 1;
+  else
+    select 2;
+  end case;
+
+  select undefined_var;
+end|
+
+call bug14643_1()|
+call bug14643_2()|
+
+drop procedure bug14643_1|
+drop procedure bug14643_2|
+
+#
+# BUG#14304: auto_increment field incorrect set in SP
+#
+--disable_warnings
+drop procedure if exists bug14304|
+drop table if exists t3, t4|
+--enable_warnings
+
+create table t3(a int primary key auto_increment)|
+create table t4(a int primary key auto_increment)|
+
+create procedure bug14304()
+begin
+  insert into t3 set a=null;
+  insert into t4 set a=null;
+  insert into t4 set a=null;
+  insert into t4 set a=null;
+  insert into t4 set a=null;
+  insert into t4 set a=null;
+  insert into t4 select null as a;
+  
+  insert into t3 set a=null;
+  insert into t3 set a=null;
+  
+  select * from t3;
+end|
+
+call bug14304()|
+
+drop procedure bug14304|
+drop table t3, t4|
+
+#
+# BUG#14376: MySQL crash on scoped variable (re)initialization
+#
+--disable_warnings
+drop procedure if exists bug14376|
+--enable_warnings
+
+create procedure bug14376()
+begin
+  declare x int default x;
+end|
+
+# Not the error we want, but that's what we got for now...
+--error ER_BAD_FIELD_ERROR
+call bug14376()|
+drop procedure bug14376|
+
+create procedure bug14376()
+begin
+  declare x int default 42;
+
+  begin
+    declare x int default x;
+
+    select x;
+  end;
+end|
+
+call bug14376()|
+
+drop procedure bug14376|
+
+create procedure bug14376(x int)
+begin
+  declare x int default x;
+
+  select x;
+end|
+
+call bug14376(4711)|
+
+drop procedure bug14376|
+
+#
+# Bug#5967 "Stored procedure declared variable used instead of column"
+# The bug should be fixed later.
+# Test precedence of names of parameters, variable declarations, 
+# variable declarations in nested compound statements, table columns,
+# table columns in cursor declarations.
+# According to the standard, table columns take precedence over
+# variable declarations. In MySQL 5.0 it's vice versa.
+#
+
+--disable_warnings
+drop procedure if exists bug5967|
+drop table if exists t3|
+--enable_warnings
+create table t3 (a varchar(255))|
+insert into t3 (a) values ("a - table column")|
+create procedure bug5967(a varchar(255))
+begin
+  declare i varchar(255);
+  declare c cursor for select a from t3;
+  select a;
+  select a from t3 into i;
+  select i as 'Parameter takes precedence over table column';                     open c;
+  fetch c into i;
+  close c;
+  select i as 'Parameter takes precedence over table column in cursors';
+  begin
+    declare a varchar(255) default 'a - local variable';
+    declare c1 cursor for select a from t3;
+    select a as 'A local variable takes precedence over parameter';
+    open c1;
+    fetch c1 into i;
+    close c1;
+    select i as 'A local variable takes precedence over parameter in cursors';
+    begin
+      declare a varchar(255) default 'a - local variable in a nested compound statement';
+      declare c2 cursor for select a from t3;
+      select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement';
+      select a from t3 into i;
+      select i as  'A local variable in a nested compound statement takes precedence over table column';
+      open c2;
+      fetch c2 into i;
+      close c2;
+      select i as  'A local variable in a nested compound statement takes precedence over table column in cursors';
+    end;
+  end;
+end|
+call bug5967("a - stored procedure parameter")|
+drop procedure bug5967|
+
+#
+# Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server"
+#
+--disable_warnings
+drop procedure if exists bug13012|
+--enable_warnings
+create procedure bug13012()
+BEGIN
+  REPAIR TABLE t1;
+  BACKUP TABLE t1 to '../tmp';
+  DROP TABLE t1;
+  RESTORE TABLE t1 FROM '../tmp';
+END|
+call bug13012()|
+drop procedure bug13012|
+create view v1 as select * from t1|
+create procedure bug13012()
+BEGIN
+  REPAIR TABLE t1,t2,t3,v1;
+  OPTIMIZE TABLE t1,t2,t3,v1;
+  ANALYZE TABLE t1,t2,t3,v1;
+END|
+call bug13012()|
+call bug13012()|
+call bug13012()|
+drop procedure bug13012|
+drop view v1;
+select * from t1|
+
+#
+# A test case for Bug#15392 "Server crashes during prepared statement
+# execute": make sure that stored procedure check for error conditions
+# properly and do not continue execution if an error has been set. 
+#
+# It's necessary to use several DBs because in the original code
+# the successful return of mysql_change_db overrode the error from
+# execution.
+drop schema if exists mysqltest1|
+drop schema if exists mysqltest2|
+drop schema if exists mysqltest3|
+create schema mysqltest1|
+create schema mysqltest2|
+create schema mysqltest3|
+use mysqltest3|
+
+create procedure mysqltest1.p1 (out prequestid varchar(100))
+begin
+  call mysqltest2.p2('call mysqltest3.p3(1, 2)');
+end|
+
+create procedure mysqltest2.p2(in psql text)
+begin
+  declare lsql text;
+  set @lsql= psql;
+  prepare lstatement from @lsql;
+  execute lstatement;
+  deallocate prepare lstatement;
+end|
+
+create procedure mysqltest3.p3(in p1 int)
+begin
+  select p1;
+end|
+
+--error ER_SP_WRONG_NO_OF_ARGS
+call mysqltest1.p1(@rs)|
+--error ER_SP_WRONG_NO_OF_ARGS
+call mysqltest1.p1(@rs)|
+--error ER_SP_WRONG_NO_OF_ARGS
+call mysqltest1.p1(@rs)|
+drop schema if exists mysqltest1|
+drop schema if exists mysqltest2|
+drop schema if exists mysqltest3|
+use test|
+
+#
+# Bug#15441 "Running SP causes Server to Crash": check that an SP variable
+# can not be used in VALUES() function.
+#
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug15441|
+--enable_warnings
+create table t3 (id int not null primary key, county varchar(25))|
+insert into t3 (id, county) values (1, 'York')|
+
+# First check that a stored procedure that refers to a parameter in VALUES()
+# function won't parse.
+
+create procedure bug15441(c varchar(25))
+begin
+  update t3 set id=2, county=values(c);
+end|
+--error ER_BAD_FIELD_ERROR
+call bug15441('county')|
+drop procedure bug15441|
+
+# Now check the case when there is an ambiguity between column names
+# and stored procedure parameters: the parser shall resolve the argument
+# of VALUES() function to the column name.
+
+# It's hard to deduce what county refers to in every case (INSERT statement):
+# 1st county refers to the column
+# 2nd county refers to the procedure parameter
+# 3d and 4th county refers to the column, again, but
+# for 4th county it has the value of SP parameter
+
+# In UPDATE statement, just check that values() function returns NULL for
+# non- INSERT...UPDATE statements, as stated in the manual.
+
+create procedure bug15441(county varchar(25))
+begin
+  declare c varchar(25) default "hello";
+
+  insert into t3 (id, county) values (1, county)
+  on duplicate key update county= values(county);
+  select * from t3;
+
+  update t3 set id=2, county=values(id);
+  select * from t3;
+end|
+call bug15441('Yale')|
+drop table t3|
+drop procedure bug15441|
+
+#
+# BUG#14498: Stored procedures: hang if undefined variable and exception
+#
+--disable_warnings
+drop procedure if exists bug14498_1|
+drop procedure if exists bug14498_2|
+drop procedure if exists bug14498_3|
+drop procedure if exists bug14498_4|
+drop procedure if exists bug14498_5|
+--enable_warnings
+
+create procedure bug14498_1()
+begin
+  declare continue handler for sqlexception select 'error' as 'Handler';
+
+  if v then
+    select 'yes' as 'v';
+  else
+    select 'no' as 'v';
+  end if;
+  select 'done' as 'End';
+end|
+
+create procedure bug14498_2()
+begin
+  declare continue handler for sqlexception select 'error' as 'Handler';
+
+  while v do
+    select 'yes' as 'v';
+  end while;
+  select 'done' as 'End';
+end|
+
+create procedure bug14498_3()
+begin
+  declare continue handler for sqlexception select 'error' as 'Handler';
+
+  repeat
+    select 'maybe' as 'v';
+  until v end repeat;
+  select 'done' as 'End';
+end|
+
+create procedure bug14498_4()
+begin
+  declare continue handler for sqlexception select 'error' as 'Handler';
+
+  case v
+  when 1 then
+    select '1' as 'v';
+  when 2 then
+    select '2' as 'v';
+  else
+    select '?' as 'v';
+  end case;
+  select 'done' as 'End';
+end|
+
+create procedure bug14498_5()
+begin
+  declare continue handler for sqlexception select 'error' as 'Handler';
+
+  case
+  when v = 1 then
+    select '1' as 'v';
+  when v = 2 then
+    select '2' as 'v';
+  else
+    select '?' as 'v';
+  end case;
+  select 'done' as 'End';
+end|
+
+call bug14498_1()|
+call bug14498_2()|
+call bug14498_3()|
+call bug14498_4()|
+call bug14498_5()|
+
+drop procedure bug14498_1|
+drop procedure bug14498_2|
+drop procedure bug14498_3|
+drop procedure bug14498_4|
+drop procedure bug14498_5|
+
+#
+# BUG#15231: Stored procedure bug with not found condition handler
+#
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug15231_1|
+drop procedure if exists bug15231_2|
+drop procedure if exists bug15231_3|
+drop procedure if exists bug15231_4|
+--enable_warnings
+
+create table t3 (id int not null)|
+  
+create procedure bug15231_1()
+begin
+  declare xid integer;
+  declare xdone integer default 0;
+  declare continue handler for not found set xdone = 1;
+
+  set xid=null;
+  call bug15231_2(xid);
+  select xid, xdone;
+end|
+
+create procedure bug15231_2(inout ioid integer)
+begin
+  select "Before NOT FOUND condition is triggered" as '1';
+  select id into ioid from t3 where id=ioid;
+  select "After NOT FOUND condtition is triggered" as '2';
+
+  if ioid is null then
+    set ioid=1;
+  end if;
+end|
+
+create procedure bug15231_3()
+begin
+  declare exit handler for sqlwarning
+    select 'Caught it (wrong)' as 'Result';
+
+  call bug15231_4();
+end|
+
+create procedure bug15231_4()
+begin
+  declare x decimal(2,1);
+
+  set x = 'zap';
+  select 'Missed it (correct)' as 'Result';
+end|
+
+call bug15231_1()|
+call bug15231_3()|
+
+drop table if exists t3|
+drop procedure if exists bug15231_1|
+drop procedure if exists bug15231_2|
+drop procedure if exists bug15231_3|
+drop procedure if exists bug15231_4|
+
+
+#
+# BUG#15011: error handler in nested block not activated
+#
+--disable_warnings
+drop procedure if exists bug15011|
+--enable_warnings
+
+create table t3 (c1 int primary key)|
+
+insert into t3 values (1)|
+
+create procedure bug15011()
+  deterministic
+begin
+  declare continue handler for 1062
+    select 'Outer' as 'Handler';
+
+  begin
+    declare continue handler for 1062
+      select 'Inner' as 'Handler';
+
+    insert into t3 values (1);
+  end;
+end|
+
+call bug15011()|
+
+drop procedure bug15011|
+drop table t3|
+
+
+#
+# BUG#NNNN: New bug synopsis
+#
+#--disable_warnings
+#drop procedure if exists bugNNNN|
+#--enable_warnings
+#create procedure bugNNNN...
+
+# Add bugs above this line. Use existing tables t1 and t2 when
+# practical, or create table t3, t4 etc temporarily (and drop them).
+delimiter ;|
+drop table t1,t2;
diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test
new file mode 100644
index 00000000000..3854297ec0c
--- /dev/null
+++ b/mysql-test/t/sp_notembedded.test
@@ -0,0 +1,262 @@
+# Can't test with embedded server
+-- source include/not_embedded.inc
+
+delimiter |;
+
+#
+# BUG#4902: Stored procedure with SHOW WARNINGS leads to packet error
+#
+# Added tests for show grants command
+--disable_warnings
+drop procedure if exists bug4902|
+--enable_warnings
+create procedure bug4902()
+begin
+  show grants for 'root'@'localhost';
+end|
+--disable_parsing
+show binlog events;
+show storage engines;
+show master status;
+show slave hosts;
+show slave status;
+--enable_parsing
+
+call bug4902()|
+call bug4902()|
+
+drop procedure bug4902|
+
+# We need separate SP for SHOW PROCESSLIST  since we want use replace_column
+--disable_warnings
+drop procedure if exists bug4902_2|
+--enable_warnings
+create procedure bug4902_2()
+begin
+  show processlist;
+end|
+--replace_column 1 # 6 # 3 localhost
+call bug4902_2()|
+--replace_column 1 # 6 # 3 localhost
+call bug4902_2()|
+drop procedure bug4902_2|
+
+
+#
+# BUG#5278: Stored procedure packets out of order if SET PASSWORD.
+#
+--disable_warnings
+drop function if exists bug5278|
+--enable_warnings
+create function bug5278 () returns char
+begin
+  SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
+  return 'okay';
+end|
+
+--error 1133
+select bug5278()|
+--error 1133
+select bug5278()|
+drop function bug5278|
+
+
+--disable_warnings
+drop table if exists t1|
+--enable_warnings
+create table t1 (
+	id   char(16) not null default '',
+        data int not null
+)|
+#
+# BUG#3583: query cache doesn't work for stored procedures
+#
+--disable_warnings
+drop procedure if exists bug3583|
+--enable_warnings
+--disable_warnings
+drop procedure if exists bug3583|
+--enable_warnings
+create procedure bug3583()
+begin
+  declare c int;
+
+  select * from t1;
+  select count(*) into c from t1;
+  select c;
+end|
+
+insert into t1 values ("x", 3), ("y", 5)|
+set @x = @@query_cache_size|
+set global query_cache_size = 10*1024*1024|
+
+flush status|
+flush query cache|
+show status like 'Qcache_hits'|
+call bug3583()|
+show status like 'Qcache_hits'|
+call bug3583()|
+call bug3583()|
+show status like 'Qcache_hits'|
+
+set global query_cache_size = @x|
+flush status|
+flush query cache|
+delete from t1|
+drop procedure bug3583|
+drop table t1;
+
+#
+# BUG#6807: Stored procedure crash if CREATE PROCEDURE ... KILL QUERY
+#
+--disable_warnings
+drop procedure if exists bug6807|
+--enable_warnings
+create procedure bug6807()
+begin
+  declare id int;
+
+  set id = connection_id();
+  kill query id;
+  select 'Not reached';
+end|
+
+--error 1317
+call bug6807()|
+--error 1317
+call bug6807()|
+
+drop procedure bug6807|
+
+
+#
+# BUG#10100: function (and stored procedure?) recursivity problem
+#
+--disable_warnings
+drop function if exists bug10100f|
+drop procedure if exists bug10100p|
+drop procedure if exists bug10100t|
+drop procedure if exists bug10100pt|
+drop procedure if exists bug10100pv|
+drop procedure if exists bug10100pd|
+drop procedure if exists bug10100pc|
+--enable_warnings
+# routines with simple recursion
+create function bug10100f(prm int) returns int
+begin
+  if prm > 1 then
+    return prm * bug10100f(prm - 1);
+  end if;
+  return 1;
+end|
+create procedure bug10100p(prm int, inout res int)
+begin
+  set res = res * prm;
+  if prm > 1 then
+    call bug10100p(prm - 1, res);
+  end if;
+end|
+create procedure bug10100t(prm int)
+begin
+  declare res int;
+  set res = 1;
+  call bug10100p(prm, res);
+  select res;
+end|
+
+# a procedure which use tables and recursion
+create table t3 (a int)|
+insert into t3 values (0)|
+create view v1 as select a from t3;
+create procedure bug10100pt(level int, lim int)
+begin
+  if level < lim then
+    update t3 set a=level;
+    FLUSH TABLES;
+    call bug10100pt(level+1, lim);
+  else
+    select * from t3;
+  end if;
+end|
+# view & recursion
+create procedure bug10100pv(level int, lim int)
+begin
+  if level < lim then
+    update v1 set a=level;
+    FLUSH TABLES;
+    call bug10100pv(level+1, lim);
+  else
+    select * from v1;
+  end if;
+end|
+# dynamic sql & recursion
+prepare stmt2 from "select * from t3;";
+create procedure bug10100pd(level int, lim int)
+begin
+  if level < lim then
+    select level;
+    prepare stmt1 from "update t3 set a=a+2";
+    execute stmt1;
+    FLUSH TABLES;
+    execute stmt1;
+    FLUSH TABLES;
+    execute stmt1;
+    FLUSH TABLES;
+    deallocate prepare stmt1;
+    execute stmt2;
+    select * from t3;
+    call bug10100pd(level+1, lim);
+  else
+    execute stmt2;
+  end if;
+end|
+# cursor & recursion
+create procedure bug10100pc(level int, lim int)
+begin
+  declare lv int;
+  declare c cursor for select a from t3;
+  open c;
+  if level < lim then
+    select level;
+    fetch c into lv;
+    select lv;
+    update t3 set a=level+lv;
+    FLUSH TABLES;
+    call bug10100pc(level+1, lim);
+  else
+    select * from t3;
+  end if;
+  close c;
+end|
+
+#end of the stack checking
+set @@max_sp_recursion_depth=255|
+set @var=1|
+#disable log because error about stack overrun contains numbers which
+#depend on a system
+-- disable_result_log
+-- error ER_STACK_OVERRUN_NEED_MORE
+call bug10100p(255, @var)|
+-- error ER_STACK_OVERRUN_NEED_MORE
+call bug10100pt(1,255)|
+-- error ER_STACK_OVERRUN_NEED_MORE
+call bug10100pv(1,255)|
+-- error ER_STACK_OVERRUN_NEED_MORE
+call bug10100pd(1,255)|
+-- error ER_STACK_OVERRUN_NEED_MORE
+call bug10100pc(1,255)|
+-- enable_result_log
+set @@max_sp_recursion_depth=0|
+
+deallocate prepare stmt2|
+
+drop function bug10100f|
+drop procedure bug10100p|
+drop procedure bug10100t|
+drop procedure bug10100pt|
+drop procedure bug10100pv|
+drop procedure bug10100pd|
+drop procedure bug10100pc|
+drop view v1|
+
+delimiter ;|
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 9e09b215951..733403f482e 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1963,12 +1963,6 @@ select * from (select max(fld) from t1) as foo;
 drop table t1;
 
 #
-# BUG #10308: purge log with subselect
-#
-
-purge master logs before (select adddate(current_timestamp(), interval -4 day));
-
-#
 # Test for bug #11762: subquery with an aggregate function in HAVING
 #
 
diff --git a/mysql-test/t/subselect_notembedded.test b/mysql-test/t/subselect_notembedded.test
new file mode 100644
index 00000000000..c5b23f6dac8
--- /dev/null
+++ b/mysql-test/t/subselect_notembedded.test
@@ -0,0 +1,8 @@
+-- source include/not_embedded.inc
+
+#
+# BUG #10308: purge log with subselect
+#
+
+purge master logs before (select adddate(current_timestamp(), interval -4 day));
+
diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test
index 6b3991c9c78..3920b305b78 100644
--- a/mysql-test/t/temp_table.test
+++ b/mysql-test/t/temp_table.test
@@ -1,3 +1,5 @@
+# mysqltest should be fixed
+-- source include/not_embedded.inc
 #
 # Test of temporary tables
 #
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index ed44bf3b7c0..77a903e2d3c 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2075,16 +2075,6 @@ order by users_names;
 drop view v1, v2;
 drop table t1, t2;
 
-#
-# DEFINER information check
-#
--- error ER_MALFORMED_DEFINER
-create definer=some_user@`` sql security invoker view v1 as select 1;
-create definer=some_user@localhost sql security invoker view v1 as select 1;
-show create view v1;
-drop view v1;
-
-#
 # Bug #6808 - Views: CREATE VIEW v ... FROM t AS v fails
 #
 
diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test
index 2490b8808da..87478a9d4b2 100644
--- a/mysql-test/t/view_grant.test
+++ b/mysql-test/t/view_grant.test
@@ -702,3 +702,14 @@ drop database mysqltest;
 disconnect user1;
 disconnect root;
 connection default;
+
+#
+# DEFINER information check
+#
+-- error ER_MALFORMED_DEFINER
+create definer=some_user@`` sql security invoker view v1 as select 1;
+create definer=some_user@localhost sql security invoker view v1 as select 1;
+show create view v1;
+drop view v1;
+
+
diff --git a/mysql-test/t/wait_timeout.test b/mysql-test/t/wait_timeout.test
index 1c9efa7c07d..1fef3deea3c 100644
--- a/mysql-test/t/wait_timeout.test
+++ b/mysql-test/t/wait_timeout.test
@@ -1,3 +1,6 @@
+# This tests not performed with embedded server
+-- source include/not_embedded.inc
+
 #
 # Bug #8731: wait_timeout does not work on Mac OS X
 #