Merge mysql.com:/usr/local/bk/mysql-5.0

into mysql.com:/home/pem/work/mysql-5.0

3 files changed, 697 insertions, 644 deletions

diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 2ee912aaea7..a04e178263b 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1250,6 +1250,299 @@ drop view v1|
 drop view v2|
 delete from t1 |
 delete from t2 |
+drop table if exists fac|
+create table fac (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.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|
+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 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)|
+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.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;
+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.primes 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 `test`.`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;
+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 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|
+show procedure status like '%p%'|
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+drop table if exists fib|
+create table fib ( f bigint unsigned not null )|
+insert into fib values (1), (1)|
+drop procedure if exists fib|
+create procedure fib(n int unsigned)
+begin
+if n > 0 then
+begin
+declare x, y bigint unsigned;
+declare c cursor for select f from fib order by f desc limit 2;
+open c;
+fetch c into y;
+fetch c into x;
+close c;
+insert into fib values (x+y);
+call fib(n-1);
+end;
+end if;
+end|
+call fib(20)|
+select * from fib order by f asc|
+f
+1
+1
+2
+3
+5
+8
+13
+21
+34
+55
+89
+144
+233
+377
+610
+987
+1597
+2584
+4181
+6765
+10946
+17711
+drop table fib|
+drop procedure fib|
+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 `test`.`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|
+Warnings:
+Note	1305	FUNCTION t1max does not exist
+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|
+drop table if exists t3|
+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 procedure if exists bug822|
 create procedure bug822(a_id char(16), a_data int)
 begin
@@ -1259,6 +1552,7 @@ 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)|
@@ -2364,299 +2658,6 @@ 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 table if exists fac|
-create table fac (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.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|
-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 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)|
-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.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;
-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.primes 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 `test`.`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;
-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 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|
-show procedure status like '%p%'|
-Db	Name	Type	Definer	Modified	Created	Security_type	Comment
-drop table if exists fib|
-create table fib ( f bigint unsigned not null )|
-insert into fib values (1), (1)|
-drop procedure if exists fib|
-create procedure fib(n int unsigned)
-begin
-if n > 0 then
-begin
-declare x, y bigint unsigned;
-declare c cursor for select f from fib order by f desc limit 2;
-open c;
-fetch c into y;
-fetch c into x;
-close c;
-insert into fib values (x+y);
-call fib(n-1);
-end;
-end if;
-end|
-call fib(20)|
-select * from fib order by f asc|
-f
-1
-1
-2
-3
-5
-8
-13
-21
-34
-55
-89
-144
-233
-377
-610
-987
-1597
-2584
-4181
-6765
-10946
-17711
-drop table fib|
-drop procedure fib|
-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 `test`.`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|
-Warnings:
-Note	1305	FUNCTION t1max does not exist
-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|
-drop table if exists t3|
-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 function if exists bug5240|
 create function bug5240 () returns int
 begin
@@ -2696,43 +2697,62 @@ call bug7992()|
 call bug7992()|
 drop procedure bug7992|
 drop table t3|
