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

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


include/mysql.h:
  Fix to embedded server to be able to run tests on it
libmysql/libmysql.c:
  Fix to embedded server to be able to run tests on it
libmysqld/emb_qcache.cc:
  Fix to embedded server to be able to run tests on it
libmysqld/embedded_priv.h:
  Fix to embedded server to be able to run tests on it
libmysqld/lib_sql.cc:
  Fix to embedded server to be able to run tests on it
libmysqld/libmysqld.c:
  Fix to embedded server to be able to run tests on it
mysql-test/mysql-test-run.sh:
  Fix to embedded server to be able to run tests on it
mysql-test/r/binlog.result:
  Updated test for embedded server
mysql-test/r/ctype_cp932.result:
  Updated test for embedded server
mysql-test/r/innodb.result:
  Updated test for embedded server
mysql-test/r/mysqltest.result:
  Updated test for embedded server
mysql-test/r/query_cache.result:
  Updated test for embedded server
mysql-test/r/query_cache_notembedded.result:
  Updated test for embedded server
mysql-test/r/sp-error.result:
  Updated test for embedded server
mysql-test/r/sp.result:
  Updated test for embedded server
mysql-test/r/subselect.result:
  Updated test for embedded server
mysql-test/r/view.result:
  Updated test for embedded server
mysql-test/r/view_grant.result:
  Updated test for embedded server
mysql-test/t/backup.test:
  Updated test for embedded server
mysql-test/t/binlog.test:
  Updated test for embedded server
mysql-test/t/blackhole.test:
  Updated test for embedded server
mysql-test/t/compress.test:
  Updated test for embedded server
mysql-test/t/ctype_cp932.test:
  Updated test for embedded server
mysql-test/t/delayed.test:
  Updated test for embedded server
mysql-test/t/handler.test:
  Updated test for embedded server
mysql-test/t/innodb.test:
  Updated test for embedded server
mysql-test/t/mysql.test:
  Updated test for embedded server
mysql-test/t/mysql_client_test.test:
  Updated test for embedded server
mysql-test/t/mysqltest.test:
  Updated test for embedded server
mysql-test/t/query_cache.test:
  Updated test for embedded server
mysql-test/t/query_cache_notembedded.test:
  Updated test for embedded server
mysql-test/t/read_only.test:
  Updated test for embedded server
mysql-test/t/skip_grants.test:
  Updated test for embedded server
mysql-test/t/sp-destruct.test:
  Updated test for embedded server
mysql-test/t/sp-error.test:
  Updated test for embedded server
mysql-test/t/sp-threads.test:
  Updated test for embedded server
mysql-test/t/sp.test:
  Updated test for embedded server
mysql-test/t/subselect.test:
  Updated test for embedded server
mysql-test/t/temp_table.test:
  Updated test for embedded server
mysql-test/t/view.test:
  Updated test for embedded server
mysql-test/t/view_grant.test:
  Updated test for embedded server
mysql-test/t/wait_timeout.test:
  Updated test for embedded server
mysys/mf_dirname.c:
  Review fix: Don't access data outside of array
mysys/my_bitmap.c:
  Remove compiler warnings
scripts/mysql_fix_privilege_tables.sql:
  Add flush privileges to .sql script so that one doesn't have to reboot mysqld when one runs the mysql_fix_privilege_script
sql-common/client.c:
  Updated test for embedded server
sql/item.cc:
  Remove DBUG_PRINT statement that can cause crashes when running with --debug
sql/mysqld.cc:
  Fix to embedded server to be able to run tests on it
sql/protocol.cc:
  Fix to embedded server to be able to run tests on it
  (Trivial reconstruction of code)
sql/protocol.h:
  Fix to embedded server to be able to run tests on it
sql/sql_base.cc:
  Better comment
sql/sql_class.cc:
  Fix to embedded server to be able to run tests on it
sql/sql_class.h:
  Fix to embedded server to be able to run tests on it
sql/sql_cursor.cc:
  Fix to embedded server to be able to run tests on it
sql/sql_parse.cc:
  Fix to embedded server to be able to run tests on it
  Don't crash for disabled commands when using embedded server
sql/sql_prepare.cc:
  Fix to embedded server to be able to run tests on it
mysql-test/r/ctype_cp932_notembedded.result:
  New BitKeeper file ``mysql-test/r/ctype_cp932_notembedded.result''
mysql-test/r/innodb_notembedded.result:
  New BitKeeper file ``mysql-test/r/innodb_notembedded.result''
mysql-test/r/sp.result.orig:
  New BitKeeper file ``mysql-test/r/sp.result.orig''
mysql-test/r/sp_notembedded.result:
  New BitKeeper file ``mysql-test/r/sp_notembedded.result''
mysql-test/r/subselect_notembedded.result:
  New BitKeeper file ``mysql-test/r/subselect_notembedded.result''
mysql-test/t/ctype_cp932_notembedded.test:
  New BitKeeper file ``mysql-test/t/ctype_cp932_notembedded.test''
mysql-test/t/innodb_notembedded.test:
  New BitKeeper file ``mysql-test/t/innodb_notembedded.test''
mysql-test/t/sp.test.orig:
  New BitKeeper file ``mysql-test/t/sp.test.orig''
mysql-test/t/sp_notembedded.test:
  New BitKeeper file ``mysql-test/t/sp_notembedded.test''
mysql-test/t/subselect_notembedded.test:
  New BitKeeper file ``mysql-test/t/subselect_notembedded.test''

16 files changed, 5330 insertions, 187 deletions

diff --git a/mysql-test/r/binlog.result b/mysql-test/r/binlog.result
index 5d5b78abe29..25930c31735 100644
--- a/mysql-test/r/binlog.result
+++ b/mysql-test/r/binlog.result
@@ -17,7 +17,7 @@ master-bin.000001	#	Query	1	#	use `test`; insert t1 values (5)
 master-bin.000001	#	Query	1	#	use `test`; COMMIT
 master-bin.000001	#	Query	1	#	use `test`; BEGIN
 master-bin.000001	#	Query	1	#	use `test`; insert t2 values (5)
-master-bin.000001	#	Xid	1	#	COMMIT /* xid=11 */
+master-bin.000001	#	Xid	1	#	COMMIT /* xid=12 */
 drop table t1,t2;
 reset master;
 create table t1 (n int) engine=innodb;
@@ -128,7 +128,7 @@ master-bin.000001	#	Query	1	#	use `test`; insert into t1 values(4 + 4)
 master-bin.000001	#	Query	1	#	use `test`; insert into t1 values(3 + 4)
 master-bin.000001	#	Query	1	#	use `test`; insert into t1 values(2 + 4)
 master-bin.000001	#	Query	1	#	use `test`; insert into t1 values(1 + 4)
-master-bin.000001	#	Xid	1	#	COMMIT /* xid=18 */
+master-bin.000001	#	Xid	1	#	COMMIT /* xid=19 */
 master-bin.000001	#	Rotate	1	#	master-bin.000002;pos=4
 show binlog events in 'master-bin.000002' from 98;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
diff --git a/mysql-test/r/ctype_cp932.result b/mysql-test/r/ctype_cp932.result
index 61540cb8467..ed57b87c1ba 100755
--- a/mysql-test/r/ctype_cp932.result
+++ b/mysql-test/r/ctype_cp932.result
@@ -11315,20 +11315,6 @@ DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t3;
 DROP TABLE t4;
-RESET MASTER;
-CREATE TABLE t1(f1 blob);
-PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(?)';
-SET @var1= x'8300';
-EXECUTE stmt1 USING @var1;
-SHOW BINLOG EVENTS FROM 98;
-Log_name	Pos	Event_type	Server_id	End_log_pos	Info
-master-bin.000001	98	Query	1	185	use `test`; CREATE TABLE t1(f1 blob)
-master-bin.000001	185	User var	1	224	@`var1`=_binary 0x8300 COLLATE binary
-master-bin.000001	224	Query	1	317	use `test`; INSERT INTO t1 VALUES(@'var1')
-SELECT HEX(f1) FROM t1;
-HEX(f1)
-8300
-DROP table t1;
 SET collation_connection='cp932_japanese_ci';
 create table t1 select repeat('a',4000) a;
 delete from t1;
diff --git a/mysql-test/r/ctype_cp932_notembedded.result b/mysql-test/r/ctype_cp932_notembedded.result
new file mode 100644
index 00000000000..d04fce7738c
--- /dev/null
+++ b/mysql-test/r/ctype_cp932_notembedded.result
@@ -0,0 +1,17 @@
+drop table if exists t1;
+set names cp932;
+set character_set_database = cp932;
+RESET MASTER;
+CREATE TABLE t1(f1 blob);
+PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(?)';
+SET @var1= x'8300';
+EXECUTE stmt1 USING @var1;
+SHOW BINLOG EVENTS FROM 98;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	98	Query	1	185	use `test`; CREATE TABLE t1(f1 blob)
+master-bin.000001	185	User var	1	224	@`var1`=_binary 0x8300 COLLATE binary
+master-bin.000001	224	Query	1	317	use `test`; INSERT INTO t1 VALUES(@'var1')
+SELECT HEX(f1) FROM t1;
+HEX(f1)
+8300
+DROP table t1;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 026ad53193d..e8af68a6067 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -2673,25 +2673,6 @@ checksum table t1;
 Table	Checksum
 test.t1	2050879373
 drop table t1;
-create table t1 (col1 integer primary key, col2 integer) engine=innodb;
-insert t1 values (1,100);
-create function f1 () returns integer begin
-declare var1 int;
-select col2 into var1 from t1 where col1=1 for update;
-return var1;
-end|
-start transaction;
-select f1();
-f1()
-100
- update t1 set col2=0 where col1=1;
-select * from t1;
-col1	col2
-1	100
-rollback;
-rollback;
-drop table t1;
-drop function f1;
 create table t1 (
 a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2))
 ) character set utf8 engine = innodb;
diff --git a/mysql-test/r/innodb_notembedded.result b/mysql-test/r/innodb_notembedded.result
new file mode 100644
index 00000000000..9aac20e515d
--- /dev/null
+++ b/mysql-test/r/innodb_notembedded.result
@@ -0,0 +1,20 @@
+drop table if exists t1;
+create table t1 (col1 integer primary key, col2 integer) engine=innodb;
+insert t1 values (1,100);
+create function f1 () returns integer begin
+declare var1 int;
+select col2 into var1 from t1 where col1=1 for update;
+return var1;
+end|
+start transaction;
+select f1();
+f1()
+100
+ update t1 set col2=0 where col1=1;
+select * from t1;
+col1	col2
+1	100
+rollback;
+rollback;
+drop table t1;
+drop function f1;
diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result
index 1a8bc542c27..de07021f217 100644
--- a/mysql-test/r/mysqltest.result
+++ b/mysql-test/r/mysqltest.result
@@ -1,6 +1,6 @@
-select -1 as "before_use_test" ;
+select 0 as "before_use_test" ;
 before_use_test
--1
+0
 select otto from (select 1 as otto) as t1;
 otto
 1
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index 5a7c387a650..4bf4ebb910d 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -1104,56 +1104,20 @@ call f1();
 s1
 s1
 s1
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	3
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	3
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	0
 call f1();
 s1
 s1
 s1
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	3
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	3
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	3
 call f1();
 s1
 s1
 s1
 select sql_cache * from t1;
 s1
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	4
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	4
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	6
 insert into t1 values (1);
 select sql_cache * from t1;
 s1
 1
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	1
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	5
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	6
 call f1();
 s1
 1
