Merge moonlight.intranet:/home/tomash/src/mysql_ab/tmp_merge

into  moonlight.intranet:/home/tomash/src/mysql_ab/mysql-5.1-merge


client/mysql.cc:
  Auto merged
mysql-test/Makefile.am:
  Auto merged
mysql-test/r/date_formats.result:
  Auto merged
mysql-test/r/func_str.result:
  Auto merged
mysql-test/r/sp.result:
  Auto merged
mysql-test/t/date_formats.test:
  Auto merged
mysql-test/t/func_str.test:
  Auto merged
mysql-test/t/ps_1general.test:
  Auto merged
mysql-test/t/sp.test:
  Auto merged
mysql-test/t/udf.test:
  Auto merged
mysys/my_open.c:
  Auto merged
BitKeeper/deleted/.del-make_win_src_distribution.sh~f80d8fca44e4e5f1:
  Auto merged
sql/field.cc:
  Auto merged
sql/field.h:
  Auto merged
sql/item_strfunc.cc:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/sql_class.cc:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/time.cc:
  Auto merged
client/mysqltest.c:
  Manual merge.
mysql-test/r/ps_1general.result:
  Manual merge.

8 files changed, 7 insertions, 5721 deletions

diff --git a/BitKeeper/etc/collapsed b/BitKeeper/etc/collapsed
new file mode 100644
index 00000000000..8b90deae622
--- /dev/null
+++ b/BitKeeper/etc/collapsed
@@ -0,0 +1 @@
+44d03f27qNdqJmARzBoP3Is_cN5e0w
diff --git a/mysql-test/Makefile.am b/mysql-test/Makefile.am
index 852ec463420..7fa6fa1e23e 100644
--- a/mysql-test/Makefile.am
+++ b/mysql-test/Makefile.am
@@ -99,6 +99,7 @@ install-data-local:
 	$(INSTALL_DATA) $(srcdir)/include/*.inc $(DESTDIR)$(testdir)/include
 	$(INSTALL_DATA) $(srcdir)/std_data/*.dat $(DESTDIR)$(testdir)/std_data
 	$(INSTALL_DATA) $(srcdir)/std_data/*.*001 $(DESTDIR)$(testdir)/std_data
+	$(INSTALL_DATA) $(srcdir)/std_data/*.cnf $(DESTDIR)$(testdir)/std_data
 	$(INSTALL_DATA) $(srcdir)/std_data/des_key_file $(DESTDIR)$(testdir)/std_data
 	$(INSTALL_DATA) $(srcdir)/std_data/Moscow_leap $(DESTDIR)$(testdir)/std_data
 	$(INSTALL_DATA) $(srcdir)/std_data/*.pem $(DESTDIR)$(testdir)/std_data
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
index 1a1d6432411..c94df7f5c2a 100644
--- a/mysql-test/r/ps_1general.result
+++ b/mysql-test/r/ps_1general.result
@@ -299,7 +299,7 @@ t9	MyISAM	10	Dynamic	2	216	432	#	2048	0	NULL	#	#	#	latin1_swedish_ci	NULL
 prepare stmt4 from ' show status like ''Threads_running'' ';
 execute stmt4;
 Variable_name	Value
-Threads_running	2
+Threads_running	#
 prepare stmt4 from ' show variables like ''sql_mode'' ';
 execute stmt4;
 Variable_name	Value
diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test
index e9b8a1c95b1..c15bc1633a6 100644
--- a/mysql-test/t/ps_1general.test
+++ b/mysql-test/t/ps_1general.test
@@ -316,6 +316,7 @@ prepare stmt4 from ' show table status from test like ''t9%'' ';
 --replace_column 8 # 12 # 13 # 14 #
 # Bug#4288
 execute stmt4;
+--replace_column 2 #
 prepare stmt4 from ' show status like ''Threads_running'' ';
 execute stmt4;
 prepare stmt4 from ' show variables like ''sql_mode'' ';
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 322e21d989b..d96d2419650 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5951,7 +5951,6 @@ drop table t3|
 drop procedure bug15217|
 
 
-#
 # Bug#21002 "Derived table not selecting from a "real" table fails in JOINs"
 #         
 # A regression caused by the fix for Bug#18444: for derived tables we should
diff --git a/mysql-test/t/sp.test.orig b/mysql-test/t/sp.test.orig
deleted file mode 100644
index a4b99620344..00000000000
--- a/mysql-test/t/sp.test.orig
+++ /dev/null
@@ -1,5716 +0,0 @@
-#
-# Basic stored PROCEDURE tests
-#
-# Please keep this file free of --error cases and other
-# things that will not run in a single debugged mysqld
-# process (e.g. master-slave things).
-#
-# Test cases for bugs are added at the end. See template there.
-#
-# Tests that require --error go into sp-error.test
-# Tests that require inndb go into sp_trans.test
-# Tests that check privilege and security issues go to sp-security.test.
-# Tests that require multiple connections, except security/privilege tests,
-#   go to sp-thread.
-# Tests that uses 'goto' to into sp-goto.test (currently disabled)
-# Tests that destroys system tables (e.g. mysql.proc) for error testing
-#   go to sp-destruct.
-
-use test;
-
-# Test tables
-#
-# t1 and t2 are reused throughout the file, and dropped at the end.
-# t3 and up are created and dropped when needed.
-#
---disable_warnings
-drop table if exists t1,t2,t3,t4;
---enable_warnings
-create table t1 (
-	id   char(16) not null default '',
-        data int not null
-);
-create table t2 (
-	s   char(16),
-        i   int,
-	d   double
-);
-
-
-# Single statement, no params.
---disable_warnings
-drop procedure if exists foo42;
---enable_warnings
-create procedure foo42()
-  insert into test.t1 values ("foo", 42);
-
-call foo42();
-select * from t1;
-delete from t1;
-drop procedure foo42;
-
-
-# Single statement, two IN params.
---disable_warnings
-drop procedure if exists bar;
---enable_warnings
-create procedure bar(x char(16), y int)
-  insert into test.t1 values (x, y);
-
-call bar("bar", 666);
-select * from t1;
-delete from t1;
-# Don't drop procedure yet...
-
-
-# Now for multiple statements...
-delimiter |;
-
-# Empty statement
---disable_warnings
-drop procedure if exists empty|
---enable_warnings
-create procedure empty()
-begin
-end|
-
-call empty()|
-drop procedure empty|
-
-# Scope test. This is legal (warnings might be possible in the future,
-# but for the time being, we just accept it).
---disable_warnings
-drop procedure if exists scope|
---enable_warnings
-create procedure scope(a int, b float)
-begin
-  declare b int;
-  declare c float;
-
-  begin
-    declare c int;
-  end;
-end|
-
-drop procedure scope|
-
-# Two statements.
---disable_warnings
-drop procedure if exists two|
---enable_warnings
-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|
-delete from t1|
-drop procedure two|
-
-
-# Simple test of local variables and SET.
---disable_warnings
-drop procedure if exists locset|
---enable_warnings
-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|
-delete from t1|
-drop procedure locset|
-
-
-# In some contexts local variables are not recognized
-# (and in some, you have to qualify the identifier).
---disable_warnings
-drop procedure if exists setcontext|
---enable_warnings
-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|
-delete from t1|
-drop procedure setcontext|
-
-
-# Set things to null
-create table t3 ( d date, i int, f double, s varchar(32) )|
-
---disable_warnings
-drop procedure if exists nullset|
---enable_warnings
-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|
-drop table t3|
-drop procedure nullset|
-
-
-# The peculiar (non-standard) mixture of variables types in SET.
---disable_warnings
-drop procedure if exists mixset|
---enable_warnings
-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'|
-select id,data,@z from t1|
-delete from t1|
-drop procedure mixset|
-
-
-# Multiple CALL statements, one with OUT parameter.
---disable_warnings
-drop procedure if exists zip|
---enable_warnings
-create procedure zip(x char(16), y int)
-begin
-  declare z int;
-  call zap(y, z);
-  call bar(x, z);
-end|
-
-# SET local variables and OUT parameter.
---disable_warnings
-drop procedure if exists zap|
---enable_warnings
-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|
-delete from t1|
-drop procedure zip|
-drop procedure bar|
-
-# Top-level OUT parameter
-call zap(7, @zap)|
-select @zap|
-
-drop procedure zap|
-
-
-# "Deep" calls...
---disable_warnings
-drop procedure if exists c1|
---enable_warnings
-create procedure c1(x int)
-  call c2("c", x)|
---disable_warnings
-drop procedure if exists c2|
---enable_warnings
-create procedure c2(s char(16), x int)
-  call c3(x, s)|
---disable_warnings
-drop procedure if exists c3|
---enable_warnings
-create procedure c3(x int, s char(16))
-  call c4("level", x, s)|
---disable_warnings
-drop procedure if exists c4|
---enable_warnings
-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|
-delete from t1|
-drop procedure c1|
-drop procedure c2|
-drop procedure c3|
-drop procedure c4|
-
-# INOUT test
---disable_warnings
-drop procedure if exists iotest|
---enable_warnings
-create procedure iotest(x1 char(16), x2 char(16), y int)
-begin
-  call inc2(x2, y);
-  insert into test.t1 values (x1, y);
-end|
-
---disable_warnings
-drop procedure if exists inc2|
---enable_warnings
-create procedure inc2(x char(16), y int)
-begin
-  call inc(y);
-  insert into test.t1 values (x, y);
-end|
-
---disable_warnings
-drop procedure if exists inc|
---enable_warnings
-create procedure inc(inout io int)
-  set io = io + 1|
-
-call iotest("io1", "io2", 1)|
-select * from t1|
-delete from t1|
-drop procedure iotest|
-drop procedure inc2|
-
-# Propagating top-level @-vars
---disable_warnings
-drop procedure if exists incr|
---enable_warnings
-create procedure incr(inout x int)
-  call inc(x)|
-
-# Before
-select @zap|
-call incr(@zap)|
-# After
-select @zap|
-
-drop procedure inc|
-drop procedure incr|
-
-# Call-by-value test
-#  The expected result is:
-#    ("cbv2", 4)
-#    ("cbv1", 4711)
---disable_warnings
-drop procedure if exists cbv1|
---enable_warnings
-create procedure cbv1()
-begin
-  declare y int default 3;
-
-  call cbv2(y+1, y);
-  insert into test.t1 values ("cbv1", y);
-end|
-
---disable_warnings
-drop procedure if exists cbv2|
---enable_warnings
-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|
-delete from t1|
-drop procedure cbv1|
-drop procedure cbv2|
-
-
-# Subselect arguments
-
-insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)|
-
---disable_warnings
-drop procedure if exists sub1|
---enable_warnings
-create procedure sub1(id char(16), x int)
-  insert into test.t1 values (id, x)|
-
---disable_warnings
-drop procedure if exists sub2|
---enable_warnings
-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|
-
---disable_warnings
-drop procedure if exists sub3|
---enable_warnings
-create function sub3(i int) returns int
-  return i+1|
-
-call sub1("sub1a", (select 7))|
-call sub1("sub1b", (select max(i) from t2))|
---error ER_OPERAND_COLUMNS
-call sub1("sub1c", (select i,d from t2 limit 1))|
-call sub1("sub1d", (select 1 from (select 1) a))|
-call sub2("sub2")|
-select * from t1|
-select sub3((select max(i) from t2))|
-drop procedure sub1|
-drop procedure sub2|
-drop function sub3|
-delete from t1|
-delete from t2|
-
-# Basic tests of the flow control constructs
-
-# Just test on 'x'...
---disable_warnings
-drop procedure if exists a0|
---enable_warnings
-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|
-delete from t1|
-drop procedure a0|
-
-
-# The same, but with a more traditional test.
---disable_warnings
-drop procedure if exists a|
---enable_warnings
-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|
-delete from t1|
-drop procedure a|
-
-
-# REPEAT
---disable_warnings
-drop procedure if exists b|
---enable_warnings
-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|
-delete from t1|
-drop procedure b|
-
-
-# Check that repeat isn't parsed the wrong way
---disable_warnings
-drop procedure if exists b2|
---enable_warnings
-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|
-
-# We don't actually want to call it.
-drop procedure b2|
-
-
-# Labelled WHILE with ITERATE (pointless really)
---disable_warnings
-drop procedure if exists c|
---enable_warnings
-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|
-delete from t1|
-drop procedure c|
-
-
-# Labelled WHILE with LEAVE
---disable_warnings
-drop procedure if exists d|
---enable_warnings
-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|
-delete from t1|
-drop procedure d|
-
-
-# LOOP, with simple IF statement
---disable_warnings
-drop procedure if exists e|
---enable_warnings
-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|
-delete from t1|
-drop procedure e|
-
-
-# A full IF statement
---disable_warnings
-drop procedure if exists f|
---enable_warnings
-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|
-delete from t1|
-drop procedure f|
-
-
-# This form of CASE is really just syntactic sugar for IF-ELSEIF-...
---disable_warnings
-drop procedure if exists g|
---enable_warnings
-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|
-delete from t1|
-drop procedure g|
-
-
-# The "simple CASE"
---disable_warnings
-drop procedure if exists h|
---enable_warnings
-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|
-delete from t1|
-drop procedure h|
-
-
-# It's actually possible to LEAVE a BEGIN-END block
---disable_warnings
-drop procedure if exists i|
---enable_warnings
-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|
-delete from t1|
-drop procedure i|
-
-
-# SELECT with one of more result set sent back to the clinet
-insert into t1 values ("foo", 3), ("bar", 19)|
-insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)|
-
---disable_warnings
-drop procedure if exists sel1|
---enable_warnings
-create procedure sel1()
-begin
-  select * from t1;
-end|
-
-call sel1()|
-drop procedure sel1|
-
---disable_warnings
-drop procedure if exists sel2|
---enable_warnings
-create procedure sel2()
-begin
-  select * from t1;
-  select * from t2;
-end|
-
-call sel2()|
-drop procedure sel2|
-delete from t1|
-delete from t2|
-
-# SELECT INTO local variables
---disable_warnings
-drop procedure if exists into_test|
---enable_warnings
-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|
-delete from t1|
-drop procedure into_test|
-
-
-# SELECT INTO with a mix of local and global variables
---disable_warnings
-drop procedure if exists into_tes2|
---enable_warnings
-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|
-delete from t1|
-drop procedure into_test2|
-
-
-# SELECT * INTO ... (bug test)
---disable_warnings
-drop procedure if exists into_test3|
---enable_warnings
-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)|
-# 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.
---disable_warnings
-drop procedure if exists into_test4|
---enable_warnings
-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()|
-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.
---disable_warnings
-drop procedure if exists into_outfile|
---enable_warnings
-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|
-
---system rm -f $MYSQLTEST_VARDIR/tmp/spout
-call into_outfile("ofile", 1)|
---system rm -f $MYSQLTEST_VARDIR/tmp/spout
-delete from t1|
-drop procedure into_outfile|
-
---disable_warnings
-drop procedure if exists into_dumpfile|
---enable_warnings
-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|
-
---system rm -f $MYSQLTEST_VARDIR/tmp/spdump
-call into_dumpfile("dfile", 1)|
---system rm -f $MYSQLTEST_VARDIR/tmp/spdump
-delete from t1|
-drop procedure into_dumpfile|
-
---disable_warnings
-drop procedure if exists create_select|
---enable_warnings
-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|
-drop table t3|
-delete from t1|
-drop procedure create_select|
-
-
-# A minimal, constant FUNCTION.
---disable_warnings
-drop function if exists e|
---enable_warnings
-create function e() returns double
-  return 2.7182818284590452354|
-
-set @e = e()|
-select e(), @e|
-
-# A minimal function with one argument
---disable_warnings
-drop function if exists inc|
---enable_warnings
-create function inc(i int) returns int
-  return i+1|
-
-select inc(1), inc(99), inc(-71)|
-
-# A minimal function with two arguments
---disable_warnings
-drop function if exists mul|
---enable_warnings
-create function mul(x int, y int) returns int
-  return x*y|
-
-select mul(1,1), mul(3,5), mul(4711, 666)|
-
-# A minimal string function
---disable_warnings
-drop function if exists append|
---enable_warnings
-create function append(s1 char(8), s2 char(8)) returns char(16)
-  return concat(s1, s2)|
-
-select append("foo", "bar")|
-
-# A function with flow control
---disable_warnings
-drop function if exists fac|
---enable_warnings
-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)|
-
-# Nested calls
---disable_warnings
-drop function if exists fun|
---enable_warnings
-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)|
-
-
-# Various function calls in differen statements
-
-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))|
-
-# Disable PS because double's give a bit different values
---disable_ps_protocol
-select * from t2 where s = append("a", "b")|
-select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)|
-select * from t2 where d = e()|
-select * from t2|
---enable_ps_protocol
-delete from t2|
-
-drop function e|
-drop function inc|
-drop function mul|
-drop function append|
-drop function fun|
-
-
-#
-# CONDITIONs and HANDLERs
-#
-
---disable_warnings
-drop procedure if exists hndlr1|
---enable_warnings
-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|
-delete from t1|
-drop procedure hndlr1|
-
---disable_warnings
-drop procedure if exists hndlr2|
---enable_warnings
-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|
-delete from t1|
-drop procedure hndlr2|
-
-
---disable_warnings
-drop procedure if exists hndlr3|
---enable_warnings
-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|
-delete from t1|
-drop procedure hndlr3|
-
-
-# Variables might be uninitialized when using handlers
-# (Otherwise the compiler can detect if a variable is not set, but
-#  not in this case.)
-create table t3 ( id   char(16), data int )|
-
---disable_warnings
-drop procedure if exists hndlr4|
---enable_warnings
-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|
-drop table t3|
-drop procedure hndlr4|
-
-
-#
-# Cursors
-#
---disable_warnings
-drop procedure if exists cur1|
---enable_warnings
-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|
-drop procedure cur1|
-
-create table t3 ( s char(16), i int )|
-
---disable_warnings
-drop procedure if exists cur2|
---enable_warnings
-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|
-delete from t1|
-delete from t2|
-drop table t3|
-drop procedure cur2|
-
-
-# The few characteristics we parse
---disable_warnings
-drop procedure if exists chistics|
---enable_warnings
-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|
-# Call it, just to make sure.
-call chistics()|
-select * from t1|
-delete from t1|
-alter procedure chistics sql security invoker|
-show create procedure chistics|
-drop procedure chistics|
-
---disable_warnings
-drop function if exists chistics|
---enable_warnings
-create function chistics() returns int
-    language sql
-    deterministic
-    sql security invoker
-    comment 'Characteristics procedure test'
-  return 42|
-
-show create function chistics|
-# Call it, just to make sure.
-select chistics()|
-alter function chistics
-   no sql
-   comment 'Characteristics function test'|
-show create function chistics|
-drop function chistics|
-
-
-# Check mode settings
-insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)|
-
-set @@sql_mode = 'ANSI'|
-delimiter $|
---disable_warnings
-drop procedure if exists modes$
---enable_warnings
-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$
-delimiter |$
-set @@sql_mode = ''|
-
-set sql_select_limit = 1|
-call modes(@c1, @c2)|
-set sql_select_limit = default|
-
-select @c1, @c2|
-delete from t1|
-drop procedure modes|
-
-
-# Check that dropping a database without routines works.
-# (Dropping with routines is tested in sp-security.test)
-# First an empty db.
-create database sp_db1|
-drop database sp_db1|
-
-# Again, with a table.
-create database sp_db2|
-use sp_db2|
-# Just put something in here...
-create table t3 ( s char(4), t int )|
-insert into t3 values ("abcd", 42), ("dcba", 666)|
-use test|
-drop database sp_db2|
-
-# And yet again, with just a procedure.
-create database sp_db3|
-use sp_db3|
---disable_warnings
-drop procedure if exists dummy|
---enable_warnings
-create procedure dummy(out x int)
-  set x = 42|
-use test|
-drop database sp_db3|
-# Check that it's gone
-select type,db,name from mysql.proc where db = 'sp_db3'|
-
-
-# ROW_COUNT() function after a CALL
-# We test the other cases here too, although it's not strictly SP specific
---disable_warnings
-drop procedure if exists rc|
---enable_warnings
-create procedure rc()
-begin
-  delete from t1;
-  insert into t1 values ("a", 1), ("b", 2), ("c", 3);
-end|
-
-call rc()|
-select row_count()|
---disable_ps_protocol
-update t1 set data=42 where id = "b";
-select row_count()|
---enable_ps_protocol
-delete from t1|
-select row_count()|
-delete from t1|
-select row_count()|
-select * from t1|
-select row_count()|
-drop procedure rc|
-
-
-#
-# Let us test how well new locking scheme works.
-#
-
-# Let us prepare playground
---disable_warnings
-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|
---enable_warnings
-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) |
-
-# Test the simplest function using tables
-create function f1() returns int
-  return (select sum(data) from t1)|
-select f1()|
-# This should work too (and give 2 rows as result)
-select id, f1() from t1|
-
-# Function which uses two instances of table simultaneously
-create function f2() returns int
-  return (select data from t1 where data <= (select sum(data) from t1) limit 1)|
-select f2()|
-select id, f2() from t1|
-
-# Function which uses the same table twice in different queries
-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()|
-select id, f3() from t1|
-
-# Calling two functions using same table
-select f1(), f3()|
-select id, f1(), f3() from t1|
-
-# Function which uses two different tables
-create function f4() returns double 
-  return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
-select f4()|
-select s, f4() from t2|
-
-# Recursive functions which due to this recursion require simultaneous
-# access to several instance of the same table won't work
-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)|
-# Since currently recursive functions are disallowed ER_SP_NO_RECURSION
-# error will be returned, once we will allow them error about
-# insufficient number of locked tables will be returned instead.
---error ER_SP_NO_RECURSION
-select f5(2)|
---error ER_SP_NO_RECURSION
-select f5(3)|
-
-# OTOH this should work 
-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()|
-select id, f6() from t1|
-
-#
-# Let us test how new locking work with views
-#
-# The most trivial view
-create view v1 (a) as select f1()|
-select * from v1|
-select id, a from t1, v1|
-select * from v1, v1 as v|
-# A bit more complex construction
-create view v2 (a) as select a*10 from v1|
-select * from v2|
-select id, a from t1, v2|
-select * from v1, v2|
-
-# Nice example where the same view is used on
-# on different expression levels
-create function f8 () returns int
-  return (select count(*) from v2)|
-
-select *, f8() from v1|
-
-# Let us test what will happen if function is missing
-drop function f1|
---error 1356
-select * from v1|
-
-# And what will happen if we have recursion which involves
-# views and functions ?
-create function f1() returns int
-  return (select sum(data) from t1) + (select sum(data) from v1)|
---error ER_SP_NO_RECURSION
-select f1()|
---error ER_SP_NO_RECURSION
-select * from v1|
---error ER_SP_NO_RECURSION
-select * from v2|
-# Back to the normal cases
-drop function f1|
-create function f1() returns int
-  return (select sum(data) from t1)|
-
-# Let us also test some weird cases where no real tables is used
-create function f0() returns int
-  return (select * from (select 100) as r)|
-select f0()|
-select *, f0() from (select 1) as t|
-create view v0 as select f0()|
-select * from v0|
-select *, f0() from v0|
-
-#
-# Let us test how well prelocking works with explicit LOCK TABLES.
-#
-lock tables t1 read, t1 as t11 read|
-# These should work well
-select f3()|
-select id, f3() from t1 as t11|
-# Degenerate cases work too :)
-select f0()|
-select * from v0|
-select *, f0() from v0, (select 123) as d1|
-# But these should not !
---error 1100
-select id, f3() from t1|
---error 1100
-select f4()|
-unlock tables|
-
-# Let us test how LOCK TABLES which implicitly depends on functions
-# works
-lock tables v2 read, mysql.proc read|
-select * from v2|
-select * from v1|
-# These should not work as we have too little instances of tables locked
---error 1100
-select * from v1, t1|
---error 1100
-select f4()|
-unlock tables|
-
-# Tests for handling of temporary tables in functions.
-#
-# Unlike for permanent tables we should be able to create, use
-# and drop such tables in functions.
-# 
-# Simplest function using temporary table. It is also test case for bug 
-# #12198 "Temporary table aliasing does not work inside stored functions"
-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|
-# This will emit warning as t3 was not existing before.
-select f9()|
-select f9() from t1 limit 1|
-
-# Function which uses both temporary and permanent tables.
-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|
-# Check that we don't ignore completely tables used in function
---error ER_NO_SUCH_TABLE
-select f10()|
-create table t4 as select 1 as id|
-select f10()|
-
-# Practical cases which we don't handle well (yet)
-#
-# Function which does not work because of well-known and documented
-# limitation of MySQL. We can't use the several instances of the
-# same temporary table in statement.
-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|
---error ER_CANT_REOPEN_TABLE
-select f11()|
---error ER_CANT_REOPEN_TABLE
-select f11() from t1|
-# We don't handle temporary tables used by nested functions well
-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)|
-# We need clean start to get error
-drop temporary table t3|
---error ER_NO_SUCH_TABLE
-select f12_1()|
---error ER_NO_SUCH_TABLE
-select f12_1() from t1 limit 1|
-
-# Cleanup
-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|
-
-# End of non-bug tests
-
-
-#
-# Some "real" examples
-#
-
-# fac
-
---disable_warnings
-drop table if exists t3|
---enable_warnings
-create table t3 (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.t3 values (i, fac(i));
-      set i = i + 1;
-    end;
-  end while;
-end|
-
-call ifac(20)|
-select * from t3|
-drop table t3|
---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 t3|
---enable_warnings
-
-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)|
-
---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.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|
-
---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.t3 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 t3 where i=45 or i=100 or i=199|
-drop table t3|
-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 t3|
---enable_warnings
-create table t3 ( f bigint unsigned not null )|
-
-# 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 > 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|
-
-# Enable recursion
-set @@max_sp_recursion_depth= 20|
-
-# Minimum test: recursion of 3 levels
-
-insert into t3 values (0), (1)|
-
-call fib(3)|
-
-select * from t3 order by f asc|
-
-delete from t3|
-
-# The original test, 20 levels, ran into memory limits on some machines
-# and builds. Try 10 instead...
-
-insert into t3 values (0), (1)|
-
-call fib(10)|
-
-select * from t3 order by f asc|
-drop table t3|
-drop procedure fib|
-set @@max_sp_recursion_depth= 0|
-
-#
-# 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_warnings
-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|
-
-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 different combinations of condition handlers in nested
-# begin-end blocks in stored procedures.
-#
-# Note that the standard specifies that the most specific handler should
-# be triggered even if it's an outer handler masked by a less specific
-# handler in an inner block.
-# Note also that '02000' is more specific than NOT FOUND; there might be
-# other '02xxx' states, even if we currently do not issue them in any
-# situation (e.g. '02001').
-#
-# The combinations we test are these:
-#
-#                                         Inner
-#              errcode      sqlstate     not found    sqlwarning   sqlexception
-#  Outer      +------------+------------+------------+------------+------------+
-#errcode      | h_ee (i)   | h_es (o)   | h_en (o)   | h_ew (o)   | h_ex (o)   |
-#sqlstate     | h_se (i)   | h_ss (i)   | h_sn (o)   | h_sw (o)   | h_sx (o)   |
-#not found    | h_ne (i)   | h_ns (i)   | h_nn (i)   |            |            |
-#sqlwarning   | h_we (i)   | h_ws (i)   |            | h_ww (i)   |            |
-#sqlexception | h_xe (i)   | h_xs (i)   |            |            | h_xx (i)   |
-#             +------------+---------------------------------------------------+
-#
-# (i) means that the inner handler is the one that should be invoked,
-# (o) means that the outer handler should be invoked.
-#
-# ('not found', 'sqlwarning' and 'sqlexception' are mutually exclusive, hence
-#  no tests for those combinations.)
-#
-
---disable_warnings
-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|
---enable_warnings
-
-# smallint    - to get out of range warnings
-# primary key - to get constraint errors
-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()|
-call h_es()|
-call h_en()|
-call h_ew()|
-call h_ex()|
-call h_se()|
-call h_ss()|
-call h_sn()|
-call h_sw()|
-call h_sx()|
-call h_ne()|
-call h_ns()|
-call h_nn()|
-call h_we()|
-call h_ws()|
-call h_ww()|
-call h_xe()|
-call h_xs()|
-call h_xx()|
-
-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|
-
-
-#
-# Test cases for old bugs
-#
-
-#
-# BUG#822
-#
---disable_warnings
-drop procedure if exists bug822|
---enable_warnings
-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|
-delete from t1|
-drop procedure bug822|
-
-#
-# BUG#1495
-#
---disable_warnings
-drop procedure if exists bug1495|
---enable_warnings
-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|
-delete from t1|
-drop procedure bug1495|
-
-#
-# BUG#1547
-#
---disable_warnings
-drop procedure if exists bug1547|
---enable_warnings
-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|
-delete from t1|
-drop procedure bug1547|
-
-#
-# BUG#1656
-#
---disable_warnings
-drop table if exists t70|
---enable_warnings
-create table t70 (s1 int,s2 int)|
-insert into t70 values (1,2)|
-
---disable_warnings
-drop procedure if exists bug1656|
---enable_warnings
-create procedure bug1656(out p1 int, out p2 int)
-  select * into p1, p1 from t70|
-
-call bug1656(@1, @2)|
-select @1, @2|
-drop table t70|
-drop procedure bug1656|
-
-#
-# BUG#1862
-#
-create table t3(a int)|
-
---disable_warnings
-drop procedure if exists bug1862|
---enable_warnings
-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|
-
-#
-# BUG#1874
-#
---disable_warnings
-drop procedure if exists bug1874|
---enable_warnings
-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|
-delete from t1|
-delete from t2|
-drop procedure bug1874|
-
-#
-# BUG#2260
-#
---disable_warnings
-drop procedure if exists bug2260|
---enable_warnings
-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|
-drop procedure bug2260|
-
-#
-# BUG#2267 "Lost connect if stored procedure has SHOW FUNCTION STATUS"
-#
---disable_warnings
-drop procedure if exists bug2267_1|
---enable_warnings
-create procedure bug2267_1()
-begin
-  show procedure status;
-end|
-
---disable_warnings
-drop procedure if exists bug2267_2|
---enable_warnings
-create procedure bug2267_2()
-begin
-  show function status;
-end|
-
---disable_warnings
-drop procedure if exists bug2267_3|
---enable_warnings
-create procedure bug2267_3()
-begin
-  show create procedure bug2267_1;
-end|
-
---disable_warnings
-drop procedure if exists bug2267_4|
-drop function if exists bug2267_4|
---enable_warnings
-create procedure bug2267_4()
-begin
-  show create function bug2267_4;
-end|
-create function bug2267_4() returns int return 100|
-
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-call bug2267_1()|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-call bug2267_2()|
-call bug2267_3()|
-call bug2267_4()|
-
-drop procedure bug2267_1|
-drop procedure bug2267_2|
-drop procedure bug2267_3|
-drop procedure bug2267_4|
-drop function bug2267_4|
-
-#
-# BUG#2227
-#
---disable_warnings
-drop procedure if exists bug2227|
---enable_warnings
-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)|
-drop procedure bug2227|
-
-#
-# BUG#2614 "Stored procedure with INSERT ... SELECT that does not
-#           contain any tables crashes server"
-#
---disable_warnings
-drop procedure if exists bug2614|
---enable_warnings
-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|
-
---disable_warnings
-call bug2614()|
---enable_warnings
-call bug2614()|
-drop table t3|
-drop procedure bug2614|
-
-#
-# BUG#2674
-#
---disable_warnings
-drop function if exists bug2674|
---enable_warnings
-create function bug2674() returns int
-  return @@sort_buffer_size|
-
-set @osbs = @@sort_buffer_size|
-set @@sort_buffer_size = 262000|
-select bug2674()|
-drop function bug2674|
-set @@sort_buffer_size = @osbs|
-
-#
-# BUG#3259
-#
---disable_warnings
-drop procedure if exists bug3259_1 |
---enable_warnings
-create procedure bug3259_1 () begin end|
---disable_warnings
-drop procedure if exists BUG3259_2 |
---enable_warnings
-create procedure BUG3259_2 () begin end|
---disable_warnings
-drop procedure if exists Bug3259_3 |
---enable_warnings
-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|
-
-#
-# BUG#2772
-#
---disable_warnings
-drop function if exists bug2772|
---enable_warnings
-create function bug2772() returns char(10) character set latin2
-  return 'a'|
-
-select bug2772()|
-drop function bug2772|
-
-#
-# BUG#2776
-#
---disable_warnings
-drop procedure if exists bug2776_1|
---enable_warnings
-create procedure bug2776_1(out x int)
-begin
-  declare v int;
-
-  set v = default;
-  set x = v;
-end|
-
---disable_warnings
-drop procedure if exists bug2776_2|
---enable_warnings
-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|
-call bug2776_2(@x)|
-select @x|
-drop procedure bug2776_1|
-drop procedure bug2776_2|
-
-#
-# BUG#2780
-#
-create table t3 (s1 smallint)|
-
-insert into t3 values (123456789012)|
-
---disable_warnings
-drop procedure if exists bug2780|
---enable_warnings
-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|
-select * from t3|
-
-drop procedure bug2780|
-drop table t3|
-
-#
-# BUG#1863
-#
-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)|
-
---disable_warnings
-drop procedure if exists bug1863|
---enable_warnings
-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)|
-call bug1863(10)|
-select * from t4|
-
-drop procedure bug1863|
-drop temporary table temp_t1;
-drop table t3, t4|
-
-#
-# BUG#2656
-#
-
-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")|
-
---disable_warnings
-drop procedure if exists bug2656_1|
---enable_warnings
-create procedure bug2656_1()
-begin 
-  select
-    m.Market
-  from  t4 m JOIN t3 o 
-        ON o.MarketID != 1 and o.MarketID = m.MarketID;
-end |
-
---disable_warnings
-drop procedure if exists bug2656_2|
---enable_warnings
-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()|
-call bug2656_1()|
-call bug2656_2()|
-call bug2656_2()|
-drop procedure bug2656_1|
-drop procedure bug2656_2|
-drop table t3, t4|
-
-
-#
-# BUG#3426
-#
---disable_warnings
-drop procedure if exists bug3426|
---enable_warnings
-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|
-call bug3426(NULL, @i)|
-select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
-# Clear SP cache
-alter procedure bug3426 sql security invoker|
-call bug3426(NULL, @i)|
-select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
-call bug3426(1000, @i)|
-select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
-
-drop procedure bug3426|
-
-#
-# BUG#3448
-#
---disable_warnings
-create table t3 (
-  a int primary key, 
-  ach char(1)
-) engine = innodb|
-
-create table t4 (
-  b int  primary key , 
-  bch char(1)
-) engine = innodb|
---enable_warnings
-
-insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')|
-insert into t4 values (1 , 'bCh1' )|
-
---disable_warnings
-drop procedure if exists bug3448|
---enable_warnings
-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|
-call bug3448()|
-call bug3448()|
-
-drop procedure bug3448|
-drop table t3, t4|
-
-
-#
-# BUG#3734
-#
-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 ...')|
-
---disable_warnings
-drop procedure if exists bug3734 |
---enable_warnings
-create procedure bug3734 (param1 varchar(100))
-  select * from t3 where match (title,body) against (param1)|
-
-call bug3734('database')|
-call bug3734('Security')|
-
-drop procedure bug3734|
-drop table t3|
-
-#
-# BUG#3863
-#
---disable_warnings
-drop procedure if exists bug3863|
---enable_warnings
-create procedure bug3863()
-begin
-  set @a = 0;
-  while @a < 5 do
-    set @a = @a + 1;
-  end while;
-end|
-
-call bug3863()|
-select @a|
-call bug3863()|
-select @a|
-
-drop procedure bug3863|
-
-#
-# BUG#2460
-#
-
-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)
-)|
-
---disable_warnings
-drop procedure if exists bug2460_1|
---enable_warnings
-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)|
-call bug2460_1(2)|
-insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')|
-call bug2460_1(2)|
-call bug2460_1(2)|
-
---disable_warnings
-drop procedure if exists bug2460_2|
---enable_warnings
-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|
-
-drop procedure bug2460_1|
-drop procedure bug2460_2|
-drop table t3|
-
-
-#
-# BUG#2564
-#
-set @@sql_mode = ''|
---disable_warnings
-drop procedure if exists bug2564_1|
---enable_warnings
-create procedure bug2564_1()
-    comment 'Joe''s procedure'
-  insert into `t1` values ("foo", 1)|
-
-set @@sql_mode = 'ANSI_QUOTES'|
---disable_warnings
-drop procedure if exists bug2564_2|
---enable_warnings
-create procedure bug2564_2()
-  insert into "t1" values ('foo', 1)|
-
-delimiter $|
-set @@sql_mode = ''$
---disable_warnings
-drop function if exists bug2564_3$
---enable_warnings
-create function bug2564_3(x int, y int) returns int
-  return x || y$
-
-set @@sql_mode = 'ANSI'$
---disable_warnings
-drop function if exists bug2564_4$
---enable_warnings
-create function bug2564_4(x int, y int) returns int
-  return x || y$
-delimiter |$
-
-set @@sql_mode = ''|
-show create procedure bug2564_1|
-show create procedure bug2564_2|
-show create function bug2564_3|
-show create function bug2564_4|
-
-drop procedure bug2564_1|
-drop procedure bug2564_2|
-drop function bug2564_3|
-drop function bug2564_4|
-
-#
-# BUG#3132
-#
---disable_warnings
-drop function if exists bug3132|
---enable_warnings
-create function bug3132(s char(20)) returns char(50)
-  return concat('Hello, ', s, '!')|
-
-select bug3132('Bob') union all select bug3132('Judy')|
-drop function bug3132|
-
-#
-# BUG#3843
-#
---disable_warnings
-drop procedure if exists bug3843|
---enable_warnings
-create procedure bug3843()
-  analyze table t1|
-
-# Testing for packets out of order
-call bug3843()|
-call bug3843()|
-select 1+2|
-
-drop procedure bug3843|
-
-#
-# BUG#3368
-#
-create table t3 ( s1 char(10) )|
-insert into t3 values ('a'), ('b')|
-
---disable_warnings
-drop procedure if exists bug3368|
---enable_warnings
-create procedure bug3368(v char(10))
-begin
-  select group_concat(v) from t3;
-end|
-
-call bug3368('x')|
-call bug3368('yz')|
-drop procedure bug3368|
-drop table t3|
-
-#
-# BUG#4579
-#
-create table t3 (f1 int, f2 int)|
-insert into t3 values (1,1)|
-
---disable_warnings
-drop procedure if exists bug4579_1|
---enable_warnings
-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|
-
---disable_warnings
-drop procedure if exists bug4579_2|
---enable_warnings
-create procedure bug4579_2 ()
-begin
-end|
-
-call bug4579_1()|
-call bug4579_1()|
-call bug4579_1()|
-
-drop procedure bug4579_1|
-drop procedure bug4579_2|
-drop table t3|
-
-#
-# BUG#2773: Function's data type ignored in stored procedures
-#
---disable_warnings
-drop procedure if exists bug2773|
---enable_warnings
-
-create function bug2773() returns int return null|
-create table t3 as select bug2773()|
-show create table t3|
-drop table t3|
-drop function bug2773|
-
-#
-# BUG#3788: Stored procedure packet error
-#
---disable_warnings
-drop procedure if exists bug3788|
---enable_warnings
-
-create function bug3788() returns date return cast("2005-03-04" as date)|
-select bug3788()|
-drop function bug3788|
-
-create function bug3788() returns binary(1) return 5|
-select bug3788()|
-drop function bug3788|
- 
-
-#
-# BUG#4726
-#
-create table t3 (f1 int, f2 int, f3 int)|
-insert into t3 values (1,1,1)|
-
---disable_warnings
-drop procedure if exists bug4726|
---enable_warnings
-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|
-
-#
-# BUG#4318
-#
-
---disable_parsing # Don't know if HANDLER commands can work with SPs, or at all..
-create table t3 (s1 int)|
-insert into t3 values (3), (4)|
-
---disable_warnings
-drop procedure if exists bug4318|
---enable_warnings
-create procedure bug4318()
-  handler t3 read next|
-
-handler t3 open|
-# Expect no results, as tables are closed, but there shouldn't be any errors
-call bug4318()|
-call bug4318()|
-handler t3 close|
-
-drop procedure bug4318|
-drop table t3|
---enable_parsing
-
-#
-# BUG#4902: Stored procedure with SHOW WARNINGS leads to packet error
-#
-# Added tests for most other show commands we could find too.
-# (Skipping those already tested, and the ones depending on optional handlers.)
-#
-# Note: This will return a large number of results of different formats,
-#       which makes it impossible to filter with --replace_column.
-#       It's possible that some of these are not deterministic across
-#       platforms. If so, just remove the offending command.
-#
---disable_warnings
-drop procedure if exists bug4902|
---enable_warnings
-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|
---disable_parsing
-show binlog events;
-show storage engines;
-show master status;
-show slave hosts;
-show slave status;
---enable_parsing
-
-call bug4902()|
-call bug4902()|
-
-drop procedure bug4902|
-
-# We need separate SP for SHOW PROCESSLIST  since we want use replace_column
---disable_warnings
-drop procedure if exists bug4902_2|
---enable_warnings
-create procedure bug4902_2()
-begin
-  show processlist;
-end|
---replace_column 1 # 6 # 3 localhost
-call bug4902_2()|
---replace_column 1 # 6 # 3 localhost
-call bug4902_2()|
-drop procedure bug4902_2|
-
-#
-# BUG#4904
-#
---disable_warnings
-drop procedure if exists bug4904|
---enable_warnings
-create procedure bug4904()
-begin
-  declare continue handler for sqlstate 'HY000' begin end;
-
-  create table t2 as select * from t3;
-end|
-
--- error 1146
-call bug4904()|
-
-drop procedure bug4904|
-
-create table t3 (s1 char character set latin1, s2 char character set latin2)|
-
---disable_warnings
-drop procedure if exists bug4904|
---enable_warnings
-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|
-
-#
-# BUG#336
-#
---disable_warnings
-drop procedure if exists bug336|
---enable_warnings
-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|
-delete from t1|
-drop procedure bug336|
-
-#
-# BUG#3157
-#
---disable_warnings
-drop procedure if exists bug3157|
---enable_warnings
-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|
-delete from t1|
-drop procedure bug3157|
-
-#
-# BUG#5251: mysql changes creation time of a procedure/function when altering
-#
---disable_warnings
-drop procedure if exists bug5251|
---enable_warnings
-create procedure bug5251()
-begin
-end|
-
-select created into @c1 from mysql.proc
-  where db='test' and name='bug5251'|
---sleep 2
-alter procedure bug5251 comment 'foobar'|
-select count(*) from mysql.proc
-  where  db='test' and name='bug5251' and created = @c1|
-
-drop procedure bug5251|
-
-#
-# BUG#5279: Stored procedure packets out of order if CHECKSUM TABLE
-#
---disable_warnings
-drop procedure if exists bug5251|
---enable_warnings
-create procedure bug5251()
-  checksum table t1|
-
-call bug5251()|
-call bug5251()|
-drop procedure bug5251|
-
-#
-# BUG#5287: Stored procedure crash if leave outside loop
-#
---disable_warnings
-drop procedure if exists bug5287|
---enable_warnings
-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|
-
-
-#
-# BUG#5307: Stored procedure allows statement after BEGIN ... END
-#
---disable_warnings
-drop procedure if exists bug5307|
---enable_warnings
-create procedure bug5307()
-begin
-end; set @x = 3|
-
-call bug5307()|
-select @x|
-drop procedure bug5307|
-
-#
-# BUG#5258: Stored procedure modified date is 0000-00-00
-# (This was a design flaw)
---disable_warnings
-drop procedure if exists bug5258|
---enable_warnings
-create procedure bug5258()
-begin
-end|
-
---disable_warnings
-drop procedure if exists bug5258_aux|
---enable_warnings
-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()|
-
-drop procedure bug5258|
-drop procedure bug5258_aux|
-
-#
-# BUG#4487: Stored procedure connection aborted if uninitialized char
-#
---disable_warnings
-drop function if exists bug4487|
---enable_warnings
-create function bug4487() returns char
-begin
-  declare v char;
-  return v;
-end|
-
-select bug4487()|
-drop function bug4487|
-
-
-#
-# BUG#4941: Stored procedure crash fetching null value into variable.
-#
---disable_warnings
-drop procedure if exists bug4941|
---enable_warnings
---disable_warnings
-drop procedure if exists bug4941|
---enable_warnings
-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|
-delete from t1|
-drop procedure bug4941|
-
-
-#
-# BUG#3583: query cache doesn't work for stored procedures
-#
---disable_warnings
-drop procedure if exists bug3583|
---enable_warnings
---disable_warnings
-drop procedure if exists bug3583|
---enable_warnings
-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'|
-call bug3583()|
-show status like 'Qcache_hits'|
-call bug3583()|
-call bug3583()|
-show status like 'Qcache_hits'|
-
-set global query_cache_size = @x|
-flush status|
-flush query cache|
-delete from t1|
-drop procedure bug3583|
-
-#
-# BUG#4905: Stored procedure doesn't clear for "Rows affected"
-#
---disable_warnings
-drop procedure if exists bug4905|
---enable_warnings
-
-create table t3 (s1 int,primary key (s1))|
-
---disable_warnings
-drop procedure if exists bug4905|
---enable_warnings
-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()|
-call bug4905()|
-select row_count()|
-call bug4905()|
-select row_count()|
-select * from t3|
-
-drop procedure bug4905|
-drop table t3|
-
-#
-# BUG#6022: Stored procedure shutdown problem with self-calling function.
-#
-
---disable_parsing # until we implement support for recursive stored functions.
---disable_warnings
-drop function if exists bug6022|
---enable_warnings
-
---disable_warnings
-drop function if exists bug6022|
---enable_warnings
-create function bug6022(x int) returns int
-begin
-  if x < 0 then
-    return 0;
-  else
-    return bug6022(x-1);
-  end if;
-end|
-
-select bug6022(5)|
-drop function bug6022|
---enable_parsing
-
-#
-# BUG#6029: Stored procedure specific handlers should have priority
-#
---disable_warnings
-drop procedure if exists bug6029|
---enable_warnings
-
---disable_warnings
-drop procedure if exists bug6029|
---enable_warnings
-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()|
-delete from t3|
-call bug6029()|
-
-drop procedure bug6029|
-drop table t3|
-
-#
-# BUG#8540: Local variable overrides an alias
-#
---disable_warnings
-drop procedure if exists bug8540|
---enable_warnings
-
-create procedure bug8540()
-begin
-  declare x int default 1;
-  select x as y, x+0 as z;
-end|
-
-call bug8540()|
-drop procedure bug8540|
-
-#
-# BUG#6642: Stored procedure crash if expression with set function
-#
-create table t3 (s1 int)|
-
---disable_warnings
-drop procedure if exists bug6642|
---enable_warnings
-
-create procedure bug6642()
-  select abs(count(s1)) from t3|
-
-call bug6642()|
-call bug6642()|
-drop procedure bug6642|
-
-#
-# BUG#7013: Stored procedure crash if group by ... with rollup
-#
-insert into t3 values (0),(1)|
---disable_warnings
-drop procedure if exists bug7013|
---enable_warnings
-create procedure bug7013()
-  select s1,count(s1) from t3 group by s1 with rollup|
-call bug7013()|
-call bug7013()|
-drop procedure bug7013|
-
-#
-# BUG#7743: 'Lost connection to MySQL server during query' on Stored Procedure
-#
---disable_warnings
-drop table if exists t4|
---enable_warnings
-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')|
-
---disable_warnings
-drop procedure if exists bug7743|
---enable_warnings
-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")|
-call bug7743("OneWord")|
-call bug7743("anotherword")|
-call bug7743("AnotherWord")|
-drop procedure bug7743|
-drop table t4|
-
-#
-# BUG#7992: SELECT .. INTO variable .. within Stored Procedure crashes
-#           the server
-#
-delete from t3|
-insert into t3 values(1)|
-drop procedure if exists bug7992_1|
-drop procedure if exists bug7992_2|
-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|
-
-#
-# BUG#8116: calling simple stored procedure twice in a row results
-#           in server crash
-#
-create table t3 (  userid bigint(20) not null default 0 )|
-
---disable_warnings
-drop procedure if exists bug8116|
---enable_warnings
-create procedure bug8116(in _userid int)
-   select * from t3 where userid = _userid|
-
-call bug8116(42)|
-call bug8116(42)|
-drop procedure bug8116|
-drop table t3|
-
-#
-# BUG#6857: current_time() in STORED PROCEDURES
-#
---disable_warnings
-drop procedure if exists bug6857|
---enable_warnings
-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|
-
-# QQ: This is currently disabled. Not only does it slow down a normal test
-#     run, it makes running with valgrind (or similar tools) extremely
-#     painful.
-# Make sure this takes at least one second on all machines in all builds.
-# 30000 makes it about 3 seconds on an old 1.1GHz linux.
-#call bug6857(300000)|
-
-drop procedure bug6857|
-
-#
-# BUG#8757: Stored Procedures: Scope of Begin and End Statements do not
-#           work properly.
---disable_warnings
-drop procedure if exists bug8757|
---enable_warnings
-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()|
-
-delete from t1|
-delete from t2|
-drop procedure bug8757|
-
-
-#
-# BUG#8762: Stored Procedures: Inconsistent behavior
-#           of DROP PROCEDURE IF EXISTS statement.
---disable_warnings
-drop procedure if exists bug8762|
---enable_warnings
-# Doesn't exist
-drop procedure if exists bug8762; create procedure bug8762() begin end|
-# Does exist
-drop procedure if exists bug8762; create procedure bug8762() begin end|
-drop procedure bug8762|
-
-
-#
-# BUG#5240: Stored procedure crash if function has cursor declaration
-#
---disable_warnings
-drop function if exists bug5240|
---enable_warnings
-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|
-drop function bug5240|
-
-#
-# BUG#5278: Stored procedure packets out of order if SET PASSWORD.
-#
---disable_warnings
-drop function if exists bug5278|
---enable_warnings
-create function bug5278 () returns char
-begin
-  SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
-  return 'okay';
-end|
-
---error 1133
-select bug5278()|
---error 1133
-select bug5278()|
-drop function bug5278|
-
-#
-# BUG#7992: rolling back temporary Item tree changes in SP
-#
---disable_warnings
-drop procedure if exists p1|
---enable_warnings
-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|
-delimiter ;|
-
-#
-# BUG#8849: problem with insert statement with table alias's
-#
-# Rolling back changes to AND/OR structure of ON and WHERE clauses  in SP
-# 
-
-delimiter |;
-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)
-)|
-
---disable_warnings
-drop procedure if exists bug8849|
---enable_warnings
-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|
-
-#
-# 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|
-
-
-#
-# BUG#6900: Stored procedure inner handler ignored
-# BUG#9074: STORED PROC: The scope of every handler declared is not
-#                        properly applied
-#
---disable_warnings
-drop procedure if exists bug6900|
-drop procedure if exists bug9074|
-drop procedure if exists bug6900_9074|
---enable_warnings
-
-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()|
-call bug9074()|
-call bug6900_9074(0)|
-call bug6900_9074(1)|
-
-drop procedure bug6900|
-drop procedure bug9074|
-drop procedure bug6900_9074|
-drop table t3|
-
-
-#
-# BUG#7185: Stored procedure crash if identifier is AVG
-#
---disable_warnings
-drop procedure if exists avg|
---enable_warnings
-create procedure avg ()
-begin
-end|
-
-call avg ()|
-drop procedure avg|
-
-
-#
-# BUG#6129: Stored procedure won't display @@sql_mode value
-#
---disable_warnings
-drop procedure if exists bug6129|
---enable_warnings
-set @old_mode= @@sql_mode;
-set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO";
-create procedure bug6129()
-  select @@sql_mode|
-call bug6129()|
-set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"|
-call bug6129()|
-set @@sql_mode= "NO_ZERO_IN_DATE"|
-call bug6129()|
-set @@sql_mode=@old_mode;
-
-drop procedure bug6129|
-
-
-#
-# BUG#9856: Stored procedures: crash if handler for sqlexception, not found
-#
---disable_warnings
-drop procedure if exists bug9856|
---enable_warnings
-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()|
-call bug9856()|
-drop procedure bug9856|
-
-
-#
-# BUG##9674: Stored Procs: Using declared vars in algebric operation causes
-#            system crash.
-#
---disable_warnings
-drop procedure if exists bug9674_1|
-drop procedure if exists bug9674_2|
---enable_warnings
-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|
-call bug9674_2()|
-call bug9674_2()|
-drop procedure bug9674_1|
-drop procedure bug9674_2|
-
-
-#
-# BUG#9598: stored procedure call within stored procedure overwrites IN variable
-#
---disable_warnings
-drop procedure if exists bug9598_1|
-drop procedure if exists bug9598_2|
---enable_warnings
-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)|
-select @tmp1, @tmp2|
-
-drop procedure bug9598_1|
-drop procedure bug9598_2|
-
-
-#
-# BUG#9902: Crash with simple stored function using user defined variables
-#
---disable_warnings
-drop procedure if exists bug9902|
---enable_warnings
-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|
-select bug9902() from t1|
-select @x|
-
-set global query_cache_size = @qcs1|
-delete from t1|
-drop function bug9902|
-
-
-#
-# BUG#9102: Stored proccedures: function which returns blob causes crash
-#
---disable_warnings
-drop function if exists bug9102|
---enable_warnings
-create function bug9102() returns blob return 'a'|
-select bug9102()|
-drop function bug9102|
-
-
-#
-# BUG#7648: Stored procedure crash when invoking a function that returns a bit
-#
---disable_warnings
-drop function if exists bug7648|
---enable_warnings
-create function bug7648() returns bit(8) return 'a'|
-select bug7648()|
-drop function bug7648|
-
-
-#
-# BUG#9775: crash if create function that returns enum or set
-#
---disable_warnings
-drop function if exists bug9775|
---enable_warnings
-create function bug9775(v1 char(1)) returns enum('a','b') return v1|
-select bug9775('a'),bug9775('b'),bug9775('c')|
-drop function bug9775|
-create function bug9775(v1 int) returns enum('a','b') return v1|
-select bug9775(1),bug9775(2),bug9775(3)|
-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')|
-drop function bug9775|
-create function bug9775(v1 int) returns set('a','b') return v1|
-select bug9775(1),bug9775(2),bug9775(3),bug9775(4)|
-drop function bug9775|
-
-
-#
-# BUG#8861: If Return is a YEAR data type, value is not shown in year format
-#
---disable_warnings
-drop function if exists bug8861|
---enable_warnings
-create function bug8861(v1 int) returns year return v1|
-select bug8861(05)|
-set @x = bug8861(05)|
-select @x|
-drop function bug8861|
-
-
-#
-# BUG#9004: Inconsistent behaviour of SP re. warnings
-#
---disable_warnings
-drop procedure if exists bug9004_1|
-drop procedure if exists bug9004_2|
---enable_warnings
-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)|
-
-# Truncation warnings expected...
-call bug9004_1('12345678901234567')|
-call bug9004_2('12345678901234567890')|
-
-delete from t1|
-drop procedure bug9004_1|
-drop procedure bug9004_2|
-
-#
-# BUG#7293: Stored procedure crash with soundex
-#
---disable_warnings
-drop procedure if exists bug7293|
---enable_warnings
-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')| 
-call bug7293 ('secrete')| 
-drop procedure bug7293|
-delete from t1|
-
-
-#
-# BUG#9841: Unexpected read lock when trying to update a view in a
-#           stored procedure
-#
---disable_warnings
-drop procedure if exists bug9841|
-drop view if exists v1|
---enable_warnings
-
-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|
-
-
-#
-# BUG#5963 subqueries in SET/IF
-#
---disable_warnings
-drop procedure if exists bug5963|
---enable_warnings
-
-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()|
-call bug5963_1()|
-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|
-
-
-#
-# BUG#9559: Functions: Numeric Operations using -ve value gives incorrect
-#           results.
-#
---disable_warnings
-drop function if exists bug9559|
---enable_warnings
-create function bug9559()
-  returns int
-begin
-  set @y = -6/2;
-  return @y;
-end|
-
-select bug9559()|
-
-drop function bug9559|
-
-
-#
-# BUG#10961: Stored procedures: crash if select * from dual
-#
---disable_warnings
-drop procedure if exists bug10961|
---enable_warnings
-# "select * from dual" results in an error, so the cursor will not open
-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()|
-call bug10961()|
-
-drop procedure bug10961|
-
-#
-# BUG #6866: Second call of a stored procedure using a view with on expressions
-#
-
---disable_warnings
-DROP PROCEDURE IF EXISTS bug6866|
---enable_warnings
-
-DROP VIEW IF EXISTS tv|
-DROP TABLE IF EXISTS tt1,tt2,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)|
-CALL bug6866(1)|
-CALL bug6866(1)|
-
-DROP PROCEDURE bug6866;
-
-DROP VIEW tv|
-DROP TABLE tt1, tt2, tt3|
-
-#
-# BUG#10136: items cleunup
-#
---disable_warnings
-DROP PROCEDURE IF EXISTS bug10136|
---enable_warnings
-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()|
-call bug10136()|
-call bug10136()|
-drop procedure bug10136|
-drop table t3|
-
-#
-# BUG#11529: crash server after use stored procedure
-#
---disable_warnings
-drop procedure if exists bug11529|
---enable_warnings
-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|
-
-
-#
-# BUG#6063: Stored procedure labels are subject to restrictions (partial)
-# BUG#7088: Stored procedures: labels won't work if character set is utf8
-#
---disable_warnings
-drop procedure if exists bug6063|
-drop procedure if exists bug7088_1|
-drop procedure if exists bug7088_2|
---enable_warnings
-
---disable_parsing # temporarily disabled until Bar fixes BUG#11986
-create procedure bug6063()
-  lâbel: begin end|
-call bug6063()|
-# QQ Known bug: this will not show the label correctly.
-show create procedure bug6063|
-
-set character set utf8|
-create procedure bug7088_1()
-  label1: begin end label1|
-create procedure bug7088_2()
-  läbel1: begin end|
-call bug7088_1()|
-call bug7088_2()|
-set character set default|
-show create procedure bug7088_1|
-show create procedure bug7088_2|
-
-drop procedure bug6063|
-drop procedure bug7088_1|
-drop procedure bug7088_2|
---enable_parsing
-
-#
-# BUG#9565: "Wrong locking in stored procedure if a sub-sequent procedure
-#           is called".
-#
---disable_warnings
-drop procedure if exists bug9565_sub|
-drop procedure if exists bug9565|
---enable_warnings
-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()|
-delete from t1|
-drop procedure bug9565_sub|
-drop procedure bug9565|
-
-
-#
-# BUG#9538: SProc: Creation fails if we try to SET system variable
-#           using @@var_name in proc
-#
---disable_warnings
-drop procedure if exists bug9538|
---enable_warnings
-create procedure bug9538()
-  set @@sort_buffer_size = 1000000|
-
-set @x = @@sort_buffer_size|
-set @@sort_buffer_size = 2000000|
-select @@sort_buffer_size|
-call bug9538()|
-select @@sort_buffer_size|
-set @@sort_buffer_size = @x|
-
-drop procedure bug9538|
-
-
-#
-# BUG#8692: Cursor fetch of empty string
-#
---disable_warnings
-drop procedure if exists bug8692|
---enable_warnings
-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)|
-
-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()|
-drop procedure bug8692|
-drop table t3|
-
-#
-# Bug#10055 "Using stored function with information_schema causes empty
-#            result set"
-#
---disable_warnings
-drop function if exists bug10055|
---enable_warnings
-create function bug10055(v char(255)) returns char(255) return lower(v)|
-# This select should not crash server and should return all fields in t1
-select t.column_name, bug10055(t.column_name)
-from information_schema.columns as t
-where t.table_schema = 'test' and t.table_name = 't1'|
-drop function bug10055|
-
-#
-# Bug #12297 "SP crashes the server if data inserted inside a lon loop"
-# The test for memleak bug, so actually there is no way to test it
-# from the suite. The test below could be used to check SP memory
-# consumption by passing large input parameter.
-#
-
---disable_warnings
-drop procedure if exists bug12297|
---enable_warnings
-
-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|
-
-#
-# Bug #11247 "Stored procedures: Function calls in long loops leak memory"
-# One more memleak bug test. One could use this test to check that the memory
-# isn't leaking by increasing the input value for p_bug11247.
-#
-
---disable_warnings
-drop function if exists f_bug11247|
-drop procedure if exists p_bug11247|
---enable_warnings
-
-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|
-#
-# BUG#12168: "'DECLARE CONTINUE HANDLER FOR NOT FOUND ...' in conditional
-# handled incorrectly"
-#
---disable_warnings
-drop procedure if exists bug12168|
-drop table if exists t3, t4|
---enable_warnings
-
-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|
-truncate t4|
-call bug12168('b')|
-select * from t4|
-truncate t4|
-call bug12168('a')|
-select * from t4|
-truncate t4|
-call bug12168('b')|
-select * from t4|
-truncate t4|
-drop table t3, t4|
-drop procedure if exists bug12168|
-
-#
-# Bug #11333 "Stored Procedure: Memory blow up on repeated SELECT ... INTO
-# query"
-# One more memleak bug. Use the test to check memory consumption.
-#
-
---disable_warnings
-drop table if exists t3|
-drop procedure if exists bug11333|
---enable_warnings
-
-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|
-
-#
-# BUG#9048: Creating a function with char binary IN parameter fails
-#
---disable_warnings
-drop function if exists bug9048|
---enable_warnings
-create function bug9048(f1 char binary) returns char binary
-begin
-  set f1= concat( 'hello', f1 );
-  return f1;
-end|
-drop function bug9048|
-
-# Bug #12849 Stored Procedure: Crash on procedure call with CHAR type
-# 'INOUT' parameter
-#
-
---disable_warnings
-drop procedure if exists bug12849_1|
---enable_warnings
-create procedure bug12849_1(inout x char) select x into x|
-set @var='a'|
-call bug12849_1(@var)|
-select @var|
-drop procedure bug12849_1|
-
---disable_warnings
-drop procedure if exists bug12849_2|
---enable_warnings
-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|
-drop procedure bug12849_2|
-
-#
-# BUG#13133: Local variables in stored procedures are not initialized correctly.
-#
---disable_warnings
-drop procedure if exists bug131333|
-drop function if exists bug131333|
---enable_warnings
-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()|
-select bug131333()|
-
-drop procedure bug131333|
-drop function bug131333|
-
-#
-# BUG#12379: PROCEDURE with HANDLER calling FUNCTION with error get
-#            strange result
-#
---disable_warnings
-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|
---enable_warnings
-
-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|
-
---error 1062
-select bug12379()|
-select 1|
-call bug12379_1()|
-select 2|
-call bug12379_2()|
-select 3|
---error 1062
-call bug12379_3()|
-select 4|
-
-drop function bug12379|
-drop procedure bug12379_1|
-drop procedure bug12379_2|
-drop procedure bug12379_3|
-drop table t3|
-
-#
-# Bug #13124    Stored Procedure using SELECT INTO crashes server
-#
-
---disable_warnings
-drop procedure if exists bug13124|
---enable_warnings
-create procedure bug13124()
-begin
-  declare y integer;
-  set @x=y;
-end|
-call bug13124()|
-drop procedure  bug13124|
-
-#
-# Bug #12979  Stored procedures: crash if inout decimal parameter
-#
-
-# check NULL inout parameters processing
-
---disable_warnings
-drop procedure if exists bug12979_1|
---enable_warnings
-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|
-
-# check NULL local variables processing
-
---disable_warnings
-drop procedure if exists bug12979_2|
---enable_warnings
-create procedure bug12979_2()
-begin
-declare internal_var decimal(5);
-set internal_var= internal_var / 2;
-select internal_var;
-end|
-call bug12979_2()|
-drop procedure bug12979_2|
-
-
-#
-# BUG#6127: Stored procedure handlers within handlers don't work
-#
---disable_warnings
-drop table if exists t3|
-drop procedure if exists bug6127|
---enable_warnings
-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|
---error ER_DUP_ENTRY
-call bug6127()|
-select * from t3|
-set sql_mode=@sm|
-drop table t3|
-drop procedure bug6127|
-
-
-#
-# BUG#12589: Assert when creating temp. table from decimal stored procedure
-#            variable
-#
---disable_warnings
-drop procedure if exists bug12589_1|
-drop procedure if exists bug12589_2|
-drop procedure if exists bug12589_3|
---enable_warnings
-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|
-
-# Note: The type of the field will match the value, not the declared
-#       type of the variable. (This is a type checking issue which
-#       might be changed later.)
-
-# Warning expected from "set spv1 = 'test'", the value is set to decimal "0".
-call bug12589_1()|
-# No warnings here
-call bug12589_2()|
-call bug12589_3()|
-drop procedure bug12589_1|
-drop procedure bug12589_2|
-drop procedure bug12589_3|
-
-#
-# BUG#7049: Stored procedure CALL errors are ignored
-#
---disable_warnings
-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|
---enable_warnings
-
-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()|
-select * from t3|
-delete from t3|
-call bug7049_4()|
-select * from t3|
-select bug7049_2()|
-
-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|
-
-
-#
-# BUG#13941: replace() string fuction behaves badly inside stored procedure
-# (BUG#13914: IFNULL is returning garbage in stored procedure)
-#
---disable_warnings
-drop function if exists bug13941|
-drop procedure if exists bug13941|
---enable_warnings
-
-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|
-
-# Note: The bug showed different behaviour in different types of builds,
-#  giving garbage results in some, and seemingly working in others.
-#  Running with valgrind (or purify) is the safe way to check that it's
-#  really working correctly.
-select bug13941('this is a test')|
-call bug13941(@a)|
-select @a|
-
-drop function bug13941|
-drop procedure bug13941|
-
-
-#
-# BUG#13095: Cannot create VIEWs in prepared statements
-#
-
-delimiter ;|
-
---disable_warnings
-DROP PROCEDURE IF EXISTS bug13095;
-DROP TABLE IF EXISTS bug13095_t1;
-DROP VIEW IF EXISTS bug13095_v1;
---enable_warnings
-
-delimiter |;
-
-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|
-
-delimiter ;|
-
-CALL bug13095('bug13095_t1');
-
---disable_warnings
-DROP PROCEDURE IF EXISTS bug13095;
-DROP VIEW IF EXISTS bug13095_v1;
-DROP TABLE IF EXISTS bug13095_t1;
---enable_warnings
-
-delimiter |;
-
-#
-# BUG#14210: "Simple query with > operator on large table gives server
-# crash"
-# Check that cursors work in case when HEAP tables are converted to
-# MyISAM
-#
---disable_warnings
-drop procedure if exists bug14210|
---enable_warnings
-set @@session.max_heap_table_size=16384|
-select @@session.max_heap_table_size|
-# To trigger the memory corruption the original table must be InnoDB.
-# No harm if it's not, so don't warn if the suite is run with --skip-innodb
---disable_warnings
-create table t3 (a char(255)) engine=InnoDB|
---enable_warnings
-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|
-
-drop table t3, t4|
-drop procedure bug14210|
-set @@session.max_heap_table_size=default|
-
-
-#
-# BUG#1473: Dumping of stored functions seems to cause corruption in
-#           the function body
-#
---disable_warnings
-drop function if exists bug14723|
-drop procedure if exists bug14723|
---enable_warnings
-
-delimiter ;;|
-/*!50003 create function bug14723()
- returns bigint(20)
-main_loop: begin
-  return 42;
-end */;;
-show create function bug14723;;
-select bug14723();;
-
-/*!50003 create procedure bug14723()
-main_loop: begin
-  select 42;
-end */;;
-show create procedure bug14723;;
-call bug14723();;
-
-delimiter |;;
-
-drop function bug14723|
-drop procedure bug14723|
-
-#
-# Bug#14845 "mysql_stmt_fetch returns MYSQL_NO_DATA when COUNT(*) is 0"
-# Check that when fetching from a cursor, COUNT(*) works properly.
-#
-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()|
-drop procedure bug14845|
-
-#
-# BUG#13549 "Server crash with nested stored procedures".
-# Server should not crash when during execution of stored procedure
-# we have to parse trigger/function definition and this new trigger/
-# function has more local variables declared than invoking stored
-# procedure and last of these variables is used in argument of NOT
-# operator.
-#
---disable_warnings
-drop procedure if exists bug13549_1|
-drop procedure if exists bug13549_2|
---enable_warnings
-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|
-
-#
-# BUG#10100: function (and stored procedure?) recursivity problem
-#
---disable_warnings
-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|
---enable_warnings
-# routines with simple recursion
-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|
-
-# a procedure which use tables and recursion
-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|
-# view & recursion
-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|
-# dynamic sql & recursion
-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|
-# cursor & recursion
-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|
--- error ER_SP_NO_RECURSION
-select bug10100f(3)|
--- error ER_SP_NO_RECURSION
-select bug10100f(6)|
-call bug10100t(5)|
-call bug10100pt(1,5)|
-call bug10100pv(1,5)|
-update t3 set a=1|
-call bug10100pd(1,5)|
-select * from t3|
-update t3 set a=1|
-call bug10100pc(1,5)|
-select * from t3|
-set @@max_sp_recursion_depth=0|
-select @@max_sp_recursion_depth|
--- error ER_SP_NO_RECURSION
-select bug10100f(5)|
--- error ER_SP_RECURSION_LIMIT
-call bug10100t(5)|
-
-#end of the stack checking
-set @@max_sp_recursion_depth=255|
-set @var=1|
-#disable log because error about stack overrun contains numbers which
-#depend on a system
--- disable_result_log
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100p(255, @var)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pt(1,255)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pv(1,255)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pd(1,255)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pc(1,255)|
--- enable_result_log
-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|
-
-#
-# BUG#13729: Stored procedures: packet error after exception handled
-#
---disable_warnings
-drop procedure if exists bug13729|
-drop table if exists t3|
---enable_warnings
-
-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()|
-# Used to cause Packets out of order
-select * from t3|
-
-drop procedure bug13729|
-drop table t3|
-
-#
-# BUG#14643: Stored Procedure: Continuing after failed var. initialization
-#            crashes server.
-#
---disable_warnings
-drop procedure if exists bug14643_1|
-drop procedure if exists bug14643_2|
---enable_warnings
-
-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()|
-call bug14643_2()|
-
-drop procedure bug14643_1|
-drop procedure bug14643_2|
-
-#
-# BUG#14304: auto_increment field incorrect set in SP
-#
---disable_warnings
-drop procedure if exists bug14304|
-drop table if exists t3, t4|
---enable_warnings
-
-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()|
-
-drop procedure bug14304|
-drop table t3, t4|
-
-#
-# BUG#14376: MySQL crash on scoped variable (re)initialization
-#
---disable_warnings
-drop procedure if exists bug14376|
---enable_warnings
-
-create procedure bug14376()
-begin
-  declare x int default x;
-end|
-
-# Not the error we want, but that's what we got for now...
---error ER_BAD_FIELD_ERROR
-call bug14376()|
-drop procedure bug14376|
-
-create procedure bug14376()
-begin
-  declare x int default 42;
-
-  begin
-    declare x int default x;
-
-    select x;
-  end;
-end|
-
-call bug14376()|
-
-drop procedure bug14376|
-
-create procedure bug14376(x int)
-begin
-  declare x int default x;
-
-  select x;
-end|
-
-call bug14376(4711)|
-
-drop procedure bug14376|
-
-#
-# Bug#5967 "Stored procedure declared variable used instead of column"
-# The bug should be fixed later.
-# Test precedence of names of parameters, variable declarations, 
-# variable declarations in nested compound statements, table columns,
-# table columns in cursor declarations.
-# According to the standard, table columns take precedence over
-# variable declarations. In MySQL 5.0 it's vice versa.
-#
-
---disable_warnings
-drop procedure if exists bug5967|
-drop table if exists t3|
---enable_warnings
-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")|
-drop procedure bug5967|
-
-#
-# Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server"
-#
---disable_warnings
-drop procedure if exists bug13012|
---enable_warnings
-create procedure bug13012()
-BEGIN
-  REPAIR TABLE t1;
-  BACKUP TABLE t1 to '../tmp';
-  DROP TABLE t1;
-  RESTORE TABLE t1 FROM '../tmp';
-END|
-call bug13012()|
-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()|
-call bug13012()|
-call bug13012()|
-drop procedure bug13012|
-drop view v1;
-select * from t1|
-
-#
-# A test case for Bug#15392 "Server crashes during prepared statement
-# execute": make sure that stored procedure check for error conditions
-# properly and do not continue execution if an error has been set. 
-#
-# It's necessary to use several DBs because in the original code
-# the successful return of mysql_change_db overrode the error from
-# execution.
-drop schema if exists mysqltest1|
-drop schema if exists mysqltest2|
-drop schema if exists mysqltest3|
-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|
-
---error ER_SP_WRONG_NO_OF_ARGS
-call mysqltest1.p1(@rs)|
---error ER_SP_WRONG_NO_OF_ARGS
-call mysqltest1.p1(@rs)|
---error ER_SP_WRONG_NO_OF_ARGS
-call mysqltest1.p1(@rs)|
-drop schema if exists mysqltest1|
-drop schema if exists mysqltest2|
-drop schema if exists mysqltest3|
-use test|
-
-#
-# Bug#15441 "Running SP causes Server to Crash": check that an SP variable
-# can not be used in VALUES() function.
-#
---disable_warnings
-drop table if exists t3|
-drop procedure if exists bug15441|
---enable_warnings
-create table t3 (id int not null primary key, county varchar(25))|
-insert into t3 (id, county) values (1, 'York')|
-
-# First check that a stored procedure that refers to a parameter in VALUES()
-# function won't parse.
-
-create procedure bug15441(c varchar(25))
-begin
-  update t3 set id=2, county=values(c);
-end|
---error ER_BAD_FIELD_ERROR
-call bug15441('county')|
-drop procedure bug15441|
-
-# Now check the case when there is an ambiguity between column names
-# and stored procedure parameters: the parser shall resolve the argument
-# of VALUES() function to the column name.
-
-# It's hard to deduce what county refers to in every case (INSERT statement):
-# 1st county refers to the column
-# 2nd county refers to the procedure parameter
-# 3d and 4th county refers to the column, again, but
-# for 4th county it has the value of SP parameter
-
-# In UPDATE statement, just check that values() function returns NULL for
-# non- INSERT...UPDATE statements, as stated in the manual.
-
-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')|
-drop table t3|
-drop procedure bug15441|
-
-#
-# BUG#14498: Stored procedures: hang if undefined variable and exception
-#
---disable_warnings
-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|
---enable_warnings
-
-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()|
-call bug14498_2()|
-call bug14498_3()|
-call bug14498_4()|
-call bug14498_5()|
-
-drop procedure bug14498_1|
-drop procedure bug14498_2|
-drop procedure bug14498_3|
-drop procedure bug14498_4|
-drop procedure bug14498_5|
-
-#
-# BUG#15231: Stored procedure bug with not found condition handler
-#
---disable_warnings
-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|
---enable_warnings
-
-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()|
-call bug15231_3()|
-
-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|
-
-
-#
-# BUG#15011: error handler in nested block not activated
-#
---disable_warnings
-drop procedure if exists bug15011|
---enable_warnings
-
-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()|
-
-drop procedure bug15011|
-drop table t3|
-
-
-#
-# BUG#NNNN: New bug synopsis
-#
-#--disable_warnings
-#drop procedure if exists bugNNNN|
-#--enable_warnings
-#create procedure bugNNNN...
-
-# Add bugs above this line. Use existing tables t1 and t2 when
-# practical, or create table t3, t4 etc temporarily (and drop them).
-delimiter ;|
-drop table t1,t2;
diff --git a/mysys/my_open.c b/mysys/my_open.c
index 6e57132ae23..ab2f7c9ff27 100644
--- a/mysys/my_open.c
+++ b/mysys/my_open.c
@@ -334,7 +334,7 @@ File my_sopen(const char *path, int oflag, int shflag, int pmode)
    * try to open/create the file
    */
   if ((osfh= CreateFile(path, fileaccess, fileshare, &SecurityAttributes, 
-                        filecreate, fileattrib, NULL)) == (HANDLE)0xffffffff)
+                        filecreate, fileattrib, NULL)) == INVALID_HANDLE_VALUE)
   {
     /*
      * OS call to open/create file failed! map the error, release
@@ -345,7 +345,7 @@ File my_sopen(const char *path, int oflag, int shflag, int pmode)
     return -1;                      /* return error to caller */
   }
 
-  fh= _open_osfhandle((long)osfh, oflag & (_O_APPEND | _O_RDONLY | _O_TEXT));
+  fh= _open_osfhandle((intptr_t)osfh, oflag & (_O_APPEND | _O_RDONLY | _O_TEXT));
 
   return fh;                        /* return handle */
 }
diff --git a/sql/udf_example.c b/sql/udf_example.c
index 62995085599..a80fce81278 100644
--- a/sql/udf_example.c
+++ b/sql/udf_example.c
@@ -806,6 +806,7 @@ char *reverse_lookup(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,
 #if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST)
   char name_buff[256];
   struct hostent tmp_hostent;
+  int tmp_errno;
 #endif
   struct hostent *hp;
   unsigned long taddr;
@@ -845,7 +846,6 @@ char *reverse_lookup(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,
     return 0;
   }
 #if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST)
-  int tmp_errno;
   if (!(hp=gethostbyaddr_r((char*) &taddr,sizeof(taddr), AF_INET,
 			   &tmp_hostent, name_buff,sizeof(name_buff),
 			   &tmp_errno)))