7 files changed, 699 insertions, 5 deletions

diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result
index 9c848c3434f..49ca53e82c3 100644
--- a/mysql-test/r/connect.result
+++ b/mysql-test/r/connect.result
@@ -8,6 +8,7 @@ help_keyword
 help_relation
 help_topic
 host
+proc
 tables_priv
 user
 show tables;
@@ -24,6 +25,7 @@ help_keyword
 help_relation
 help_topic
 host
+proc
 tables_priv
 user
 show tables;
@@ -40,6 +42,7 @@ help_keyword
 help_relation
 help_topic
 host
+proc
 tables_priv
 user
 show tables;
diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result
index ebd34dd7668..3be04584749 100644
--- a/mysql-test/r/insert.result
+++ b/mysql-test/r/insert.result
@@ -64,3 +64,16 @@ use test_$1;
 create table t1 (c int);
 insert into test_$1.t1 set test_$1.t1.c = '1';
 drop database test_$1;
+use test;
+drop table if exists t1,t2,t3;
+create table t1(id1 int not null auto_increment primary key, t char(12));
+create table t2(id2 int not null, t char(12));
+create table t3(id3 int not null, t char(12), index(id3));
+select count(*) from t2;
+count(*)
+500
+insert into  t2 select t1.* from t1, t2 t, t3 where  t1.id1 = t.id2 and t.id2 = t3.id3;
+select count(*) from t2;
+count(*)
+25500
+drop table if exists t1,t2,t3;
diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
index 201d1b541ae..c9ea5c6661c 100644
--- a/mysql-test/r/show_check.result
+++ b/mysql-test/r/show_check.result
@@ -127,6 +127,7 @@ insert into t1 values (1);
 show open tables;
 Database	Table	In_use	Name_locked
 test	t1	0	0
+mysql	proc	0	0
 drop table t1;
 create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" TYPE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed;
 show create table t1;
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
new file mode 100644
index 00000000000..0c1db1703ac
--- /dev/null
+++ b/mysql-test/r/sp-error.result
@@ -0,0 +1,98 @@
+delete from mysql.proc;
+create procedure syntaxerror(t int);
+You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
+create procedure syntaxerror(t int);
+You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
+create procedure syntaxerror(t int);
+You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
+create procedure proc1()
+set @x = 42;
+create function func1() returns int
+return 42;
+create procedure foo()
+create procedure bar() set @x=3;
+Can't create a PROCEDURE from within another stored routine
+create procedure foo()
+create function bar() returns double return 2.3;
+Can't create a FUNCTION from within another stored routine
+create procedure proc1()
+set @x = 42;
+PROCEDURE proc1 already exists
+create function func1() returns int
+return 42;
+FUNCTION func1 already exists
+drop procedure proc1;
+drop function func1;
+alter procedure foo;
+PROCEDURE foo does not exist
+alter function foo;
+FUNCTION foo does not exist
+drop procedure foo;
+PROCEDURE foo does not exist
+drop function foo;
+FUNCTION foo does not exist
+call foo();
+PROCEDURE foo does not exist
+drop procedure if exists foo;
+Warnings:
+Warning	1261	PROCEDURE foo does not exist
+create procedure foo()
+foo: loop
+leave bar;
+end loop;
+LEAVE with no matching label: bar
+create procedure foo()
+foo: loop
+iterate bar;
+end loop;
+ITERATE with no matching label: bar
+create procedure foo()
+foo: loop
+foo: loop
+set @x=2;
+end loop foo;
+end loop foo;
+Redefining label foo
+create procedure foo()
+foo: loop
+set @x=2;
+end loop bar;
+End-label bar without match
+create procedure foo(out x int)
+begin
+declare y int;
+set x = y;
+end;
+Referring to uninitialized variable y
+create procedure foo()
+begin
+select name from mysql.proc;
+select type from mysql.proc;
+end;
+call foo();
+SELECT in a stored procedure must have INTO
+drop procedure foo;
+create procedure foo()
+return 42;
+RETURN is only allowed in a FUNCTION
+create function foo() returns int
+begin
+declare x int;
+select max(c) into x from test.t;
+return x;
+end;
+Queries, like SELECT, INSERT, UPDATE (and others), are not allowed in a FUNCTION
+create procedure p(x int)
+insert into test.t1 values (x);
+create function f(x int) returns int
+return x+42;
+call p();
+Wrong number of arguments for PROCEDURE p, expected 1, got 0
+call p(1, 2);
+Wrong number of arguments for PROCEDURE p, expected 1, got 2
+select f();
+Wrong number of arguments for FUNCTION f, expected 1, got 0
+select f(1, 2);
+Wrong number of arguments for FUNCTION f, expected 1, got 2
+drop procedure p;
+drop function f;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
new file mode 100644
index 00000000000..c08d252cfe3
--- /dev/null
+++ b/mysql-test/r/sp.result
@@ -0,0 +1,555 @@
+use test;
+drop table if exists t1;
+drop table if exists t2;
+create table t1 (
+id   char(16) not null,
+data int not null
+);
+create table t2 (
+s   char(16) not null,
+i   int not null,
+d   double not null
+);
+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;
+create procedure u()
+use sptmp;
+drop database if exists sptmp;
+create database sptmp;
+use test;
+call u();
+select database();
+database()
+test
+drop database sptmp;
+drop procedure u;
+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;
+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;
+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;
+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;
+create procedure zip(x char(16), y int)
+begin
+declare z int;
+call zap(y, z);
+call bar(x, z);
+end;
+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 zap;
+drop procedure bar;
+create procedure c1(x int)
+call c2("c", x);
+create procedure c2(s char(16), x int)
+call c3(x, s);
+create procedure c3(x int, s char(16))
+call c4("level", x, s);
+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;
+create procedure iotest(x1 char(16), x2 char(16), y int)
+begin
+call inc2(x2, y);
+insert into test.t1 values (x1, y);
+end;
+create procedure inc2(x char(16), y int)
+begin
+call inc(y);
+insert into test.t1 values (x, y);
+end;
+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 inc;
+create procedure cbv1()
+begin
+declare y int default 3;
+call cbv2(y+1, y);
+insert into test.t1 values ("cbv1", y);
+end;
+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);
+create procedure sub1(id char(16), x int)
+insert into test.t1 values (id, x);
+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));
+call sub1("sub1d", (select 1 from (select 1) a));
+select * from t1;
+id	data
+sub1a	7
+sub1b	3
+sub1c	1
+sub1d	1
+select sub3((select max(i) from t2));
+sub3((select max(i) from t2))
+4
+drop procedure sub1;
+drop function sub3;
+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
+sub1a	7
+sub1b	3
+sub1c	1
+sub1d	1
+a0	2
+a0	1
+a0	0
+delete from t1;
+drop procedure a0;
+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;
+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;
+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;
+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;
+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 hmm;
+call d(3);
+select * from t1;
+id	data
+d	3
+delete from t1;
+drop procedure d;
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+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;
+create procedure create_select(x char(16), y int)
+begin
+insert into test.t1 values (x, y);
+create table test.t3 select * from test.t1;
+insert into test.t3 values (concat(x, "2"), y+2);
+end;
+drop table if exists t3;
+call create_select("cs", 90);
+select * from t1, t3;
+id	data	id	data
+cs	90	cs	90
+cs	90	cs2	92
+drop table if exists t3;
+delete from t1;
+drop procedure create_select;
+create function e() returns double
+return 2.7182818284590452354;
+set @e = e();
+select e(), @e;
+e()	@e
+2.718281828459	2.718281828459
+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
+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
+create function append(s1 char(8), s2 char(8)) returns char(16)
+return concat(s1, s2);
+select append("foo", "bar");
+append("foo", "bar")
+foobar
+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
+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
+a	1	1.1
+b	2	1.2
+c	3	1.3
+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 table if exists fac;
+create table fac (n int unsigned not null primary key, f bigint unsigned);
+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.fac values (i, fac(i));
+set i = i + 1;
+end;
+end while;
+end;
+call ifac(20);
+select * from fac;
+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 fac;
+drop procedure ifac;
+drop function fac;
+drop table if exists primes;
+create table primes (
+i int unsigned not null primary key,
+p bigint unsigned not null
+);
+insert into primes 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);
+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.primes 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 again;
+end;
+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.primes values (i, p);
+set i = i+1;
+end if;
+set p = p+2;
+end;
+end while;
+end;
+call ip(200);
+select * from primes where i=45 or i=100 or i=199;
+i	p
+45	211
+100	557
+199	1229
+drop table primes;
+drop procedure opp;
+drop procedure ip;
+drop table t1;
+drop table t2;
diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result
index 3ef6cec32b3..f93147d00c5 100644
--- a/mysql-test/r/status.result
+++ b/mysql-test/r/status.result
@@ -14,6 +14,6 @@ update t1 set n = 3;
 unlock tables;
 show status like 'Table_lock%';
 Variable_name	Value