-drop table t1;
-drop table t2;
-CREATE TABLE t1 (
-lpitnumber int(11) default NULL,
-lrecordtype int(11) default NULL
-);
-CREATE TABLE t2 (
-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 t3 (
-lbsiid int(11) NOT NULL default '0',
-ltradingareaid int(11) NOT NULL default '0',
-PRIMARY KEY  (lbsiid,ltradingareaid)
-);
-CREATE PROCEDURE bug8849()
+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 t3
+insert into t5
 (
-t3.lbsiid,
-t3.ltradingareaid
+t5.lbsiid,
+t5.ltradingareaid
 )
-select distinct t1.lpitnumber, t2.ltradingareaid
+select distinct t3.lpitnumber, t4.ltradingareaid
 from
-t2 join t1 on
-t1.lpitnumber = t2.lbsiid
-and t1.lrecordtype = 1
-left join t2 as price01 on
-price01.lbsiid = t2.lbsiid and
+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
-t2.ltradingareaid = price01.ltradingareaid;
+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|
-call bug8849();
-call bug8849();
-call bug8849();
-drop procedure bug8849;
-drop tables t1,t2,t3;
+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.2000
+drop procedure bug8937|
+delete from t1|
+drop table t1,t2;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 3c98e5ca4ba..c120e7e94b7 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -1474,6 +1474,285 @@ delete from t2 |
 
 
 #
+# Some "real" examples
+#
+
+# fac
+
+--disable_warnings
+drop table if exists fac|
+--enable_warnings
+create table fac (n int unsigned not null primary key, f bigint unsigned)|
+
+--disable_warnings
+drop procedure if exists ifac|
+--enable_warnings
+create procedure ifac(n int unsigned)
+begin
+  declare i int unsigned default 1;
+
+  if n > 20 then
+    set n = 20;		# bigint overflow otherwise
+  end if;
+  while i <= n do
+    begin
+      insert into test.fac values (i, fac(i));
+      set i = i + 1;
+    end;
+  end while;
+end|
+
+call ifac(20)|
+select * from fac|
+drop table fac|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show function status like '%f%'|
+drop procedure ifac|
+drop function fac|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show function status like '%f%'|
+
+
+# primes
+
+--disable_warnings
+drop table if exists primes|
+--enable_warnings
+
+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)|
+
+--disable_warnings
+drop procedure if exists opp|
+--enable_warnings
+create procedure opp(n bigint unsigned, out pp bool)
+begin
+  declare r double;
+  declare b, s bigint unsigned default 0;
+
+  set r = sqrt(n);
+
+ again:
+  loop
+    if s = 45 then
+      set b = b+200, s = 0;
+    else
+      begin
+        declare p bigint unsigned;
+
+        select t.p into p from test.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;
+end|
+
+--disable_warnings
+drop procedure if exists ip|
+--enable_warnings
+create procedure ip(m int unsigned)
+begin
+  declare p bigint unsigned;
+  declare i int unsigned;
+
+  set i=45, p=201;
+
+  while i < m do
+    begin
+      declare pp bool default 0;
+
+      call opp(p, pp);
+      if pp then
+        insert into test.primes values (i, p);
+        set i = i+1;
+      end if;
+      set p = p+2;
+    end;
+  end while;
+end|
+show create procedure opp|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like '%p%'|
+
+# This isn't the fastest way in the world to compute prime numbers, so
+# don't be too ambitious. ;-)
+call ip(200)|
+# We don't want to select the entire table here, just pick a few
+# examples.
+# The expected result is:
+#    i      p
+#   ---   ----
+#    45    211
+#   100    557
+#   199   1229
+select * from primes where i=45 or i=100 or i=199|
+drop table primes|
+drop procedure opp|
+drop procedure ip|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like '%p%'|
+
+
+# Fibonacci, for recursion test. (Yet Another Numerical series :)
+
+--disable_warnings
+drop table if exists fib|
+--enable_warnings
+create table fib ( f bigint unsigned not null )|
+
+insert into fib values (1), (1)|
+
+# We deliberately do it the awkward way, fetching the last two
+# values from the table, in order to exercise various statements
+# and table accesses at each turn.
+--disable_warnings
+drop procedure if exists fib|
+--enable_warnings
+create procedure fib(n int unsigned)
+begin
+  if n > 0 then
+    begin
+      declare x, y bigint unsigned;
+      declare c cursor for select f from fib order by f desc limit 2;
+
+      open c;
+      fetch c into y;
+      fetch c into x;
+      close c;
+      insert into fib values (x+y);
+      call fib(n-1);
+    end;
+  end if;
+end|
+
+call fib(20)|
+
+select * from fib order by f asc|
+drop table fib|
+drop procedure fib|
+
+
+#
+# Comment & suid
+#
+
+--disable_warnings
+drop procedure if exists bar|
+--enable_warnings
+create procedure bar(x char(16), y int)
+ comment "111111111111" sql security invoker
+ insert into test.t1 values (x, y)|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like 'bar'|
+alter procedure bar comment "2222222222" sql security definer|
+alter procedure bar comment "3333333333"|
+alter procedure bar|
+show create procedure bar|
+--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status like 'bar'|
+drop procedure bar|
+
+#
+# rexecution
+#
+--disable_warnings
+drop procedure if exists p1|
+--enable_warnings
+create procedure p1 ()
+  select (select s1 from t3) from t3|
+
+create table t3 (s1 int)|
+
+call p1()|
+insert into t3 values (1)|
+call p1()|
+drop procedure p1|
+drop table t3|
+
+#
+# backticks
+#
+--disable_warnings
+drop function if exists foo|
+--enable_warnings
+create function `foo` () returns int
+  return 5|
+select `foo` ()|
+drop function `foo`|
+
+#
+# Implicit LOCK/UNLOCK TABLES for table access in functions
+#
+
+--disable_warning
+drop function if exists t1max|
+--enable_warnings
+create function t1max() returns int
+begin
+  declare x int;
+  select max(data) into x from t1;
+  return x;
+end|
+
+insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)|
+select t1max()|
+drop function t1max|
+
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+create table t3 (
+  v char(16) not null primary key,
+  c int unsigned not null
+)|
+
+create function getcount(s char(16)) returns int
+begin
+  declare x int;
+
+  select count(*) into x from t3 where v = s;
+  if x = 0 then
+    insert into t3 values (s, 1);
+  else
+    update t3 set c = c+1 where v = s;
+  end if;
+  return x;
+end|
+
+select * from t1 where data = getcount("bar")|
+select * from t3|
+select getcount("zip")|
+select getcount("zip")|
+select * from t3|
+select getcount(id) from t1 where data = 3|
+select getcount(id) from t1 where data = 5|
+select * from t3|
+drop table t3|
+drop function getcount|
+
+
+#
 # Test cases for old bugs
 #
 
