summaryrefslogtreecommitdiff
path: root/mysql-test/main/sp.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/sp.test')
-rw-r--r--mysql-test/main/sp.test10122
1 files changed, 10122 insertions, 0 deletions
diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test
new file mode 100644
index 00000000000..c97f6dbba68
--- /dev/null
+++ b/mysql-test/main/sp.test
@@ -0,0 +1,10122 @@
+#
+# Basic stored PROCEDURE tests
+#
+# Test cases for bugs are added at the end. See template there.
+#
+# Some tests that require --error go into sp-error.test
+# Tests that require innodb 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.
+# Tests that require --with-geometry go into sp_gis.test
+# Tests that require multibyte character sets, which are not always available,
+# go into separate files (e.g. sp-ucs2.test)
+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;
+drop view if exists v1;
+drop procedure if exists p1;
+drop procedure if exists p2;
+drop function if exists f1;
+drop function if exists f2;
+--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 order by data|
+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|
+
+let $start_value= `SELECT @@max_join_size`|
+call mixset("mixset", 19)|
+show variables like 'max_join_size'|
+select id,data,@z from t1|
+delete from t1|
+drop procedure mixset|
+--disable_query_log
+eval SET @@max_join_size= $start_value|
+--enable_query_log
+
+# 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 order by data desc|
+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 order by data|
+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 deterministic
+ 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 order by id|
+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 order by data desc|
+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 order by data desc|
+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 order by data desc|
+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 order by data desc|
+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 order by data desc|
+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 order by data|
+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 order by data|
+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 order by data|
+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 order by data;
+end|
+
+call sel1()|
+drop procedure sel1|
+
+--disable_warnings
+drop procedure if exists sel2|
+--enable_warnings
+create procedure sel2()
+begin
+ select * from t1 order by data;
+ select * from t2 order by s;
+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 order by data|
+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 order by data|
+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
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval create procedure into_outfile(x char(16), y int)
+begin
+ insert into test.t1 values (x, y);
+ select * into outfile "$MYSQLTEST_VARDIR/tmp/spout" from test.t1;
+ insert into test.t1 values (concat(x, "2"), y+2);
+end|
+
+# Check that file does not exists
+--error 1
+--file_exists $MYSQLTEST_VARDIR/tmp/spout
+call into_outfile("ofile", 1)|
+--remove_file $MYSQLTEST_VARDIR/tmp/spout
+delete from t1|
+drop procedure into_outfile|
+
+--disable_warnings
+drop procedure if exists into_dumpfile|
+--enable_warnings
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval create procedure into_dumpfile(x char(16), y int)
+begin
+ insert into test.t1 values (x, y);
+ select * into dumpfile "$MYSQLTEST_VARDIR/tmp/spdump" from test.t1 limit 1;
+ insert into test.t1 values (concat(x, "2"), y+2);
+end|
+
+# Check that file does not exists
+--error 1
+--file_exists $MYSQLTEST_VARDIR/tmp/spdump
+call into_dumpfile("dfile", 1)|
+--remove_file $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) order by i|
+select * from t2 where d = e()|
+select * from t2 order by i|
+--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 order by id,data;
+ declare c2 cursor for select i from test.t2 order by i;
+ 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 order by i,s|
+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 order by id|
+
+# 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) order by data limit 1)|
+select f2()|
+select id, f2() from t1 order by id|
+
+# 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 order by id|
+
+# Calling two functions using same table
+select f1(), f3()|
+select id, f1(), f3() from t1 order by id|
+
+# 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 order by s|
+
+# 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 order by id|
+
+#
+# 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 order by id|
+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 order by id|
+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 ER_VIEW_INVALID
+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 order by id|
+# Degenerate cases work too :)
+select f0()|
+# But these should not (particularly views should be locked explicitly).
+--error ER_TABLE_NOT_LOCKED
+select * from v0|
+--error ER_TABLE_NOT_LOCKED
+select *, f0() from v0, (select 123) as d1|
+--error ER_TABLE_NOT_LOCKED
+select id, f3() from t1|
+--error ER_TABLE_NOT_LOCKED
+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 ER_TABLE_NOT_LOCKED
+select * from v1, t1|
+--error ER_TABLE_NOT_LOCKED
+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()|
+
+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|
+select f11()|
+select f11() from t1|
+# Test that using a single table instance at a time works
+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)|
+
+drop temporary table t3|
+select f12_1()|
+drop temporary table t3|
+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|
+truncate table t1 |
+truncate table 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 4 'root@localhost' 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 4 'root@localhost' 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 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status where name like '%p%' and db='test'|
+
+# 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 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+show procedure status where name like '%p%' and db='test'|
+
+
+#
+# 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 4 'root@localhost' 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 4 'root@localhost' 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 order by data|
+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 order by data|
+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 order by id|
+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 order by i|
+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 where db='test';
+end|
+
+--disable_warnings
+drop procedure if exists bug2267_2|
+--enable_warnings
+create procedure bug2267_2()
+begin
+ show function status where db='test';
+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|
+
+# so that from_unixtime() has a deterministic result
+set time_zone='+03:00';
+
+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#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 create table t1;
+ show create database test;
+ show databases like 'foo';
+ show errors;
+ show columns from t1;
+ show keys from t1;
+ show open tables like 'foo';
+ # Removed because result will differ in embedded mode.
+ #show privileges;
+ show status like 'foo';
+ show tables like 'foo';
+ show variables like 'foo';
+ show warnings;
+end|
+--disable_parsing
+--replace_regex /table_id: [0-9]+/table_id: #/
+show binlog events|
+show storage engines|
+show master status|
+show slave hosts|
+show slave status|
+--enable_parsing
+
+call bug4902()|
+call bug4902()|
+
+drop procedure bug4902|
+
+#
+# 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#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()
+begin
+ declare t0, t1 int;
+ declare plus bool default 0;
+ set t0 = unix_timestamp();
+ select sleep(1.1);
+ set t1 = unix_timestamp();
+ if t1 > t0 then
+ set plus = 1;
+ end if;
+ select plus;
+end|
+
+call bug6857()|
+
+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#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|
+
+--disable_query_log
+--echo # Set query cache size, if we have query cache
+if (`select @@have_query_cache='YES'`) {
+ set @qcs1 = @@query_cache_size|
+ set global query_cache_size = 102400|
+}
+--enable_query_log
+set @x = 1|
+insert into t1 values ("qc", 42)|
+select bug9902() from t1|
+select bug9902() from t1|
+select @x|
+
+--echo # Restore the old query cache size
+--disable_query_log
+if (`select @@have_query_cache='YES'`) {
+ set global query_cache_size = @qcs1|
+}
+--enable_query_log
+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
+#
+
+set character set utf8|
+
+--disable_warnings
+drop procedure if exists bug6063|
+drop procedure if exists bug7088_1|
+drop procedure if exists bug7088_2|
+--enable_warnings
+
+create procedure bug6063()
+begin
+ lâbel: begin end;
+ label: begin end;
+ label1: begin end;
+end|
+
+create procedure bug7088_1()
+ label1: begin end label1|
+
+create procedure bug7088_2()
+ läbel1: begin end|
+
+call bug6063()|
+call bug7088_1()|
+call bug7088_2()|
+
+set character set default|
+
+show create procedure bug6063|
+show create procedure bug7088_1|
+show create procedure bug7088_2|
+
+drop procedure bug6063|
+drop procedure bug7088_1|
+drop procedure bug7088_2|
+
+#
+# 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
+begin
+ set f1= concat( 'hello', f1 );
+ return f1;
+end|
+drop function bug9048|
+
+create function bug9048(f1 char binary) returns char binary
+begin
+ set f1= concat( 'hello', f1 );
+ return f1;
+end|
+select collation(bug9048("foo"))|
+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 ER_DUP_ENTRY
+select bug12379()|
+select 1|
+# statement-based binlogging will show warning which row-based won't;
+# so we hide it (this warning is already tested in rpl_stm_sp.test)
+--disable_warnings
+call bug12379_1()|
+select 2|
+call bug12379_2()|
+--enable_warnings
+select 3|
+--error ER_DUP_ENTRY
+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#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
+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"
+#
+--let $backupdir = $MYSQLTEST_VARDIR/tmp/
+--error 0,1
+--remove_file $backupdir/t1.frm
+--error 0,1
+--remove_file $backupdir/t1.MYD
+
+--disable_warnings
+drop procedure if exists bug13012|
+--enable_warnings
+# Disable warnings also for BACKUP/RESTORE: they are deprecated.
+eval create procedure bug13012()
+ BEGIN
+ REPAIR TABLE t1;
+ END|
+call bug13012()|
+
+--enable_warnings
+
+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 order by data|
+
+#
+# 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=value(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=value(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|
+drop procedure if exists bug15231_5|
+drop procedure if exists bug15231_6|
+--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 (correct)' 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';
+ show warnings;
+end|
+
+create procedure bug15231_5()
+begin
+ declare exit handler for sqlwarning
+ select 'Caught it (wrong)' as 'Result';
+
+ call bug15231_6();
+end|
+
+create procedure bug15231_6()
+begin
+ declare x decimal(2,1);
+
+ set x = 'zap';
+ select 'Missed it (correct)' as 'Result';
+ select id from t3;
+end|
+
+call bug15231_1()|
+call bug15231_3()|
+call bug15231_5()|
+
+drop table t3|
+drop procedure bug15231_1|
+drop procedure bug15231_2|
+drop procedure bug15231_3|
+drop procedure bug15231_4|
+drop procedure bug15231_5|
+drop procedure bug15231_6|
+
+
+#
+# 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#17476: Stored procedure not returning data when it is called first
+# time per connection
+#
+--disable_warnings
+drop procedure if exists bug17476|
+--enable_warnings
+
+create table t3 ( d date )|
+insert into t3 values
+ ( '2005-01-01' ), ( '2005-01-02' ), ( '2005-01-03' ),
+ ( '2005-01-04' ), ( '2005-02-01' ), ( '2005-02-02' )|
+
+create procedure bug17476(pDateFormat varchar(10))
+ select date_format(t3.d, pDateFormat), count(*)
+ from t3
+ group by date_format(t3.d, pDateFormat)|
+
+call bug17476('%Y-%m')|
+call bug17476('%Y-%m')|
+
+drop table t3|
+drop procedure bug17476|
+
+
+#
+# BUG#16887: Cursor causes server segfault
+#
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug16887|
+--enable_warnings
+
+create table t3 ( c varchar(1) )|
+
+insert into t3 values
+ (' '),('.'),(';'),(','),('-'),('_'),('('),(')'),('/'),('\\')|
+
+create procedure bug16887()
+begin
+ declare i int default 10;
+
+ again:
+ while i > 0 do
+ begin
+ declare breakchar varchar(1);
+ declare done int default 0;
+ declare t3_cursor cursor for select c from t3;
+ declare continue handler for not found set done = 1;
+
+ set i = i - 1;
+ select i;
+
+ if i = 3 then
+ iterate again;
+ end if;
+
+ open t3_cursor;
+
+ loop
+ fetch t3_cursor into breakchar;
+
+ if done = 1 then
+ begin
+ close t3_cursor;
+ iterate again;
+ end;
+ end if;
+ end loop;
+ end;
+ end while;
+end|
+
+call bug16887()|
+
+drop table t3|
+drop procedure bug16887|
+
+#
+# BUG#16474: SP crashed MySQL
+# (when using "order by localvar", where 'localvar' is just that.
+#
+--disable_warnings
+drop procedure if exists bug16474_1|
+drop procedure if exists bug16474_2|
+--enable_warnings
+
+delete from t1|
+insert into t1 values ('c', 2), ('b', 3), ('a', 1)|
+
+create procedure bug16474_1()
+begin
+ declare x int;
+
+ select id from t1 order by x, id;
+end|
+
+#
+# BUG#14945: Truncate table doesn't reset the auto_increment counter
+#
+--disable_warnings
+drop procedure if exists bug14945|
+--enable_warnings
+create table t3 (id int not null auto_increment primary key)|
+create procedure bug14945() deterministic truncate t3|
+insert into t3 values (null)|
+call bug14945()|
+insert into t3 values (null)|
+select * from t3|
+drop table t3|
+drop procedure bug14945|
+
+# This does NOT order by column index; variable is an expression.
+create procedure bug16474_2(x int)
+ select id from t1 order by x, id|
+
+call bug16474_1()|
+call bug16474_2(1)|
+call bug16474_2(2)|
+drop procedure bug16474_1|
+drop procedure bug16474_2|
+
+# For reference: user variables are expressions too and do not affect ordering.
+set @x = 2|
+select * from t1 order by @x, data|
+
+delete from t1|
+
+
+#
+# BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
+#
+# The solution is not to reset last_insert_id on enter to sub-statement.
+#
+--disable_warnings
+drop function if exists bug15728|
+drop table if exists t3|
+--enable_warnings
+
+create table t3 (
+ id int not null auto_increment,
+ primary key (id)
+)|
+create function bug15728() returns int(11)
+ return last_insert_id()|
+
+insert into t3 values (0)|
+select last_insert_id()|
+select bug15728()|
+
+drop function bug15728|
+drop table t3|
+
+
+#
+# BUG#18787: Server crashed when calling a stored procedure containing
+# a misnamed function
+#
+--disable_warnings
+drop procedure if exists bug18787|
+--enable_warnings
+create procedure bug18787()
+begin
+ declare continue handler for sqlexception begin end;
+
+ select no_such_function();
+end|
+
+call bug18787()|
+drop procedure bug18787|
+
+
+#
+# BUG#18344: DROP DATABASE does not drop associated routines
+# (... if the database name is longer than 21 characters)
+#
+# 1234567890123456789012
+create database bug18344_012345678901|
+use bug18344_012345678901|
+create procedure bug18344() begin end|
+create procedure bug18344_2() begin end|
+
+create database bug18344_0123456789012|
+use bug18344_0123456789012|
+create procedure bug18344() begin end|
+create procedure bug18344_2() begin end|
+
+use test|
+
+--sorted_result
+select schema_name from information_schema.schemata where
+ schema_name like 'bug18344%'|
+--sorted_result
+select routine_name,routine_schema from information_schema.routines where
+ routine_schema like 'bug18344%'|
+
+drop database bug18344_012345678901|
+drop database bug18344_0123456789012|
+
+# Should be nothing left.
+select schema_name from information_schema.schemata where
+ schema_name like 'bug18344%'|
+select routine_name,routine_schema from information_schema.routines where
+ routine_schema like 'bug18344%'|
+
+
+#
+# BUG#12472/BUG#15137 'CREATE TABLE ... SELECT ... which explicitly or
+# implicitly uses stored function gives "Table not locked" error'.
+#
+--disable_warnings
+drop function if exists bug12472|
+--enable_warnings
+create function bug12472() returns int return (select count(*) from t1)|
+# Check case when function is used directly
+create table t3 as select bug12472() as i|
+show create table t3|
+select * from t3|
+drop table t3|
+# Check case when function is used indirectly through view
+create view v1 as select bug12472() as j|
+create table t3 as select * from v1|
+show create table t3|
+select * from t3|
+drop table t3|
+drop view v1|
+drop function bug12472|
+
+
+#
+# BUG#18587: Function that accepts and returns TEXT garbles data if longer than
+# 766 chars
+#
+
+# Prepare.
+
+--disable_warnings
+DROP FUNCTION IF EXISTS bug18589_f1|
+DROP PROCEDURE IF EXISTS bug18589_p1|
+DROP PROCEDURE IF EXISTS bug18589_p2|
+--enable_warnings
+
+CREATE FUNCTION bug18589_f1(arg TEXT) RETURNS TEXT
+BEGIN
+ RETURN CONCAT(arg, "");
+END|
+
+CREATE PROCEDURE bug18589_p1(arg TEXT, OUT ret TEXT)
+BEGIN
+ SET ret = CONCAT(arg, "");
+END|
+
+CREATE PROCEDURE bug18589_p2(arg TEXT)
+BEGIN
+ DECLARE v TEXT;
+ CALL bug18589_p1(arg, v);
+ SELECT v;
+END|
+
+# Test case.
+
+SELECT bug18589_f1(REPEAT("a", 767))|
+
+SET @bug18589_v1 = ""|
+CALL bug18589_p1(REPEAT("a", 767), @bug18589_v1)|
+SELECT @bug18589_v1|
+
+CALL bug18589_p2(REPEAT("a", 767))|
+
+# Cleanup.
+
+DROP FUNCTION bug18589_f1|
+DROP PROCEDURE bug18589_p1|
+DROP PROCEDURE bug18589_p2|
+
+
+#
+# BUG#18037: Server crash when returning system variable in stored procedures
+# BUG#19633: Stack corruption in fix_fields()/THD::rollback_item_tree_changes()
+#
+
+# Prepare.
+
+--disable_warnings
+DROP FUNCTION IF EXISTS bug18037_f1|
+DROP PROCEDURE IF EXISTS bug18037_p1|
+DROP PROCEDURE IF EXISTS bug18037_p2|
+--enable_warnings
+
+# Test case.
+
+CREATE FUNCTION bug18037_f1() RETURNS INT
+BEGIN
+ RETURN @@server_id;
+END|
+
+CREATE PROCEDURE bug18037_p1()
+BEGIN
+ DECLARE v INT DEFAULT @@server_id;
+END|
+
+CREATE PROCEDURE bug18037_p2()
+BEGIN
+ CASE @@server_id
+ WHEN -1 THEN
+ SELECT 0;
+ ELSE
+ SELECT 1;
+ END CASE;
+END|
+
+SELECT bug18037_f1()|
+CALL bug18037_p1()|
+CALL bug18037_p2()|
+
+# Cleanup.
+
+DROP FUNCTION bug18037_f1|
+DROP PROCEDURE bug18037_p1|
+DROP PROCEDURE bug18037_p2|
+
+#
+# Bug#17199: "Table not found" error occurs if the query contains a call
+# to a function from another database.
+# See also ps.test for an additional test case for this bug.
+#
+use test|
+create table t3 (i int)|
+insert into t3 values (1), (2)|
+create database mysqltest1|
+use mysqltest1|
+create function bug17199() returns varchar(2) deterministic return 'ok'|
+use test|
+select *, mysqltest1.bug17199() from t3|
+#
+# Bug#18444: Fully qualified stored function names don't work correctly
+# in select statements
+#
+use mysqltest1|
+create function bug18444(i int) returns int no sql deterministic return i + 1|
+use test|
+select mysqltest1.bug18444(i) from t3|
+drop database mysqltest1|
+#
+# Check that current database has no influence to a stored procedure
+#
+create database mysqltest1 charset=utf8|
+create database mysqltest2 charset=utf8|
+create procedure mysqltest1.p1()
+begin
+-- alters the default collation of database test
+ alter database character set koi8r;
+end|
+use mysqltest1|
+call p1()|
+show create database mysqltest1|
+show create database mysqltest2|
+alter database mysqltest1 character set utf8|
+use mysqltest2|
+call mysqltest1.p1()|
+show create database mysqltest1|
+show create database mysqltest2|
+drop database mysqltest1|
+drop database mysqltest2|
+#
+# Restore the old environemnt
+use test|
+#
+# Bug#15217 "Using a SP cursor on a table created with PREPARE fails with
+# weird error". Check that the code that is supposed to work at
+# the first execution of a stored procedure actually works for
+# sp_instr_copen.
+
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists bug15217|
+--enable_warnings
+create table t3 as select 1|
+create procedure bug15217()
+begin
+ declare var1 char(255);
+ declare cur1 cursor for select * from t3;
+ open cur1;
+ fetch cur1 into var1;
+ select concat('data was: /', var1, '/');
+ close cur1;
+end |
+# Returns expected result
+call bug15217()|
+flush tables |
+# Returns error with garbage as column name
+call bug15217()|
+drop table t3|
+drop procedure bug15217|
+
+
+#
+# BUG#21013: Performance Degrades when importing data that uses
+# Trigger and Stored Procedure
+#
+# This is a performance and memory leak test. Run with large number
+# passed to bug21013() procedure.
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug21013 |
+--enable_warnings
+
+CREATE PROCEDURE bug21013(IN lim INT)
+BEGIN
+ DECLARE i INT DEFAULT 0;
+ WHILE (i < lim) DO
+ SET @b = LOCATE(_latin1'b', @a, 1);
+ SET i = i + 1;
+ END WHILE;
+END |
+
+SET @a = _latin2"aaaaaaaaaa" |
+CALL bug21013(10) |
+
+DROP PROCEDURE bug21013 |
+
+
+#
+# BUG#16211: Stored function return type for strings is ignored
+#
+
+# Prepare: create database with fixed, pre-defined character set.
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest1|
+DROP DATABASE IF EXISTS mysqltest2|
+--enable_warnings
+
+CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
+CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8|
+
+# Test case:
+
+use mysqltest1|
+
+# - Create two stored functions -- with and without explicit CHARSET-clause
+# for return value;
+
+CREATE FUNCTION bug16211_f1() RETURNS CHAR(10)
+ RETURN ""|
+
+CREATE FUNCTION bug16211_f2() RETURNS CHAR(10) CHARSET koi8r
+ RETURN ""|
+
+CREATE FUNCTION mysqltest2.bug16211_f3() RETURNS CHAR(10)
+ RETURN ""|
+
+CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r
+ RETURN ""|
+
+# - Check that CHARSET-clause is specified for the second function;
+
+SHOW CREATE FUNCTION bug16211_f1|
+SHOW CREATE FUNCTION bug16211_f2|
+
+SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
+SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
+
+SELECT CHARSET(bug16211_f1())|
+SELECT CHARSET(bug16211_f2())|
+
+SELECT CHARSET(mysqltest2.bug16211_f3())|
+SELECT CHARSET(mysqltest2.bug16211_f4())|
+
+# - Alter database character set.
+
+ALTER DATABASE mysqltest1 CHARACTER SET cp1251|
+ALTER DATABASE mysqltest2 CHARACTER SET cp1251|
+
+# - Check that CHARSET-clause has not changed.
+
+SHOW CREATE FUNCTION bug16211_f1|
+SHOW CREATE FUNCTION bug16211_f2|
+
+SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
+SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
+
+SELECT CHARSET(bug16211_f1())|
+SELECT CHARSET(bug16211_f2())|
+
+SELECT CHARSET(mysqltest2.bug16211_f3())|
+SELECT CHARSET(mysqltest2.bug16211_f4())|
+
+# Cleanup.
+
+use test|
+
+DROP DATABASE mysqltest1|
+DROP DATABASE mysqltest2|
+
+
+#
+# BUG#16676: Database CHARSET not used for stored procedures
+#
+
+# Prepare: create database with fixed, pre-defined character set.
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest1|
+--enable_warnings
+
+CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
+
+# Test case:
+
+use mysqltest1|
+
+# - Create two stored procedures -- with and without explicit CHARSET-clause;
+
+CREATE PROCEDURE bug16676_p1(
+ IN p1 CHAR(10),
+ INOUT p2 CHAR(10),
+ OUT p3 CHAR(10))
+BEGIN
+ SELECT CHARSET(p1), COLLATION(p1);
+ SELECT CHARSET(p2), COLLATION(p2);
+ SELECT CHARSET(p3), COLLATION(p3);
+END|
+
+CREATE PROCEDURE bug16676_p2(
+ IN p1 CHAR(10) CHARSET koi8r,
+ INOUT p2 CHAR(10) CHARSET cp1251,
+ OUT p3 CHAR(10) CHARSET greek)
+BEGIN
+ SELECT CHARSET(p1), COLLATION(p1);
+ SELECT CHARSET(p2), COLLATION(p2);
+ SELECT CHARSET(p3), COLLATION(p3);
+END|
+
+# - Call procedures.
+
+SET @v2 = 'b'|
+SET @v3 = 'c'|
+
+CALL bug16676_p1('a', @v2, @v3)|
+CALL bug16676_p2('a', @v2, @v3)|
+
+# Cleanup.
+
+use test|
+
+DROP DATABASE mysqltest1|
+#
+# BUG#8153: Stored procedure with subquery and continue handler, wrong result
+#
+
+--disable_warnings
+drop table if exists t3|
+drop table if exists t4|
+drop procedure if exists bug8153_subselect|
+drop procedure if exists bug8153_subselect_a|
+drop procedure if exists bug8153_subselect_b|
+drop procedure if exists bug8153_proc_a|
+drop procedure if exists bug8153_proc_b|
+--enable_warnings
+
+create table t3 (a int)|
+create table t4 (a int)|
+insert into t3 values (1), (1), (2), (3)|
+insert into t4 values (1), (1)|
+
+## Testing the use case reported in Bug#8153
+
+create procedure bug8153_subselect()
+begin
+ declare continue handler for sqlexception
+ begin
+ select 'statement failed';
+ end;
+ update t3 set a=a+1 where (select a from t4 where a=1) is null;
+ select 'statement after update';
+end|
+
+call bug8153_subselect()|
+select * from t3|
+
+call bug8153_subselect()|
+select * from t3|
+
+drop procedure bug8153_subselect|
+
+## Testing a subselect with a non local handler
+
+create procedure bug8153_subselect_a()
+begin
+ declare continue handler for sqlexception
+ begin
+ select 'in continue handler';
+ end;
+
+ select 'reachable code a1';
+ call bug8153_subselect_b();
+ select 'reachable code a2';
+end|
+
+create procedure bug8153_subselect_b()
+begin
+ select 'reachable code b1';
+ update t3 set a=a+1 where (select a from t4 where a=1) is null;
+ select 'unreachable code b2';
+end|
+
+call bug8153_subselect_a()|
+select * from t3|
+
+call bug8153_subselect_a()|
+select * from t3|
+
+drop procedure bug8153_subselect_a|
+drop procedure bug8153_subselect_b|
+
+## Testing extra use cases, found while investigating
+## This is related to BUG#18787, with a non local handler
+
+create procedure bug8153_proc_a()
+begin
+ declare continue handler for sqlexception
+ begin
+ select 'in continue handler';
+ end;
+
+ select 'reachable code a1';
+ call bug8153_proc_b();
+ select 'reachable code a2';
+end|
+
+create procedure bug8153_proc_b()
+begin
+ select 'reachable code b1';
+ select no_such_function();
+ select 'unreachable code b2';
+end|
+
+call bug8153_proc_a()|
+
+drop procedure bug8153_proc_a|
+drop procedure bug8153_proc_b|
+drop table t3|
+drop table t4|
+
+#
+# BUG#19862: Sort with filesort by function evaluates function twice
+#
+--disable_warnings
+drop procedure if exists bug19862|
+--enable_warnings
+CREATE TABLE t11 (a INT)|
+CREATE TABLE t12 (a INT)|
+CREATE FUNCTION bug19862(x INT) RETURNS INT
+ BEGIN
+ INSERT INTO t11 VALUES (x);
+ RETURN x+1;
+ END|
+INSERT INTO t12 VALUES (1), (2)|
+SELECT bug19862(a) FROM t12 ORDER BY 1|
+SELECT * FROM t11|
+DROP TABLE t11, t12|
+DROP FUNCTION bug19862|
+
+
+# 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
+# set an empty string as the current database. They do not belong to any
+# database and must be usable even if there is no database
+# selected.
+--disable_warnings
+drop table if exists t3|
+drop database if exists mysqltest1|
+--enable_warnings
+create table t3 (a int)|
+insert into t3 (a) values (1), (2)|
+
+create database mysqltest1|
+use mysqltest1|
+drop database mysqltest1|
+
+# No current database
+select database()|
+
+select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2|
+use test|
+drop table t3|
+
+
+# Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause.
+#
+# Prepare.
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug16899_p1|
+DROP FUNCTION IF EXISTS bug16899_f1|
+--enable_warnings
+
+--error ER_WRONG_STRING_LENGTH
+CREATE DEFINER=longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost PROCEDURE bug16899_p1()
+BEGIN
+ SET @a = 1;
+END|
+
+--error ER_WRONG_STRING_LENGTH
+CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY
+ FUNCTION bug16899_f1() RETURNS INT
+BEGIN
+ RETURN 1;
+END|
+
+
+#
+# BUG#21416: SP: Recursion level higher than zero needed for non-recursive call
+#
+--disable_warnings
+drop procedure if exists bug21416|
+--enable_warnings
+create procedure bug21416() show create procedure bug21416|
+call bug21416()|
+drop procedure bug21416|
+
+
+#
+# BUG#21414: SP: Procedure undroppable, to some extent
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug21414|
+--enable_warnings
+
+CREATE PROCEDURE bug21414() SELECT 1|
+
+FLUSH TABLES WITH READ LOCK|
+
+--error ER_CANT_UPDATE_WITH_READLOCK
+DROP PROCEDURE bug21414|
+
+UNLOCK TABLES|
+
+--echo The following should succeed.
+DROP PROCEDURE bug21414|
+
+
+#
+# BUG#21311: Possible stack overrun if SP has non-latin1 name
+#
+set names utf8|
+--disable_warnings
+drop database if exists това_е_дълго_име_за_база_данни_нали|
+--enable_warnings
+create database това_е_дълго_име_за_база_данни_нали|
+INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_Ñ_доÑта_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_Ñ_доÑта_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','', 'utf8', 'utf8_general_ci', 'utf8_general_ci', 'n/a', 'NONE')|
+--error ER_SP_PROC_TABLE_CORRUPT
+call това_е_дълго_име_за_база_данни_нали.това_е_процедура_Ñ_доÑта_дълго_име_нали_и_още_по_дълго()|
+drop database това_е_дълго_име_за_база_данни_нали|
+
+
+#
+# BUG#21493: Crash on the second call of a procedure containing
+# a select statement that uses an IN aggregating subquery
+#
+
+CREATE TABLE t3 (
+ Member_ID varchar(15) NOT NULL,
+ PRIMARY KEY (Member_ID)
+)|
+
+CREATE TABLE t4 (
+ ID int(10) unsigned NOT NULL auto_increment,
+ Member_ID varchar(15) NOT NULL default '',
+ Action varchar(12) NOT NULL,
+ Action_Date datetime NOT NULL,
+ Track varchar(15) default NULL,
+ User varchar(12) default NULL,
+ Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update
+ CURRENT_TIMESTAMP,
+ PRIMARY KEY (ID),
+ KEY Action (Action),
+ KEY Action_Date (Action_Date)
+)|
+
+
+INSERT INTO t3(Member_ID) VALUES
+ ('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')|
+
+INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES
+ ('111111', 'Disenrolled', '2006-03-01', 'CAD' ),
+ ('111111', 'Enrolled', '2006-03-01', 'CAD' ),
+ ('111111', 'Disenrolled', '2006-07-03', 'CAD' ),
+ ('222222', 'Enrolled', '2006-03-07', 'CAD' ),
+ ('222222', 'Enrolled', '2006-03-07', 'CHF' ),
+ ('222222', 'Disenrolled', '2006-08-02', 'CHF' ),
+ ('333333', 'Enrolled', '2006-03-01', 'CAD' ),
+ ('333333', 'Disenrolled', '2006-03-01', 'CAD' ),
+ ('444444', 'Enrolled', '2006-03-01', 'CAD' ),
+ ('555555', 'Disenrolled', '2006-03-01', 'CAD' ),
+ ('555555', 'Enrolled', '2006-07-21', 'CAD' ),
+ ('555555', 'Disenrolled', '2006-03-01', 'CHF' ),
+ ('666666', 'Enrolled', '2006-02-09', 'CAD' ),
+ ('666666', 'Enrolled', '2006-05-12', 'CHF' ),
+ ('666666', 'Disenrolled', '2006-06-01', 'CAD' )|
+
+--disable_warnings
+DROP FUNCTION IF EXISTS bug21493|
+--enable_warnings
+
+CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45)
+BEGIN
+DECLARE tracks VARCHAR(45);
+SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4
+ WHERE Member_ID=paramMember AND Action='Enrolled' AND
+ (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4
+ WHERE Member_ID=paramMember GROUP BY Track);
+RETURN tracks;
+END|
+
+SELECT bug21493('111111')|
+SELECT bug21493('222222')|
+
+SELECT bug21493(Member_ID) FROM t3|
+
+DROP FUNCTION bug21493|
+DROP TABLE t3,t4|
+
+#
+# Bug#20028 Function with select return no data
+#
+
+--disable_warnings
+drop function if exists func_20028_a|
+drop function if exists func_20028_b|
+drop function if exists func_20028_c|
+drop procedure if exists proc_20028_a|
+drop procedure if exists proc_20028_b|
+drop procedure if exists proc_20028_c|
+drop table if exists table_20028|
+--enable_warnings
+
+create table table_20028 (i int)|
+
+SET @save_sql_mode=@@sql_mode|
+
+SET sql_mode=''|
+
+create function func_20028_a() returns integer
+begin
+ declare temp integer;
+ select i into temp from table_20028 limit 1;
+ return ifnull(temp, 0);
+end|
+
+create function func_20028_b() returns integer
+begin
+ return func_20028_a();
+end|
+
+create function func_20028_c() returns integer
+begin
+ declare div_zero integer;
+ set SQL_MODE='TRADITIONAL';
+ select 1/0 into div_zero;
+ return div_zero;
+end|
+
+create procedure proc_20028_a()
+begin
+ declare temp integer;
+ select i into temp from table_20028 limit 1;
+end|
+
+create procedure proc_20028_b()
+begin
+ call proc_20028_a();
+end|
+
+create procedure proc_20028_c()
+begin
+ declare div_zero integer;
+ set SQL_MODE='TRADITIONAL';
+ select 1/0 into div_zero;
+end|
+
+select func_20028_a()|
+select func_20028_b()|
+--error ER_DIVISION_BY_ZERO
+select func_20028_c()|
+call proc_20028_a()|
+call proc_20028_b()|
+--error ER_DIVISION_BY_ZERO
+call proc_20028_c()|
+
+SET sql_mode='TRADITIONAL'|
+
+drop function func_20028_a|
+drop function func_20028_b|
+drop function func_20028_c|
+drop procedure proc_20028_a|
+drop procedure proc_20028_b|
+drop procedure proc_20028_c|
+
+create function func_20028_a() returns integer
+begin
+ declare temp integer;
+ select i into temp from table_20028 limit 1;
+ return ifnull(temp, 0);
+end|
+
+create function func_20028_b() returns integer
+begin
+ return func_20028_a();
+end|
+
+create function func_20028_c() returns integer
+begin
+ declare div_zero integer;
+ set SQL_MODE='';
+ select 1/0 into div_zero;
+ return div_zero;
+end|
+
+create procedure proc_20028_a()
+begin
+ declare temp integer;
+ select i into temp from table_20028 limit 1;
+end|
+
+create procedure proc_20028_b()
+begin
+ call proc_20028_a();
+end|
+
+create procedure proc_20028_c()
+begin
+ declare div_zero integer;
+ set SQL_MODE='';
+ select 1/0 into div_zero;
+end|
+
+select func_20028_a()|
+select func_20028_b()|
+select func_20028_c()|
+call proc_20028_a()|
+call proc_20028_b()|
+call proc_20028_c()|
+
+SET @@sql_mode=@save_sql_mode|
+
+drop function func_20028_a|
+drop function func_20028_b|
+drop function func_20028_c|
+drop procedure proc_20028_a|
+drop procedure proc_20028_b|
+drop procedure proc_20028_c|
+drop table table_20028|
+
+#
+# Bug#21462 Stored procedures with no arguments require parenthesis
+#
+
+--disable_warnings
+drop procedure if exists proc_21462_a|
+drop procedure if exists proc_21462_b|
+--enable_warnings
+
+create procedure proc_21462_a()
+begin
+ select "Called A";
+end|
+
+create procedure proc_21462_b(x int)
+begin
+ select "Called B";
+end|
+
+call proc_21462_a|
+call proc_21462_a()|
+-- error ER_SP_WRONG_NO_OF_ARGS
+call proc_21462_a(1)|
+
+-- error ER_SP_WRONG_NO_OF_ARGS
+call proc_21462_b|
+-- error ER_SP_WRONG_NO_OF_ARGS
+call proc_21462_b()|
+call proc_21462_b(1)|
+
+drop procedure proc_21462_a|
+drop procedure proc_21462_b|
+
+
+#
+# Bug#19733 "Repeated alter, or repeated create/drop, fails"
+# Check that CREATE/DROP INDEX is re-execution friendly.
+#
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists proc_bug19733|
+--enable_warnings
+create table t3 (s1 int)|
+
+create procedure proc_bug19733()
+begin
+ declare v int default 0;
+ while v < 100 do
+ create index i on t3 (s1);
+ drop index i on t3;
+ set v = v + 1;
+ end while;
+end|
+
+call proc_bug19733()|
+call proc_bug19733()|
+call proc_bug19733()|
+
+drop procedure proc_bug19733|
+drop table t3|
+
+
+#
+# BUG#20492: Subsequent calls to stored procedure yeild incorrect
+# result if join is used
+#
+# Optimized ON expression in join wasn't properly saved for reuse.
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1|
+DROP VIEW IF EXISTS v1, v2|
+DROP TABLE IF EXISTS t3, t4|
+--enable_warnings
+
+CREATE TABLE t3 (t3_id INT)|
+
+INSERT INTO t3 VALUES (0)|
+INSERT INTO t3 VALUES (1)|
+
+CREATE TABLE t4 (t4_id INT)|
+
+INSERT INTO t4 VALUES (2)|
+
+CREATE VIEW v1 AS
+SELECT t3.t3_id, t4.t4_id
+FROM t3 JOIN t4 ON t3.t3_id = 0|
+
+CREATE VIEW v2 AS
+SELECT t3.t3_id AS t3_id_1, v1.t3_id AS t3_id_2, v1.t4_id
+FROM t3 LEFT JOIN v1 ON t3.t3_id = 0|
+
+CREATE PROCEDURE p1() SELECT * FROM v2|
+
+# Results should not differ.
+CALL p1()|
+CALL p1()|
+
+DROP PROCEDURE p1|
+DROP VIEW v1, v2|
+DROP TABLE t3, t4|
+
+--echo End of 5.0 tests
+
+--echo Begin of 5.1 tests
+
+#
+# BUG#18239: Possible to overload internal functions with stored functions
+#
+
+delimiter ;|
+
+--disable_warnings
+drop function if exists pi;
+--enable_warnings
+
+create function pi() returns varchar(50)
+return "pie, my favorite desert.";
+
+SET @save_sql_mode=@@sql_mode;
+
+SET SQL_MODE='IGNORE_SPACE';
+
+select pi(), pi ();
+
+# Non deterministic warnings from db_load_routine
+--disable_warnings
+select test.pi(), test.pi ();
+--enable_warnings
+
+SET SQL_MODE='';
+
+select pi(), pi ();
+
+# Non deterministic warnings from db_load_routine
+--disable_warnings
+select test.pi(), test.pi ();
+--enable_warnings
+
+SET @@sql_mode=@save_sql_mode;
+
+drop function pi;
+# End of BUG#18239
+
+#
+# BUG#22619: Spaces considered harmful
+#
+
+--disable_warnings
+drop function if exists test.database;
+drop function if exists test.current_user;
+drop function if exists test.md5;
+--enable_warnings
+
+create database nowhere;
+use nowhere;
+drop database nowhere;
+
+SET @save_sql_mode=@@sql_mode;
+
+SET SQL_MODE='IGNORE_SPACE';
+
+select database(), database ();
+select current_user(), current_user ();
+select md5("aaa"), md5 ("aaa");
+
+SET SQL_MODE='';
+
+select database(), database ();
+select current_user(), current_user ();
+select md5("aaa"), md5 ("aaa");
+
+use test;
+
+create function `database`() returns varchar(50)
+return "Stored function database";
+
+create function `current_user`() returns varchar(50)
+return "Stored function current_user";
+
+create function md5(x varchar(50)) returns varchar(50)
+return "Stored function md5";
+
+SET SQL_MODE='IGNORE_SPACE';
+
+select database(), database ();
+select current_user(), current_user ();
+select md5("aaa"), md5 ("aaa");
+
+# Non deterministic warnings from db_load_routine
+--disable_warnings
+select test.database(), test.database ();
+select test.current_user(), test.current_user ();
+select test.md5("aaa"), test.md5 ("aaa");
+--enable_warnings
+
+SET SQL_MODE='';
+
+select database(), database ();
+select current_user(), current_user ();
+select md5("aaa"), md5 ("aaa");
+
+# Non deterministic warnings from db_load_routine
+--disable_warnings
+select test.database(), test.database ();
+select test.current_user(), test.current_user ();
+select test.md5("aaa"), test.md5 ("aaa");
+--enable_warnings
+
+SET @@sql_mode=@save_sql_mode;
+
+drop function test.database;
+drop function test.current_user;
+drop function md5;
+
+use test;
+delimiter |;
+# End of BUG#22619
+
+--echo End of 5.1 tests
+
+#
+# BUG#23760: ROW_COUNT() and store procedure not owrking together
+#
+--disable_warnings
+DROP TABLE IF EXISTS bug23760|
+DROP TABLE IF EXISTS bug23760_log|
+DROP PROCEDURE IF EXISTS bug23760_update_log|
+DROP PROCEDURE IF EXISTS bug23760_test_row_count|
+DROP FUNCTION IF EXISTS bug23760_rc_test|
+--enable_warnings
+CREATE TABLE bug23760 (
+ id INT NOT NULL AUTO_INCREMENT ,
+ num INT NOT NULL ,
+ PRIMARY KEY ( id )
+)|
+
+CREATE TABLE bug23760_log (
+ id INT NOT NULL AUTO_INCREMENT ,
+ reason VARCHAR(50)NULL ,
+ ammount INT NOT NULL ,
+ PRIMARY KEY ( id )
+)|
+
+CREATE PROCEDURE bug23760_update_log(r Varchar(50), a INT)
+BEGIN
+ INSERT INTO bug23760_log (reason, ammount) VALUES(r, a);
+END|
+
+CREATE PROCEDURE bug23760_test_row_count()
+BEGIN
+ UPDATE bug23760 SET num = num + 1;
+ CALL bug23760_update_log('Test is working', ROW_COUNT());
+ UPDATE bug23760 SET num = num - 1;
+END|
+
+
+CREATE PROCEDURE bug23760_test_row_count2(level INT)
+BEGIN
+ IF level THEN
+ UPDATE bug23760 SET num = num + 1;
+ CALL bug23760_update_log('Test2 is working', ROW_COUNT());
+ CALL bug23760_test_row_count2(level - 1);
+ END IF;
+END|
+
+CREATE FUNCTION bug23760_rc_test(in_var INT) RETURNS INT RETURN in_var|
+
+INSERT INTO bug23760 (num) VALUES (0), (1), (1), (2), (3), (5), (8)|
+SELECT ROW_COUNT()|
+
+CALL bug23760_test_row_count()|
+SELECT * FROM bug23760_log ORDER BY id|
+
+SET @save_max_sp_recursion= @@max_sp_recursion_depth|
+SELECT @save_max_sp_recursion|
+SET max_sp_recursion_depth= 5|
+SELECT @@max_sp_recursion_depth|
+CALL bug23760_test_row_count2(2)|
+SELECT ROW_COUNT()|
+SELECT * FROM bug23760_log ORDER BY id|
+SELECT * FROM bug23760 ORDER by ID|
+SET max_sp_recursion_depth= @save_max_sp_recursion|
+
+SELECT bug23760_rc_test(123)|
+INSERT INTO bug23760 (num) VALUES (13), (21), (34), (55)|
+SELECT bug23760_rc_test(ROW_COUNT())|
+
+DROP TABLE bug23760, bug23760_log|
+DROP PROCEDURE bug23760_update_log|
+DROP PROCEDURE bug23760_test_row_count|
+DROP PROCEDURE bug23760_test_row_count2|
+DROP FUNCTION bug23760_rc_test|
+
+#
+# BUG#24117: server crash on a FETCH with a cursor on a table which is not in
+# the table cache
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS bug24117|
+DROP TABLE IF EXISTS t3|
+--enable_warnings
+CREATE TABLE t3(c1 ENUM('abc'))|
+INSERT INTO t3 VALUES('abc')|
+CREATE PROCEDURE bug24117()
+BEGIN
+ DECLARE t3c1 ENUM('abc');
+ DECLARE mycursor CURSOR FOR SELECT c1 FROM t3;
+ OPEN mycursor;
+ FLUSH TABLES;
+ FETCH mycursor INTO t3c1;
+ CLOSE mycursor;
+END|
+CALL bug24117()|
+DROP PROCEDURE bug24117|
+DROP TABLE t3|
+
+#
+# Bug#8407(Stored functions/triggers ignore exception handler)
+#
+
+--disable_warnings
+drop function if exists func_8407_a|
+drop function if exists func_8407_b|
+--enable_warnings
+
+create function func_8407_a() returns int
+begin
+ declare x int;
+
+ declare continue handler for sqlexception
+ begin
+ end;
+
+ select 1 from no_such_view limit 1 into x;
+
+ return x;
+end|
+
+create function func_8407_b() returns int
+begin
+ declare x int default 0;
+
+ declare continue handler for sqlstate '42S02'
+ begin
+ set x:= x+1000;
+ end;
+
+ case (select 1 from no_such_view limit 1)
+ when 1 then set x:= x+1;
+ when 2 then set x:= x+2;
+ else set x:= x+100;
+ end case;
+ set x:=x + 500;
+
+ return x;
+end|
+
+select func_8407_a()|
+select func_8407_b()|
+
+drop function func_8407_a|
+drop function func_8407_b|
+
+#
+# Bug#26503 (Illegal SQL exception handler code causes the server to crash)
+#
+
+--disable_warnings
+drop table if exists table_26503|
+drop procedure if exists proc_26503_ok_1|
+drop procedure if exists proc_26503_ok_2|
+drop procedure if exists proc_26503_ok_3|
+drop procedure if exists proc_26503_ok_4|
+--enable_warnings
+
+create table table_26503(a int unique)|
+
+create procedure proc_26503_ok_1(v int)
+begin
+ declare i int default 5;
+
+ declare continue handler for sqlexception
+ begin
+ select 'caught something';
+ retry:
+ while i > 0 do
+ begin
+ set i = i - 1;
+ select 'looping', i;
+ iterate retry;
+ select 'dead code';
+ end;
+ end while retry;
+ select 'leaving handler';
+ end;
+
+ select 'do something';
+ insert into table_26503 values (v);
+ select 'do something again';
+ insert into table_26503 values (v);
+end|
+
+create procedure proc_26503_ok_2(v int)
+begin
+ declare i int default 5;
+
+ declare continue handler for sqlexception
+ begin
+ select 'caught something';
+ retry:
+ while i > 0 do
+ begin
+ set i = i - 1;
+ select 'looping', i;
+ leave retry;
+ select 'dead code';
+ end;
+ end while;
+ select 'leaving handler';
+ end;
+
+ select 'do something';
+ insert into table_26503 values (v);
+ select 'do something again';
+ insert into table_26503 values (v);
+end|
+
+## The outer retry label should not prevent using the inner label.
+
+create procedure proc_26503_ok_3(v int)
+begin
+ declare i int default 5;
+
+retry:
+ begin
+ declare continue handler for sqlexception
+ begin
+ select 'caught something';
+ retry:
+ while i > 0 do
+ begin
+ set i = i - 1;
+ select 'looping', i;
+ iterate retry;
+ select 'dead code';
+ end;
+ end while retry;
+ select 'leaving handler';
+ end;
+
+ select 'do something';
+ insert into table_26503 values (v);
+ select 'do something again';
+ insert into table_26503 values (v);
+ end;
+end|
+
+## The outer retry label should not prevent using the inner label.
+
+create procedure proc_26503_ok_4(v int)
+begin
+ declare i int default 5;
+
+retry:
+ begin
+ declare continue handler for sqlexception
+ begin
+ select 'caught something';
+ retry:
+ while i > 0 do
+ begin
+ set i = i - 1;
+ select 'looping', i;
+ leave retry;
+ select 'dead code';
+ end;
+ end while;
+ select 'leaving handler';
+ end;
+
+ select 'do something';
+ insert into table_26503 values (v);
+ select 'do something again';
+ insert into table_26503 values (v);
+ end;
+end|
+
+call proc_26503_ok_1(1)|
+call proc_26503_ok_2(2)|
+call proc_26503_ok_3(3)|
+call proc_26503_ok_4(4)|
+
+drop table table_26503|
+drop procedure proc_26503_ok_1|
+drop procedure proc_26503_ok_2|
+drop procedure proc_26503_ok_3|
+drop procedure proc_26503_ok_4|
+
+#
+# Bug#25373: Stored functions wasn't compared correctly which leads to a wrong
+# result.
+#
+--disable_warnings
+DROP FUNCTION IF EXISTS bug25373|
+--disable_warnings
+CREATE FUNCTION bug25373(p1 INTEGER) RETURNS INTEGER
+LANGUAGE SQL DETERMINISTIC
+RETURN p1;|
+CREATE TABLE t3 (f1 INT, f2 FLOAT)|
+INSERT INTO t3 VALUES (1, 3.4), (1, 2), (1, 0.9), (2, 8), (2, 7)|
+SELECT SUM(f2), bug25373(f1) FROM t3 GROUP BY bug25373(f1) WITH ROLLUP|
+DROP FUNCTION bug25373|
+DROP TABLE t3|
+
+
+#
+# BUG#25082: Default database change on trigger execution breaks replication.
+#
+# As it turned out, this bug has actually two bugs. So, here we have two test
+# cases -- one in sp.test, the other in sp-security.test.
+#
+
+#
+# Test case 1: error on dropping the current database.
+#
+
+# Prepare.
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest1|
+DROP DATABASE IF EXISTS mysqltest2|
+--enable_warnings
+
+CREATE DATABASE mysqltest1|
+CREATE DATABASE mysqltest2|
+
+# Test.
+
+CREATE PROCEDURE mysqltest1.p1()
+ DROP DATABASE mysqltest2|
+
+use mysqltest2|
+
+CALL mysqltest1.p1()|
+
+SELECT DATABASE()|
+
+# Cleanup.
+
+DROP DATABASE mysqltest1|
+
+use test|
+
+
+#
+# Bug#20777: Function w BIGINT UNSIGNED shows diff. behaviour --ps-protocol
+--disable_warnings
+drop function if exists bug20777|
+drop table if exists examplebug20777|
+--enable_warnings
+create function bug20777(f1 bigint unsigned) returns bigint unsigned
+begin
+ set f1 = (f1 - 10); set f1 = (f1 + 10);
+return f1;
+end|
+delimiter ;|
+select bug20777(9223372036854775803) as '9223372036854775803 2**63-5';
+select bug20777(9223372036854775804) as '9223372036854775804 2**63-4';
+select bug20777(9223372036854775805) as '9223372036854775805 2**63-3';
+select bug20777(9223372036854775806) as '9223372036854775806 2**63-2';
+select bug20777(9223372036854775807) as '9223372036854775807 2**63-1';
+select bug20777(9223372036854775808) as '9223372036854775808 2**63+0';
+select bug20777(9223372036854775809) as '9223372036854775809 2**63+1';
+select bug20777(9223372036854775810) as '9223372036854775810 2**63+2';
+--error ER_DATA_OUT_OF_RANGE
+select bug20777(-9223372036854775808) as 'lower bounds signed bigint';
+select bug20777(9223372036854775807) as 'upper bounds signed bigint';
+--error ER_DATA_OUT_OF_RANGE
+select bug20777(0) as 'lower bounds unsigned bigint';
+select bug20777(18446744073709551615) as 'upper bounds unsigned bigint';
+select bug20777(18446744073709551616) as 'upper bounds unsigned bigint + 1';
+--error ER_DATA_OUT_OF_RANGE
+select bug20777(-1) as 'lower bounds unsigned bigint - 1';
+
+create table examplebug20777 as select
+ 0 as 'i',
+ bug20777(9223372036854775806) as '2**63-2',
+ bug20777(9223372036854775807) as '2**63-1',
+ bug20777(9223372036854775808) as '2**63',
+ bug20777(9223372036854775809) as '2**63+1',
+ bug20777(18446744073709551614) as '2**64-2',
+ bug20777(18446744073709551615) as '2**64-1',
+ bug20777(18446744073709551616) as '2**64';
+insert into examplebug20777 values (1, 9223372036854775806, 9223372036854775807, 223372036854775808, 9223372036854775809, 18446744073709551614, 18446744073709551615, 8446744073709551616);
+show create table examplebug20777;
+select * from examplebug20777 order by i;
+
+drop table examplebug20777;
+select bug20777(18446744073709551613)+1;
+drop function bug20777;
+delimiter |;
+
+
+#
+# BUG#5274: Stored procedure crash if length of CHAR variable too great.
+#
+
+# Prepare.
+
+--disable_warnings
+DROP FUNCTION IF EXISTS bug5274_f1|
+DROP FUNCTION IF EXISTS bug5274_f2|
+--enable_warnings
+
+# Test.
+
+CREATE FUNCTION bug5274_f1(p1 CHAR) RETURNS CHAR
+ RETURN CONCAT(p1, p1)|
+
+CREATE FUNCTION bug5274_f2() RETURNS CHAR
+BEGIN
+ DECLARE v1 INT DEFAULT 0;
+ DECLARE v2 CHAR DEFAULT 'x';
+
+ WHILE v1 < 30 DO
+ SET v1 = v1 + 1;
+ SET v2 = bug5274_f1(v2);
+ END WHILE;
+
+ RETURN v2;
+END|
+
+SELECT bug5274_f2()|
+
+# Cleanup.
+
+DROP FUNCTION bug5274_f1|
+DROP FUNCTION bug5274_f2|
+
+#
+# Bug#21513 (SP having body starting with quoted label rendered unusable)
+#
+--disable_warnings
+drop procedure if exists proc_21513|
+--enable_warnings
+
+create procedure proc_21513()`my_label`:BEGIN END|
+show create procedure proc_21513|
+
+drop procedure proc_21513|
+
+###
+--echo End of 5.0 tests.
+
+#
+# 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).
+# NOTE: The delimiter is `|`, and not `;`. It is changed to `;`
+# at the end of the file!
+#
+
+delimiter ;|
+drop table t1,t2;
+
+# Disable warnings to allow test run without InnoDB
+--disable_warnings
+CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM;
+CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb;
+--enable_warnings
+set @a=0;
+
+delimiter |;
+CREATE function bug27354() RETURNS int not deterministic
+begin
+insert into t1 values (null);
+set @a=@a+1;
+return @a;
+end|
+
+delimiter ;|
+update t2 set b=1 where a=bug27354();
+select count(t_1.a),count(t_2.a) from t1 as t_1, t2 as t_2 /* must be 0,0 */;
+insert into t2 values (1,1),(2,2),(3,3);
+update t2 set b=-b where a=bug27354();
+select * from t2 /* must return 1,-1 ... */;
+select count(*) from t1 /* must be 3 */;
+
+
+drop table t1,t2;
+drop function bug27354;
+
+#
+# Bug #28605: SHOW CREATE VIEW with views using stored_procedures no longer
+# showing SP names.
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE FUNCTION metered(a INT) RETURNS INT RETURN 12;
+
+CREATE VIEW v1 AS SELECT test.metered(a) as metered FROM t1;
+
+SHOW CREATE VIEW v1;
+
+DROP VIEW v1;
+DROP FUNCTION metered;
+DROP TABLE t1;
+
+#
+# Bug#29834: Accessing a view column by name in SP/PS causes a memory leak.
+#
+# This is leak test. Run with large number assigned to $execute_cnt,
+# $p1_cnt, $p2_cnt, @p1_p2_cnt, $f1_normal_cnt or $f1_prep_cnt variables.
+#
+
+let $execute_cnt= 2;
+let $p1_cnt= 2;
+let $p2_cnt= 2;
+SET @p1_p2_cnt= 2;
+let $f1_normal_cnt= 2;
+let $f1_prep_cnt= 2;
+
+CREATE TABLE t1 (c1 INT);
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+PREPARE s1 FROM 'SELECT c1 FROM v1';
+while ($execute_cnt)
+{
+ EXECUTE s1;
+ dec $execute_cnt;
+}
+
+DELIMITER |;
+
+CREATE PROCEDURE p1(IN loops BIGINT(19) UNSIGNED)
+BEGIN
+ WHILE loops > 0 DO
+ SELECT c1 FROM v1;
+ SET loops = loops - 1;
+ END WHILE;
+END|
+
+CREATE PROCEDURE p2(IN loops BIGINT(19) UNSIGNED)
+BEGIN
+ WHILE loops > 0 DO
+ SELECT c1 FROM v1;
+ CALL p1(@p1_p2_cnt);
+ SET loops = loops - 1;
+ END WHILE;
+END|
+
+CREATE FUNCTION f1(loops INT UNSIGNED)
+ RETURNS INT
+BEGIN
+ DECLARE tmp INT;
+ WHILE loops > 0 DO
+ SELECT c1 INTO tmp FROM v1;
+ SET loops = loops - 1;
+ END WHILE;
+ RETURN loops;
+END|
+
+DELIMITER ;|
+
+eval CALL p1($p1_cnt);
+eval CALL p2($p2_cnt);
+
+eval SELECT f1($f1_normal_cnt);
+
+eval PREPARE s1 FROM 'SELECT f1($f1_prep_cnt)';
+EXECUTE s1;
+EXECUTE s1;
+
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP FUNCTION f1;
+DROP VIEW v1;
+DROP TABLE t1;
+
+#
+# Bug#28551 "The warning 'No database selected' is reported when calling
+# stored procedures"
+#
+--disable_warnings
+drop database if exists mysqltest_db1;
+--enable_warnings
+create database mysqltest_db1;
+create procedure mysqltest_db1.sp_bug28551() begin end;
+call mysqltest_db1.sp_bug28551();
+show warnings;
+drop database mysqltest_db1;
+#
+# Bug#29050 Creation of a legal stored procedure fails if a database is not
+# selected prior
+#
+--disable_warnings
+drop database if exists mysqltest_db1;
+drop table if exists test.t1;
+--enable_warnings
+create database mysqltest_db1;
+use mysqltest_db1;
+# For the sake of its side effect
+drop database mysqltest_db1;
+# Now we have no current database selected.
+create table test.t1 (id int);
+insert into test.t1 (id) values (1);
+delimiter //;
+create procedure test.sp_bug29050() begin select * from t1; end//
+delimiter ;//
+show warnings;
+call test.sp_bug29050();
+show warnings;
+# Restore the old current database
+use test;
+drop procedure sp_bug29050;
+drop table t1;
+
+#
+# Bug #30120 SP with local variables with non-ASCII names crashes server.
+#
+
+SET NAMES latin1;
+
+DELIMITER |;
+
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE áâä INT;
+ SELECT áâä;
+END|
+
+DELIMITER ;|
+
+CALL p1();
+
+SET NAMES default;
+DROP PROCEDURE p1;
+
+#
+# Bug#25411 (trigger code truncated)
+#
+
+--disable_warnings
+drop procedure if exists proc_25411_a;
+drop procedure if exists proc_25411_b;
+drop procedure if exists proc_25411_c;
+--enable_warnings
+
+delimiter $$;
+
+create procedure proc_25411_a()
+begin
+ /* real comment */
+ select 1;
+ /*! select 2; */
+ select 3;
+ /*!00000 select 4; */
+ /*!999999 select 5; */
+end
+$$
+
+create procedure proc_25411_b(
+/* real comment */
+/*! p1 int, */
+/*!00000 p2 int */
+/*!999999 ,p3 int */
+)
+begin
+ select p1, p2;
+end
+$$
+
+create procedure proc_25411_c()
+begin
+ select 1/*!,2*//*!00000,3*//*!999999,4*/;
+ select 1/*! ,2*//*!00000 ,3*//*!999999 ,4*/;
+ select 1/*!,2 *//*!00000,3 *//*!999999,4 */;
+ select 1/*! ,2 *//*!00000 ,3 *//*!999999 ,4 */;
+ select 1 /*!,2*/ /*!00000,3*/ /*!999999,4*/ ;
+end
+$$
+
+delimiter ;$$
+
+show create procedure proc_25411_a;
+call proc_25411_a();
+
+show create procedure proc_25411_b;
+select name, param_list, body from mysql.proc where name like "%25411%";
+call proc_25411_b(10, 20);
+
+show create procedure proc_25411_c;
+call proc_25411_c();
+
+drop procedure proc_25411_a;
+drop procedure proc_25411_b;
+drop procedure proc_25411_c;
+
+
+#
+# Bug#26302 (MySQL server cuts off trailing "*/" from comments in SP/func)
+#
+
+--disable_warnings
+drop procedure if exists proc_26302;
+--enable_warnings
+
+create procedure proc_26302()
+select 1 /* testing */;
+
+show create procedure proc_26302;
+
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES
+where ROUTINE_NAME = "proc_26302";
+
+drop procedure proc_26302;
+
+
+# Bug #29338: no optimization for stored functions with a trivial body
+# always returning constant.
+#
+
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2;
+CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3;
+
+CREATE TABLE t1 (c1 INT, INDEX(c1));
+
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+
+CREATE VIEW v1 AS SELECT c1 FROM t1;
+
+EXPLAIN SELECT * FROM t1 WHERE c1=1;
+EXPLAIN SELECT * FROM t1 WHERE c1=f1();
+
+EXPLAIN SELECT * FROM v1 WHERE c1=1;
+EXPLAIN SELECT * FROM v1 WHERE c1=f1();
+
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
+EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand());
+
+
+DROP VIEW v1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
+
+#
+# Bug#29408 Cannot find view in columns table if the selection contains a function
+#
+delimiter |;
+
+create function f1()
+ returns int(11)
+not deterministic
+contains sql
+sql security definer
+comment ''
+begin
+ declare x int(11);
+ set x=-1;
+ return x;
+end|
+
+delimiter ;|
+
+create view v1 as select 1 as one, f1() as days;
+
+connect (bug29408, localhost, root,,*NO-ONE*);
+connection bug29408;
+
+show create view test.v1;
+select column_name from information_schema.columns
+where table_name='v1' and table_schema='test';
+
+connection default;
+disconnect bug29408;
+drop view v1;
+drop function f1;
+
+#
+# Bug#13675: DATETIME/DATE type in store proc param seems to be converted as
+# varbinary
+#
+
+--echo
+--echo # Bug#13675.
+--echo
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+--echo
+
+CREATE PROCEDURE p1(v DATETIME) CREATE TABLE t1 SELECT v;
+
+CREATE PROCEDURE p2(v INT) CREATE TABLE t1 SELECT v;
+
+--echo
+CALL p1(NOW());
+SHOW CREATE TABLE t1;
+
+--echo
+DROP TABLE t1;
+
+--echo
+CALL p1('text');
+SHOW CREATE TABLE t1;
+
+--echo
+DROP TABLE t1;
+
+--echo
+CALL p2(10);
+SHOW CREATE TABLE t1;
+
+--echo
+DROP TABLE t1;
+
+--echo
+CALL p2('text');
+SHOW CREATE TABLE t1;
+
+--echo
+DROP TABLE t1;
+
+--echo
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+
+###########################################################################
+
+#
+# Bug#31035: select from function, group by result crasher.
+#
+
+###########################################################################
+
+--echo
+
+--echo #
+--echo # Bug#31035.
+--echo #
+
+--echo
+
+--echo #
+--echo # - Prepare.
+--echo #
+
+--echo
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP FUNCTION IF EXISTS f3;
+DROP FUNCTION IF EXISTS f4;
+--enable_warnings
+
+--echo
+
+--echo #
+--echo # - Create required objects.
+--echo #
+
+--echo
+
+CREATE TABLE t1(c1 INT);
+
+--echo
+
+INSERT INTO t1 VALUES (1), (2), (3);
+
+--echo
+
+CREATE FUNCTION f1()
+ RETURNS INT
+ NOT DETERMINISTIC
+ RETURN 1;
+
+--echo
+
+CREATE FUNCTION f2(p INT)
+ RETURNS INT
+ NOT DETERMINISTIC
+ RETURN 1;
+
+--echo
+
+CREATE FUNCTION f3()
+ RETURNS INT
+ DETERMINISTIC
+ RETURN 1;
+
+--echo
+
+CREATE FUNCTION f4(p INT)
+ RETURNS INT
+ DETERMINISTIC
+ RETURN 1;
+
+--echo
+
+--echo #
+--echo # - Check.
+--echo #
+
+--echo
+
+# Not deterministic function, no arguments.
+
+SELECT f1() AS a FROM t1 GROUP BY a;
+
+--echo
+
+# Not deterministic function, non-constant argument.
+
+SELECT f2(@a) AS a FROM t1 GROUP BY a;
+
+--echo
+
+# Deterministic function, no arguments.
+
+SELECT f3() AS a FROM t1 GROUP BY a;
+
+--echo
+
+# Deterministic function, constant argument.
+
+SELECT f4(0) AS a FROM t1 GROUP BY a;
+
+--echo
+
+# Deterministic function, non-constant argument.
+
+SELECT f4(@a) AS a FROM t1 GROUP BY a;
+
+--echo
+
+--echo #
+--echo # - Cleanup.
+--echo #
+
+--echo
+
+DROP TABLE t1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP FUNCTION f3;
+DROP FUNCTION f4;
+
+--echo
+
+###########################################################################
+
+#
+# Bug#31191: JOIN in combination with stored function crashes the server.
+#
+
+###########################################################################
+
+--echo #
+--echo # Bug#31191.
+--echo #
+
+--echo
+
+--echo #
+--echo # - Prepare.
+--echo #
+
+--echo
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+--echo
+
+--echo #
+--echo # - Create required objects.
+--echo #
+
+--echo
+
+CREATE TABLE t1 (
+ id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ barcode INT(8) UNSIGNED ZEROFILL nOT NULL,
+ PRIMARY KEY (id),
+ UNIQUE KEY barcode (barcode)
+);
+
+--echo
+
+INSERT INTO t1 (id, barcode) VALUES (1, 12345678);
+INSERT INTO t1 (id, barcode) VALUES (2, 12345679);
+
+--echo
+
+CREATE TABLE test.t2 (
+ id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ barcode BIGINT(11) UNSIGNED ZEROFILL NOT NULL,
+ PRIMARY KEY (id)
+);
+
+--echo
+
+INSERT INTO test.t2 (id, barcode) VALUES (1, 12345106708);
+INSERT INTO test.t2 (id, barcode) VALUES (2, 12345106709);
+
+--echo
+
+CREATE FUNCTION f1(p INT(8))
+ RETURNS BIGINT(11) UNSIGNED
+ READS SQL DATA
+ RETURN FLOOR(p/1000)*1000000 + 100000 + FLOOR((p MOD 1000)/10)*100 + (p MOD 10);
+
+--echo
+
+--echo #
+--echo # - Check.
+--echo #
+
+--echo
+
+SELECT DISTINCT t1.barcode, f1(t1.barcode)
+FROM t1
+INNER JOIN t2
+ON f1(t1.barcode) = t2.barcode
+WHERE t1.barcode=12345678;
+
+--echo
+
+--echo #
+--echo # - Cleanup.
+--echo #
+
+--echo
+
+DROP TABLE t1;
+DROP TABLE t2;
+DROP FUNCTION f1;
+
+--echo
+
+###########################################################################
+
+#
+# Bug#31226: Group by function crashes mysql.
+#
+
+###########################################################################
+
+--echo #
+--echo # Bug#31226.
+--echo #
+
+--echo
+
+--echo #
+--echo # - Prepare.
+--echo #
+
+--echo
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+--echo
+
+--echo #
+--echo # - Create required objects.
+--echo #
+
+--echo
+
+CREATE TABLE t1(id INT);
+
+--echo
+
+INSERT INTO t1 VALUES (1), (2), (3);
+
+--echo
+
+CREATE FUNCTION f1()
+ RETURNS DATETIME
+ NOT DETERMINISTIC NO SQL
+ RETURN NOW();
+
+--echo
+
+--echo #
+--echo # - Check.
+--echo #
+
+--echo
+
+--replace_column 1 <timestamp>
+SELECT f1() FROM t1 GROUP BY 1;
+
+--echo
+
+--echo #
+--echo # - Cleanup.
+--echo #
+
+--echo
+
+DROP TABLE t1;
+DROP FUNCTION f1;
+
+--echo
+
+###########################################################################
+
+#
+# Bug#28318 (CREATE FUNCTION (UDF) requires a schema)
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS db28318_a.t1;
+DROP PROCEDURE IF EXISTS db28318_b.t2;
+DROP DATABASE IF EXISTS db28318_a;
+DROP DATABASE IF EXISTS db28318_b;
+--enable_warnings
+
+CREATE DATABASE db28318_a;
+CREATE DATABASE db28318_b;
+
+CREATE PROCEDURE db28318_a.t1() SELECT "db28318_a.t1";
+CREATE PROCEDURE db28318_b.t2() CALL t1();
+
+use db28318_a;
+
+# In db28318_b.t2, t1 refers to db28318_b.t1
+--error ER_SP_DOES_NOT_EXIST
+CALL db28318_b.t2();
+
+DROP PROCEDURE db28318_a.t1;
+DROP PROCEDURE db28318_b.t2;
+DROP DATABASE db28318_a;
+DROP DATABASE db28318_b;
+use test;
+
+###########################################################################
+
+#
+# Bug#29770 Two handlers are allowed to catch an error in an stored procedure.
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP PROCEDURE IF EXISTS bug29770;
+--enable_warnings
+
+CREATE TABLE t1(a int);
+delimiter |;
+CREATE PROCEDURE bug29770()
+BEGIN
+ DECLARE CONTINUE HANDLER FOR SQLSTATE '42S22' SET @state:= 'run';
+ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @exception:= 'run';
+ SELECT x FROM t1;
+END|
+delimiter ;|
+CALL bug29770();
+SELECT @state, @exception;
+DROP TABLE t1;
+DROP PROCEDURE bug29770;
+
+#
+# Bug#33618 Crash in sp_rcontext
+#
+
+use test;
+
+--disable_warnings
+drop table if exists t_33618;
+drop procedure if exists proc_33618;
+--enable_warnings
+
+create table t_33618 (`a` int, unique(`a`), `b` varchar(30)) engine=myisam;
+insert into t_33618 (`a`,`b`) values (1,'1'),(2,'2');
+
+delimiter //;
+
+create procedure proc_33618(num int)
+begin
+ declare count1 int default '0';
+ declare vb varchar(30);
+ declare last_row int;
+
+ while(num>=1) do
+ set num=num-1;
+ begin
+ declare cur1 cursor for select `a` from t_33618;
+ declare continue handler for not found set last_row = 1;
+ set last_row:=0;
+ open cur1;
+ rep1:
+ repeat
+ begin
+ declare exit handler for 1062 begin end;
+ fetch cur1 into vb;
+ if (last_row = 1) then
+ leave rep1;
+ end if;
+ end;
+ until last_row=1
+ end repeat;
+ close cur1;
+ end;
+ end while;
+end//
+
+delimiter ;//
+
+call proc_33618(20);
+
+drop table t_33618;
+drop procedure proc_33618;
+
+--echo #
+--echo # Bug#30787: Stored function ignores user defined alias.
+--echo #
+use test;
+--disable_warnings
+drop function if exists func30787;
+--enable_warnings
+create table t1(f1 int);
+insert into t1 values(1),(2);
+delimiter |;
+create function func30787(p1 int) returns int
+begin
+ return p1;
+end |
+delimiter ;|
+select (select func30787(f1)) as ttt from t1;
+drop function func30787;
+drop table t1;
+
+#
+# Bug #33811: Call to stored procedure with SELECT * / RIGHT JOIN fails
+# after the first time
+#
+CREATE TABLE t1 (id INT);
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+
+CREATE PROCEDURE test_sp()
+ SELECT t1.* FROM t1 RIGHT JOIN t1 t2 ON t1.id=t2.id;
+
+CALL test_sp();
+CALL test_sp();
+
+DROP PROCEDURE test_sp;
+DROP TABLE t1;
+
+
+###########################################################################
+#
+# Bug#38291 memory corruption and server crash with view/sp/function
+#
+
+create table t1(c1 INT);
+create function f1(p1 int) returns varchar(32)
+ return 'aaa';
+create view v1 as select f1(c1) as parent_control_name from t1;
+
+delimiter //;
+create procedure p1()
+begin
+ select parent_control_name as c1 from v1;
+end //
+delimiter ;//
+
+call p1();
+call p1();
+
+drop procedure p1;
+drop function f1;
+drop view v1;
+drop table t1;
+
+#
+# Bug#38469 invalid memory read and/or crash with utf8 text field, stored procedure, uservar
+#
+delimiter $;
+--disable_warnings
+drop procedure if exists `p2` $
+--enable_warnings
+create procedure `p2`(in `a` text charset utf8)
+begin
+ declare `pos` int default 1;
+ declare `str` text charset utf8;
+ set `str` := `a`;
+ select substr(`str`, `pos`+ 1 ) into `str`;
+end $
+delimiter ;$
+call `p2`('s s s s s s');
+drop procedure `p2`;
+
+#
+# Bug#38823: Invalid memory access when a SP statement does wildcard expansion
+#
+
+--disable_warnings
+drop table if exists t1;
+drop procedure if exists p1;
+--enable_warnings
+
+delimiter $;
+create procedure p1() begin select * from t1; end$
+--error ER_NO_SUCH_TABLE
+call p1$
+create table t1 (a integer)$
+call p1$
+alter table t1 add b integer;
+call p1$
+delimiter ;$
+
+drop table t1;
+drop procedure p1;
+
+--echo # ------------------------------------------------------------------
+--echo # -- End of 5.0 tests
+--echo # ------------------------------------------------------------------
+
+###########################################################################
+
+#
+# Bug#20550: Stored function: wrong RETURN type metadata when used in a VIEW.
+#
+
+###########################################################################
+
+--echo
+
+--echo #
+--echo # Bug#20550.
+--echo #
+
+--echo
+
+--echo #
+--echo # - Prepare.
+--echo #
+
+--echo
+
+--disable_warnings
+DROP VIEW IF EXISTS v1;
+DROP VIEW IF EXISTS v2;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+--enable_warnings
+
+--echo
+
+--echo #
+--echo # - Create required objects.
+--echo #
+
+--echo
+
+CREATE FUNCTION f1() RETURNS VARCHAR(65525) RETURN 'Hello';
+
+--echo
+
+CREATE FUNCTION f2() RETURNS TINYINT RETURN 1;
+
+--echo
+
+CREATE VIEW v1 AS SELECT f1();
+
+--echo
+
+CREATE VIEW v2 AS SELECT f2();
+
+--echo
+
+--echo #
+--echo # - Check.
+--echo #
+
+--echo
+
+SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v1';
+
+--echo
+
+SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v2';
+
+--echo
+
+--echo #
+--echo # - Cleanup.
+--echo #
+
+--echo
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP VIEW v1;
+DROP VIEW v2;
+
+--echo
+
+###########################################################################
+
+#
+# Bug#24923: Functions with ENUM issues.
+#
+
+###########################################################################
+
+--echo #
+--echo # - Bug#24923: prepare.
+--echo #
+
+--echo
+
+--disable_warnings
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+--echo
+
+--echo #
+--echo # - Bug#24923: create required objects.
+--echo #
+
+--echo
+
+delimiter |;
+
+CREATE FUNCTION f1(p INT)
+ RETURNS ENUM ('Very_long_enum_element_identifier',
+ 'Another_very_long_enum_element_identifier')
+ BEGIN
+ CASE p
+ WHEN 1 THEN
+ RETURN 'Very_long_enum_element_identifier';
+ ELSE
+ RETURN 'Another_very_long_enum_element_identifier';
+ END CASE;
+ END|
+
+delimiter ;|
+
+--echo
+
+--echo #
+--echo # - Bug#24923: check.
+--echo #
+
+--echo
+
+SELECT f1(1);
+
+--echo
+
+SELECT f1(2);
+
+--echo
+
+SHOW CREATE FUNCTION f1;
+
+--echo #
+--echo # - Bug#24923: cleanup.
+--echo #
+
+--echo
+
+DROP FUNCTION f1;
+
+--echo
+
+###########################################################################
+
+#
+# Bug#32633 Can not create any routine if SQL_MODE=no_engine_substitution
+#
+# Ensure that when new SQL modes are introduced, they are also added to
+# the mysql.proc table.
+#
+
+--disable_warnings
+drop procedure if exists p;
+--enable_warnings
+set @old_mode= @@sql_mode;
+set @@sql_mode= cast(pow(2,32)-1 as unsigned integer);
+select @@sql_mode into @full_mode;
+create procedure p() as begin end;
+call p();
+set @@sql_mode= @old_mode;
+# Rename SQL modes that differ in name between the server and the table definition.
+select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode;
+select name from mysql.proc where name = 'p' and sql_mode = @full_mode;
+drop procedure p;
+
+#
+# Bug#43962 "Packets out of order" calling a SHOW TABLE STATUS
+#
+DELIMITER //;
+CREATE DEFINER = 'root'@'localhost' PROCEDURE p1()
+NOT DETERMINISTIC
+CONTAINS SQL
+SQL SECURITY DEFINER
+COMMENT ''
+BEGIN
+ SHOW TABLE STATUS like 't1';
+END;//
+DELIMITER ;//
+
+
+CREATE TABLE t1 (f1 INT);
+--disable_result_log
+let $tab_count= 4;
+while ($tab_count)
+{
+ EVAL CALL p1();
+ dec $tab_count ;
+}
+--enable_result_log
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+#
+# Bug#47649 crash during CALL procedure
+#
+CREATE TABLE t1 ( f1 integer, primary key (f1));
+CREATE TABLE t2 LIKE t1;
+CREATE TEMPORARY TABLE t3 LIKE t1;
+delimiter |;
+CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ;
+END|
+delimiter ;|
+CALL p1;
+CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 );
+DROP TABLE t3;
+CALL p1;
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+DROP VIEW t3;
+
+--echo #
+--echo # Bug #46629: Item_in_subselect::val_int(): Assertion `0'
+--echo # on subquery inside a SP
+--echo #
+CREATE TABLE t1(a INT);
+CREATE TABLE t2(a INT, b INT PRIMARY KEY);
+
+DELIMITER |;
+CREATE PROCEDURE p1 ()
+BEGIN
+ SELECT a FROM t1 A WHERE A.b IN (SELECT b FROM t2 AS B);
+END|
+DELIMITER ;|
+--error ER_BAD_FIELD_ERROR
+CALL p1;
+--error ER_BAD_FIELD_ERROR
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#47627: SET @@{global.session}.local_variable in stored routine causes crash
+--echo # Bug#48626: Crash or lost connection using SET for declared variables with @@
+--echo #
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP PROCEDURE IF EXISTS p3;
+--enable_warnings
+
+delimiter //;
+
+--error ER_UNKNOWN_SYSTEM_VARIABLE
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE v INT DEFAULT 0;
+ SET @@SESSION.v= 10;
+END//
+
+CREATE PROCEDURE p2()
+BEGIN
+ DECLARE v INT DEFAULT 0;
+ SET v= 10;
+END//
+call p2()//
+
+--error ER_UNKNOWN_SYSTEM_VARIABLE
+CREATE PROCEDURE p3()
+BEGIN
+ DECLARE v INT DEFAULT 0;
+ SELECT @@SESSION.v;
+END//
+
+--error ER_UNKNOWN_SYSTEM_VARIABLE
+CREATE PROCEDURE p4()
+BEGIN
+ DECLARE v INT DEFAULT 0;
+ SET @@GLOBAL.v= 10;
+END//
+
+CREATE PROCEDURE p5()
+BEGIN
+ DECLARE init_connect INT DEFAULT 0;
+ SET init_connect= 10;
+ SET @@GLOBAL.init_connect= 'SELECT 1';
+ SET @@SESSION.IDENTITY= 1;
+ SELECT @@SESSION.IDENTITY;
+ SELECT @@GLOBAL.init_connect;
+ SELECT init_connect;
+END//
+
+--error ER_UNKNOWN_SYSTEM_VARIABLE
+CREATE PROCEDURE p6()
+BEGIN
+ DECLARE v INT DEFAULT 0;
+ SET @@v= 0;
+END//
+
+delimiter ;//
+
+SET @old_init_connect= @@GLOBAL.init_connect;
+CALL p5();
+SET @@GLOBAL.init_connect= @old_init_connect;
+
+DROP PROCEDURE p2;
+DROP PROCEDURE p5;
+
+
+--echo #
+--echo # Bug#11840395 (formerly known as bug#60347):
+--echo # The string "versiondata" seems
+--echo # to be 'leaking' into the schema name space
+--echo #
+--disable_warnings
+DROP DATABASE IF EXISTS mixedCaseDbName;
+--enable_warnings
+CREATE DATABASE mixedCaseDbName;
+DELIMITER |;
+CREATE PROCEDURE mixedCaseDbName.tryMyProc() begin end|
+CREATE FUNCTION mixedCaseDbName.tryMyFunc() returns text begin return 'IT WORKS'; end
+|
+DELIMITER ;|
+call mixedCaseDbName.tryMyProc();
+select mixedCaseDbName.tryMyFunc();
+DROP DATABASE mixedCaseDbName;
+
+
+--echo #
+--echo # Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, KEY(b));
+CREATE TABLE t2 (c INT, d INT, KEY(c));
+INSERT INTO t1 VALUES (1,1),(1,1),(1,2);
+INSERT INTO t2 VALUES (1,1),(1,2);
+
+DELIMITER $;
+
+CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
+BEGIN
+ DECLARE a int;
+ -- SQL statement inside
+ SELECT 1 INTO a;
+ RETURN a;
+END $
+
+DELIMITER ;$
+
+SELECT COUNT(DISTINCT d) FROM t1, t2 WHERE a = c AND b = f1();
+
+DROP FUNCTION f1;
+DROP TABLE t1, t2;
+
+
+--echo # ------------------------------------------------------------------
+--echo # -- End of 5.1 tests
+--echo # ------------------------------------------------------------------
+
+#
+# Bug#39255: Stored procedures: crash if function references nonexistent table
+#
+
+--disable_warnings
+DROP FUNCTION IF EXISTS f1;
+DROP TABLE IF EXISTS t_non_existing;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+delimiter |;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+ DECLARE v INT;
+ SELECT a INTO v FROM t_non_existing;
+ RETURN 1;
+END|
+delimiter ;|
+
+CREATE TABLE t1 (a INT) ENGINE = myisam;
+INSERT INTO t1 VALUES (1);
+
+--error ER_NO_SUCH_TABLE
+SELECT * FROM t1 WHERE a = f1();
+
+DROP FUNCTION f1;
+DROP TABLE t1;
+
+#
+# Bug#36649: Condition area is not properly cleaned up after stored routine invocation
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+delimiter |;
+CREATE PROCEDURE p1(a INT, b CHAR)
+BEGIN
+ IF a > 0 THEN
+ CALL p1(a-1, 'ab');
+ ELSE
+ SELECT 1;
+ END IF;
+END|
+delimiter ;|
+
+SET @save_max_sp_recursion= @@max_sp_recursion_depth;
+SET @@max_sp_recursion_depth= 5;
+CALL p1(4, 'a');
+SET @@max_sp_recursion_depth= @save_max_sp_recursion;
+
+DROP PROCEDURE p1;
+
+#
+# Ensure that rules for message list clean up are being respected.
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+delimiter |;
+CREATE PROCEDURE p1(a CHAR)
+BEGIN
+ SELECT 1;
+ SELECT CAST('10 ' as UNSIGNED INTEGER);
+ SELECT 1;
+END|
+delimiter ;|
+
+CALL p1('data truncated parameter');
+
+DROP PROCEDURE p1;
+
+#
+# Cascading stored procedure/function calls.
+#
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP PROCEDURE IF EXISTS p3;
+DROP PROCEDURE IF EXISTS p4;
+--enable_warnings
+
+delimiter |;
+CREATE PROCEDURE p1()
+ CALL p2()|
+CREATE PROCEDURE p2()
+ CALL p3()|
+CREATE PROCEDURE p3()
+ CALL p4()|
+CREATE PROCEDURE p4()
+BEGIN
+ SELECT 1;
+ SELECT CAST('10 ' as UNSIGNED INTEGER);
+ SELECT 2;
+END|
+delimiter ;|
+
+CALL p1();
+
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP PROCEDURE p3;
+DROP PROCEDURE p4;
+
+--disable_warnings
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP FUNCTION IF EXISTS f3;
+DROP FUNCTION IF EXISTS f4;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (a CHAR(2));
+
+INSERT INTO t1 VALUES ('aa');
+
+delimiter |;
+CREATE FUNCTION f1() RETURNS CHAR
+ RETURN (SELECT f2())|
+CREATE FUNCTION f2() RETURNS CHAR
+ RETURN (SELECT f3())|
+CREATE FUNCTION f3() RETURNS CHAR
+ RETURN (SELECT f4())|
+CREATE FUNCTION f4() RETURNS CHAR
+BEGIN
+ RETURN (SELECT a FROM t1);
+END|
+delimiter ;|
+
+SELECT f1();
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP FUNCTION f3;
+DROP FUNCTION f4;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#34197: CREATE PROCEDURE fails when COMMENT truncated in non
+--echo # strict SQL mode
+--echo #
+
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE PROCEDURE p1 ()
+COMMENT
+'12345678901234567890123456789012345678901234567890123456789012345678901234567890'
+BEGIN
+END;
+
+SELECT comment FROM mysql.proc WHERE name = "p1";
+
+SELECT routine_comment FROM information_schema.routines WHERE routine_name = "p1";
+
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Bug #47313 assert in check_key_in_view during CALL procedure
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP VIEW IF EXISTS t1, t2_unrelated;
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x);
+CREATE VIEW t1 AS SELECT 10 AS f1;
+
+--echo # t1 refers to the view
+--error ER_NON_INSERTABLE_TABLE
+CALL p1(1);
+
+CREATE TEMPORARY TABLE t1 (f1 INT);
+
+--echo # t1 still refers to the view since it was inlined
+--error ER_NON_INSERTABLE_TABLE
+CALL p1(2);
+
+DROP VIEW t1;
+
+--echo # t1 now refers to the temporary table
+CALL p1(3);
+
+--echo # Check which values were inserted into the temp table.
+SELECT * FROM t1;
+
+DROP TEMPORARY TABLE t1;
+DROP PROCEDURE p1;
+
+--echo # Now test what happens if the sp cache is invalidated.
+
+CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x);
+CREATE VIEW t1 AS SELECT 10 AS f1;
+CREATE VIEW v2_unrelated AS SELECT 1 AS r1;
+
+--echo # Load the procedure into the sp cache
+--error ER_NON_INSERTABLE_TABLE
+CALL p1(4);
+
+CREATE TEMPORARY TABLE t1 (f1 int);
+
+ALTER VIEW v2_unrelated AS SELECT 2 AS r1;
+
+--echo # Alter view causes the sp cache to be invalidated.
+--echo # Now t1 refers to the temporary table, not the view.
+CALL p1(5);
+
+--echo # Check which values were inserted into the temp table.
+SELECT * FROM t1;
+
+DROP TEMPORARY TABLE t1;
+DROP VIEW t1, v2_unrelated;
+DROP PROCEDURE p1;
+
+CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x);
+CREATE TEMPORARY TABLE t1 (f1 INT);
+
+--echo # t1 refers to the temporary table
+CALL p1(6);
+
+CREATE VIEW t1 AS SELECT 10 AS f1;
+
+--echo # Create view causes the sp cache to be invalidated.
+--echo # t1 still refers to the temporary table since it shadows the view.
+CALL p1(7);
+
+DROP VIEW t1;
+
+--echo # Check which values were inserted into the temp table.
+SELECT * FROM t1;
+
+DROP TEMPORARY TABLE t1;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # Bug #11918 Can't use a declared variable in LIMIT clause
+--echo #
+--disable_warnings
+drop table if exists t1;
+drop procedure if exists p1;
+--enable_warnings
+create table t1 (c1 int);
+insert into t1 (c1) values (1), (2), (3), (4), (5);
+
+delimiter |;
+create procedure p1()
+begin
+ declare a integer;
+ declare b integer;
+ select * from t1 limit a, b;
+end|
+delimiter ;|
+--echo # How do we handle NULL limit values?
+call p1();
+drop table t1;
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3), (4), (5);
+--echo #
+--echo # Do we correctly resolve identifiers in LIMIT?
+--echo # Since DROP and CREATE did not invalidate
+--echo # the SP cache, we can't test until
+--echo # we drop and re-create the procedure.
+--echo #
+--error ER_BAD_FIELD_ERROR
+call p1();
+--echo #
+--echo # Drop and recreate the procedure, then repeat
+--echo #
+drop procedure p1;
+delimiter |;
+create procedure p1()
+begin
+ declare a integer;
+ declare b integer;
+ select * from t1 limit a, b;
+end|
+delimiter ;|
+--echo # Stored procedure variables are resolved correctly in the LIMIT
+call p1();
+drop table t1;
+create table t1 (c1 int);
+insert into t1 (c1) values (1), (2), (3), (4), (5);
+drop procedure p1;
+--echo # Try to create a procedure that
+--echo # refers to non-existing variables.
+--error ER_SP_UNDECLARED_VAR
+create procedure p1(p1 integer, p2 integer)
+ select * from t1 limit a, b;
+--echo #
+--echo # Try to use data types not allowed in LIMIT
+--echo #
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create procedure p1(p1 date, p2 date) select * from t1 limit p1, p2;
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create procedure p1(p1 integer, p2 float) select * from t1 limit p1, p2;
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create procedure p1(p1 integer, p2 char(1)) select * from t1 limit p1, p2;
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create procedure p1(p1 varchar(5), p2 char(1)) select * from t1 limit p1, p2;
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create procedure p1(p1 decimal, p2 decimal) select * from t1 limit p1, p2;
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create procedure p1(p1 double, p2 double) select * from t1 limit p1, p2;
+
+--echo #
+--echo # Finally, test the valid case.
+--echo #
+create procedure p1(p1 integer, p2 integer)
+ select * from t1 limit p1, p2;
+
+call p1(NULL, NULL);
+call p1(0, 0);
+call p1(0, -1);
+call p1(-1, 0);
+call p1(-1, -1);
+call p1(0, 1);
+call p1(1, 0);
+call p1(1, 5);
+call p1(3, 2);
+
+delimiter |;
+--echo # Try to create a function that
+--echo # refers to non-existing variables.
+--error ER_SP_UNDECLARED_VAR
+create function f1(p1 integer, p2 integer)
+ returns int
+begin
+ declare a int;
+ set a = (select count(*) from t1 limit a, b);
+ return a;
+end|
+
+create function f1()
+ returns int
+begin
+ declare a, b, c int;
+ set a = (select count(*) from t1 limit b, c);
+ return a;
+end|
+
+delimiter ;|
+--echo # How do we handle NULL limit values?
+select f1();
+
+drop function f1;
+
+delimiter |;
+--echo #
+--echo # Try to use data types not allowed in LIMIT
+--echo #
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create function f1(p1 date, p2 date)
+ returns int
+begin
+ declare a int;
+ set a = (select count(*) from t1 limit p1, p2);
+ return a;
+end|
+
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create function f1(p1 integer, p2 float)
+ returns int
+begin
+ declare a int;
+ set a = (select count(*) from t1 limit p1, p2);
+ return a;
+end|
+
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create function f1(p1 integer, p2 char(1))
+ returns int
+begin
+ declare a int;
+ set a = (select count(*) from t1 limit p1, p2);
+ return a;
+end|
+
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create function f1(p1 varchar(5), p2 char(1))
+ returns int
+begin
+ declare a int;
+ set a = (select count(*) from t1 limit p1, p2);
+ return a;
+end|
+
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create function f1(p1 decimal, p2 decimal)
+ returns int
+begin
+ declare a int;
+ set a = (select count(*) from t1 limit p1, p2);
+ return a;
+end|
+
+--error ER_WRONG_SPVAR_TYPE_IN_LIMIT
+create function f1(p1 double, p2 double)
+ returns int
+begin
+ declare a int;
+ set a = (select count(*) from t1 limit p1, p2);
+ return a;
+end|
+
+--echo #
+--echo # Finally, test the valid case.
+--echo #
+
+create function f1(p1 integer, p2 integer)
+returns int
+begin
+ declare count int;
+ set count= (select count(*) from (select * from t1 limit p1, p2) t_1);
+ return count;
+end|
+
+delimiter ;|
+
+select f1(0, 0);
+select f1(0, -1);
+select f1(-1, 0);
+select f1(-1, -1);
+select f1(0, 1);
+select f1(1, 0);
+select f1(1, 5);
+select f1(3, 2);
+
+--echo # Cleanup
+drop table t1;
+drop procedure p1;
+drop function f1;
+
+--echo #
+--echo # BUG#11766234: 59299: ASSERT (TABLE_REF->TABLE || TABLE_REF->VIEW)
+--echo # FAILS IN SET_FIELD_ITERATOR
+--echo #
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE VIEW v1 AS SELECT a FROM t2;
+CREATE PROCEDURE proc() SELECT * FROM t1 NATURAL JOIN v1;
+ALTER TABLE t2 CHANGE COLUMN a b CHAR;
+
+--echo
+--error ER_VIEW_INVALID
+CALL proc();
+--error ER_VIEW_INVALID
+CALL proc();
+
+--echo
+DROP TABLE t1,t2;
+DROP VIEW v1;
+DROP PROCEDURE proc;
+
+
+--echo
+--echo # --
+--echo # -- Bug 11765684 - 58674: SP-cache does not detect changes in
+--echo # -- pre-locking list caused by triggers
+--echo # ---
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP TABLE IF EXISTS t3;
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE TABLE t1(a INT);
+CREATE TABLE t2(a INT);
+CREATE TABLE t3(a INT);
+
+CREATE PROCEDURE p1()
+ INSERT INTO t1(a) VALUES (1);
+
+--echo
+CREATE TRIGGER t1_ai AFTER INSERT ON t1
+ FOR EACH ROW
+ INSERT INTO t2(a) VALUES (new.a);
+
+--echo
+CALL p1();
+
+--echo
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1
+ FOR EACH ROW
+ INSERT INTO t3(a) VALUES (new.a);
+
+--echo
+CALL p1();
+
+--echo
+DROP TABLE t1, t2, t3;
+DROP PROCEDURE p1;
+--echo
+
+
+--echo
+--echo # --
+--echo # -- Bug#12652769 - 61470: case operator in stored routine retains old
+--echo # -- value of input parameter
+--echo # ---
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET utf8);
+INSERT INTO t1 VALUES ('a');
+
+delimiter |;
+
+CREATE PROCEDURE p1(dt DATETIME, i INT)
+BEGIN
+ SELECT
+ CASE
+ WHEN i = 1 THEN 2
+ ELSE dt
+ END AS x1;
+
+ SELECT
+ CASE _latin1'a'
+ WHEN _utf8'a' THEN 'A'
+ END AS x2;
+
+ SELECT
+ CASE _utf8'a'
+ WHEN _latin1'a' THEN _utf8'A'
+ END AS x3;
+
+ SELECT
+ CASE s1
+ WHEN _latin1'a' THEN _latin1'b'
+ ELSE _latin1'c'
+ END AS x4
+ FROM t1;
+END|
+
+delimiter ;|
+
+--echo
+CALL p1('2011-04-03 05:14:10', 1);
+CALL p1('2011-04-03 05:14:11', 2);
+CALL p1('2011-04-03 05:14:12', 2);
+CALL p1('2011-04-03 05:14:13', 2);
+
+--echo
+DROP TABLE t1;
+DROP PROCEDURE p1;
+--echo
+
+--echo #
+--echo # Bug#12621017 - Crash if a sp variable is used in the
+--echo # limit clause of a set statement
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+--enable_warnings
+
+CREATE TABLE t1 (c1 INT);
+INSERT INTO t1 VALUES (1);
+
+delimiter |;
+
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE foo, cnt INT UNSIGNED DEFAULT 1;
+ SET foo = (SELECT MIN(c1) FROM t1 LIMIT cnt);
+END|
+
+CREATE PROCEDURE p2()
+BEGIN
+
+DECLARE iLimit INT;
+DECLARE iVal INT;
+
+DECLARE cur1 CURSOR FOR
+ SELECT c1 FROM t1
+ LIMIT iLimit;
+
+SET iLimit=1;
+
+OPEN cur1;
+FETCH cur1 INTO iVal;
+
+END|
+
+delimiter ;|
+
+CALL p1();
+CALL p2();
+
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+DROP TABLE t1;
+
+--echo
+--echo # Bug#13805127: Stored program cache produces wrong result in same THD
+--echo
+
+delimiter |;
+
+CREATE PROCEDURE p1(x INT UNSIGNED)
+BEGIN
+ SELECT c1, t2.c2, count(c3)
+ FROM
+ (
+ SELECT 3 as c2 FROM dual WHERE x = 1
+ UNION
+ SELECT 2 FROM dual WHERE x = 1 OR x = 2
+ ) AS t1,
+ (
+ SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+ UNION
+ SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+ UNION
+ SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
+ ) AS t2
+ WHERE t2.c2 = t1.c2
+ GROUP BY c1, c2
+ ;
+END|
+
+delimiter ;|
+
+--echo
+CALL p1(1);
+CALL p1(2);
+CALL p1(1);
+
+DROP PROCEDURE p1;
+
+--echo # End of 5.5 test
+
+
+--echo #
+--echo # Bug#12663165 SP DEAD CODE REMOVAL DOESN'T UNDERSTAND CONTINUE HANDLERS
+--echo #
+
+--disable_warnings
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+delimiter $;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
+ BEGIN
+ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN f1();
+ BEGIN
+ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN f1();
+ RETURN f1();
+ END;
+ END;
+RETURN 1;
+END $
+delimiter ;$
+
+# This used to cause an assertion.
+SELECT f1();
+
+DROP FUNCTION f1;
+
+--echo # ------------------------------------------------------------------
+--echo # -- End of 5.1 tests
+--echo # ------------------------------------------------------------------
+
+--echo #
+--echo # lp:993459 Execution of PS for a query with GROUP BY
+--echo # returns wrong result (see also mysql bug#13805127)
+--echo #
+
+--echo
+--echo # Bug#13805127: Stored program cache produces wrong result in same THD
+--echo
+
+delimiter |;
+
+CREATE PROCEDURE p1(x INT UNSIGNED)
+BEGIN
+ SELECT c1, t2.c2, count(c3)
+ FROM
+ (
+ SELECT 3 as c2 FROM dual WHERE x = 1
+ UNION
+ SELECT 2 FROM dual WHERE x = 1 OR x = 2
+ ) AS t1,
+ (
+ SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
+ UNION
+ SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
+ UNION
+ SELECT '2012-03-01 01:00:00', 2, 1 FROM dual
+ ) AS t2
+ WHERE t2.c2 = t1.c2
+ GROUP BY c1, c2
+ ;
+END|
+
+delimiter ;|
+
+--echo
+CALL p1(1);
+CALL p1(2);
+CALL p1(1);
+
+DROP PROCEDURE p1;
+
+--echo
+--echo MDEV-3900 Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements
+--echo
+
+CREATE FUNCTION tdn() RETURNS int(7) DETERMINISTIC RETURN to_days(now());
+
+CREATE TABLE t1 (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, daynum INT, a CHAR(1), INDEX(daynum), INDEX(a)) ENGINE=MyISAM;
+INSERT INTO t1 (daynum) VALUES (1),(2),(3),(4),(5),(TO_DAYS(NOW())),(7),(8);
+INSERT INTO t1 (daynum) SELECT a1.daynum FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5;
+
+FLUSH TABLES;
+FLUSH STATUS;
+
+SHOW STATUS LIKE '%Handler_read%';
+UPDATE t1 SET a = '+' WHERE daynum=tdn();
+SHOW STATUS LIKE '%Handler_read%';
+
+drop function tdn;
+drop table t1;
+
+--echo #
+--echo # lp:1002157 : testing stored function
+--echo # bug#62125 result for null incorrectly yields 1292 warning.
+--echo #
+
+--disable_warnings
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP FUNCTION IF EXISTS f3;
+DROP FUNCTION IF EXISTS f4;
+--enable_warnings
+
+delimiter /;
+
+CREATE FUNCTION f1() RETURNS VARCHAR(1)
+BEGIN RETURN 'X'; END;/
+
+CREATE FUNCTION f2() RETURNS CHAR(1)
+BEGIN RETURN 'X'; END;/
+
+CREATE FUNCTION f3() RETURNS VARCHAR(1)
+BEGIN RETURN NULL; END;/
+
+CREATE FUNCTION f4() RETURNS CHAR(1)
+BEGIN RETURN NULL; END;/
+
+delimiter ;/
+
+SELECT f1() IS NULL;
+SELECT f2() IS NULL;
+SELECT f3() IS NULL;
+SELECT f4() IS NULL;
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP FUNCTION f3;
+DROP FUNCTION f4;
+
+--echo
+--echo Stored procedures and a condition handler in a nested procedure call
+--echo doesn't suppress the condition from being passed on to the calling
+--echo procedure
+--echo
+
+--disable_warnings
+drop procedure if exists p1;
+drop procedure if exists p0;
+--enable_warnings
+
+create table t1 (id int);
+delimiter $$;
+create procedure p1 () begin
+declare i int default 0;
+declare continue handler for not found begin
+select "You should see this message and the warning that generated this" as "message";
+show warnings;
+end;
+select id into i from t1;
+end$$
+create procedure p0 () begin
+declare continue handler for not found begin
+select "You should NOT see this message" as "message";
+end;
+call p1();
+end$$
+delimiter ;$$
+call p0();
+
+drop procedure p1;
+drop procedure p0;
+drop table t1;
+
+--echo
+--echo Test if stored procedures propagates errors
+--echo
+
+create table t1 (id int primary key);
+delimiter $$;
+create procedure p1 () begin
+insert into t1 values(1);
+insert into t1 values(2);
+insert into t1 values(2);
+insert into t1 values(3);
+end$$
+create procedure p2 () begin
+ declare x int;
+ select id into x from t1 where id=5;
+end$$
+delimiter ;$$
+--error ER_DUP_ENTRY
+call p1();
+show warnings;
+select * from t1;
+call p2();
+
+drop procedure p1;
+drop procedure p2;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-4978 - Server cursor is broken with blobs in the select list,
+--echo # ORDER BY does not work
+--echo #
+CREATE TABLE t1(a INT, b BLOB);
+INSERT INTO t1 VALUES(1,REPEAT('a',4835)),(2,'b'),(3,'c'),(4,'d'),(5,REPEAT('e',805)),(6,'f');
+
+DELIMITER |;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE done INT DEFAULT 0;
+ DECLARE v1 INT;
+ DECLARE v2 BLOB;
+ DECLARE c1 CURSOR FOR SELECT * FROM t1 ORDER BY a;
+ DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
+ OPEN c1;
+ REPEAT
+ FETCH c1 INTO v1, v2;
+ IF NOT done THEN
+ SELECT v1;
+ END IF;
+ UNTIL done END REPEAT;
+ CLOSE c1;
+END|
+DELIMITER ;|
+
+CALL p1;
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-10713: signal 11 error on multi-table update - crash in
+--echo # handler::increment_statistics or in make_select or assertion
+--echo # failure pfs_thread == ((PFS_thread*) pthread_getspecific((THR_PFS)))
+--echo #
+
+CREATE TABLE `t1` (
+ `CLOSE_YN` varchar(10) COLLATE utf8_bin DEFAULT NULL
+) DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;
+
+
+CREATE TABLE `t2` (
+ `ap_close_to` varchar(8) COLLATE utf8_bin DEFAULT NULL
+) DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;
+insert t1 values (1);
+
+
+--delimiter $$
+
+CREATE FUNCTION `f1`(`P_DC_CD` VARBINARY(50), `P_SYS_DATE` DATETIME) RETURNS datetime
+ DETERMINISTIC
+ SQL SECURITY INVOKER
+BEGIN
+ DECLARE V_SYS_DATE DATETIME;
+ SELECT now() AS LOC_DATE INTO V_SYS_DATE ;
+ RETURN v_sys_date ;
+END $$
+
+--delimiter ;
+
+update t1 S
+JOIN
+(
+ SELECT CASE
+ WHEN DATE_FORMAT( f1('F01', NOW()) , '%Y%m%d') <= CLOSE_YMD
+ THEN '99991231'
+ ELSE '' END ACCOUNT_APPLY_YYYYMMDD
+ FROM (
+ select case
+ when 'AP'='AP'
+ then ap_close_to
+ end AS CLOSE_YMD
+ from t2
+ ) A
+) X
+SET S.CLOSE_YN = ''
+where 1=1;
+
+drop function if exists f1;
+drop table t1,t2;
+
+--echo # End of 5.5 test
+
+--echo #
+--echo # MDEV-7040: Crash in field_conv, memcpy_field_possible, part#2
+--echo #
+create table t1 (
+ col1 bigint(20),
+ col2 char(1),
+ col3 char(2)
+);
+insert into t1 values (1,'a','a'), (2,'b','b');
+
+create table t2 as select * from t1;
+create table t3 as select * from t1;
+create table t4 as select * from t1;
+create table t5 as select * from t1;
+create table t6 as select * from t1;
+
+flush tables;
+
+DELIMITER |;
+
+CREATE PROCEDURE p1()
+begin
+ DECLARE _var1 bigint(20) UNSIGNED;
+ DECLARE _var2 CHAR(1) DEFAULT NULL;
+ DECLARE _var3 CHAR(1) DEFAULT NULL;
+
+ DECLARE _done BOOLEAN DEFAULT 0;
+
+ declare cur1 cursor for
+ select col1, col2, col3
+ from t1
+ where
+ col1 in (select t2.col1 from t2 where t2.col2=t1.col2) or
+ col2 in (select t3.col3 from t3 where t3.col3=t1.col2) ;
+
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1;
+
+ OPEN cur1;
+
+ set _var1 = (select _var1 from t4 limit 1);
+ set _var1 = (select _var1 from t5 limit 1);
+ set _var1 = (select _var1 from t6 limit 1);
+label1:
+ LOOP
+ SET _done = 0;
+ FETCH cur1 INTO _var1, _var2, _var3;
+ IF _done THEN
+ LEAVE label1;
+ END IF;
+ END LOOP label1;
+ CLOSE cur1;
+end|
+DELIMITER ;|
+
+set @tmp_toc= @@table_open_cache;
+set @tmp_tdc= @@table_definition_cache;
+
+set global table_open_cache=1;
+set global table_definition_cache=1;
+call p1();
+
+set global table_open_cache= @tmp_toc;
+set global table_definition_cache= @tmp_tdc;
+drop procedure p1;
+
+drop table t1,t2,t3,t4,t5,t6;
+
+--echo #
+--echo # MDEV-11935: Queries in stored procedures with and
+--echo # EXISTS(SELECT * FROM VIEW) crashes and closes hte conneciton.
+--echo #
+
+CREATE TABLE ANY_TABLE (
+ ENTITY_UID BIGINT NOT NULL
+);
+CREATE TABLE SECURITY_PATH(
+origid BIGINT UNSIGNED NOT NULL,
+destid BIGINT UNSIGNED NOT NULL,
+KEY (destid)
+);
+CREATE VIEW ENTITY_ACCESS (
+ENTITY_UID,
+OWNER_UID
+) AS
+SELECT SP1.origid,
+ SP2.destid
+FROM SECURITY_PATH SP1
+JOIN SECURITY_PATH SP2 ON SP1.destid = SP2.origid
+;
+--delimiter //
+CREATE PROCEDURE SP_EXAMPLE_SELECT ()
+BEGIN
+ SELECT *
+ FROM ANY_TABLE AT1
+ WHERE EXISTS ( SELECT *
+ FROM ENTITY_ACCESS EA
+ WHERE AT1.ENTITY_UID = EA.ENTITY_UID
+ AND EA.OWNER_UID IS NULL );
+END
+//
+--delimiter ;
+CALL SP_EXAMPLE_SELECT ();
+CALL SP_EXAMPLE_SELECT ();
+
+drop procedure SP_EXAMPLE_SELECT;
+drop view ENTITY_ACCESS;
+drop table ANY_TABLE, SECURITY_PATH;
+--echo # End of 10.0 test
+
+DELIMITER |;
+CREATE FUNCTION f(f1 VARCHAR(64) COLLATE latin1_german2_ci)
+ RETURNS VARCHAR(64)
+BEGIN
+ RETURN 'str';
+END|
+DROP FUNCTION f|
+
+CREATE FUNCTION f(f1 VARCHAR(64))
+ RETURNS VARCHAR(64) COLLATE latin1_german2_ci
+BEGIN
+ RETURN 'str';
+END|
+DROP FUNCTION f|
+
+CREATE FUNCTION f(f1 VARCHAR(64))
+ RETURNS VARCHAR(64)
+BEGIN
+ DECLARE f2 VARCHAR(64) COLLATE latin1_german2_ci;
+ RETURN 'str';
+END|
+DROP FUNCTION f|
+DELIMITER ;|
+
+--echo #
+--echo # MDEV-7023: Error 2027: Malformed packet and assertion
+--echo # `field_types == 0 || field_types[field_pos] == MYSQL_TYPE_INT24 ||
+--echo #field_types[field_pos] == MYSQL_TYPE_LONG' failure in
+--echo #Protocol_text::store_long
+--echo #
+create table t1 (i int);
+create table t2 (i int);
+--delimiter |
+--error ER_SP_NO_RETSET
+create function f() returns int
+begin
+ analyze insert into t1 values (1);
+ return 1;
+end |
+--error ER_SP_NO_RETSET
+create function f() returns int
+begin
+ analyze insert t1 select * from t2;
+ return 1;
+end |
+--error ER_SP_NO_RETSET
+create function f() returns int
+begin
+ analyze delete from t1;
+ return 1;
+end |
+--error ER_SP_NO_RETSET
+create function f() returns int
+begin
+ analyze delete t1 from t1,t2;
+ return 1;
+end |
+--error ER_SP_NO_RETSET
+create function f() returns int
+begin
+ analyze update t1 set i=1;
+ return 1;
+end |
+--error ER_SP_NO_RETSET
+create function f() returns int
+begin
+ analyze update t1,t2 set i=1;
+ return 1;
+end |
+--error ER_SP_NO_RETSET
+create function f() returns int
+begin
+ analyze replace t1 set i=1;
+ return 1;
+end |
+--error ER_SP_NO_RETSET
+create function f() returns int
+begin
+ analyze replace t1 select * from t2;
+ return 1;
+end |
+--delimiter ;
+
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-11584: GRANT inside an SP does not work well on 2nd execution
+--echo #
+
+CREATE PROCEDURE sp1()
+ GRANT ALL PRIVILEGES ON *.* TO 'foo'@'%' IDENTIFIED BY 'pass';
+CALL sp1();
+CALL sp1();
+drop user 'foo'@'%';
+drop procedure sp1;
+
+--echo #
+--echo # MDEV-10972: Insert from select / view / union --
+--echo # repeatable crash in 10.1, 10.2 Linux/Mac/Windows
+--echo #
+
+create table t (id int auto_increment primary key);
+insert into t values (9494),(9495),(9496),(9497),(9498),(9499),(9500),(9501),(9502),(9503);
+
+create VIEW v AS
+select id from t
+union
+select id from t
+;
+
+drop procedure if exists p;
+create procedure p()
+insert into tmp_t select t.id from (
+ select id from v
+ union
+ select id from v
+) sq
+inner join t on (sq.id = t.id);
+
+--error ER_NO_SUCH_TABLE
+CALL p();
+create table tmp_t (id int null);
+CALL p();
+
+drop procedure p;
+drop view v;
+drop table t, tmp_t;
+
+
+--echo #
+--echo # MDEV-13936: Server crashes in Time_and_counter_tracker::incr_loops
+--echo #
+CREATE TABLE t1 (i INT);
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE RAND() > 0.5;
+CREATE FUNCTION f1() RETURNS INT RETURN ( SELECT MAX(i) FROM v1 );
+
+--error ER_NON_INSERTABLE_TABLE
+REPLACE INTO v1 VALUES (f1());
+SET @aux = f1();
+
+# Cleanup
+DROP FUNCTION f1;
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-14857: problem with 10.2.11 server crashing when
+--echo # executing stored procedure
+--echo #
+
+SET max_sp_recursion_depth=10;
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (b INT);
+
+delimiter ||;
+
+CREATE PROCEDURE proc_0()
+BEGIN
+ CALL empty_1();
+ CALL proc_1();
+END ||
+
+CREATE PROCEDURE proc_1()
+BEGIN
+ CALL proc_2();
+ CALL proc_3();
+ CALL proc_4();
+ CALL proc_5();
+END ||
+
+CREATE PROCEDURE proc_2()
+ CALL proc_6();
+||
+
+CREATE PROCEDURE proc_3()
+BEGIN
+ CALL empty_2();
+ CALL empty_3();
+END ||
+
+CREATE PROCEDURE proc_4()
+ CALL proc_7();
+||
+
+CREATE PROCEDURE proc_5()
+ CALL proc_select();
+||
+
+CREATE PROCEDURE proc_6()
+BEGIN
+ CALL empty_4();
+ CALL empty_5();
+ CALL empty_6();
+ CALL empty_7();
+ CALL proc_8();
+END ||
+
+CREATE PROCEDURE proc_7()
+ CALL proc_9('foo');
+||
+
+CREATE PROCEDURE proc_8()
+ CALL proc_10();
+||
+
+CREATE PROCEDURE proc_9(IN opt VARCHAR(40))
+ IF LEFT(opt,1) <> '_' THEN
+ CALL proc_11();
+ END IF;
+||
+
+CREATE PROCEDURE proc_10()
+ CALL proc_12();
+||
+
+CREATE PROCEDURE proc_11()
+BEGIN
+ CALL empty_8();
+ CALL empty_9();
+ CALL empty_10();
+ CALL proc_13();
+END ||
+
+CREATE PROCEDURE proc_12()
+BEGIN
+ CALL empty_11();
+ CALL empty_12();
+ CALL empty_13();
+END ||
+
+CREATE PROCEDURE proc_13()
+BEGIN
+ CALL proc_9('_bar');
+ CALL empty_14();
+END ||
+
+delimiter ;||
+
+CREATE PROCEDURE empty_1() BEGIN END ;
+CREATE PROCEDURE empty_2() BEGIN END ;
+CREATE PROCEDURE empty_3() BEGIN END ;
+CREATE PROCEDURE empty_4() BEGIN END ;
+CREATE PROCEDURE empty_5() BEGIN END ;
+CREATE PROCEDURE empty_6() BEGIN END ;
+CREATE PROCEDURE empty_7() BEGIN END ;
+CREATE PROCEDURE empty_8() BEGIN END ;
+CREATE PROCEDURE empty_9() BEGIN END ;
+CREATE PROCEDURE empty_10() BEGIN END ;
+CREATE PROCEDURE empty_11() BEGIN END ;
+CREATE PROCEDURE empty_12() BEGIN END ;
+CREATE PROCEDURE empty_13() BEGIN END ;
+CREATE PROCEDURE empty_14() BEGIN END ;
+
+CREATE PROCEDURE proc_select()
+ SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2)
+;
+
+CALL proc_0();
+
+# Cleanup
+DROP PROCEDURE empty_1;
+DROP PROCEDURE empty_2;
+DROP PROCEDURE empty_3;
+DROP PROCEDURE empty_4;
+DROP PROCEDURE empty_5;
+DROP PROCEDURE empty_6;
+DROP PROCEDURE empty_7;
+DROP PROCEDURE empty_8;
+DROP PROCEDURE empty_9;
+DROP PROCEDURE empty_10;
+DROP PROCEDURE empty_11;
+DROP PROCEDURE empty_12;
+DROP PROCEDURE empty_13;
+DROP PROCEDURE empty_14;
+DROP PROCEDURE proc_0;
+DROP PROCEDURE proc_1;
+DROP PROCEDURE proc_2;
+DROP PROCEDURE proc_3;
+DROP PROCEDURE proc_4;
+DROP PROCEDURE proc_5;
+DROP PROCEDURE proc_6;
+DROP PROCEDURE proc_7;
+DROP PROCEDURE proc_8;
+DROP PROCEDURE proc_9;
+DROP PROCEDURE proc_10;
+DROP PROCEDURE proc_11;
+DROP PROCEDURE proc_12;
+DROP PROCEDURE proc_13;
+DROP PROCEDURE proc_select;
+DROP TABLE t1, t2;
+
+SET max_sp_recursion_depth=default;
+
+--echo #End of 10.1 tests
+
+--echo #
+--echo # MDEV-11081: CURSOR for query with GROUP BY
+--echo #
+
+CREATE TABLE t1 (name VARCHAR(10), value INT);
+INSERT INTO t1 VALUES ('b',1);
+INSERT INTO t1 VALUES ('b',1);
+INSERT INTO t1 VALUES ('c',1);
+INSERT INTO t1 VALUES ('a',1);
+INSERT INTO t1 VALUES ('a',1);
+INSERT INTO t1 VALUES ('a',1);
+DELIMITER |;
+CREATE PROCEDURE p1 ()
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+ DECLARE v_name VARCHAR(10);
+ DECLARE v_total INT;
+ DECLARE c CURSOR FOR
+ SELECT name, SUM(value) AS total FROM t1 GROUP BY name;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+ OPEN c;
+read_loop:
+ LOOP
+ FETCH c INTO v_name, v_total;
+ IF done THEN
+ LEAVE read_loop;
+ END IF;
+ SELECT v_name, v_total;
+ END LOOP;
+ CLOSE c;
+END;
+|
+DELIMITER ;|
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-13346: CURSOR a query with GROUP BY using derived table
+--echo #
+
+DELIMITER |;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE c CURSOR FOR
+ SELECT
+ IFNULL(NULL,1) AS col
+ FROM
+ ( select 1 as id ) AS t
+ GROUP BY t.id
+ ;
+ OPEN c;
+END
+|
+DELIMITER ;|
+CALL p1();
+DROP PROCEDURE p1;
+
+--echo #
+--echo # MDEV-15057 Crash when using an unknown identifier as an SP parameter
+--echo #
+
+CREATE OR REPLACE PROCEDURE p1 (a VARCHAR(10)) SELECT 1;
+--error ER_BAD_FIELD_ERROR
+CALL p1(a);
+drop procedure p1;
+
+DELIMITER |;
+
+CREATE OR REPLACE PROCEDURE p1 (a VARCHAR(10)) SELECT a|
+CREATE OR REPLACE PROCEDURE p2 ()
+BEGIN
+ DECLARE name VARCHAR(10);
+ SET name="hello";
+ call p1(name);
+END|
+CREATE OR REPLACE PROCEDURE p3 ()
+BEGIN
+ DECLARE name VARCHAR(10);
+ SET name="hello";
+ call p1(name2);
+END|
+
+DELIMITER ;|
+
+call p2();
+--error ER_BAD_FIELD_ERROR
+call p3();
+drop procedure p1;
+drop procedure p2;
+drop procedure p3;
+
+--echo #
+--echo # MDEV-15328: MariaDB 10.2.13 Crashes upon CALL PROCEDURE PARAM
+--echo # LAST_INSERT_ID ()
+--echo # (part 1, part 2 is in query_cache.test)
+--echo #
+
+CREATE PROCEDURE foo ( IN i INT UNSIGNED ) BEGIN END;
+CALL foo( LAST_INSERT_ID() );
+DROP PROCEDURE foo;
+
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+--echo #
+--echo # MDEV-12007 Allow ROW variables as a cursor FETCH target
+--echo #
+
+
+--echo # The cursor and the ROW variable in FETCH must have the same number of fields
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+ DECLARE rec ROW(aa INT, bb VARCHAR(32), cc INT);
+ DECLARE cur CURSOR FOR SELECT 10 AS a,'b10' AS b;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+ OPEN cur;
+read_loop:
+ LOOP
+ FETCH cur INTO rec;
+ IF done THEN
+ LEAVE read_loop;
+ END IF;
+ END LOOP;
+ CLOSE cur;
+END;
+$$
+DELIMITER ;$$
+--error ER_SP_WRONG_NO_OF_FETCH_ARGS
+CALL p1();
+DROP PROCEDURE p1;
+
+
+--echo # Multiple ROW variables in FETCH
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+ DECLARE rec1 ROW(aa INT);
+ DECLARE rec2 ROW(aa INT);
+ DECLARE cur CURSOR FOR SELECT 10 AS a, 20 AS b;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+ OPEN cur;
+read_loop:
+ LOOP
+ FETCH cur INTO rec1, rec2;
+ IF done THEN
+ LEAVE read_loop;
+ END IF;
+ END LOOP;
+ CLOSE cur;
+END;
+$$
+DELIMITER ;$$
+--error ER_OPERAND_COLUMNS
+CALL p1();
+DROP PROCEDURE p1;
+
+
+--echo # A complete working example
+CREATE TABLE t1 (a INT, b VARCHAR(32));
+INSERT INTO t1 VALUES (10,'b10');
+INSERT INTO t1 VALUES (20,'b20');
+INSERT INTO t1 VALUES (30,'b30');
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+ DECLARE rec ROW(aa INT, bb VARCHAR(32));
+ DECLARE cur CURSOR FOR SELECT a,b FROM t1;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+ OPEN cur;
+read_loop:
+ LOOP
+ FETCH cur INTO rec;
+ IF done THEN
+ LEAVE read_loop;
+ END IF;
+ SELECT CONCAT('rec=(',rec.aa,',',rec.bb,')') AS c;
+ END LOOP;
+ CLOSE cur;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo # A ROW variable with a single field
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE done INT DEFAULT FALSE;
+ DECLARE rec ROW(aa INT);
+ DECLARE cur CURSOR FOR SELECT 10 AS a UNION SELECT 20;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+ OPEN cur;
+read_loop:
+ LOOP
+ FETCH cur INTO rec;
+ IF done THEN
+ LEAVE read_loop;
+ END IF;
+ SELECT CONCAT('rec=(',rec.aa,')') AS c;
+ END LOOP;
+ CLOSE cur;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
+
+--echo #
+--echo # MDEV-14228 MariaDB crashes with function
+--echo #
+
+CREATE TABLE t1 (c VARCHAR(16), KEY(c));
+INSERT INTO t1 VALUES ('foo');
+
+DELIMITER $$;
+CREATE FUNCTION f1() RETURNS VARCHAR(16)
+BEGIN
+ DECLARE v VARCHAR(16);
+ FOR v IN (SELECT DISTINCT c FROM t1)
+ DO
+ IF (v = 'bar') THEN
+ SELECT 1 INTO @a;
+ END IF;
+ END FOR;
+ RETURN 'qux';
+END $$
+DELIMITER ;$$
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT f1();
+DROP FUNCTION f1;
+
+DELIMITER $$;
+CREATE FUNCTION f1() RETURNS VARCHAR(16)
+BEGIN
+ DECLARE v ROW TYPE OF t1;
+ IF v = 'bar' THEN
+ RETURN 'eq';
+ END IF;
+ RETURN 'ne';
+END $$
+DELIMITER ;$$
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT f1();
+DROP FUNCTION f1;
+
+DELIMITER $$;
+CREATE FUNCTION f1() RETURNS VARCHAR(16)
+BEGIN
+ DECLARE v ROW(a INT);
+ IF v = 'bar' THEN
+ RETURN 'eq';
+ END IF;
+ RETURN 'ne';
+END $$
+DELIMITER ;$$
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+SELECT f1();
+DROP FUNCTION f1;
+
+DROP TABLE t1;
+
+
+DELIMITER $$;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+BEGIN NOT ATOMIC
+ DECLARE v ROW(a INT);
+ SELECT v IN ('a','b');
+END $$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+BEGIN NOT ATOMIC
+DECLARE v ROW(a INT);
+ SELECT 'a' IN (v,'b');
+END $$
+DELIMITER ;$$
+
+DELIMITER $$;
+--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
+BEGIN NOT ATOMIC
+ DECLARE v ROW(a INT);
+ SELECT 'a' IN ('b',v);
+END $$
+DELIMITER ;$$
+
+--echo #
+--echo # MDEV-15112 Inconsistent evaluation of spvariable=0 in strict mode
+--echo #
+
+SET sql_mode=STRICT_ALL_TABLES;
+CREATE OR REPLACE TABLE t1 (e TIMESTAMP(6));
+INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
+
+DELIMITER $$;
+CREATE FUNCTION f1(a VARBINARY(255))
+RETURNS INT
+DETERMINISTIC
+BEGIN
+ RETURN a = timestamp'2038-01-19 03:14:07.999999'
+ OR a = 0;
+END
+$$
+CREATE FUNCTION f2(a VARBINARY(255))
+RETURNS INT
+DETERMINISTIC
+BEGIN
+ RETURN a = 0;
+END
+$$
+CREATE OR REPLACE FUNCTION f3(a VARBINARY(255))
+RETURNS INT
+DETERMINISTIC
+BEGIN
+ RETURN a = timestamp'2038-01-19 03:14:07.999999'
+ OR a = sleep(0);
+END
+$$
+DELIMITER ;$$
+
+--error ER_TRUNCATED_WRONG_VALUE
+SELECT f1(e) FROM t1;
+--error ER_TRUNCATED_WRONG_VALUE
+SELECT f2(e) FROM t1;
+--error ER_TRUNCATED_WRONG_VALUE
+SELECT f3(e) FROM t1;
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP FUNCTION f3;
+DROP TABLE t1;
+
+--echo # Test affected rows from an sp
+
+create table t1 (a int);
+
+DELIMITER $$;
+create procedure p1()
+begin
+insert into t1 values(1);
+insert into t1 values(2);
+end;
+$$
+create procedure p2()
+begin
+insert into t1 values(1);
+call p1();
+select row_count();
+insert into t1 values(2);
+insert into t1 values(2);
+end;
+$$
+DELIMITER ;$$
+
+--enable_info
+CALL p2();
+--disable_info
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+drop table t1;