@@ -1171,15 +1135,6 @@ s1
 select sql_cache * from t1;
 s1
 1
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	4
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	8
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	10
 flush query cache;
 reset query cache;
 flush status;
diff --git a/mysql-test/r/query_cache_notembedded.result b/mysql-test/r/query_cache_notembedded.result
index e773a63525b..77fa198eb80 100644
--- a/mysql-test/r/query_cache_notembedded.result
+++ b/mysql-test/r/query_cache_notembedded.result
@@ -94,4 +94,224 @@ a
 SELECT * FROM t1;
 a
 drop table t1;
+flush query cache;
+reset query cache;
+flush status;
+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;//
+call f1();
+s1
+s1
+s1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	3
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	3
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+call f1();
+s1
+s1
+s1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	3
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	3
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	3
+call f1();
+s1
+s1
+s1
+select sql_cache * from t1;
+s1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	4
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	4
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	6
+insert into t1 values (1);
+select sql_cache * from t1;
+s1
+1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	5
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	6
+call f1();
+s1
+1
+s1
+1
+s1
+1
+call f1();
+s1
+1
+s1
+1
+s1
+1
+select sql_cache * from t1;
+s1
+1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	4
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	8
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	10
+flush query cache;
+reset query cache;
+flush status;
+select sql_cache * from t1;
+s1
+1
+select sql_cache * from t1 where s1=1;
+s1
+1
+call f1();
+s1
+1
+s1
+1
+s1
+1
+call f2();
+s1
+1
+s1
+1
+call f3();
+s1
+1
+s1
+1
+call f4();
+s1
+1
+s1
+1
+s1
+1
+s1
+1
+s1
+1
+call f4();
+s1
+1
+s1
+1
+s1
+1
+s1
+1
+s1
+1
+call f3();
+s1
+1
+s1
+1
+call f2();
+s1
+1
+s1
+1
+select sql_cache * from t1 where s1=1;
+s1
+1
+insert into t1 values (2);
+call f1();
+s1
+1
+2
+s1
+1
+2
+s1
+1
+2
+select sql_cache * from t1 where s1=1;
+s1
+1
+select sql_cache * from t1;
+s1
+1
+2
+call f1();
+s1
+1
+2
+s1
+1
+2
+s1
+1
+2
+call f3();
+s1
+1
+2
+s1
+1
+call f3();
+s1
+1
+2
+s1
+1
+call f1();
+s1
+1
+2
+s1
+1
+2
+s1
+1
+2
+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/r/sp-error.result b/mysql-test/r/sp-error.result
index c0e02cbeb6f..67a6e55b29e 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -464,19 +464,6 @@ create table t5 (x int)|
 call bug3294()|
 ERROR 42S02: Unknown table 't5'
 drop procedure bug3294|
-drop procedure if exists bug6807|
-create procedure bug6807()
-begin
-declare id int;
-set id = connection_id();
-kill query id;
-select 'Not reached';
-end|
-call bug6807()|
-ERROR 70100: Query execution was interrupted
-call bug6807()|
-ERROR 70100: Query execution was interrupted
-drop procedure bug6807|
 drop procedure if exists bug8776_1|
 drop procedure if exists bug8776_2|
 drop procedure if exists bug8776_3|
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 663204681f2..a4c920f8e15 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -2463,7 +2463,6 @@ 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;
@@ -2490,8 +2489,6 @@ Level	Code	Message
 Field	Type	Null	Key	Default	Extra
 id	char(16)	NO			
 data	int(11)	NO			
-Grants for root@localhost
-GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 Database	Table	In_use	Name_locked
 Privilege	Context	Comment
@@ -2544,8 +2541,6 @@ Level	Code	Message
 Field	Type	Null	Key	Default	Extra
 id	char(16)	NO			
 data	int(11)	NO			
-Grants for root@localhost
-GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
 Database	Table	In_use	Name_locked
 Privilege	Context	Comment
@@ -2581,18 +2576,6 @@ Tables_in_test (foo)
 Variable_name	Value
 Level	Code	Message
 drop procedure bug4902|
-drop procedure if exists bug4902_2|
-create procedure bug4902_2()
-begin
-show processlist;
-end|
-call bug4902_2()|
-Id	User	Host	db	Command	Time	State	Info
-#	root	localhost	test	Query	#	NULL	show processlist
-call bug4902_2()|
-Id	User	Host	db	Command	Time	State	Info
-#	root	localhost	test	Query	#	NULL	show processlist
-drop procedure bug4902_2|
 drop procedure if exists bug4904|
 create procedure bug4904()
 begin
@@ -2735,52 +2718,6 @@ select @x|
 NULL
 delete from t1|
 drop procedure bug4941|
-drop procedure if exists bug3583|
-drop procedure if exists bug3583|
-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'|
-Variable_name	Value
-Qcache_hits	0
-call bug3583()|
-id	data
-x	3
-y	5
-c
-2
-show status like 'Qcache_hits'|
-Variable_name	Value
-Qcache_hits	0
-call bug3583()|
-id	data
-x	3
-y	5
-c
-2
-call bug3583()|
-id	data
-x	3
-y	5
-c
-2
-show status like 'Qcache_hits'|
-Variable_name	Value
-Qcache_hits	2
-set global query_cache_size = @x|
-flush status|
-flush query cache|
-delete from t1|
-drop procedure bug3583|
 drop procedure if exists bug4905|
 create table t3 (s1 int,primary key (s1))|
 drop procedure if exists bug4905|
@@ -2996,17 +2933,6 @@ select id, bug5240() from t1|
 id	bug5240()
 answer	42
 drop function bug5240|
-drop function if exists bug5278|
-create function bug5278 () returns char
-begin
-SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
-return 'okay';
-end|
-select bug5278()|
-ERROR 42000: Can't find any matching row in the user table
-select bug5278()|
-ERROR 42000: Can't find any matching row in the user table
-drop function bug5278|
 drop procedure if exists p1|
 create table t3(id int)|
 insert into t3 values(1)|
@@ -4352,14 +4278,6 @@ select bug10100f(5)|
 ERROR HY000: Recursive stored functions and triggers are not allowed.
 call bug10100t(5)|
 ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug10100p
-set @@max_sp_recursion_depth=255|
-set @var=1|
-call bug10100p(255, @var)|
-call bug10100pt(1,255)|
-call bug10100pv(1,255)|
-call bug10100pd(1,255)|
-call bug10100pc(1,255)|
-set @@max_sp_recursion_depth=0|
 deallocate prepare stmt2|
 drop function bug10100f|
 drop procedure bug10100p|
