Merged 4.1 -> 5.0.

7 files changed, 587 insertions, 2 deletions

diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result
index b7243ac5d0b..8c2e2d1def7 100644
--- a/mysql-test/r/connect.result
+++ b/mysql-test/r/connect.result
@@ -7,6 +7,7 @@ help_category
 help_relation
 help_topic
 host
+proc
 tables_priv
 user
 show tables;
@@ -22,6 +23,7 @@ help_category
 help_relation
 help_topic
 host
+proc
 tables_priv
 user
 show tables;
@@ -37,6 +39,7 @@ help_category
 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 de652b05154..c5e7ca154df 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..ee99e871c3e
--- /dev/null
+++ b/mysql-test/r/sp-error.result
@@ -0,0 +1,70 @@
+delete from mysql.proc;
+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
+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
+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(x int)
+select * from test.t1;
+SELECT in a stored procedure must have INTO
+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
+drop procedure proc1;
+drop function func1;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
new file mode 100644
index 00000000000..b188a6ed57d
--- /dev/null
+++ b/mysql-test/r/sp.result
@@ -0,0 +1,497 @@
+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 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 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;
+set y = 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;
+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;
+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.t2 select * from test.t1;
+insert into test.t2 values (concat(x, "2"), y+2);
+end;
+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;
+set f = 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
+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;
+set i = 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;
+set b = 0, s = 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;
+set pp = 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/subselect.result b/mysql-test/r/subselect.result
index 795348f1897..fc434cd6249 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -593,7 +593,6 @@ x
 3
 3
 INSERT INTO t1 (x) select (SELECT SUM(x)+2 FROM t1) FROM t2;
-You can't specify target table 't1' for update in FROM clause
 INSERT DELAYED INTO t1 (x) VALUES ((SELECT SUM(x) FROM t2));
 select * from t1;
 x
@@ -601,6 +600,8 @@ x
 2
 3
 3
+11
+11
 0
 drop table t1, t2, t3;
 CREATE TABLE t1 (x int not null, y int, primary key (x));