@@ -1492,6 +1771,7 @@ begin
   end if;
 end|
 
+delete from t1|
 call bug822('foo', 42)|
 call bug822('foo', 42)|
 call bug822('bar', 666)|
@@ -2931,284 +3211,6 @@ drop procedure bug8762|
 
 
 #
-# Some "real" examples
-#
-
-# fac
-
---disable_warnings
-drop table if exists fac|
---enable_warnings
-create table fac (n int unsigned not null primary key, f bigint unsigned)|
-
---disable_warnings
-drop procedure if exists ifac|
---enable_warnings
-create procedure ifac(n int unsigned)
-begin
-  declare i int unsigned default 1;
-
-  if n > 20 then
-    set n = 20;		# bigint overflow otherwise
-  end if;
-  while i <= n do
-    begin
-      insert into test.fac values (i, fac(i));
-      set i = i + 1;
-    end;
-  end while;
-end|
-
-call ifac(20)|
-select * from fac|
-drop table fac|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-show function status like '%f%'|
-drop procedure ifac|
-drop function fac|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-show function status like '%f%'|
-
-
-# primes
-
---disable_warnings
-drop table if exists primes|
---enable_warnings
-
-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)|
-
---disable_warnings
-drop procedure if exists opp|
---enable_warnings
-create procedure opp(n bigint unsigned, out pp bool)
-begin
-  declare r double;
-  declare b, s bigint unsigned default 0;
-
-  set r = sqrt(n);
-
- again:
-  loop
-    if s = 45 then
-      set b = b+200, s = 0;
-    else
-      begin
-        declare p bigint unsigned;
-
-        select t.p into p from test.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;
-end|
-
---disable_warnings
-drop procedure if exists ip|
---enable_warnings
-create procedure ip(m int unsigned)
-begin
-  declare p bigint unsigned;
-  declare i int unsigned;
-
-  set i=45, p=201;
-
-  while i < m do
-    begin
-      declare pp bool default 0;
-
-      call opp(p, pp);
-      if pp then
-        insert into test.primes values (i, p);
-        set i = i+1;
-      end if;
-      set p = p+2;
-    end;
-  end while;
-end|
-show create procedure opp|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-show procedure status like '%p%'|
-
-# This isn't the fastest way in the world to compute prime numbers, so
-# don't be too ambitious. ;-)
-call ip(200)|
-# We don't want to select the entire table here, just pick a few
-# examples.
-# The expected result is:
-#    i      p
-#   ---   ----
-#    45    211
-#   100    557
-#   199   1229
-select * from primes where i=45 or i=100 or i=199|
-drop table primes|
-drop procedure opp|
-drop procedure ip|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-show procedure status like '%p%'|
-
-
-# Fibonacci, for recursion test. (Yet Another Numerical series :)
-
---disable_warnings
-drop table if exists fib|
---enable_warnings
-create table fib ( f bigint unsigned not null )|
-
-insert into fib values (1), (1)|
-
-# We deliberately do it the awkward way, fetching the last two
-# values from the table, in order to exercise various statements
-# and table accesses at each turn.
---disable_warnings
-drop procedure if exists fib|
---enable_warnings
-create procedure fib(n int unsigned)
-begin
-  if n > 0 then
-    begin
-      declare x, y bigint unsigned;
-      declare c cursor for select f from fib order by f desc limit 2;
-
-      open c;
-      fetch c into y;
-      fetch c into x;
-      close c;
-      insert into fib values (x+y);
-      call fib(n-1);
-    end;
-  end if;
-end|
-
-call fib(20)|
-
-select * from fib order by f asc|
-drop table fib|
-drop procedure fib|
-
-
-#
-# Comment & suid
-#
-
---disable_warnings
-drop procedure if exists bar|
---enable_warnings
-create procedure bar(x char(16), y int)
- comment "111111111111" sql security invoker
- insert into test.t1 values (x, y)|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-show procedure status like 'bar'|
-alter procedure bar comment "2222222222" sql security definer|
-alter procedure bar comment "3333333333"|
-alter procedure bar|
-show create procedure bar|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-show procedure status like 'bar'|
-drop procedure bar|
-
-#
-# rexecution
-#
---disable_warnings
-drop procedure if exists p1|
---enable_warnings
-create procedure p1 ()
-  select (select s1 from t3) from t3|
-
-create table t3 (s1 int)|
-
-call p1()|
-insert into t3 values (1)|
-call p1()|
-drop procedure p1|
-drop table t3|
-
-#
-# backticks
-#
---disable_warnings
-drop function if exists foo|
---enable_warnings
-create function `foo` () returns int
-  return 5|
-select `foo` ()|
-drop function `foo`|
-
-#
-# Implicit LOCK/UNLOCK TABLES for table access in functions
-#
-
---disable_warning
-drop function if exists t1max|
---enable_warnings
-create function t1max() returns int
-begin
-  declare x int;
-  select max(data) into x from t1;
-  return x;
-end|
-
-insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)|
-select t1max()|
-drop function t1max|
-
---disable_warnings
-drop table if exists t3|
---enable_warnings
-create table t3 (
-  v char(16) not null primary key,
-  c int unsigned not null
-)|
-
-create function getcount(s char(16)) returns int
-begin
-  declare x int;
-
-  select count(*) into x from t3 where v = s;
-  if x = 0 then
-    insert into t3 values (s, 1);
-  else
-    update t3 set c = c+1 where v = s;
-  end if;
-  return x;
-end|
-
-select * from t1 where data = getcount("bar")|
-select * from t3|
-select getcount("zip")|
-select getcount("zip")|
-select * from t3|
-select getcount(id) from t1 where data = 3|
-select getcount(id) from t1 where data = 5|
-select * from t3|
-drop table t3|
-drop function getcount|
-
-#
 # BUG#5240: Stored procedure crash if function has cursor declaration
 #
 # The following test case fails in --ps-protocol mode due to some bugs
