Fixed BUG#1862 (flush table in SPs didn't work).

Fixed various bugs: setting local variables to NULL, SELECT INTO var now actually
might work, SELECT INTO with not row now gives a "no data" warning (instead of
the "empty query" error), etc.
Updated test cases accordingly.

4 files changed, 175 insertions, 12 deletions

diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 58905a74507..adfecc151b7 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -187,7 +187,7 @@ end;
 call p();
 ERROR HY000: Cursor is not open
 drop procedure p;
-alter procedure bar3 SECURITY INVOKER;
+alter procedure bar3 sql security invoker;
 ERROR HY000: PROCEDURE bar3 does not exist
 alter procedure bar3 name
 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 990a8b7c2da..56f6bc59087 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -75,6 +75,23 @@ id	data
 locset	21
 delete from t1;
 drop procedure locset;
+drop table if exists t3;
+create table t3 ( d date, i int, f double, s varchar(32) );
+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);
+end;
+call nullset();
+select * from t3;
+d	i	f	s
+NULL	NULL	NULL	NULL
+drop table t3;
+drop procedure nullset;
 create procedure mixset(x char(16), y int)
 begin
 declare z int;
@@ -390,6 +407,47 @@ into	100	100
 into2	102	100
 delete from t1;
 drop procedure into_test2;
+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);
+delete from t2;
+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;
+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;
+drop table if exists t3;
+create table t3 ( s char(16), d int);
+call into_test4();
+Warnings:
+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;
 create procedure into_outfile(x char(16), y int)
 begin
 insert into test.t1 values (x, y);
@@ -475,9 +533,6 @@ 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;
@@ -551,7 +606,7 @@ create procedure hndlr4()
 begin
 declare x int default 0;
 declare val int;	                           # No default
-declare continue handler for sqlexception set x=1;
+declare continue handler for 1306 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;
@@ -695,6 +750,21 @@ select @1, @2;
 2	NULL
 drop table t70;
 drop procedure bug1656;
+drop table if exists t3;
+create table t3(a int);
+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 table if exists fac;
 create table fac (n int unsigned not null primary key, f bigint unsigned);
 create procedure ifac(n int unsigned)
@@ -839,21 +909,22 @@ drop table primes;
 drop procedure opp;
 drop procedure ip;
 create procedure bar(x char(16), y int)
-comment "111111111111" SECURITY INVOKER
+comment "111111111111" sql security invoker
 insert into test.t1 values (x, y);
 show procedure status like 'bar';
 Name	Type	Creator	Modified	Created	Suid	Comment
 bar	procedure	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	N	111111111111
-alter procedure bar name bar2 comment "2222222222" SECURITY DEFINER;
+alter procedure bar name bar2 comment "2222222222" sql security definer;
 alter procedure bar2 name bar comment "3333333333";
 alter procedure bar;
 show create procedure bar;
 Procedure	Create Procedure
 bar	create procedure bar(x char(16), y int)
-comment "111111111111" SECURITY INVOKER
+comment "111111111111" sql security invoker
 insert into test.t1 values (x, y)
 show procedure status like 'bar';
 Name	Type	Creator	Modified	Created	Suid	Comment
 bar	procedure	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	Y	3333333333
+drop procedure bar;
 drop table t1;
 drop table t2;
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index 38f5a34a7bb..87f765cb7c8 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -257,7 +257,7 @@ call p()|
 drop procedure p|
 
 --error 1282
-alter procedure bar3 SECURITY INVOKER|
+alter procedure bar3 sql security invoker|
 --error 1059
 alter procedure bar3 name
 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA|
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 5b2c9bf18f5..091a3e364ce 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -109,6 +109,29 @@ delete from t1|
 drop procedure locset|
 
 
+# Set things to null
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+create table t3 ( d date, i int, f double, s varchar(32) )|
+
+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);
+end|
+
+call nullset()|
+select * from t3|
+drop table t3|
+drop procedure nullset|
+
+
 # The peculiar (non-standard) mixture of variables types in SET.
 create procedure mixset(x char(16), y int)
 begin
@@ -460,6 +483,52 @@ delete from t1|
 drop procedure into_test2|
 
 
+# SELECT * INTO ... (bug test)
+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)|
+delete from t2|
+# Two call needed for bug test
+call into_test3()|
+call into_test3()|
+select * from t2|
+delete from t1|
+delete from t2|
+drop procedure into_test3|
+
+
+# SELECT INTO with no data is a warning ("no data", which we will
+# not see normally). When not caught, execution proceeds.
+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|
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+create table t3 ( s char(16), d int)|
+call into_test4()|
+select * from t3|
+insert into t1 values ("i4", 77)|
+call into_test4()|
+select * from t3|
+delete from t1|
+drop table t3|
+drop procedure into_test4|
+
+
 # These two (and the two procedures above) caused an assert() to fail in
 # sql_base.cc:lock_tables() at some point.
 
@@ -658,7 +727,7 @@ create procedure hndlr4()
 begin
   declare x int default 0;
   declare val int;	                           # No default
-  declare continue handler for sqlexception set x=1;
+  declare continue handler for 1306 set x=1;
 
   select data into val from test.t3 where id='z' limit 1;  # No hits
 
@@ -824,6 +893,28 @@ drop procedure bug1656|
 
 
 #
+# BUG#1862
+#
+--disable_warnings
+drop table if exists t3|
+--enable_warnings
+create table t3(a int)|
+
+create procedure bug1862()
+begin
+  insert into t3 values(2);    
+  flush tables;
+end|
+
+call bug1862()|
+# the second call caused a segmentation
+call bug1862()|
+select * from t3|
+drop table t3|
+drop procedure bug1862|
+
+
+#
 # Some "real" examples
 #
 
@@ -946,16 +1037,17 @@ drop procedure ip|
 
 # Comment & suid
 create procedure bar(x char(16), y int)
- comment "111111111111" SECURITY INVOKER
+ comment "111111111111" sql security invoker
  insert into test.t1 values (x, y)|
 --replace_column 4 '0000-00-00 00:00:00' 5 '0000-00-00 00:00:00'
 show procedure status like 'bar'|
-alter procedure bar name bar2 comment "2222222222" SECURITY DEFINER|
+alter procedure bar name bar2 comment "2222222222" sql security definer|
 alter procedure bar2 name bar comment "3333333333"|
 alter procedure bar|
 show create procedure bar|
 --replace_column 4 '0000-00-00 00:00:00' 5 '0000-00-00 00:00:00'
 show procedure status like 'bar'|
+drop procedure bar|
 
 delimiter ;|
 drop table t1;