-Table_locks_immediate	3
+Table_locks_immediate	4
 Table_locks_waited	1
 drop table t1;
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index 1a773acd23e..6588e8dd4a0 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -1,8 +1,30 @@
 drop table if exists t1,t2;
-set @`test`=1,@TEST=3,@select=2,@t5=1.23456;
-select @test,@`select`,@TEST,@not_used;
-@test	@`select`	@TEST	@not_used
-1	2	3	NULL
+set @`test`=1;
+select @test, @`test`, @TEST, @`TEST`, @"teSt";
+@test	@`test`	@TEST	@`TEST`	@"teSt"
+1	1	1	1	1
+set @TEST=2;
+select @test, @`test`, @TEST, @`TEST`, @"teSt";
+@test	@`test`	@TEST	@`TEST`	@"teSt"
+2	2	2	2	2
+set @"tEST"=3;
+select @test, @`test`, @TEST, @`TEST`, @"teSt";
+@test	@`test`	@TEST	@`TEST`	@"teSt"
+3	3	3	3	3
+set @`TeST`=4;
+select @test, @`test`, @TEST, @`TEST`, @"teSt";
+@test	@`test`	@TEST	@`TEST`	@"teSt"
+4	4	4	4	4
+select @`teST`:=5;
+@`teST`:=5
+5
+select @test, @`test`, @TEST, @`TEST`, @"teSt";
+@test	@`test`	@TEST	@`TEST`	@"teSt"
+5	5	5	5	5
+set @select=2,@t5=1.23456;
+select @`select`,@not_used;
+@`select`	@not_used
+2	NULL
 set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL;
 select @test_int,@test_double,@test_string,@test_string2,@select;
 @test_int	@test_double	@test_string	@test_string2	@select
@@ -286,6 +308,8 @@ set sql_buffer_result=1;
 set sql_log_bin=1;
 set sql_log_off=1;
 set sql_log_update=1;
+Warnings:
+Note	1271	The update log is deprecated and replaced by the binary log. SET SQL_LOG_UPDATE has been ignored.
 set sql_low_priority_updates=1;
 set sql_max_join_size=200;
 select @@sql_max_join_size,@@max_join_size;