@@ -3275,56 +3277,85 @@ call bug7992()|
 drop procedure bug7992|
 drop table t3|
 
-delimiter ;|
-drop table t1;
-drop table t2;
 
 #
-# Bug#8849: rolling back changes to AND/OR structure of ON and WHERE clauses 
-# in SP
+# BUG#8849: problem with insert statement with table alias's
+#
+# Rolling back changes to AND/OR structure of ON and WHERE clauses  in SP
 # 
 
-CREATE TABLE t1 (
-  lpitnumber int(11) default NULL,
-  lrecordtype int(11) default NULL
-);
+create table t3 (
+  lpitnumber int(11) default null,
+  lrecordtype int(11) default null
+)|
 
-CREATE TABLE t2 (
-  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 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 t3 (
-  lbsiid int(11) NOT NULL default '0',
-  ltradingareaid int(11) NOT NULL default '0',
-  PRIMARY KEY  (lbsiid,ltradingareaid)
-);
+create table t5 (
+  lbsiid int(11) not null default '0',
+  ltradingareaid int(11) not null default '0',
+  primary key  (lbsiid,ltradingareaid)
+)|
 
-delimiter |;
-CREATE PROCEDURE bug8849()
+--disable_warnings
+drop procedure if exists bug8849|
+--enable_warnings
+create procedure bug8849()
 begin
-  insert into t3
+  insert into t5
   (
-   t3.lbsiid,
-   t3.ltradingareaid
+   t5.lbsiid,
+   t5.ltradingareaid
   )
-  select distinct t1.lpitnumber, t2.ltradingareaid
+  select distinct t3.lpitnumber, t4.ltradingareaid
   from
-    t2 join t1 on
-      t1.lpitnumber = t2.lbsiid
-      and t1.lrecordtype = 1
-    left join t2 as price01 on
-      price01.lbsiid = t2.lbsiid and
+    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
-      t2.ltradingareaid = price01.ltradingareaid;
+      t4.ltradingareaid = price01.ltradingareaid;
 end|
-delimiter ;|
 
-call bug8849();
-call bug8849();
-call bug8849();
-drop procedure bug8849;
-drop tables t1,t2,t3;
+call bug8849()|
+call bug8849()|
+call bug8849()|
+drop procedure bug8849|
+drop tables t3,t4,t5|
+
+#
+# BUG#8937: Stored Procedure: AVG() works as SUM() in SELECT ... INTO statement
+#
+--disable_warnings
+drop procedure if exists bug8937|
+--enable_warnings
+create procedure bug8937()
+begin
+  declare s,x,y,z int;
+  declare a float;
+
+  select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1;
+  select s,x,y,z;
+  select avg(data) into a from t1;
+  select a;
+end|
+
+delete from t1|
+insert into t1 (data) values (1), (2), (3), (4), (6)|
+call bug8937()|
+
+drop procedure bug8937|
+delete from t1|
+
+
+# Add bugs above this line. Use existing tables t1 and t2 when
+# practical, or create table t3, t3 etc temporarily (and drop them).
+delimiter ;|
+drop table t1,t2;
diff --git a/sql/item_sum.h b/sql/item_sum.h
index fead45f1881..af2710d7800 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -356,6 +356,8 @@ public:
   void clear();
   bool add();
   double val_real();
+  // In SPs we might force the "wrong" type with select into a declare variable
+  longlong val_int() { return (longlong)val_real(); }
   my_decimal *val_decimal(my_decimal *);
   String *val_str(String *str);
   void reset_field();