diff --git a/mysql-test/r/sp.result.orig b/mysql-test/r/sp.result.orig
new file mode 100644
index 00000000000..663204681f2
--- /dev/null
+++ b/mysql-test/r/sp.result.orig
@@ -0,0 +1,4853 @@
+use test;
+drop table if exists t1,t2,t3,t4;
+create table t1 (
+id   char(16) not null default '',
+data int not null
+);
+create table t2 (
+s   char(16),
+i   int,
+d   double
+);
+drop procedure if exists foo42;
+create procedure foo42()
+insert into test.t1 values ("foo", 42);
+call foo42();
+select * from t1;
+id	data
+foo	42
+delete from t1;
+drop procedure foo42;
+drop procedure if exists bar;
+create procedure bar(x char(16), y int)
+insert into test.t1 values (x, y);
+call bar("bar", 666);
+select * from t1;
+id	data
+bar	666
+delete from t1;
+drop procedure if exists empty|
+create procedure empty()
+begin
+end|
+call empty()|
+drop procedure empty|
+drop procedure if exists scope|
+create procedure scope(a int, b float)
+begin
+declare b int;
+declare c float;
+begin
+declare c int;
+end;
+end|
+drop procedure scope|
+drop procedure if exists two|
+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|
+id	data
+one	3
+two	3
+delete from t1|
+drop procedure two|
+drop procedure if exists locset|
+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|
+id	data
+locset	21
+delete from t1|
+drop procedure locset|
+drop procedure if exists setcontext|
+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|
+id	data
+foo	1
+kaka	3
+delete from t1|
+drop procedure setcontext|
+create table t3 ( d date, i int, f double, s varchar(32) )|
+drop procedure if exists nullset|
+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|
+d	i	f	s
+NULL	NULL	NULL	NULL
+NULL	1	1	ld is null
+NULL	1	1	li is null
+NULL	NULL	NULL	li = 0
+NULL	1	1	lf is null
+NULL	NULL	NULL	lf = 0
+NULL	1	1	ls is null
+drop table t3|
+drop procedure nullset|
+drop procedure if exists mixset|
+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'|
+Variable_name	Value
+max_join_size	100
+select id,data,@z from t1|
+id	data	@z
+mixset	666	19
+delete from t1|
+drop procedure mixset|
+drop procedure if exists zip|
+create procedure zip(x char(16), y int)
+begin
+declare z int;
+call zap(y, z);
+call bar(x, z);
+end|
+drop procedure if exists zap|
+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|
+id	data
+zip	100
+delete from t1|
+drop procedure zip|
+drop procedure bar|
+call zap(7, @zap)|
+select @zap|
+@zap
+8
+drop procedure zap|
+drop procedure if exists c1|
+create procedure c1(x int)
+call c2("c", x)|
+drop procedure if exists c2|
+create procedure c2(s char(16), x int)
+call c3(x, s)|
+drop procedure if exists c3|
+create procedure c3(x int, s char(16))
+call c4("level", x, s)|
+drop procedure if exists c4|
+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|
+id	data
+levelc	42
+delete from t1|
+drop procedure c1|
+drop procedure c2|
+drop procedure c3|
+drop procedure c4|
+drop procedure if exists iotest|
+create procedure iotest(x1 char(16), x2 char(16), y int)
+begin
+call inc2(x2, y);
+insert into test.t1 values (x1, y);
+end|
+drop procedure if exists inc2|
+create procedure inc2(x char(16), y int)
+begin
+call inc(y);
+insert into test.t1 values (x, y);
+end|
+drop procedure if exists inc|
+create procedure inc(inout io int)
+set io = io + 1|
+call iotest("io1", "io2", 1)|
+select * from t1|
+id	data
+io2	2
+io1	1
+delete from t1|
+drop procedure iotest|
+drop procedure inc2|
+drop procedure if exists incr|
+create procedure incr(inout x int)
+call inc(x)|
+select @zap|
+@zap
+8
+call incr(@zap)|
+select @zap|
+@zap
+9
+drop procedure inc|
+drop procedure incr|
+drop procedure if exists cbv1|
+create procedure cbv1()
+begin
+declare y int default 3;
+call cbv2(y+1, y);
+insert into test.t1 values ("cbv1", y);
+end|
+drop procedure if exists cbv2|
+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|
+id	data
+cbv2	4
+cbv1	4711
+delete from t1|
+drop procedure cbv1|
+drop procedure cbv2|
+insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)|
+drop procedure if exists sub1|
+create procedure sub1(id char(16), x int)
+insert into test.t1 values (id, x)|
+drop procedure if exists sub2|
+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|
+drop procedure if exists sub3|
+create function sub3(i int) returns int
+return i+1|
+call sub1("sub1a", (select 7))|
+call sub1("sub1b", (select max(i) from t2))|
+call sub1("sub1c", (select i,d from t2 limit 1))|
+ERROR 21000: Operand should contain 1 column(s)
+call sub1("sub1d", (select 1 from (select 1) a))|
+call sub2("sub2")|
+select * from t1|
+id	data
+sub1a	7
+sub1b	3
+sub1d	1
+sub2	6
+select sub3((select max(i) from t2))|
+sub3((select max(i) from t2))
+4
+drop procedure sub1|
+drop procedure sub2|
+drop function sub3|
+delete from t1|
+delete from t2|
+drop procedure if exists a0|
+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|
+id	data
+a0	2
+a0	1
+a0	0
+delete from t1|
+drop procedure a0|
+drop procedure if exists a|
+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|
+id	data
+a	2
+a	1
+a	0
+delete from t1|
+drop procedure a|
+drop procedure if exists b|
+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|
+id	data
+bbb	3
+bbb	2
+bbb	1
+delete from t1|
+drop procedure b|
+drop procedure if exists b2|
+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|
+drop procedure b2|
+drop procedure if exists c|
+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|
+id	data
+c	3
+c	2
+c	1
+delete from t1|
+drop procedure c|
+drop procedure if exists d|
+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|
+id	data
+d	3
+delete from t1|
+drop procedure d|
+drop procedure if exists e|
+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|
+id	data
+e	3
+e	2
+e	1
+delete from t1|
+drop procedure e|
+drop procedure if exists f|
+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|
+id	data
+f	0
+f	1
+f	2
+delete from t1|
+drop procedure f|
+drop procedure if exists g|
+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|
+id	data
+g	0
+g	1
+g	2
+delete from t1|
+drop procedure g|
+drop procedure if exists h|
+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|
+id	data
+h0	0
+h1	1
+h?	17
+delete from t1|
+drop procedure h|
+drop procedure if exists i|
+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|
+id	data
+i	3
+delete from t1|
+drop procedure i|
+insert into t1 values ("foo", 3), ("bar", 19)|
+insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)|
+drop procedure if exists sel1|
+create procedure sel1()
+begin
+select * from t1;
+end|
+call sel1()|
+id	data
+foo	3
+bar	19
+drop procedure sel1|
+drop procedure if exists sel2|
+create procedure sel2()
+begin
+select * from t1;
+select * from t2;
+end|
+call sel2()|
+id	data
+foo	3
+bar	19
+s	i	d
+x	9	4.1
+y	-1	19.2
+z	3	2.2
+drop procedure sel2|
+delete from t1|
+delete from t2|
+drop procedure if exists into_test|
+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|
+id	data
+into	100
+into2	102
+delete from t1|
+drop procedure into_test|
+drop procedure if exists into_tes2|
+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|
+id	data	@z
+into	100	100
+into2	102	100
+delete from t1|
+drop procedure into_test2|
+drop procedure if exists into_test3|
+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)|
+call into_test3()|
+call into_test3()|
+select * from t2|
+s	i	d
+into3	19	0
+into3	19	0
+delete from t1|
+delete from t2|
+drop procedure into_test3|
+drop procedure if exists into_test4|
+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()|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select * from t3|
+s	d
+into4	NULL
+insert into t1 values ("i4", 77)|
+call into_test4()|
+select * from t3|
+s	d
+into4	NULL
+into4	77
+delete from t1|
+drop table t3|
+drop procedure into_test4|
+drop procedure if exists into_outfile|
+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|
+call into_outfile("ofile", 1)|
+delete from t1|
+drop procedure into_outfile|
+drop procedure if exists into_dumpfile|
+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|
+call into_dumpfile("dfile", 1)|
+delete from t1|
+drop procedure into_dumpfile|
+drop procedure if exists create_select|
+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|
+id	data	id	data
+cs	90	cs	90
+cs	90	cs2	92
+drop table t3|
+delete from t1|
+drop procedure create_select|
+drop function if exists e|
+create function e() returns double
+return 2.7182818284590452354|
+set @e = e()|
+select e(), @e|
+e()	@e
+2.718281828459	2.718281828459
+drop function if exists inc|
+create function inc(i int) returns int
+return i+1|
+select inc(1), inc(99), inc(-71)|
+inc(1)	inc(99)	inc(-71)
+2	100	-70
+drop function if exists mul|
+create function mul(x int, y int) returns int
+return x*y|
+select mul(1,1), mul(3,5), mul(4711, 666)|
+mul(1,1)	mul(3,5)	mul(4711, 666)
+1	15	3137526
+drop function if exists append|
+create function append(s1 char(8), s2 char(8)) returns char(16)
+return concat(s1, s2)|
+select append("foo", "bar")|
+append("foo", "bar")
+foobar
+drop function if exists fac|
+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)|
+fac(1)	fac(2)	fac(5)	fac(10)
+1	2	120	3628800
+drop function if exists fun|
+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)|
+fun(2.3, 3, 5)
+176.58213176229
+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))|
+select * from t2 where s = append("a", "b")|
+s	i	d
+ab	24	1324.36598821719
+select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)|
+s	i	d
+xxxyyy	12	2.71828182845905
+ab	24	1324.36598821719
+select * from t2 where d = e()|
+s	i	d
+xxxyyy	12	2.71828182845905
+select * from t2|
+s	i	d
+xxxyyy	12	2.71828182845905
+ab	24	1324.36598821719
+delete from t2|
+drop function e|
+drop function inc|
+drop function mul|
+drop function append|
+drop function fun|
+drop procedure if exists hndlr1|
+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|
+id	data
+hndlr1	42
+delete from t1|
+drop procedure hndlr1|
+drop procedure if exists hndlr2|
+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|
+id	data
+hndlr2	1
+delete from t1|
+drop procedure hndlr2|
+drop procedure if exists hndlr3|
+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|
+id	data
+hndlr3	13
+delete from t1|
+drop procedure hndlr3|
+create table t3 ( id   char(16), data int )|
+drop procedure if exists hndlr4|
+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|
+id	data
+z	NULL
+drop table t3|
+drop procedure hndlr4|
+drop procedure if exists cur1|
+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|
+id	data
+foo	40
+bar	15
+zap	663
+drop procedure cur1|
+create table t3 ( s char(16), i int )|
+drop procedure if exists cur2|
+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|
+s	i
+foo	40
+bar	3
+zap	663
+delete from t1|
+delete from t2|
+drop table t3|
+drop procedure cur2|
+drop procedure if exists chistics|
+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|
+Procedure	sql_mode	Create Procedure
+chistics		CREATE PROCEDURE `chistics`()
+    MODIFIES SQL DATA
+    COMMENT 'Characteristics procedure test'
+insert into t1 values ("chistics", 1)
+call chistics()|
+select * from t1|
+id	data
+chistics	1
+delete from t1|
+alter procedure chistics sql security invoker|
+show create procedure chistics|
+Procedure	sql_mode	Create Procedure
+chistics		CREATE PROCEDURE `chistics`()
+    MODIFIES SQL DATA
+    SQL SECURITY INVOKER
+    COMMENT 'Characteristics procedure test'
+insert into t1 values ("chistics", 1)
+drop procedure chistics|
+drop function if exists chistics|
+create function chistics() returns int
+language sql
+deterministic
+sql security invoker
+comment 'Characteristics procedure test'
+  return 42|
+show create function chistics|
+Function	sql_mode	Create Function
+chistics		CREATE FUNCTION `chistics`() RETURNS int(11)
+    DETERMINISTIC
+    SQL SECURITY INVOKER
+    COMMENT 'Characteristics procedure test'
+return 42
+select chistics()|
+chistics()
+42
+alter function chistics
+no sql
+comment 'Characteristics function test'|
+show create function chistics|
+Function	sql_mode	Create Function
+chistics		CREATE FUNCTION `chistics`() RETURNS int(11)
+    NO SQL
+    DETERMINISTIC
+    SQL SECURITY INVOKER
+    COMMENT 'Characteristics function test'
+return 42
+drop function chistics|
+insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)|
+set @@sql_mode = 'ANSI'|
+drop procedure if exists modes$
+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$
+set @@sql_mode = ''|
+set sql_select_limit = 1|
+call modes(@c1, @c2)|
+set sql_select_limit = default|
+select @c1, @c2|
+@c1	@c2
+12	3
+delete from t1|
+drop procedure modes|
+create database sp_db1|
+drop database sp_db1|
+create database sp_db2|
+use sp_db2|
+create table t3 ( s char(4), t int )|
+insert into t3 values ("abcd", 42), ("dcba", 666)|
+use test|
+drop database sp_db2|
+create database sp_db3|
+use sp_db3|
+drop procedure if exists dummy|
+create procedure dummy(out x int)
+set x = 42|
+use test|
+drop database sp_db3|
+select type,db,name from mysql.proc where db = 'sp_db3'|
+type	db	name
+drop procedure if exists rc|
+create procedure rc()
+begin
+delete from t1;
+insert into t1 values ("a", 1), ("b", 2), ("c", 3);
+end|
+call rc()|
+select row_count()|
+row_count()
+3
+update t1 set data=42 where id = "b";
+select row_count()|
+row_count()
+1
+delete from t1|
+select row_count()|
+row_count()
+3
+delete from t1|
+select row_count()|
+row_count()
+0
+select * from t1|
+id	data
+select row_count()|
+row_count()
+-1
+drop procedure rc|
+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|
+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) |
+create function f1() returns int
+return (select sum(data) from t1)|
+select f1()|
+f1()
+3
+select id, f1() from t1|
+id	f1()
+a	3
+b	3
+create function f2() returns int
+return (select data from t1 where data <= (select sum(data) from t1) limit 1)|
+select f2()|
+f2()
+1
+select id, f2() from t1|
+id	f2()
+a	1
+b	1
+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()|
+f3()
+1
+select id, f3() from t1|
+id	f3()
+a	1
+b	1
+select f1(), f3()|
+f1()	f3()
+3	1
+select id, f1(), f3() from t1|
+id	f1()	f3()
+a	3	1
+b	3	1
+create function f4() returns double 
+return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
+select f4()|
+f4()
+2
+select s, f4() from t2|
+s	f4()
+a	2
+b	2
+c	2
+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)|
+f5(1)
+1
+select f5(2)|
+ERROR HY000: Recursive stored functions and triggers are not allowed.
+select f5(3)|
+ERROR HY000: Recursive stored functions and triggers are not allowed.
+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()|
+f6()
+2
+select id, f6() from t1|
+id	f6()
+a	2
+b	2
+create view v1 (a) as select f1()|
+select * from v1|
+a
+3
+select id, a from t1, v1|
+id	a
+a	3
+b	3
+select * from v1, v1 as v|
+a	a
+3	3
+create view v2 (a) as select a*10 from v1|
+select * from v2|
+a
+30
+select id, a from t1, v2|
+id	a
+a	30
+b	30
+select * from v1, v2|
+a	a
+3	30
+create function f8 () returns int
+return (select count(*) from v2)|
+select *, f8() from v1|
+a	f8()
+3	1
+drop function f1|
+select * from v1|
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+create function f1() returns int
+return (select sum(data) from t1) + (select sum(data) from v1)|
+select f1()|
+ERROR HY000: Recursive stored functions and triggers are not allowed.
+select * from v1|
+ERROR HY000: Recursive stored functions and triggers are not allowed.
+select * from v2|
+ERROR HY000: Recursive stored functions and triggers are not allowed.
+drop function f1|
+create function f1() returns int
+return (select sum(data) from t1)|
+create function f0() returns int
+return (select * from (select 100) as r)|
+select f0()|
+f0()
+100
+select *, f0() from (select 1) as t|
+1	f0()
+1	100
+create view v0 as select f0()|
+select * from v0|
+f0()
+100
+select *, f0() from v0|
+f0()	f0()
+100	100
+lock tables t1 read, t1 as t11 read|
+select f3()|
+f3()
+1
+select id, f3() from t1 as t11|
+id	f3()
+a	1
+b	1
+select f0()|
+f0()
+100
+select * from v0|
+f0()
+100
+select *, f0() from v0, (select 123) as d1|
+f0()	123	f0()
+100	123	100
+select id, f3() from t1|
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+select f4()|
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables|
+lock tables v2 read, mysql.proc read|
+select * from v2|
+a
+30
+select * from v1|
+a
+3
+select * from v1, t1|
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+select f4()|
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables|
+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|
+select f9()|
+f9()
+6
+Warnings:
+Note	1051	Unknown table 't3'
+select f9() from t1 limit 1|
+f9()
+6
+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|
+select f10()|
+ERROR 42S02: Table 'test.t4' doesn't exist
+create table t4 as select 1 as id|
+select f10()|
+f10()
+1
+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|
+select f11()|
+ERROR HY000: Can't reopen table: 'a'
+select f11() from t1|
+ERROR HY000: Can't reopen table: 'a'
+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)|
+drop temporary table t3|
+select f12_1()|
+ERROR 42S02: Table 'test.t3' doesn't exist
+select f12_1() from t1 limit 1|
+ERROR 42S02: Table 'test.t3' doesn't exist
+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|
+drop table if exists t3|
+create table t3 (n int unsigned not null primary key, f bigint unsigned)|
+drop procedure if exists ifac|
+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|
+n	f
+1	1
+2	2
+3	6
+4	24
+5	120
+6	720
+7	5040
+8	40320
+9	362880
+10	3628800
+11	39916800
+12	479001600
+13	6227020800
+14	87178291200
+15	1307674368000
+16	20922789888000
+17	355687428096000
+18	6402373705728000
+19	121645100408832000
+20	2432902008176640000
+drop table t3|
+show function status like '%f%'|
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	fac	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+drop procedure ifac|
+drop function fac|
+show function status like '%f%'|
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+drop table if exists t3|
+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)|
+drop procedure if exists opp|
+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|
+drop procedure if exists ip|
+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|
+Procedure	sql_mode	Create Procedure
+opp		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
+show procedure status like '%p%'|
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	ip	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+test	opp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+call ip(200)|
+select * from t3 where i=45 or i=100 or i=199|
+i	p
+45	211
+100	557
+199	1229
+drop table t3|
+drop procedure opp|
+drop procedure ip|
+show procedure status like '%p%'|
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+drop table if exists t3|
+create table t3 ( f bigint unsigned not null )|
+drop procedure if exists fib|
+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|
+set @@max_sp_recursion_depth= 20|
+insert into t3 values (0), (1)|
+call fib(3)|
+select * from t3 order by f asc|
+f
+0
+1
+1
+2
+delete from t3|
+insert into t3 values (0), (1)|
+call fib(10)|
+select * from t3 order by f asc|
+f
+0
+1
+1
+2
+3
+5
+8
+13
+21
+34
+55
+drop table t3|
+drop procedure fib|
+set @@max_sp_recursion_depth= 0|
+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)|
+show procedure status like 'bar'|
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	bar	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	111111111111
+alter procedure bar comment "2222222222" sql security definer|
+alter procedure bar comment "3333333333"|
+alter procedure bar|
+show create procedure bar|
+Procedure	sql_mode	Create Procedure
+bar		CREATE PROCEDURE `bar`(x char(16), y int)
+    COMMENT '3333333333'
+insert into test.t1 values (x, y)
+show procedure status like 'bar'|
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	bar	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	3333333333
+drop procedure bar|
+drop procedure if exists p1|
+create procedure p1 ()
+select (select s1 from t3) from t3|
+create table t3 (s1 int)|
+call p1()|
+(select s1 from t3)
+insert into t3 values (1)|
+call p1()|
+(select s1 from t3)
+1
+drop procedure p1|
+drop table t3|
+drop function if exists foo|
+create function `foo` () returns int
+return 5|
+select `foo` ()|
+`foo` ()
+5
+drop function `foo`|
+drop function if exists t1max|
+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()|
+t1max()
+5
+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")|
+id	data
+zap	1
+select * from t3|
+v	c
+bar	4
+select getcount("zip")|
+getcount("zip")
+0
+select getcount("zip")|
+getcount("zip")
+1
+select * from t3|
+v	c
+bar	4
+zip	2
+select getcount(id) from t1 where data = 3|
+getcount(id)
+0
+select getcount(id) from t1 where data = 5|
+getcount(id)
+1
+select * from t3|
+v	c
+bar	4
+zip	3
+foo	1
+drop table t3|
+drop function getcount|
+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|
+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()|
+h_ee
+Inner (good)
+call h_es()|
+h_es
+Outer (good)
+call h_en()|
+h_en
+Outer (good)
+call h_ew()|
+h_ew
+Outer (good)
+call h_ex()|
+h_ex
+Outer (good)
+call h_se()|
+h_se
+Inner (good)
+call h_ss()|
+h_ss
+Inner (good)
+call h_sn()|
+h_sn
+Outer (good)
+call h_sw()|
+h_sw
+Outer (good)
+call h_sx()|
+h_sx
+Outer (good)
+call h_ne()|
+h_ne
+Inner (good)
+call h_ns()|
+h_ns
+Inner (good)
+call h_nn()|
+h_nn
+Inner (good)
+call h_we()|
+h_we
+Inner (good)
+call h_ws()|
+h_ws
+Inner (good)
+call h_ww()|
+h_ww
+Inner (good)
+call h_xe()|
+h_xe
+Inner (good)
+call h_xs()|
+h_xs
+Inner (good)
+call h_xx()|
+h_xx
+Inner (good)
+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|
+drop procedure if exists bug822|
+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|
+id	data
+foo	42
+bar	666
+delete from t1|
+drop procedure bug822|
+drop procedure if exists bug1495|
+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|
+id	data
+less	2
+more	17
+delete from t1|
+drop procedure bug1495|
+drop procedure if exists bug1547|
+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|
+id	data
+foo	12
+bar	7
+less	2
+more	17
+delete from t1|
+drop procedure bug1547|
+drop table if exists t70|
+create table t70 (s1 int,s2 int)|
+insert into t70 values (1,2)|
+drop procedure if exists bug1656|
+create procedure bug1656(out p1 int, out p2 int)
+select * into p1, p1 from t70|
+call bug1656(@1, @2)|
+select @1, @2|
+@1	@2
+2	NULL
+drop table t70|
+drop procedure bug1656|
+create table t3(a int)|
+drop procedure if exists bug1862|
+create procedure bug1862()
+begin
+insert into t3 values(2);    
+flush tables;
+end|
+call bug1862()|
+call bug1862()|
+select * from t3|
+a
+2
+2
+drop table t3|
+drop procedure bug1862|
+drop procedure if exists bug1874|
+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|
+s	i	d
+max	9	0
+min	1	0
+sum	22	0
+avg	0	4.4
+delete from t1|
+delete from t2|
+drop procedure bug1874|
+drop procedure if exists bug2260|
+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|
+@x2
+2
+drop procedure bug2260|
+drop procedure if exists bug2267_1|
+create procedure bug2267_1()
+begin
+show procedure status;
+end|
+drop procedure if exists bug2267_2|
+create procedure bug2267_2()
+begin
+show function status;
+end|
+drop procedure if exists bug2267_3|
+create procedure bug2267_3()
+begin
+show create procedure bug2267_1;
+end|
+drop procedure if exists bug2267_4|
+drop function if exists bug2267_4|
+create procedure bug2267_4()
+begin
+show create function bug2267_4;
+end|
+create function bug2267_4() returns int return 100|
+call bug2267_1()|
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	bug2267_1	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+test	bug2267_2	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+test	bug2267_3	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+test	bug2267_4	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+call bug2267_2()|
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	bug2267_4	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+call bug2267_3()|
+Procedure	sql_mode	Create Procedure
+bug2267_1		CREATE PROCEDURE `bug2267_1`()
+begin
+show procedure status;
+end
+call bug2267_4()|
+Function	sql_mode	Create Function
+bug2267_4		CREATE FUNCTION `bug2267_4`() RETURNS int(11)
+return 100
+drop procedure bug2267_1|
+drop procedure bug2267_2|
+drop procedure bug2267_3|
+drop procedure bug2267_4|
+drop function bug2267_4|
+drop procedure if exists bug2227|
+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)|
+1.3	x	y	42	z
+1.3	9	2.6	42	zzz
+drop procedure bug2227|
+drop procedure if exists bug2614|
+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|
+call bug2614()|
+call bug2614()|
+drop table t3|
+drop procedure bug2614|
+drop function if exists bug2674|
+create function bug2674() returns int
+return @@sort_buffer_size|
+set @osbs = @@sort_buffer_size|
+set @@sort_buffer_size = 262000|
+select bug2674()|
+bug2674()
+262000
+drop function bug2674|
+set @@sort_buffer_size = @osbs|
+drop procedure if exists bug3259_1 |
+create procedure bug3259_1 () begin end|
+drop procedure if exists BUG3259_2 |
+create procedure BUG3259_2 () begin end|
+drop procedure if exists Bug3259_3 |
+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|
+drop function if exists bug2772|
+create function bug2772() returns char(10) character set latin2
+return 'a'|
+select bug2772()|
+bug2772()
+a
+drop function bug2772|
+drop procedure if exists bug2776_1|
+create procedure bug2776_1(out x int)
+begin
+declare v int;
+set v = default;
+set x = v;
+end|
+drop procedure if exists bug2776_2|
+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|
+@x
+NULL
+call bug2776_2(@x)|
+select @x|
+@x
+42
+drop procedure bug2776_1|
+drop procedure bug2776_2|
+create table t3 (s1 smallint)|
+insert into t3 values (123456789012)|
+Warnings:
+Warning	1264	Out of range value adjusted for column 's1' at row 1
+drop procedure if exists bug2780|
+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|
+@x
+1
+select * from t3|
+s1
+32767
+32767
+drop procedure bug2780|
+drop table t3|
+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)|
+drop procedure if exists bug1863|
+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)|
+Warnings:
+Note	1051	Unknown table 'temp_t1'
+Warning	1329	No data - zero rows fetched, selected, or processed
+call bug1863(10)|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+select * from t4|
+f1	rc	t3
+2	0	NULL
+2	0	NULL
+drop procedure bug1863|
+drop temporary table temp_t1;
+drop table t3, t4|
+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")|
+drop procedure if exists bug2656_1|
+create procedure bug2656_1()
+begin 
+select
+m.Market
+from  t4 m JOIN t3 o 
+ON o.MarketID != 1 and o.MarketID = m.MarketID;
+end |
+drop procedure if exists bug2656_2|
+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()|
+Market
+MarketID Two
+call bug2656_1()|
+Market
+MarketID Two
+call bug2656_2()|
+Market
+MarketID Two
+call bug2656_2()|
+Market
+MarketID Two
+drop procedure bug2656_1|
+drop procedure bug2656_2|
+drop table t3, t4|
+drop procedure if exists bug3426|
+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|
+@i	time
+2	01-01-1970 03:16:40
+call bug3426(NULL, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+@i	time
+1	01-01-1970 03:00:10
+alter procedure bug3426 sql security invoker|
+call bug3426(NULL, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+@i	time
+1	01-01-1970 03:00:10
+call bug3426(1000, @i)|
+select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
+@i	time
+2	01-01-1970 03:16:40
+drop procedure bug3426|
+create table t3 (
+a int primary key, 
+ach char(1)
+) engine = innodb|
+create table t4 (
+b int  primary key , 
+bch char(1)
+) engine = innodb|
+insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')|
+Warnings:
+Warning	1265	Data truncated for column 'ach' at row 1
+Warning	1265	Data truncated for column 'ach' at row 2
+insert into t4 values (1 , 'bCh1' )|
+Warnings:
+Warning	1265	Data truncated for column 'bch' at row 1
+drop procedure if exists bug3448|
+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|
+a	ach	b	bch
+1	a	1	b
+call bug3448()|
+a	ach	b	bch
+1	a	1	b
+call bug3448()|
+a	ach	b	bch
+1	a	1	b
+drop procedure bug3448|
+drop table t3, t4|
+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 ...')|
+drop procedure if exists bug3734 |
+create procedure bug3734 (param1 varchar(100))
+select * from t3 where match (title,body) against (param1)|
+call bug3734('database')|
+id	title	body
+5	MySQL vs. YourSQL	In the following database comparison ...
+1	MySQL Tutorial	DBMS stands for DataBase ...
+call bug3734('Security')|
+id	title	body
+6	MySQL Security	When configured properly, MySQL ...
+drop procedure bug3734|
+drop table t3|
+drop procedure if exists bug3863|
+create procedure bug3863()
+begin
+set @a = 0;
+while @a < 5 do
+set @a = @a + 1;
+end while;
+end|
+call bug3863()|
+select @a|
+@a
+5
+call bug3863()|
+select @a|
+@a
+5
+drop procedure bug3863|
+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)
+)|
+drop procedure if exists bug2460_1|
+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)|
+id
+call bug2460_1(2)|
+id
+insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')|
+call bug2460_1(2)|
+id
+2
+1
+call bug2460_1(2)|
+id
+2
+1
+drop procedure if exists bug2460_2|
+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|
+s1
+1
+drop procedure bug2460_1|
+drop procedure bug2460_2|
+drop table t3|
+set @@sql_mode = ''|
+drop procedure if exists bug2564_1|
+create procedure bug2564_1()
+comment 'Joe''s procedure'
+  insert into `t1` values ("foo", 1)|
+set @@sql_mode = 'ANSI_QUOTES'|
+drop procedure if exists bug2564_2|
+create procedure bug2564_2()
+insert into "t1" values ('foo', 1)|
+set @@sql_mode = ''$
+drop function if exists bug2564_3$
+create function bug2564_3(x int, y int) returns int
+return x || y$
+set @@sql_mode = 'ANSI'$
+drop function if exists bug2564_4$
+create function bug2564_4(x int, y int) returns int
+return x || y$
+set @@sql_mode = ''|
+show create procedure bug2564_1|
+Procedure	sql_mode	Create Procedure
+bug2564_1		CREATE PROCEDURE `bug2564_1`()
+    COMMENT 'Joe''s procedure'
+insert into `t1` values ("foo", 1)
+show create procedure bug2564_2|
+Procedure	sql_mode	Create Procedure
+bug2564_2	ANSI_QUOTES	CREATE PROCEDURE "bug2564_2"()
+insert into "t1" values ('foo', 1)
+show create function bug2564_3|
+Function	sql_mode	Create Function
+bug2564_3		CREATE FUNCTION `bug2564_3`(x int, y int) RETURNS int(11)
+return x || y
+show create function bug2564_4|
+Function	sql_mode	Create Function
+bug2564_4	REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI	CREATE FUNCTION "bug2564_4"(x int, y int) RETURNS int(11)
+return x || y
+drop procedure bug2564_1|
+drop procedure bug2564_2|
+drop function bug2564_3|
+drop function bug2564_4|
+drop function if exists bug3132|
+create function bug3132(s char(20)) returns char(50)
+return concat('Hello, ', s, '!')|
+select bug3132('Bob') union all select bug3132('Judy')|
+bug3132('Bob')
+Hello, Bob!
+Hello, Judy!
+drop function bug3132|
+drop procedure if exists bug3843|
+create procedure bug3843()
+analyze table t1|
+call bug3843()|
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+call bug3843()|
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Table is already up to date
+select 1+2|
+1+2
+3
+drop procedure bug3843|
+create table t3 ( s1 char(10) )|
+insert into t3 values ('a'), ('b')|
+drop procedure if exists bug3368|
+create procedure bug3368(v char(10))
+begin
+select group_concat(v) from t3;
+end|
+call bug3368('x')|
+group_concat(v)
+x,x
+call bug3368('yz')|
+group_concat(v)
+yz,yz
+drop procedure bug3368|
+drop table t3|
+create table t3 (f1 int, f2 int)|
+insert into t3 values (1,1)|
+drop procedure if exists bug4579_1|
+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|
+drop procedure if exists bug4579_2|
+create procedure bug4579_2 ()
+begin
+end|
+call bug4579_1()|
+call bug4579_1()|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+call bug4579_1()|
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+drop procedure bug4579_1|
+drop procedure bug4579_2|
+drop table t3|
+drop procedure if exists bug2773|
+create function bug2773() returns int return null|
+create table t3 as select bug2773()|
+show create table t3|
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `bug2773()` int(11) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t3|
+drop function bug2773|
+drop procedure if exists bug3788|
+create function bug3788() returns date return cast("2005-03-04" as date)|
+select bug3788()|
+bug3788()
+2005-03-04
+drop function bug3788|
+create function bug3788() returns binary(1) return 5|
+select bug3788()|
+bug3788()
+5
+drop function bug3788|
+create table t3 (f1 int, f2 int, f3 int)|
+insert into t3 values (1,1,1)|
+drop procedure if exists bug4726|
+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|
+drop procedure if exists bug4902|
+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|
+call bug4902()|
+Charset	Description	Default collation	Maxlen
+Collation	Charset	Id	Default	Compiled	Sortlen
+Type	Size	Min_Value	Max_Value	Prec	Scale	Nullable	Auto_Increment	Unsigned	Zerofill	Searchable	Case_Sensitive	Default	Comment
+tinyint	1	-128	127	0	0	YES	YES	NO	YES	YES	NO	NULL,0	A very small integer
+tinyint unsigned	1	0	255	0	0	YES	YES	YES	YES	YES	NO	NULL,0	A very small integer
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` char(16) NOT NULL default '',
+  `data` int(11) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+Database	Create Database
+test	CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
+Database (foo)
+Level	Code	Message
+Field	Type	Null	Key	Default	Extra
+id	char(16)	NO			
+data	int(11)	NO			
+Grants for root@localhost
+GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
+Database	Table	In_use	Name_locked
+Privilege	Context	Comment
+Alter	Tables	To alter the table
+Alter routine	Functions,Procedures	To alter or drop stored functions/procedures
+Create	Databases,Tables,Indexes	To create new databases and tables
+Create routine	Functions,Procedures	To use CREATE FUNCTION/PROCEDURE
+Create temporary tables	Databases	To use CREATE TEMPORARY TABLE
+Create view	Tables	To create new views
+Create user	Server Admin	To create new users
+Delete	Tables	To delete existing rows
+Drop	Databases,Tables	To drop databases, tables, and views
+Execute	Functions,Procedures	To execute stored routines
+File	File access on server	To read and write files on the server
+Grant option	Databases,Tables,Functions,Procedures	To give to other users those privileges you possess
+Index	Tables	To create or drop indexes
+Insert	Tables	To insert data into tables
+Lock tables	Databases	To use LOCK TABLES (together with SELECT privilege)
+Process	Server Admin	To view the plain text of currently executing queries
+References	Databases,Tables	To have references on tables
+Reload	Server Admin	To reload or refresh tables, logs and privileges
+Replication client	Server Admin	To ask where the slave or master servers are
+Replication slave	Server Admin	To read binary log events from the master
+Select	Tables	To retrieve rows from table
+Show databases	Server Admin	To see all databases with SHOW DATABASES
+Show view	Tables	To see views with SHOW CREATE VIEW
+Shutdown	Server Admin	To shut down the server
+Super	Server Admin	To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
+Update	Tables	To update existing rows
+Usage	Server Admin	No privileges - allow connect only
+Variable_name	Value
+Tables_in_test (foo)
+Variable_name	Value
+Level	Code	Message
+call bug4902()|
+Charset	Description	Default collation	Maxlen
+Collation	Charset	Id	Default	Compiled	Sortlen
+Type	Size	Min_Value	Max_Value	Prec	Scale	Nullable	Auto_Increment	Unsigned	Zerofill	Searchable	Case_Sensitive	Default	Comment
+tinyint	1	-128	127	0	0	YES	YES	NO	YES	YES	NO	NULL,0	A very small integer
+tinyint unsigned	1	0	255	0	0	YES	YES	YES	YES	YES	NO	NULL,0	A very small integer
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` char(16) NOT NULL default '',
+  `data` int(11) NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+Database	Create Database
+test	CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */
+Database (foo)
+Level	Code	Message
+Field	Type	Null	Key	Default	Extra
+id	char(16)	NO			
+data	int(11)	NO			
+Grants for root@localhost
+GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
+Database	Table	In_use	Name_locked
+Privilege	Context	Comment
+Alter	Tables	To alter the table
+Alter routine	Functions,Procedures	To alter or drop stored functions/procedures
+Create	Databases,Tables,Indexes	To create new databases and tables
+Create routine	Functions,Procedures	To use CREATE FUNCTION/PROCEDURE
+Create temporary tables	Databases	To use CREATE TEMPORARY TABLE
+Create view	Tables	To create new views
+Create user	Server Admin	To create new users
+Delete	Tables	To delete existing rows
+Drop	Databases,Tables	To drop databases, tables, and views
+Execute	Functions,Procedures	To execute stored routines
+File	File access on server	To read and write files on the server
+Grant option	Databases,Tables,Functions,Procedures	To give to other users those privileges you possess
+Index	Tables	To create or drop indexes
+Insert	Tables	To insert data into tables
+Lock tables	Databases	To use LOCK TABLES (together with SELECT privilege)
+Process	Server Admin	To view the plain text of currently executing queries
+References	Databases,Tables	To have references on tables
+Reload	Server Admin	To reload or refresh tables, logs and privileges
+Replication client	Server Admin	To ask where the slave or master servers are
+Replication slave	Server Admin	To read binary log events from the master
+Select	Tables	To retrieve rows from table
+Show databases	Server Admin	To see all databases with SHOW DATABASES
+Show view	Tables	To see views with SHOW CREATE VIEW
+Shutdown	Server Admin	To shut down the server
+Super	Server Admin	To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
+Update	Tables	To update existing rows
+Usage	Server Admin	No privileges - allow connect only
+Variable_name	Value
+Tables_in_test (foo)
+Variable_name	Value
+Level	Code	Message
+drop procedure bug4902|
+drop procedure if exists bug4902_2|
+create procedure bug4902_2()
+begin
+show processlist;
+end|
+call bug4902_2()|
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	test	Query	#	NULL	show processlist
+call bug4902_2()|
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	test	Query	#	NULL	show processlist
+drop procedure bug4902_2|
+drop procedure if exists bug4904|
+create procedure bug4904()
+begin
+declare continue handler for sqlstate 'HY000' begin end;
+create table t2 as select * from t3;
+end|
+call bug4904()|
+ERROR 42S02: Table 'test.t3' doesn't exist
+drop procedure bug4904|
+create table t3 (s1 char character set latin1, s2 char character set latin2)|
+drop procedure if exists bug4904|
+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|
+drop procedure if exists bug336|
+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|
+@y
+5
+delete from t1|
+drop procedure bug336|
+drop procedure if exists bug3157|
+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|
+@n
+2
+delete from t1|
+drop procedure bug3157|
+drop procedure if exists bug5251|
+create procedure bug5251()
+begin
+end|
+select created into @c1 from mysql.proc
+where db='test' and name='bug5251'|
+alter procedure bug5251 comment 'foobar'|
+select count(*) from mysql.proc
+where  db='test' and name='bug5251' and created = @c1|
+count(*)
+1
+drop procedure bug5251|
+drop procedure if exists bug5251|
+create procedure bug5251()
+checksum table t1|
+call bug5251()|
+Table	Checksum
+test.t1	0
+call bug5251()|
+Table	Checksum
+test.t1	0
+drop procedure bug5251|
+drop procedure if exists bug5287|
+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|
+drop procedure if exists bug5307|
+create procedure bug5307()
+begin
+end; set @x = 3|
+call bug5307()|
+select @x|
+@x
+3
+drop procedure bug5307|
+drop procedure if exists bug5258|
+create procedure bug5258()
+begin
+end|
+drop procedure if exists bug5258_aux|
+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()|
+Ok
+Ok
+drop procedure bug5258|
+drop procedure bug5258_aux|
+drop function if exists bug4487|
+create function bug4487() returns char
+begin
+declare v char;
+return v;
+end|
+select bug4487()|
+bug4487()
+NULL
+drop function bug4487|
+drop procedure if exists bug4941|
+drop procedure if exists bug4941|
+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|
+@x
+NULL
+delete from t1|
+drop procedure bug4941|
+drop procedure if exists bug3583|
+drop procedure if exists bug3583|
+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'|
+Variable_name	Value
+Qcache_hits	0
+call bug3583()|
+id	data
+x	3
+y	5
+c
+2
+show status like 'Qcache_hits'|
+Variable_name	Value
+Qcache_hits	0
+call bug3583()|
+id	data
+x	3
+y	5
+c
+2
+call bug3583()|
+id	data
+x	3
+y	5
+c
+2
+show status like 'Qcache_hits'|
+Variable_name	Value
+Qcache_hits	2
+set global query_cache_size = @x|
+flush status|
+flush query cache|
+delete from t1|
+drop procedure bug3583|
+drop procedure if exists bug4905|
+create table t3 (s1 int,primary key (s1))|
+drop procedure if exists bug4905|
+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()|
+row_count()
+1
+call bug4905()|
+select row_count()|
+row_count()
+0
+call bug4905()|
+select row_count()|
+row_count()
+0
+select * from t3|
+s1
+1
+drop procedure bug4905|
+drop table t3|
+drop procedure if exists bug6029|
+drop procedure if exists bug6029|
+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()|
+sqlstate 23000
+sqlstate 23000
+delete from t3|
+call bug6029()|
+1136
+1136
+drop procedure bug6029|
+drop table t3|
+drop procedure if exists bug8540|
+create procedure bug8540()
+begin
+declare x int default 1;
+select x as y, x+0 as z;
+end|
+call bug8540()|
+y	z
+1	1
+drop procedure bug8540|
+create table t3 (s1 int)|
+drop procedure if exists bug6642|
+create procedure bug6642()
+select abs(count(s1)) from t3|
+call bug6642()|
+abs(count(s1))
+0
+call bug6642()|
+abs(count(s1))
+0
+drop procedure bug6642|
+insert into t3 values (0),(1)|
+drop procedure if exists bug7013|
+create procedure bug7013()
+select s1,count(s1) from t3 group by s1 with rollup|
+call bug7013()|
+s1	count(s1)
+0	1
+1	1
+NULL	2
+call bug7013()|
+s1	count(s1)
+0	1
+1	1
+NULL	2
+drop procedure bug7013|
+drop table if exists t4|
+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')|
+drop procedure if exists bug7743|
+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")|
+var
+1
+call bug7743("OneWord")|
+var
+NULL
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+call bug7743("anotherword")|
+var
+2
+call bug7743("AnotherWord")|
+var
+NULL
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+drop procedure bug7743|
+drop table t4|
+delete from t3|
+insert into t3 values(1)|
+drop procedure if exists bug7992_1|
+Warnings:
+Note	1305	PROCEDURE bug7992_1 does not exist
+drop procedure if exists bug7992_2|
+Warnings:
+Note	1305	PROCEDURE bug7992_2 does not exist
+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|
+create table t3 (  userid bigint(20) not null default 0 )|
+drop procedure if exists bug8116|
+create procedure bug8116(in _userid int)
+select * from t3 where userid = _userid|
+call bug8116(42)|
+userid
+call bug8116(42)|
+userid
+drop procedure bug8116|
+drop table t3|
+drop procedure if exists bug6857|
+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|
+drop procedure bug6857|
+drop procedure if exists bug8757|
+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()|
+2	y
+2	2
+1	x
+1	1
+delete from t1|
+delete from t2|
+drop procedure bug8757|
+drop procedure if exists bug8762|
+drop procedure if exists bug8762; create procedure bug8762() begin end|
+drop procedure if exists bug8762; create procedure bug8762() begin end|
+drop procedure bug8762|
+drop function if exists bug5240|
+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|
+id	bug5240()
+answer	42
+drop function bug5240|
+drop function if exists bug5278|
+create function bug5278 () returns char
+begin
+SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
+return 'okay';
+end|
+select bug5278()|
+ERROR 42000: Can't find any matching row in the user table
+select bug5278()|
+ERROR 42000: Can't find any matching row in the user table
+drop function bug5278|
+drop procedure if exists p1|
+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|
+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)
+)|
+drop procedure if exists bug8849|
+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|
+drop procedure if exists bug8937|
+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()|
+s	x	y	z
+16	3	1	6
+a
+3.2
+drop procedure bug8937|
+delete from t1|
+drop procedure if exists bug6900|
+drop procedure if exists bug9074|
+drop procedure if exists bug6900_9074|
+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()|
+2
+2
+call bug9074()|
+x1	x2	x3	x4	x5	x6
+1	1	1	1	1	1
+call bug6900_9074(0)|
+sqlexception
+sqlexception
+call bug6900_9074(1)|
+23000
+23000
+drop procedure bug6900|
+drop procedure bug9074|
+drop procedure bug6900_9074|
+drop table t3|
+drop procedure if exists avg|
+create procedure avg ()
+begin
+end|
+call avg ()|
+drop procedure avg|
+drop procedure if exists bug6129|
+set @old_mode= @@sql_mode;
+set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO";
+create procedure bug6129()
+select @@sql_mode|
+call bug6129()|
+@@sql_mode
+ERROR_FOR_DIVISION_BY_ZERO
+set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"|
+call bug6129()|
+@@sql_mode
+ERROR_FOR_DIVISION_BY_ZERO
+set @@sql_mode= "NO_ZERO_IN_DATE"|
+call bug6129()|
+@@sql_mode
+ERROR_FOR_DIVISION_BY_ZERO
+set @@sql_mode=@old_mode;
+drop procedure bug6129|
+drop procedure if exists bug9856|
+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()|
+16
+16
+call bug9856()|
+16
+16
+drop procedure bug9856|
+drop procedure if exists bug9674_1|
+drop procedure if exists bug9674_2|
+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|
+@sptmp
+10
+call bug9674_2()|
+v/10
+10.0000
+call bug9674_2()|
+v/10
+10.0000
+drop procedure bug9674_1|
+drop procedure bug9674_2|
+drop procedure if exists bug9598_1|
+drop procedure if exists bug9598_2|
+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)|
+v1	v2	v3	v4	v5
+Test	2	3	4	5
+v1	v2	v3	v4	v5
+Test	2	3	4	5
+select @tmp1, @tmp2|
+@tmp1	@tmp2
+50	60
+drop procedure bug9598_1|
+drop procedure bug9598_2|
+drop procedure if exists bug9902|
+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|
+bug9902()
+2
+select bug9902() from t1|
+bug9902()
+3
+select @x|
+@x
+3
+set global query_cache_size = @qcs1|
+delete from t1|
+drop function bug9902|
+drop function if exists bug9102|
+create function bug9102() returns blob return 'a'|
+select bug9102()|
+bug9102()
+a
+drop function bug9102|
+drop function if exists bug7648|
+create function bug7648() returns bit(8) return 'a'|
+select bug7648()|
+bug7648()
+a
+drop function bug7648|
+drop function if exists bug9775|
+create function bug9775(v1 char(1)) returns enum('a','b') return v1|
+select bug9775('a'),bug9775('b'),bug9775('c')|
+bug9775('a')	bug9775('b')	bug9775('c')
+a	b	
+Warnings:
+Warning	1265	Data truncated for column 'bug9775('c')' at row 1
+drop function bug9775|
+create function bug9775(v1 int) returns enum('a','b') return v1|
+select bug9775(1),bug9775(2),bug9775(3)|
+bug9775(1)	bug9775(2)	bug9775(3)
+a	b	
+Warnings:
+Warning	1265	Data truncated for column 'bug9775(3)' at row 1
+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')|
+bug9775('a')	bug9775('b')	bug9775('a,b')	bug9775('c')
+a	b	a	
+Warnings:
+Warning	1265	Data truncated for column 'v1' at row 1
+Warning	1265	Data truncated for column 'bug9775('c')' at row 1
+drop function bug9775|
+create function bug9775(v1 int) returns set('a','b') return v1|
+select bug9775(1),bug9775(2),bug9775(3),bug9775(4)|
+bug9775(1)	bug9775(2)	bug9775(3)	bug9775(4)
+a	b	a,b	
+Warnings:
+Warning	1265	Data truncated for column 'bug9775(4)' at row 1
+drop function bug9775|
+drop function if exists bug8861|
+create function bug8861(v1 int) returns year return v1|
+select bug8861(05)|
+bug8861(05)
+2005
+set @x = bug8861(05)|
+select @x|
+@x
+2005
+drop function bug8861|
+drop procedure if exists bug9004_1|
+drop procedure if exists bug9004_2|
+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)|
+call bug9004_1('12345678901234567')|
+Warnings:
+Warning	1265	Data truncated for column 'x' at row 1
+call bug9004_2('12345678901234567890')|
+Warnings:
+Warning	1265	Data truncated for column 'x' at row 1
+delete from t1|
+drop procedure bug9004_1|
+drop procedure bug9004_2|
+drop procedure if exists bug7293|
+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')|
+yes
+yes
+call bug7293 ('secrete')|
+yes
+yes
+drop procedure bug7293|
+delete from t1|
+drop procedure if exists bug9841|
+drop view if exists v1|
+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|
+drop procedure if exists bug5963|
+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()|
+v
+5
+call bug5963_1()|
+v
+5
+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|
+drop function if exists bug9559|
+create function bug9559()
+returns int
+begin
+set @y = -6/2;
+return @y;
+end|
+select bug9559()|
+bug9559()
+-3
+drop function bug9559|
+drop procedure if exists bug10961|
+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()|
+x
+1
+x
+2
+x
+3
+call bug10961()|
+x
+1
+x
+2
+x
+3
+drop procedure bug10961|
+DROP PROCEDURE IF EXISTS bug6866|
+DROP VIEW IF EXISTS tv|
+Warnings:
+Note	1051	Unknown table 'test.tv'
+DROP TABLE IF EXISTS tt1,tt2,tt3|
+Warnings:
+Note	1051	Unknown table 'tt1'
+Note	1051	Unknown table 'tt2'
+Note	1051	Unknown table '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)|
+a1	a2	a3	data	data2	data3
+1	1	4	xx	a	d
+CALL bug6866(1)|
+a1	a2	a3	data	data2	data3
+1	1	4	xx	a	d
+CALL bug6866(1)|
+a1	a2	a3	data	data2	data3
+1	1	4	xx	a	d
+DROP PROCEDURE bug6866;
+DROP VIEW tv|
+DROP TABLE tt1, tt2, tt3|
+DROP PROCEDURE IF EXISTS bug10136|
+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()|
+name	val
+aaaaa	1
+bbbbb	2
+ccccc	3
+name	val
+aaaaa	1
+bbbbb	2
+ccccc	3
+name	val
+aaaaa	1
+bbbbb	2
+ccccc	3
+call bug10136()|
+name	val
+aaaaa	1
+bbbbb	2
+ccccc	3
+name	val
+aaaaa	1
+bbbbb	2
+ccccc	3
+name	val
+aaaaa	1
+bbbbb	2
+ccccc	3
+call bug10136()|
+name	val
+aaaaa	1
+bbbbb	2
+ccccc	3
+name	val
+aaaaa	1
+bbbbb	2
+ccccc	3
+name	val
+aaaaa	1
+bbbbb	2
+ccccc	3
+drop procedure bug10136|
+drop table t3|
+drop procedure if exists bug11529|
+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|
+drop procedure if exists bug6063|
+drop procedure if exists bug7088_1|
+drop procedure if exists bug7088_2|
+drop procedure if exists bug9565_sub|
+drop procedure if exists bug9565|
+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()|
+id	data
+one	1
+delete from t1|
+drop procedure bug9565_sub|
+drop procedure bug9565|
+drop procedure if exists bug9538|
+create procedure bug9538()
+set @@sort_buffer_size = 1000000|
+set @x = @@sort_buffer_size|
+set @@sort_buffer_size = 2000000|
+select @@sort_buffer_size|
+@@sort_buffer_size
+2000000
+call bug9538()|
+select @@sort_buffer_size|
+@@sort_buffer_size
+1000000
+set @@sort_buffer_size = @x|
+drop procedure bug9538|
+drop procedure if exists bug8692|
+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)|
+Warnings:
+Warning	1265	Data truncated for column 'c3' at row 1
+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()|
+v1	v2	v3	v4	v5	v6	v7
+						NULL
+drop procedure bug8692|
+drop table t3|
+drop function if exists bug10055|
+create function bug10055(v char(255)) returns char(255) return lower(v)|
+select t.column_name, bug10055(t.column_name)
+from information_schema.columns as t
+where t.table_schema = 'test' and t.table_name = 't1'|
+column_name	bug10055(t.column_name)
+id	id
+data	data
+drop function bug10055|
+drop procedure if exists bug12297|
+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|
+drop function if exists f_bug11247|
+drop procedure if exists p_bug11247|
+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|
+drop procedure if exists bug12168|
+drop table if exists t3, t4|
+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|
+a
+1
+3
+truncate t4|
+call bug12168('b')|
+select * from t4|
+a
+2
+4
+truncate t4|
+call bug12168('a')|
+select * from t4|
+a
+1
+3
+truncate t4|
+call bug12168('b')|
+select * from t4|
+a
+2
+4
+truncate t4|
+drop table t3, t4|
+drop procedure if exists bug12168|
+drop table if exists t3|
+drop procedure if exists bug11333|
+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|
+drop function if exists bug9048|
+create function bug9048(f1 char binary) returns char binary
+begin
+set f1= concat( 'hello', f1 );
+return f1;
+end|
+drop function bug9048|
+drop procedure if exists bug12849_1|
+create procedure bug12849_1(inout x char) select x into x|
+set @var='a'|
+call bug12849_1(@var)|
+select @var|
+@var
+a
+drop procedure bug12849_1|
+drop procedure if exists bug12849_2|
+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|
+@var
+abcdabcd
+drop procedure bug12849_2|
+drop procedure if exists bug131333|
+drop function if exists bug131333|
+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()|
+a
+NULL
+a
+1
+b
+NULL
+select bug131333()|
+bug131333()
+NULL
+drop procedure bug131333|
+drop function bug131333|
+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|
+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|
+select bug12379()|
+ERROR 23000: Duplicate entry 'X' for key 1
+select 1|
+1
+1
+call bug12379_1()|
+bug12379()
+42
+42
+select 2|
+2
+2
+call bug12379_2()|
+bug12379()
+select 3|
+3
+3
+call bug12379_3()|
+ERROR 23000: Duplicate entry 'X' for key 1
+select 4|
+4
+4
+drop function bug12379|
+drop procedure bug12379_1|
+drop procedure bug12379_2|
+drop procedure bug12379_3|
+drop table t3|
+drop procedure if exists bug13124|
+create procedure bug13124()
+begin
+declare y integer;
+set @x=y;
+end|
+call bug13124()|
+drop procedure  bug13124|
+drop procedure if exists bug12979_1|
+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|
+drop procedure if exists bug12979_2|
+create procedure bug12979_2()
+begin
+declare internal_var decimal(5);
+set internal_var= internal_var / 2;
+select internal_var;
+end|
+call bug12979_2()|
+internal_var
+NULL
+drop procedure bug12979_2|
+drop table if exists t3|
+drop procedure if exists bug6127|
+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|
+s1
+0
+1
+call bug6127()|
+ERROR 23000: Duplicate entry '0' for key 1
+select * from t3|
+s1
+0
+1
+set sql_mode=@sm|
+drop table t3|
+drop procedure bug6127|
+drop procedure if exists bug12589_1|
+drop procedure if exists bug12589_2|
+drop procedure if exists bug12589_3|
+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|
+call bug12589_1()|
+Table	Create Table
+tm1	CREATE TEMPORARY TABLE `tm1` (
+  `spv1` decimal(3,3) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+Warnings:
+Warning	1264	Out of range value adjusted for column 'spv1' at row 1
+Warning	1366	Incorrect decimal value: 'test' for column 'spv1' at row 1
+call bug12589_2()|
+Table	Create Table
+tm1	CREATE TEMPORARY TABLE `tm1` (
+  `spv1` decimal(6,3) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+call bug12589_3()|
+Table	Create Table
+tm1	CREATE TEMPORARY TABLE `tm1` (
+  `spv1` decimal(6,3) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop procedure bug12589_1|
+drop procedure bug12589_2|
+drop procedure bug12589_3|
+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|
+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()|
+Result
+Caught it
+select * from t3|
+x
+42
+delete from t3|
+call bug7049_4()|
+Result
+Caught it
+select * from t3|
+x
+42
+select bug7049_2()|
+bug7049_2()
+1
+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|
+drop function if exists bug13941|
+drop procedure if exists bug13941|
+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|
+select bug13941('this is a test')|
+bug13941('this is a test')
+questo una prova
+call bug13941(@a)|
+select @a|
+@a
+Local
+drop function bug13941|
+drop procedure bug13941|
+DROP PROCEDURE IF EXISTS bug13095;
+DROP TABLE IF EXISTS bug13095_t1;
+DROP VIEW IF EXISTS bug13095_v1;
+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|
+CALL bug13095('bug13095_t1');
+@str
+CREATE TABLE bug13095_t1(stuff char(15))
+@str
+INSERT INTO bug13095_t1 VALUES('row1'),('row2'),('row3')
+@str
+CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM bug13095_t1
+c1
+row1
+row2
+row3
+@str
+DROP VIEW bug13095_v1
+DROP PROCEDURE IF EXISTS bug13095;
+DROP VIEW IF EXISTS bug13095_v1;
+DROP TABLE IF EXISTS bug13095_t1;
+drop procedure if exists bug14210|
+set @@session.max_heap_table_size=16384|
+select @@session.max_heap_table_size|
+@@session.max_heap_table_size
+16384
+create table t3 (a char(255)) engine=InnoDB|
+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|
+count(*)
+256
+drop table t3, t4|
+drop procedure bug14210|
+set @@session.max_heap_table_size=default|
+drop function if exists bug14723|
+drop procedure if exists bug14723|
+/*!50003 create function bug14723()
+returns bigint(20)
+main_loop: begin
+return 42;
+end */;;
+show create function bug14723;;
+Function	sql_mode	Create Function
+bug14723		CREATE FUNCTION `bug14723`() RETURNS bigint(20)
+main_loop: begin
+return 42;
+end
+select bug14723();;
+bug14723()
+42
+/*!50003 create procedure bug14723()
+main_loop: begin
+select 42;
+end */;;
+show create procedure bug14723;;
+Procedure	sql_mode	Create Procedure
+bug14723		CREATE PROCEDURE `bug14723`()
+main_loop: begin
+select 42;
+end
+call bug14723();;
+42
+42
+drop function bug14723|
+drop procedure bug14723|
+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()|
+a
+0
+drop procedure bug14845|
+drop procedure if exists bug13549_1|
+drop procedure if exists bug13549_2|
+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|
+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|
+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|
+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|
+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|
+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|
+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|
+@@max_sp_recursion_depth
+4
+select bug10100f(3)|
+ERROR HY000: Recursive stored functions and triggers are not allowed.
+select bug10100f(6)|
+ERROR HY000: Recursive stored functions and triggers are not allowed.
+call bug10100t(5)|
+res
+120
+call bug10100pt(1,5)|
+a
+4
+call bug10100pv(1,5)|
+a
+4
+update t3 set a=1|
+call bug10100pd(1,5)|
+level
+1
+a
+7
+a
+7
+level
+2
+a
+13
+a
+13
+level
+3
+a
+19
+a
+19
+level
+4
+a
+25
+a
+25
+a
+25
+select * from t3|
+a
+25
+update t3 set a=1|
+call bug10100pc(1,5)|
+level
+1
+lv
+1
+level
+2
+lv
+2
+level
+3
+lv
+4
+level
+4
+lv
+7
+a
+11
+select * from t3|
+a
+11
+set @@max_sp_recursion_depth=0|
+select @@max_sp_recursion_depth|
+@@max_sp_recursion_depth
+0
+select bug10100f(5)|
+ERROR HY000: Recursive stored functions and triggers are not allowed.
+call bug10100t(5)|
+ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug10100p
+set @@max_sp_recursion_depth=255|
+set @var=1|
+call bug10100p(255, @var)|
+call bug10100pt(1,255)|
+call bug10100pv(1,255)|
+call bug10100pd(1,255)|
+call bug10100pc(1,255)|
+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|
+drop procedure if exists bug13729|
+drop table if exists t3|
+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()|
+55
+55
+select * from t3|
+s1
+1
+2
+drop procedure bug13729|
+drop table t3|
+drop procedure if exists bug14643_1|
+drop procedure if exists bug14643_2|
+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()|
+Handler
+boo
+v	isnull(v)
+NULL	1
+call bug14643_2()|
+Handler
+boo
+Handler
+boo
+drop procedure bug14643_1|
+drop procedure bug14643_2|
+drop procedure if exists bug14304|
+drop table if exists t3, t4|
+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()|
+a
+1
+2
+3
+drop procedure bug14304|
+drop table t3, t4|
+drop procedure if exists bug14376|
+create procedure bug14376()
+begin
+declare x int default x;
+end|
+call bug14376()|
+ERROR 42S22: Unknown column 'x' in 'field list'
+drop procedure bug14376|
+create procedure bug14376()
+begin
+declare x int default 42;
+begin
+declare x int default x;
+select x;
+end;
+end|
+call bug14376()|
+x
+42
+drop procedure bug14376|
+create procedure bug14376(x int)
+begin
+declare x int default x;
+select x;
+end|
+call bug14376(4711)|
+x
+4711
+drop procedure bug14376|
+drop procedure if exists bug5967|
+drop table if exists t3|
+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")|
+a
+a - stored procedure parameter
+Parameter takes precedence over table column
+a - stored procedure parameter
+Parameter takes precedence over table column in cursors
+a - stored procedure parameter
+A local variable takes precedence over parameter
+a - local variable
+A local variable takes precedence over parameter in cursors
+a - local variable
+A local variable in a nested compound statement takes precedence over a local variable in the outer statement
+a - local variable in a nested compound statement
+A local variable in a nested compound statement takes precedence over table column
+a - local variable in a nested compound statement
+A local variable in a nested compound statement takes precedence over table column in cursors
+a - local variable in a nested compound statement
+drop procedure bug5967|
+drop procedure if exists bug13012|
+create procedure bug13012()
+BEGIN
+REPAIR TABLE t1;
+BACKUP TABLE t1 to '../tmp';
+DROP TABLE t1;
+RESTORE TABLE t1 FROM '../tmp';
+END|
+call bug13012()|
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+Table	Op	Msg_type	Msg_text
+test.t1	backup	status	OK
+Table	Op	Msg_type	Msg_text
+test.t1	restore	status	OK
+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()|
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+test.t2	repair	status	OK
+test.t3	repair	status	OK
+test.v1	repair	error	'test.v1' is not BASE TABLE
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+test.t2	optimize	status	OK
+test.t3	optimize	status	OK
+test.v1	optimize	error	'test.v1' is not BASE TABLE
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Table is already up to date
+test.t2	analyze	status	Table is already up to date
+test.t3	analyze	status	Table is already up to date
+test.v1	analyze	error	'test.v1' is not BASE TABLE
+Warnings:
+Error	1347	'test.v1' is not BASE TABLE
+call bug13012()|
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+test.t2	repair	status	OK
+test.t3	repair	status	OK
+test.v1	repair	error	'test.v1' is not BASE TABLE
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+test.t2	optimize	status	OK
+test.t3	optimize	status	OK
+test.v1	optimize	error	'test.v1' is not BASE TABLE
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Table is already up to date
+test.t2	analyze	status	Table is already up to date
+test.t3	analyze	status	Table is already up to date
+test.v1	analyze	error	'test.v1' is not BASE TABLE
+Warnings:
+Error	1347	'test.v1' is not BASE TABLE
+call bug13012()|
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+test.t2	repair	status	OK
+test.t3	repair	status	OK
+test.v1	repair	error	'test.v1' is not BASE TABLE
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+test.t2	optimize	status	OK
+test.t3	optimize	status	OK
+test.v1	optimize	error	'test.v1' is not BASE TABLE
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Table is already up to date
+test.t2	analyze	status	Table is already up to date
+test.t3	analyze	status	Table is already up to date
+test.v1	analyze	error	'test.v1' is not BASE TABLE
+Warnings:
+Error	1347	'test.v1' is not BASE TABLE
+drop procedure bug13012|
+drop view v1;
+select * from t1|
+id	data
+aa	0
+aa	1
+aa	2
+aa	3
+aa	4
+aa	5
+aa	6
+aa	7
+aa	8
+aa	9
+drop schema if exists mysqltest1|
+Warnings:
+Note	1008	Can't drop database 'mysqltest1'; database doesn't exist
+drop schema if exists mysqltest2|
+Warnings:
+Note	1008	Can't drop database 'mysqltest2'; database doesn't exist
+drop schema if exists mysqltest3|
+Warnings:
+Note	1008	Can't drop database 'mysqltest3'; database doesn't exist
+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|
+call mysqltest1.p1(@rs)|
+ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2
+call mysqltest1.p1(@rs)|
+ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2
+call mysqltest1.p1(@rs)|
+ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2
+drop schema if exists mysqltest1|
+drop schema if exists mysqltest2|
+drop schema if exists mysqltest3|
+use test|
+drop table if exists t3|
+drop procedure if exists bug15441|
+create table t3 (id int not null primary key, county varchar(25))|
+insert into t3 (id, county) values (1, 'York')|
+create procedure bug15441(c varchar(25))
+begin
+update t3 set id=2, county=values(c);
+end|
+call bug15441('county')|
+ERROR 42S22: Unknown column 'c' in 'field list'
+drop procedure bug15441|
+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')|
+id	county
+1	Yale
+id	county
+2	NULL
+drop table t3|
+drop procedure bug15441|
+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|
+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()|
+Handler
+error
+End
+done
+call bug14498_2()|
+Handler
+error
+End
+done
+call bug14498_3()|
+v
+maybe
+Handler
+error
+End
+done
+call bug14498_4()|
+Handler
+error
+End
+done
+call bug14498_5()|
+Handler
+error
+End
+done
+drop procedure bug14498_1|
+drop procedure bug14498_2|
+drop procedure bug14498_3|
+drop procedure bug14498_4|
+drop procedure bug14498_5|
+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|
+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()|
+1
+Before NOT FOUND condition is triggered
+2
+After NOT FOUND condtition is triggered
+xid	xdone
+1	0
+Warnings:
+Warning	1329	No data - zero rows fetched, selected, or processed
+call bug15231_3()|
+Result
+Missed it (correct)
+Warnings:
+Warning	1366	Incorrect decimal value: 'zap' for column 'x' at row 1
+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|
+drop procedure if exists bug15011|
+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()|
+Handler
+Inner
+drop procedure bug15011|
+drop table t3|
+drop table t1,t2;
diff --git a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result
new file mode 100644
index 00000000000..d434f5c32ce
--- /dev/null
+++ b/mysql-test/r/sp_notembedded.result
@@ -0,0 +1,206 @@
+drop procedure if exists bug4902|
+create procedure bug4902()
+begin
+show grants for 'root'@'localhost';
+end|
+call bug4902()|
+Grants for root@localhost
+GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
+call bug4902()|
+Grants for root@localhost
+GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
+drop procedure bug4902|
+drop procedure if exists bug4902_2|
+create procedure bug4902_2()
+begin
+show processlist;
+end|
+call bug4902_2()|
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	test	Query	#	NULL	show processlist
+call bug4902_2()|
+Id	User	Host	db	Command	Time	State	Info
+#	root	localhost	test	Query	#	NULL	show processlist
+drop procedure bug4902_2|
+drop function if exists bug5278|
+create function bug5278 () returns char
+begin
+SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
+return 'okay';
+end|
+select bug5278()|
+ERROR 42000: Can't find any matching row in the user table
+select bug5278()|
+ERROR 42000: Can't find any matching row in the user table
+drop function bug5278|
+drop table if exists t1|
+create table t1 (
+id   char(16) not null default '',
+data int not null
+)|
+drop procedure if exists bug3583|
+drop procedure if exists bug3583|
+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'|
+Variable_name	Value
+Qcache_hits	0
+call bug3583()|
+id	data
+x	3
+y	5
+c
+2
+show status like 'Qcache_hits'|
+Variable_name	Value
+Qcache_hits	0
+call bug3583()|
+id	data
+x	3
+y	5
+c
+2
+call bug3583()|
+id	data
+x	3
+y	5
+c
+2
+show status like 'Qcache_hits'|
+Variable_name	Value
+Qcache_hits	2
+set global query_cache_size = @x|
+flush status|
+flush query cache|
+delete from t1|
+drop procedure bug3583|
+drop table t1;
+#|
+drop procedure if exists bug6807|
+create procedure bug6807()
+begin
+declare id int;
+set id = connection_id();
+kill query id;
+select 'Not reached';
+end|
+call bug6807()|
+ERROR 70100: Query execution was interrupted
+call bug6807()|
+ERROR 70100: Query execution was interrupted
+drop procedure bug6807|
+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|
+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|
+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|
+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|
+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|
+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=255|
+set @var=1|
+call bug10100p(255, @var)|
+call bug10100pt(1,255)|
+call bug10100pv(1,255)|
+call bug10100pd(1,255)|
+call bug10100pc(1,255)|
+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|
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 33b12c05f98..48b8625d839 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -2987,7 +2987,6 @@ select * from (select max(fld) from t1) as foo;
 max(fld)
 1
 drop table t1;
-purge master logs before (select adddate(current_timestamp(), interval -4 day));
 CREATE TABLE t1 (a int, b int);
 CREATE TABLE t2 (c int, d int);
 CREATE TABLE t3 (e int);
diff --git a/mysql-test/r/subselect_notembedded.result b/mysql-test/r/subselect_notembedded.result
new file mode 100644
index 00000000000..dd4b0701c32
--- /dev/null
+++ b/mysql-test/r/subselect_notembedded.result
@@ -0,0 +1 @@
+purge master logs before (select adddate(current_timestamp(), interval -4 day));
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index e209e393d00..adf92909709 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2213,15 +2213,6 @@ r_object_id	users_names
 120001a080000542	guser02
 drop view v1, v2;
 drop table t1, t2;
-create definer=some_user@`` sql security invoker view v1 as select 1;
-ERROR HY000: Definer is not fully qualified
-create definer=some_user@localhost sql security invoker view v1 as select 1;
-Warnings:
-Note	1449	There is no 'some_user'@'localhost' registered
-show create view v1;
-View	Create View
-v1	CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1`
-drop view v1;
 create table t1 (s1 int);
 create view abc as select * from t1 as abc;
 drop table t1;
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result
index b20990e95a4..46fb4698838 100644
--- a/mysql-test/r/view_grant.result
+++ b/mysql-test/r/view_grant.result
@@ -519,3 +519,12 @@ use test;
 use test;
 drop user mysqltest_1@localhost;
 drop database mysqltest;
+create definer=some_user@`` sql security invoker view v1 as select 1;
+ERROR HY000: Definer is not fully qualified
+create definer=some_user@localhost sql security invoker view v1 as select 1;
+Warnings:
+Note	1449	There is no 'some_user'@'localhost' registered
+show create view v1;
+View	Create View
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`some_user`@`localhost` SQL SECURITY INVOKER VIEW `v1` AS select 1 AS `1`
+drop view v1;