diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/sp.result | |
parent | Initial commit. (diff) | |
download | mariadb-upstream.tar.xz mariadb-upstream.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r-- | mysql-test/main/sp.result | 9023 |
1 files changed, 9023 insertions, 0 deletions
diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result new file mode 100644 index 00000000..670a039b --- /dev/null +++ b/mysql-test/main/sp.result @@ -0,0 +1,9023 @@ +set @save_character_set_client=@@character_set_client; +set @save_userstat=@@global.userstat, @@global.userstat= 0; +use test; +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; +create table t1 ( +id char(16) not null default '', +data int not null +); +create table t2 ( +s char(16), +i int, +d double +); +drop procedure if exists foo42; +create procedure foo42() +insert into test.t1 values ("foo", 42); +call foo42(); +select * from t1; +id data +foo 42 +delete from t1; +drop procedure foo42; +drop procedure if exists bar; +create procedure bar(x char(16), y int) +insert into test.t1 values (x, y); +call bar("bar", 666); +select * from t1; +id data +bar 666 +delete from t1; +drop procedure if exists empty| +create procedure empty() +begin +end| +call empty()| +drop procedure empty| +drop procedure if exists scope| +create procedure scope(a int, b float) +begin +declare b int; +declare c float; +begin +declare c int; +end; +end| +drop procedure scope| +drop procedure if exists two| +create procedure two(x1 char(16), x2 char(16), y int) +begin +insert into test.t1 values (x1, y); +insert into test.t1 values (x2, y); +end| +call two("one", "two", 3)| +select * from t1| +id data +one 3 +two 3 +delete from t1| +drop procedure two| +drop procedure if exists locset| +create procedure locset(x char(16), y int) +begin +declare z1, z2 int; +set z1 = y; +set z2 = z1+2; +insert into test.t1 values (x, z2); +end| +call locset("locset", 19)| +select * from t1| +id data +locset 21 +delete from t1| +drop procedure locset| +drop procedure if exists setcontext| +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| +id data +foo 1 +kaka 3 +delete from t1| +drop procedure setcontext| +create table t3 ( d date, i int, f double, s varchar(32) )| +drop procedure if exists nullset| +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| +d i f s +NULL NULL NULL NULL +NULL 1 1 ld is null +NULL 1 1 li is null +NULL NULL NULL li = 0 +NULL 1 1 lf is null +NULL NULL NULL lf = 0 +NULL 1 1 ls is null +drop table t3| +drop procedure nullset| +drop procedure if exists mixset| +create procedure mixset(x char(16), y int) +begin +declare z int; +set @z = y, z = 666, max_join_size = 100; +insert into test.t1 values (x, z); +end| +call mixset("mixset", 19)| +show variables like 'max_join_size'| +Variable_name Value +max_join_size 100 +select id,data,@z from t1| +id data @z +mixset 666 19 +delete from t1| +drop procedure mixset| +drop procedure if exists zip| +create procedure zip(x char(16), y int) +begin +declare z int; +call zap(y, z); +call bar(x, z); +end| +drop procedure if exists zap| +create procedure zap(x int, out y int) +begin +declare z int; +set z = x+1, y = z; +end| +call zip("zip", 99)| +select * from t1| +id data +zip 100 +delete from t1| +drop procedure zip| +drop procedure bar| +call zap(7, @zap)| +select @zap| +@zap +8 +drop procedure zap| +drop procedure if exists c1| +create procedure c1(x int) +call c2("c", x)| +drop procedure if exists c2| +create procedure c2(s char(16), x int) +call c3(x, s)| +drop procedure if exists c3| +create procedure c3(x int, s char(16)) +call c4("level", x, s)| +drop procedure if exists c4| +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| +id data +levelc 42 +delete from t1| +drop procedure c1| +drop procedure c2| +drop procedure c3| +drop procedure c4| +drop procedure if exists iotest| +create procedure iotest(x1 char(16), x2 char(16), y int) +begin +call inc2(x2, y); +insert into test.t1 values (x1, y); +end| +drop procedure if exists inc2| +create procedure inc2(x char(16), y int) +begin +call inc(y); +insert into test.t1 values (x, y); +end| +drop procedure if exists inc| +create procedure inc(inout io int) +set io = io + 1| +call iotest("io1", "io2", 1)| +select * from t1 order by data desc| +id data +io2 2 +io1 1 +delete from t1| +drop procedure iotest| +drop procedure inc2| +drop procedure if exists incr| +create procedure incr(inout x int) +call inc(x)| +select @zap| +@zap +8 +call incr(@zap)| +select @zap| +@zap +9 +drop procedure inc| +drop procedure incr| +drop procedure if exists cbv1| +create procedure cbv1() +begin +declare y int default 3; +call cbv2(y+1, y); +insert into test.t1 values ("cbv1", y); +end| +drop procedure if exists cbv2| +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| +id data +cbv2 4 +cbv1 4711 +delete from t1| +drop procedure cbv1| +drop procedure cbv2| +insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)| +drop procedure if exists sub1| +create procedure sub1(id char(16), x int) +insert into test.t1 values (id, x)| +drop procedure if exists sub2| +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| +drop procedure if exists sub3| +create function sub3(i int) returns int deterministic +return i+1| +call sub1("sub1a", (select 7))| +call sub1("sub1b", (select max(i) from t2))| +call sub1("sub1c", (select i,d from t2 limit 1))| +ERROR HY000: Cannot cast 'row' as 'int' in assignment of `x` +call sub1("sub1d", (select 1 from (select 1) a))| +call sub2("sub2")| +select * from t1 order by id| +id data +sub1a 7 +sub1b 3 +sub1d 1 +sub2 6 +select sub3((select max(i) from t2))| +sub3((select max(i) from t2)) +4 +drop procedure sub1| +drop procedure sub2| +drop function sub3| +delete from t1| +delete from t2| +drop procedure if exists a0| +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| +id data +a0 2 +a0 1 +a0 0 +delete from t1| +drop procedure a0| +drop procedure if exists a| +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| +id data +a 2 +a 1 +a 0 +delete from t1| +drop procedure a| +drop procedure if exists b| +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| +id data +bbb 3 +bbb 2 +bbb 1 +delete from t1| +drop procedure b| +drop procedure if exists b2| +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| +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +drop procedure b2| +drop procedure if exists c| +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| +id data +c 3 +c 2 +c 1 +delete from t1| +drop procedure c| +drop procedure if exists d| +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| +id data +d 3 +delete from t1| +drop procedure d| +drop procedure if exists e| +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| +id data +e 3 +e 2 +e 1 +delete from t1| +drop procedure e| +drop procedure if exists f| +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| +id data +f 0 +f 1 +f 2 +delete from t1| +drop procedure f| +drop procedure if exists g| +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| +id data +g 0 +g 1 +g 2 +delete from t1| +drop procedure g| +drop procedure if exists h| +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| +id data +h0 0 +h1 1 +h? 17 +delete from t1| +drop procedure h| +drop procedure if exists i| +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| +id data +i 3 +delete from t1| +drop procedure i| +insert into t1 values ("foo", 3), ("bar", 19)| +insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)| +drop procedure if exists sel1| +create procedure sel1() +begin +select * from t1 order by data; +end| +call sel1()| +id data +foo 3 +bar 19 +drop procedure sel1| +drop procedure if exists sel2| +create procedure sel2() +begin +select * from t1 order by data; +select * from t2 order by s; +end| +call sel2()| +id data +foo 3 +bar 19 +s i d +x 9 4.1 +y -1 19.2 +z 3 2.2 +drop procedure sel2| +delete from t1| +delete from t2| +drop procedure if exists into_test| +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| +id data +into 100 +into2 102 +delete from t1| +drop procedure into_test| +drop procedure if exists into_tes2| +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| +id data @z +into 100 100 +into2 102 100 +delete from t1| +drop procedure into_test2| +drop procedure if exists into_test3| +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)| +call into_test3()| +call into_test3()| +select * from t2| +s i d +into3 19 0 +into3 19 0 +delete from t1| +delete from t2| +drop procedure into_test3| +drop procedure if exists into_test4| +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| +s d +into4 NULL +insert into t1 values ("i4", 77)| +call into_test4()| +select * from t3| +s d +into4 NULL +into4 77 +delete from t1| +drop table t3| +drop procedure into_test4| +drop procedure if exists into_outfile| +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| +call into_outfile("ofile", 1)| +delete from t1| +drop procedure into_outfile| +drop procedure if exists into_dumpfile| +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| +call into_dumpfile("dfile", 1)| +delete from t1| +drop procedure into_dumpfile| +drop procedure if exists create_select| +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| +id data id data +cs 90 cs 90 +cs 90 cs2 92 +drop table t3| +delete from t1| +drop procedure create_select| +drop function if exists e| +create function e() returns double +return 2.7182818284590452354| +set @e = e()| +select e(), @e| +e() @e +2.718281828459045 2.718281828459045 +drop function if exists inc| +create function inc(i int) returns int +return i+1| +select inc(1), inc(99), inc(-71)| +inc(1) inc(99) inc(-71) +2 100 -70 +drop function if exists mul| +create function mul(x int, y int) returns int +return x*y| +select mul(1,1), mul(3,5), mul(4711, 666)| +mul(1,1) mul(3,5) mul(4711, 666) +1 15 3137526 +drop function if exists append| +create function append(s1 char(8), s2 char(8)) returns char(16) +return concat(s1, s2)| +select append("foo", "bar")| +append("foo", "bar") +foobar +drop function if exists fac| +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)| +fac(1) fac(2) fac(5) fac(10) +1 2 120 3628800 +drop function if exists fun| +create function fun(d double, i int, u int unsigned) returns double +return mul(inc(i), fac(u)) / e()| +select fun(2.3, 3, 5)| +fun(2.3, 3, 5) +176.58213176229233 +insert into t2 values (append("xxx", "yyy"), mul(4,3), e())| +insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))| +select * from t2 where s = append("a", "b")| +s i d +ab 24 1324.3659882171924 +select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2) order by i| +s i d +xxxyyy 12 2.718281828459045 +ab 24 1324.3659882171924 +select * from t2 where d = e()| +s i d +xxxyyy 12 2.718281828459045 +select * from t2 order by i| +s i d +xxxyyy 12 2.718281828459045 +ab 24 1324.3659882171924 +delete from t2| +drop function e| +drop function inc| +drop function mul| +drop function append| +drop function fun| +drop procedure if exists hndlr1| +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| +id data +hndlr1 42 +delete from t1| +drop procedure hndlr1| +drop procedure if exists hndlr2| +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| +id data +hndlr2 1 +delete from t1| +drop procedure hndlr2| +drop procedure if exists hndlr3| +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| +id data +hndlr3 13 +delete from t1| +drop procedure hndlr3| +create table t3 ( id char(16), data int )| +drop procedure if exists hndlr4| +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| +id data +z NULL +drop table t3| +drop procedure hndlr4| +drop procedure if exists cur1| +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| +id data +foo 40 +bar 15 +zap 663 +drop procedure cur1| +create table t3 ( s char(16), i int )| +drop procedure if exists cur2| +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| +s i +bar 3 +foo 40 +zap 663 +delete from t1| +delete from t2| +drop table t3| +drop procedure cur2| +drop procedure if exists chistics| +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| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +chistics STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`() + MODIFIES SQL DATA + COMMENT 'Characteristics procedure test' +insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci +call chistics()| +select * from t1| +id data +chistics 1 +delete from t1| +alter procedure chistics sql security invoker| +show create procedure chistics| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +chistics STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`() + MODIFIES SQL DATA + SQL SECURITY INVOKER + COMMENT 'Characteristics procedure test' +insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci +drop procedure chistics| +drop function if exists chistics| +create function chistics() returns int +language sql +deterministic +sql security invoker +comment 'Characteristics procedure test' + return 42| +show create function chistics| +Function sql_mode Create Function character_set_client collation_connection Database Collation +chistics STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11) + DETERMINISTIC + SQL SECURITY INVOKER + COMMENT 'Characteristics procedure test' +return 42 latin1 latin1_swedish_ci latin1_swedish_ci +select chistics()| +chistics() +42 +alter function chistics +no sql +comment 'Characteristics function test'| +show create function chistics| +Function sql_mode Create Function character_set_client collation_connection Database Collation +chistics STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11) + NO SQL + DETERMINISTIC + SQL SECURITY INVOKER + COMMENT 'Characteristics function test' +return 42 latin1 latin1_swedish_ci latin1_swedish_ci +drop function chistics| +insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)| +set @@sql_mode = 'ANSI'| +drop procedure if exists modes$ +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$ +set @@sql_mode = ''| +set sql_select_limit = 1| +call modes(@c1, @c2)| +set sql_select_limit = default| +select @c1, @c2| +@c1 @c2 +12 3 +delete from t1| +drop procedure modes| +create database sp_db1| +drop database sp_db1| +create database sp_db2| +use sp_db2| +create table t3 ( s char(4), t int )| +insert into t3 values ("abcd", 42), ("dcba", 666)| +use test| +drop database sp_db2| +create database sp_db3| +use sp_db3| +drop procedure if exists dummy| +create procedure dummy(out x int) +set x = 42| +use test| +drop database sp_db3| +select type,db,name from mysql.proc where db = 'sp_db3'| +type db name +drop procedure if exists rc| +create procedure rc() +begin +delete from t1; +insert into t1 values ("a", 1), ("b", 2), ("c", 3); +end| +call rc()| +select row_count()| +row_count() +3 +update t1 set data=42 where id = "b"; +select row_count()| +row_count() +1 +delete from t1| +select row_count()| +row_count() +3 +delete from t1| +select row_count()| +row_count() +0 +select * from t1| +id data +select row_count()| +row_count() +-1 +drop procedure rc| +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| +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) | +create function f1() returns int +return (select sum(data) from t1)| +select f1()| +f1() +3 +select id, f1() from t1 order by id| +id f1() +a 3 +b 3 +create function f2() returns int +return (select data from t1 where data <= (select sum(data) from t1) order by data limit 1)| +select f2()| +f2() +1 +select id, f2() from t1 order by id| +id f2() +a 1 +b 1 +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()| +f3() +1 +select id, f3() from t1 order by id| +id f3() +a 1 +b 1 +select f1(), f3()| +f1() f3() +3 1 +select id, f1(), f3() from t1 order by id| +id f1() f3() +a 3 1 +b 3 1 +create function f4() returns double +return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| +select f4()| +f4() +2 +select s, f4() from t2 order by s| +s f4() +a 2 +b 2 +c 2 +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)| +f5(1) +1 +select f5(2)| +ERROR HY000: Recursive stored functions and triggers are not allowed +select f5(3)| +ERROR HY000: Recursive stored functions and triggers are not allowed +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()| +f6() +2 +select id, f6() from t1 order by id| +id f6() +a 2 +b 2 +create view v1 (a) as select f1()| +select * from v1| +a +3 +select id, a from t1, v1 order by id| +id a +a 3 +b 3 +select * from v1, v1 as v| +a a +3 3 +create view v2 (a) as select a*10 from v1| +select * from v2| +a +30 +select id, a from t1, v2 order by id| +id a +a 30 +b 30 +select * from v1, v2| +a a +3 30 +create function f8 () returns int +return (select count(*) from v2)| +select *, f8() from v1| +a f8() +3 1 +drop function f1| +select * from v1| +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +create function f1() returns int +return (select sum(data) from t1) + (select sum(data) from v1)| +select f1()| +ERROR HY000: Recursive stored functions and triggers are not allowed +select * from v1| +ERROR HY000: Recursive stored functions and triggers are not allowed +select * from v2| +ERROR HY000: Recursive stored functions and triggers are not allowed +drop function f1| +create function f1() returns int +return (select sum(data) from t1)| +create function f0() returns int +return (select * from (select 100) as r)| +select f0()| +f0() +100 +select *, f0() from (select 1) as t| +1 f0() +1 100 +create view v0 as select f0()| +select * from v0| +f0() +100 +select *, f0() from v0| +f0() f0() +100 100 +lock tables t1 read, t1 as t11 read| +select f3()| +f3() +1 +select id, f3() from t1 as t11 order by id| +id f3() +a 1 +b 1 +select f0()| +f0() +100 +select * from v0| +ERROR HY000: Table 'v0' was not locked with LOCK TABLES +select *, f0() from v0, (select 123) as d1| +ERROR HY000: Table 'v0' was not locked with LOCK TABLES +select id, f3() from t1| +ERROR HY000: Table 't1' was not locked with LOCK TABLES +select f4()| +ERROR HY000: Table 't2' was not locked with LOCK TABLES +unlock tables| +lock tables v2 read, mysql.proc read| +select * from v2| +a +30 +select * from v1| +a +3 +select * from v1, t1| +ERROR HY000: Table 't1' was not locked with LOCK TABLES +select f4()| +ERROR HY000: Table 't2' was not locked with LOCK TABLES +unlock tables| +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| +select f9()| +f9() +6 +select f9() from t1 limit 1| +f9() +6 +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| +select f10()| +ERROR 42S02: Table 'test.t4' doesn't exist +create table t4 as select 1 as id| +select f10()| +f10() +1 +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()| +f11() +9 +select f11() from t1| +f11() +9 +9 +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()| +f12_1() +3 +drop temporary table t3| +select f12_1() from t1 limit 1| +f12_1() +3 +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| +drop table if exists t3| +create table t3 (n int unsigned not null primary key, f bigint unsigned)| +drop procedure if exists ifac| +create procedure ifac(n int unsigned) +begin +declare i int unsigned default 1; +if n > 20 then +set n = 20; # bigint overflow otherwise +end if; +while i <= n do +begin +insert into test.t3 values (i, fac(i)); +set i = i + 1; +end; +end while; +end| +call ifac(20)| +select * from t3| +n f +1 1 +2 2 +3 6 +4 24 +5 120 +6 720 +7 5040 +8 40320 +9 362880 +10 3628800 +11 39916800 +12 479001600 +13 6227020800 +14 87178291200 +15 1307674368000 +16 20922789888000 +17 355687428096000 +18 6402373705728000 +19 121645100408832000 +20 2432902008176640000 +drop table t3| +show function status where name like '%f%' and Db <> 'sys'| +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +drop procedure ifac| +drop function fac| +show function status where name like '%f%' and Db <> 'sys'| +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +drop table if exists t3| +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)| +drop procedure if exists opp| +create procedure opp(n bigint unsigned, out pp bool) +begin +declare r double; +declare b, s bigint unsigned default 0; +set r = sqrt(n); +again: +loop +if s = 45 then +set b = b+200, s = 0; +else +begin +declare p bigint unsigned; +select t.p into p from test.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| +drop procedure if exists ip| +create procedure ip(m int unsigned) +begin +declare p bigint unsigned; +declare i int unsigned; +set i=45, p=201; +while i < m do +begin +declare pp bool default 0; +call opp(p, pp); +if pp then +insert into test.t3 values (i, p); +set i = i+1; +end if; +set p = p+2; +end; +end while; +end| +show create procedure opp| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +opp CREATE DEFINER=`root`@`localhost` 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 latin1 latin1_swedish_ci latin1_swedish_ci +show procedure status where name like '%p%' and db='test'| +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +call ip(200)| +select * from t3 where i=45 or i=100 or i=199| +i p +45 211 +100 557 +199 1229 +drop table t3| +drop procedure opp| +drop procedure ip| +show procedure status where name like '%p%' and db='test'| +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +drop procedure if exists bar| +create procedure bar(x char(16), y int) +comment "111111111111" sql security invoker +insert into test.t1 values (x, y)| +show procedure status like 'bar'| +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER 111111111111 latin1 latin1_swedish_ci latin1_swedish_ci +alter procedure bar comment "2222222222" sql security definer| +alter procedure bar comment "3333333333"| +alter procedure bar| +show create procedure bar| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +bar CREATE DEFINER=`root`@`localhost` PROCEDURE `bar`(x char(16), y int) + COMMENT '3333333333' +insert into test.t1 values (x, y) latin1 latin1_swedish_ci latin1_swedish_ci +show procedure status like 'bar'| +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER 3333333333 latin1 latin1_swedish_ci latin1_swedish_ci +drop procedure bar| +drop procedure if exists p1| +create procedure p1 () +select (select s1 from t3) from t3| +create table t3 (s1 int)| +call p1()| +(select s1 from t3) +insert into t3 values (1)| +call p1()| +(select s1 from t3) +1 +drop procedure p1| +drop table t3| +drop function if exists foo| +create function `foo` () returns int +return 5| +select `foo` ()| +`foo` () +5 +drop function `foo`| +drop function if exists t1max| +create function t1max() returns int +begin +declare x int; +select max(data) into x from t1; +return x; +end| +insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)| +select t1max()| +t1max() +5 +drop function t1max| +create table t3 ( +v char(16) not null primary key, +c int unsigned not null +)| +create function getcount(s char(16)) returns int +begin +declare x int; +select count(*) into x from t3 where v = s; +if x = 0 then +insert into t3 values (s, 1); +else +update t3 set c = c+1 where v = s; +end if; +return x; +end| +select * from t1 where data = getcount("bar")| +id data +zap 1 +select * from t3| +v c +bar 4 +select getcount("zip")| +getcount("zip") +0 +select getcount("zip")| +getcount("zip") +1 +select * from t3| +v c +bar 4 +zip 2 +select getcount(id) from t1 where data = 3| +getcount(id) +0 +select getcount(id) from t1 where data = 5| +getcount(id) +1 +select * from t3| +v c +bar 4 +zip 3 +foo 1 +drop table t3| +drop function getcount| +drop 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| +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()| +h_ee +Inner (good) +call h_es()| +h_es +Inner (bad) +call h_en()| +h_en +Inner (bad) +call h_ew()| +h_ew +Inner (bad) +call h_ex()| +h_ex +Inner (bad) +call h_se()| +h_se +Inner (good) +call h_ss()| +h_ss +Inner (good) +call h_sn()| +h_sn +Inner (bad) +call h_sw()| +h_sw +Inner (bad) +call h_sx()| +h_sx +Inner (bad) +call h_ne()| +h_ne +Inner (good) +call h_ns()| +h_ns +Inner (good) +call h_nn()| +h_nn +Inner (good) +call h_we()| +h_we +Inner (good) +call h_ws()| +h_ws +Inner (good) +call h_ww()| +h_ww +Inner (good) +call h_xe()| +h_xe +Inner (good) +call h_xs()| +h_xs +Inner (good) +call h_xx()| +h_xx +Inner (good) +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| +drop procedure if exists bug822| +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| +id data +foo 42 +bar 666 +delete from t1| +drop procedure bug822| +drop procedure if exists bug1495| +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| +id data +less 2 +more 17 +delete from t1| +drop procedure bug1495| +drop procedure if exists bug1547| +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| +id data +bar 7 +foo 12 +less 2 +more 17 +delete from t1| +drop procedure bug1547| +drop table if exists t70| +create table t70 (s1 int,s2 int)| +insert into t70 values (1,2)| +drop procedure if exists bug1656| +create procedure bug1656(out p1 int, out p2 int) +select * into p1, p1 from t70| +call bug1656(@1, @2)| +select @1, @2| +@1 @2 +2 NULL +drop table t70| +drop procedure bug1656| +create table t3(a int)| +drop procedure if exists bug1862| +create procedure bug1862() +begin +insert into t3 values(2); +flush tables; +end| +call bug1862()| +call bug1862()| +select * from t3| +a +2 +2 +drop table t3| +drop procedure bug1862| +drop procedure if exists bug1874| +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| +s i d +avg 0 4.4 +min 1 0 +max 9 0 +sum 22 0 +delete from t1| +delete from t2| +drop procedure bug1874| +drop procedure if exists bug2260| +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| +@x2 +2 +drop procedure bug2260| +drop procedure if exists bug2267_1| +create procedure bug2267_1() +begin +show procedure status where db='test'; +end| +drop procedure if exists bug2267_2| +create procedure bug2267_2() +begin +show function status where db='test'; +end| +drop procedure if exists bug2267_3| +create procedure bug2267_3() +begin +show create procedure bug2267_1; +end| +drop procedure if exists bug2267_4| +drop function if exists bug2267_4| +create procedure bug2267_4() +begin +show create function bug2267_4; +end| +create function bug2267_4() returns int return 100| +call bug2267_1()| +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test bug2267_1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +test bug2267_2 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +test bug2267_3 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +test bug2267_4 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +call bug2267_2()| +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test bug2267_4 FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +call bug2267_3()| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +bug2267_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2267_1`() +begin +show procedure status where db='test'; +end latin1 latin1_swedish_ci latin1_swedish_ci +call bug2267_4()| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug2267_4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2267_4`() RETURNS int(11) +return 100 latin1 latin1_swedish_ci latin1_swedish_ci +drop procedure bug2267_1| +drop procedure bug2267_2| +drop procedure bug2267_3| +drop procedure bug2267_4| +drop function bug2267_4| +drop procedure if exists bug2227| +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)| +1.3 x y 42 z +1.3 9 2.6 42 zzz +drop procedure bug2227| +drop procedure if exists bug2614| +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| +call bug2614()| +call bug2614()| +drop table t3| +drop procedure bug2614| +drop function if exists bug2674| +create function bug2674() returns int +return @@sort_buffer_size| +set @osbs = @@sort_buffer_size| +set @@sort_buffer_size = 262000| +select bug2674()| +bug2674() +262000 +drop function bug2674| +set @@sort_buffer_size = @osbs| +drop procedure if exists bug3259_1 | +create procedure bug3259_1 () begin end| +drop procedure if exists BUG3259_2 | +create procedure BUG3259_2 () begin end| +drop procedure if exists Bug3259_3 | +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| +drop function if exists bug2772| +create function bug2772() returns char(10) character set latin2 +return 'a'| +select bug2772()| +bug2772() +a +drop function bug2772| +drop procedure if exists bug2776_1| +create procedure bug2776_1(out x int) +begin +declare v int; +set v = default; +set x = v; +end| +drop procedure if exists bug2776_2| +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| +@x +NULL +call bug2776_2(@x)| +select @x| +@x +42 +drop procedure bug2776_1| +drop procedure bug2776_2| +create table t3 (s1 smallint)| +insert into t3 values (123456789012)| +Warnings: +Warning 1264 Out of range value for column 's1' at row 1 +drop procedure if exists bug2780| +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| +@x +1 +select * from t3| +s1 +32767 +32767 +drop procedure bug2780| +drop table t3| +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)| +drop procedure if exists bug1863| +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| +f1 rc t3 +2 0 NULL +2 0 NULL +drop procedure bug1863| +drop temporary table temp_t1; +drop table t3, t4| +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")| +drop procedure if exists bug2656_1| +create procedure bug2656_1() +begin +select +m.Market +from t4 m JOIN t3 o +ON o.MarketID != 1 and o.MarketID = m.MarketID; +end | +drop procedure if exists bug2656_2| +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()| +Market +MarketID Two +call bug2656_1()| +Market +MarketID Two +call bug2656_2()| +Market +MarketID Two +call bug2656_2()| +Market +MarketID Two +drop procedure bug2656_1| +drop procedure bug2656_2| +drop table t3, t4| +drop procedure if exists bug3426| +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| +set time_zone='+03:00'; +call bug3426(1000, @i)| +select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| +@i time +2 01-01-1970 03:16:40 +call bug3426(NULL, @i)| +select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| +@i time +1 01-01-1970 03:00:10 +alter procedure bug3426 sql security invoker| +call bug3426(NULL, @i)| +select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| +@i time +1 01-01-1970 03:00:10 +call bug3426(1000, @i)| +select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| +@i time +2 01-01-1970 03:16:40 +drop procedure bug3426| +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 ...')| +drop procedure if exists bug3734 | +create procedure bug3734 (param1 varchar(100)) +select * from t3 where match (title,body) against (param1)| +call bug3734('database')| +id title body +5 MySQL vs. YourSQL In the following database comparison ... +1 MySQL Tutorial DBMS stands for DataBase ... +call bug3734('Security')| +id title body +6 MySQL Security When configured properly, MySQL ... +drop procedure bug3734| +drop table t3| +drop procedure if exists bug3863| +create procedure bug3863() +begin +set @a = 0; +while @a < 5 do +set @a = @a + 1; +end while; +end| +call bug3863()| +select @a| +@a +5 +call bug3863()| +select @a| +@a +5 +drop procedure bug3863| +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) +)| +drop procedure if exists bug2460_1| +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)| +id +call bug2460_1(2)| +id +insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')| +call bug2460_1(2)| +id +2 +1 +call bug2460_1(2)| +id +2 +1 +drop procedure if exists bug2460_2| +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| +s1 +1 +drop procedure bug2460_1| +drop procedure bug2460_2| +drop table t3| +set @@sql_mode = ''| +drop procedure if exists bug2564_1| +create procedure bug2564_1() +comment 'Joe''s procedure' + insert into `t1` values ("foo", 1)| +set @@sql_mode = 'ANSI_QUOTES'| +drop procedure if exists bug2564_2| +create procedure bug2564_2() +insert into "t1" values ('foo', 1)| +set @@sql_mode = ''$ +drop function if exists bug2564_3$ +create function bug2564_3(x int, y int) returns int +return x || y$ +set @@sql_mode = 'ANSI'$ +drop function if exists bug2564_4$ +create function bug2564_4(x int, y int) returns int +return x || y$ +set @@sql_mode = ''| +show create procedure bug2564_1| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +bug2564_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2564_1`() + COMMENT 'Joe''s procedure' +insert into `t1` values ("foo", 1) latin1 latin1_swedish_ci latin1_swedish_ci +show create procedure bug2564_2| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +bug2564_2 ANSI_QUOTES CREATE DEFINER="root"@"localhost" PROCEDURE "bug2564_2"() +insert into "t1" values ('foo', 1) latin1 latin1_swedish_ci latin1_swedish_ci +show create function bug2564_3| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug2564_3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2564_3`(x int, y int) RETURNS int(11) +return x || y latin1 latin1_swedish_ci latin1_swedish_ci +show create function bug2564_4| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI CREATE DEFINER="root"@"localhost" FUNCTION "bug2564_4"(x int, y int) RETURNS int(11) +return x || y latin1 latin1_swedish_ci latin1_swedish_ci +drop procedure bug2564_1| +drop procedure bug2564_2| +drop function bug2564_3| +drop function bug2564_4| +drop function if exists bug3132| +create function bug3132(s char(20)) returns char(50) +return concat('Hello, ', s, '!')| +select bug3132('Bob') union all select bug3132('Judy')| +bug3132('Bob') +Hello, Bob! +Hello, Judy! +drop function bug3132| +drop procedure if exists bug3843| +create procedure bug3843() +analyze table t1| +call bug3843()| +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +call bug3843()| +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +select 1+2| +1+2 +3 +drop procedure bug3843| +create table t3 ( s1 char(10) )| +insert into t3 values ('a'), ('b')| +drop procedure if exists bug3368| +create procedure bug3368(v char(10)) +begin +select group_concat(v) from t3; +end| +call bug3368('x')| +group_concat(v) +x,x +call bug3368('yz')| +group_concat(v) +yz,yz +drop procedure bug3368| +drop table t3| +create table t3 (f1 int, f2 int)| +insert into t3 values (1,1)| +drop procedure if exists bug4579_1| +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| +drop procedure if exists bug4579_2| +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| +drop procedure if exists bug2773| +create function bug2773() returns int return null| +create table t3 as select bug2773()| +show create table t3| +Table Create Table +t3 CREATE TABLE `t3` ( + `bug2773()` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table t3| +drop function bug2773| +drop procedure if exists bug3788| +create function bug3788() returns date return cast("2005-03-04" as date)| +select bug3788()| +bug3788() +2005-03-04 +drop function bug3788| +create function bug3788() returns binary(1) return 5| +select bug3788()| +bug3788() +5 +drop function bug3788| +create table t3 (f1 int, f2 int, f3 int)| +insert into t3 values (1,1,1)| +drop procedure if exists bug4726| +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| +drop procedure if exists bug4902| +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| +call bug4902()| +Charset Description Default collation Maxlen +Collation Charset Id Default Compiled Sortlen +Table Create Table +t1 CREATE TABLE `t1` ( + `id` char(16) NOT NULL DEFAULT '', + `data` int(11) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +Database Create Database +test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */ +Database (foo) +Level Code Message +Field Type Null Key Default Extra +id char(16) NO +data int(11) NO NULL +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +Database Table In_use Name_locked +Variable_name Value +Tables_in_test (foo) +Variable_name Value +Level Code Message +call bug4902()| +Charset Description Default collation Maxlen +Collation Charset Id Default Compiled Sortlen +Table Create Table +t1 CREATE TABLE `t1` ( + `id` char(16) NOT NULL DEFAULT '', + `data` int(11) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +Database Create Database +test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */ +Database (foo) +Level Code Message +Field Type Null Key Default Extra +id char(16) NO +data int(11) NO NULL +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +Database Table In_use Name_locked +Variable_name Value +Tables_in_test (foo) +Variable_name Value +Level Code Message +drop procedure bug4902| +drop procedure if exists bug4904| +create procedure bug4904() +begin +declare continue handler for sqlstate 'HY000' begin end; +create table not_existing_table as select * from t3; +end| +call bug4904()| +ERROR 42S02: Table 'test.t3' doesn't exist +drop procedure bug4904| +create table t3 (s1 char character set latin1, s2 char character set latin2)| +drop procedure if exists bug4904| +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| +drop procedure if exists bug336| +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| +@y +5 +delete from t1| +drop procedure bug336| +drop procedure if exists bug3157| +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| +@n +2 +delete from t1| +drop procedure bug3157| +drop procedure if exists bug5251| +create procedure bug5251() +begin +end| +select created into @c1 from mysql.proc +where db='test' and name='bug5251'| +alter procedure bug5251 comment 'foobar'| +select count(*) from mysql.proc +where db='test' and name='bug5251' and created = @c1| +count(*) +1 +drop procedure bug5251| +drop procedure if exists bug5251| +create procedure bug5251() +checksum table t1| +call bug5251()| +Table Checksum +test.t1 0 +call bug5251()| +Table Checksum +test.t1 0 +drop procedure bug5251| +drop procedure if exists bug5287| +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| +drop procedure if exists bug5307| +create procedure bug5307() +begin +end; set @x = 3| +call bug5307()| +select @x| +@x +3 +drop procedure bug5307| +drop procedure if exists bug5258| +create procedure bug5258() +begin +end| +drop procedure if exists bug5258_aux| +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()| +Ok +Ok +drop procedure bug5258| +drop procedure bug5258_aux| +drop function if exists bug4487| +create function bug4487() returns char +begin +declare v char; +return v; +end| +select bug4487()| +bug4487() +NULL +drop function bug4487| +drop procedure if exists bug4941| +drop procedure if exists bug4941| +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| +@x +NULL +delete from t1| +drop procedure bug4941| +drop procedure if exists bug4905| +create table t3 (s1 int,primary key (s1))| +drop procedure if exists bug4905| +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()| +row_count() +1 +call bug4905()| +select row_count()| +row_count() +0 +call bug4905()| +select row_count()| +row_count() +0 +select * from t3| +s1 +1 +drop procedure bug4905| +drop table t3| +drop procedure if exists bug6029| +drop procedure if exists bug6029| +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()| +sqlstate 23000 +sqlstate 23000 +delete from t3| +call bug6029()| +1136 +1136 +drop procedure bug6029| +drop table t3| +drop procedure if exists bug8540| +create procedure bug8540() +begin +declare x int default 1; +select x as y, x+0 as z; +end| +call bug8540()| +y z +1 1 +drop procedure bug8540| +create table t3 (s1 int)| +drop procedure if exists bug6642| +create procedure bug6642() +select abs(count(s1)) from t3| +call bug6642()| +abs(count(s1)) +0 +call bug6642()| +abs(count(s1)) +0 +drop procedure bug6642| +insert into t3 values (0),(1)| +drop procedure if exists bug7013| +create procedure bug7013() +select s1,count(s1) from t3 group by s1 with rollup| +call bug7013()| +s1 count(s1) +0 1 +1 1 +NULL 2 +call bug7013()| +s1 count(s1) +0 1 +1 1 +NULL 2 +drop procedure bug7013| +drop table if exists t4| +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')| +drop procedure if exists bug7743| +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")| +var +1 +call bug7743("OneWord")| +var +NULL +Warnings: +Warning 1329 No data - zero rows fetched, selected, or processed +call bug7743("anotherword")| +var +2 +call bug7743("AnotherWord")| +var +NULL +Warnings: +Warning 1329 No data - zero rows fetched, selected, or processed +drop procedure bug7743| +drop table t4| +delete from t3| +insert into t3 values(1)| +drop procedure if exists bug7992_1| +Warnings: +Note 1305 PROCEDURE test.bug7992_1 does not exist +drop procedure if exists bug7992_2| +Warnings: +Note 1305 PROCEDURE test.bug7992_2 does not exist +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| +create table t3 ( userid bigint(20) not null default 0 )| +drop procedure if exists bug8116| +create procedure bug8116(in _userid int) +select * from t3 where userid = _userid| +call bug8116(42)| +userid +call bug8116(42)| +userid +drop procedure bug8116| +drop table t3| +drop procedure if exists bug6857| +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()| +sleep(1.1) +0 +plus +1 +drop procedure bug6857| +drop procedure if exists bug8757| +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()| +2 y +2 2 +1 x +1 1 +delete from t1| +delete from t2| +drop procedure bug8757| +drop procedure if exists bug8762| +drop procedure if exists bug8762; create procedure bug8762() begin end| +drop procedure if exists bug8762; create procedure bug8762() begin end| +drop procedure bug8762| +drop function if exists bug5240| +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| +id bug5240() +answer 42 +drop function bug5240| +drop procedure if exists p1| +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| +create table t3 ( +lpitnumber int(11) default null, +lrecordtype int(11) default null +)| +create table t4 ( +lbsiid int(11) not null default '0', +ltradingmodeid int(11) not null default '0', +ltradingareaid int(11) not null default '0', +csellingprice decimal(19,4) default null, +primary key (lbsiid,ltradingmodeid,ltradingareaid) +)| +create table t5 ( +lbsiid int(11) not null default '0', +ltradingareaid int(11) not null default '0', +primary key (lbsiid,ltradingareaid) +)| +drop procedure if exists bug8849| +create procedure bug8849() +begin +insert into 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| +drop procedure if exists bug8937| +create procedure bug8937() +begin +declare s,x,y,z int; +declare a float; +select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1; +select s,x,y,z; +select avg(data) into a from t1; +select a; +end| +delete from t1| +insert into t1 (data) values (1), (2), (3), (4), (6)| +call bug8937()| +s x y z +16 3 1 6 +a +3.2 +drop procedure bug8937| +delete from t1| +drop procedure if exists bug6900| +drop procedure if exists bug9074| +drop procedure if exists bug6900_9074| +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()| +2 +2 +call bug9074()| +x1 x2 x3 x4 x5 x6 +1 1 1 1 1 1 +call bug6900_9074(0)| +sqlexception +sqlexception +call bug6900_9074(1)| +sqlexception +sqlexception +drop procedure bug6900| +drop procedure bug9074| +drop procedure bug6900_9074| +drop table t3| +drop procedure if exists avg| +create procedure avg () +begin +end| +call avg ()| +drop procedure avg| +drop procedure if exists bug6129| +set @old_mode= @@sql_mode; +set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO"; +create procedure bug6129() +select @@sql_mode| +call bug6129()| +@@sql_mode +ERROR_FOR_DIVISION_BY_ZERO +set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"| +call bug6129()| +@@sql_mode +ERROR_FOR_DIVISION_BY_ZERO +set @@sql_mode= "NO_ZERO_IN_DATE"| +call bug6129()| +@@sql_mode +ERROR_FOR_DIVISION_BY_ZERO +set @@sql_mode=@old_mode; +drop procedure bug6129| +drop procedure if exists bug9856| +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()| +16 +16 +call bug9856()| +16 +16 +drop procedure bug9856| +drop procedure if exists bug9674_1| +drop procedure if exists bug9674_2| +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| +@sptmp +10 +call bug9674_2()| +v/10 +10.0000 +call bug9674_2()| +v/10 +10.0000 +drop procedure bug9674_1| +drop procedure bug9674_2| +drop procedure if exists bug9598_1| +drop procedure if exists bug9598_2| +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)| +v1 v2 v3 v4 v5 +Test 2 3 4 5 +v1 v2 v3 v4 v5 +Test 2 3 4 5 +select @tmp1, @tmp2| +@tmp1 @tmp2 +50 60 +drop procedure bug9598_1| +drop procedure bug9598_2| +drop procedure if exists bug9902| +create function bug9902() returns int(11) +begin +set @x = @x + 1; +return @x; +end| +# Set query cache size, if we have query cache +set @x = 1| +insert into t1 values ("qc", 42)| +select bug9902() from t1| +bug9902() +2 +select bug9902() from t1| +bug9902() +3 +select @x| +@x +3 +# Restore the old query cache size +delete from t1| +drop function bug9902| +drop function if exists bug9102| +create function bug9102() returns blob return 'a'| +select bug9102()| +bug9102() +a +drop function bug9102| +drop function if exists bug7648| +create function bug7648() returns bit(8) return 'a'| +select bug7648()| +bug7648() +a +drop function bug7648| +drop function if exists bug9775| +create function bug9775(v1 char(1)) returns enum('a','b') return v1| +select bug9775('a'),bug9775('b'),bug9775('c')| +bug9775('a') bug9775('b') bug9775('c') +a b +Warnings: +Warning 1265 Data truncated for column 'bug9775('c')' at row 0 +drop function bug9775| +create function bug9775(v1 int) returns enum('a','b') return v1| +select bug9775(1),bug9775(2),bug9775(3)| +bug9775(1) bug9775(2) bug9775(3) +a b +Warnings: +Warning 1265 Data truncated for column 'bug9775(3)' at row 0 +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')| +bug9775('a') bug9775('b') bug9775('a,b') bug9775('c') +a b a +Warnings: +Warning 1265 Data truncated for column 'v1' at row 1 +Warning 1265 Data truncated for column 'bug9775('c')' at row 0 +drop function bug9775| +create function bug9775(v1 int) returns set('a','b') return v1| +select bug9775(1),bug9775(2),bug9775(3),bug9775(4)| +bug9775(1) bug9775(2) bug9775(3) bug9775(4) +a b a,b +Warnings: +Warning 1265 Data truncated for column 'bug9775(4)' at row 0 +drop function bug9775| +drop function if exists bug8861| +create function bug8861(v1 int) returns year return v1| +select bug8861(05)| +bug8861(05) +2005 +set @x = bug8861(05)| +select @x| +@x +2005 +drop function bug8861| +drop procedure if exists bug9004_1| +drop procedure if exists bug9004_2| +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)| +call bug9004_1('12345678901234567')| +Warnings: +Warning 1265 Data truncated for column 'x' at row 0 +call bug9004_2('12345678901234567890')| +Warnings: +Warning 1265 Data truncated for column 'x' at row 0 +delete from t1| +drop procedure bug9004_1| +drop procedure bug9004_2| +drop procedure if exists bug7293| +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')| +yes +yes +call bug7293 ('secrete')| +yes +yes +drop procedure bug7293| +delete from t1| +drop procedure if exists bug9841| +drop view if exists v1| +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| +drop procedure if exists bug5963| +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()| +v +5 +call bug5963_1()| +v +5 +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| +drop function if exists bug9559| +create function bug9559() +returns int +begin +set @y = -6/2; +return @y; +end| +select bug9559()| +bug9559() +-3 +drop function bug9559| +drop procedure if exists bug10961| +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()| +x +1 +x +2 +x +3 +call bug10961()| +x +1 +x +2 +x +3 +drop procedure bug10961| +DROP PROCEDURE IF EXISTS bug6866| +DROP VIEW IF EXISTS tv| +Warnings: +Note 4092 Unknown VIEW: 'test.tv' +DROP TABLE IF EXISTS tt1,tt2,tt3| +Warnings: +Note 1051 Unknown table 'test.tt1,test.tt2,test.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)| +a1 a2 a3 data data2 data3 +1 1 4 xx a d +CALL bug6866(1)| +a1 a2 a3 data data2 data3 +1 1 4 xx a d +CALL bug6866(1)| +a1 a2 a3 data data2 data3 +1 1 4 xx a d +DROP PROCEDURE bug6866; +DROP VIEW tv| +DROP TABLE tt1, tt2, tt3| +DROP PROCEDURE IF EXISTS bug10136| +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()| +name val +aaaaa 1 +bbbbb 2 +ccccc 3 +name val +aaaaa 1 +bbbbb 2 +ccccc 3 +name val +aaaaa 1 +bbbbb 2 +ccccc 3 +call bug10136()| +name val +aaaaa 1 +bbbbb 2 +ccccc 3 +name val +aaaaa 1 +bbbbb 2 +ccccc 3 +name val +aaaaa 1 +bbbbb 2 +ccccc 3 +call bug10136()| +name val +aaaaa 1 +bbbbb 2 +ccccc 3 +name val +aaaaa 1 +bbbbb 2 +ccccc 3 +name val +aaaaa 1 +bbbbb 2 +ccccc 3 +drop procedure bug10136| +drop table t3| +drop procedure if exists bug11529| +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| +set character set utf8| +drop procedure if exists bug6063| +drop procedure if exists bug7088_1| +drop procedure if exists bug7088_2| +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_client=@save_character_set_client| +set @@character_set_results=@save_character_set_client| +show create procedure bug6063| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +bug6063 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug6063`() +begin +lâbel: begin end; +label: begin end; +label1: begin end; +end utf8mb3 latin1_swedish_ci latin1_swedish_ci +show create procedure bug7088_1| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +bug7088_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug7088_1`() +label1: begin end label1 utf8mb3 latin1_swedish_ci latin1_swedish_ci +show create procedure bug7088_2| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +bug7088_2 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug7088_2`() +läbel1: begin end utf8mb3 latin1_swedish_ci latin1_swedish_ci +drop procedure bug6063| +drop procedure bug7088_1| +drop procedure bug7088_2| +drop procedure if exists bug9565_sub| +drop procedure if exists bug9565| +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()| +id data +one 1 +delete from t1| +drop procedure bug9565_sub| +drop procedure bug9565| +drop procedure if exists bug9538| +create procedure bug9538() +set @@sort_buffer_size = 1000000| +set @x = @@sort_buffer_size| +set @@sort_buffer_size = 2000000| +select @@sort_buffer_size| +@@sort_buffer_size +2000000 +call bug9538()| +select @@sort_buffer_size| +@@sort_buffer_size +1000000 +set @@sort_buffer_size = @x| +drop procedure bug9538| +drop procedure if exists bug8692| +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)| +Warnings: +Warning 1265 Data truncated for column 'c3' at row 1 +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()| +v1 v2 v3 v4 v5 v6 v7 + NULL +drop procedure bug8692| +drop table t3| +drop function if exists bug10055| +create function bug10055(v char(255)) returns char(255) return lower(v)| +select t.column_name, bug10055(t.column_name) +from information_schema.columns as t +where t.table_schema = 'test' and t.table_name = 't1'| +column_name bug10055(t.column_name) +id id +data data +drop function bug10055| +drop procedure if exists bug12297| +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| +drop function if exists f_bug11247| +drop procedure if exists p_bug11247| +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| +drop procedure if exists bug12168| +drop table if exists t3, t4| +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| +a +1 +3 +truncate t4| +call bug12168('b')| +select * from t4| +a +2 +4 +truncate t4| +call bug12168('a')| +select * from t4| +a +1 +3 +truncate t4| +call bug12168('b')| +select * from t4| +a +2 +4 +truncate t4| +drop table t3, t4| +drop procedure if exists bug12168| +drop table if exists t3| +drop procedure if exists bug11333| +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| +drop function if exists bug9048| +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"))| +collation(bug9048("foo")) +latin1_bin +drop function bug9048| +drop procedure if exists bug12849_1| +create procedure bug12849_1(inout x char) select x into x| +set @var='a'| +call bug12849_1(@var)| +select @var| +@var +a +drop procedure bug12849_1| +drop procedure if exists bug12849_2| +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| +@var +abcdabcd +drop procedure bug12849_2| +drop procedure if exists bug131333| +drop function if exists bug131333| +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()| +a +NULL +a +1 +b +NULL +select bug131333()| +bug131333() +NULL +drop procedure bug131333| +drop function bug131333| +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| +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| +select bug12379()| +ERROR 23000: Duplicate entry 'X' for key 'PRIMARY' +select 1| +1 +1 +call bug12379_1()| +bug12379() +42 +42 +select 2| +2 +2 +call bug12379_2()| +bug12379() +select 3| +3 +3 +call bug12379_3()| +ERROR 23000: Duplicate entry 'X' for key 'PRIMARY' +select 4| +4 +4 +drop function bug12379| +drop procedure bug12379_1| +drop procedure bug12379_2| +drop procedure bug12379_3| +drop table t3| +drop procedure if exists bug13124| +create procedure bug13124() +begin +declare y integer; +set @x=y; +end| +call bug13124()| +drop procedure bug13124| +drop procedure if exists bug12979_1| +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| +drop procedure if exists bug12979_2| +create procedure bug12979_2() +begin +declare internal_var decimal(5); +set internal_var= internal_var / 2; +select internal_var; +end| +call bug12979_2()| +internal_var +NULL +drop procedure bug12979_2| +drop table if exists t3| +drop procedure if exists bug6127| +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| +s1 +0 +1 +call bug6127()| +ERROR 23000: Duplicate entry '0' for key 's1' +select * from t3| +s1 +0 +1 +set sql_mode=@sm| +drop table t3| +drop procedure bug6127| +drop procedure if exists bug12589_1| +drop procedure if exists bug12589_2| +drop procedure if exists bug12589_3| +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| +call bug12589_1()| +Table Create Table +tm1 CREATE TEMPORARY TABLE `tm1` ( + `spv1` decimal(3,3) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +call bug12589_2()| +Table Create Table +tm1 CREATE TEMPORARY TABLE `tm1` ( + `spv1` decimal(6,3) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +call bug12589_3()| +Table Create Table +tm1 CREATE TEMPORARY TABLE `tm1` ( + `spv1` decimal(6,3) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop procedure bug12589_1| +drop procedure bug12589_2| +drop procedure bug12589_3| +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| +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()| +Result +Caught it +select * from t3| +x +42 +delete from t3| +call bug7049_4()| +Result +Caught it +select * from t3| +x +42 +select bug7049_2()| +bug7049_2() +1 +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| +drop function if exists bug13941| +drop procedure if exists bug13941| +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| +select bug13941('this is a test')| +bug13941('this is a test') +questo una prova +call bug13941(@a)| +select @a| +@a +Local +drop function bug13941| +drop procedure bug13941| +DROP PROCEDURE IF EXISTS bug13095; +DROP TABLE IF EXISTS bug13095_t1; +DROP VIEW IF EXISTS bug13095_v1; +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| +CALL bug13095('bug13095_t1'); +@str +CREATE TABLE bug13095_t1(stuff char(15)) +@str +INSERT INTO bug13095_t1 VALUES('row1'),('row2'),('row3') +@str +CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM bug13095_t1 +c1 +row1 +row2 +row3 +@str +DROP VIEW bug13095_v1 +DROP PROCEDURE IF EXISTS bug13095; +DROP VIEW IF EXISTS bug13095_v1; +DROP TABLE IF EXISTS bug13095_t1; +drop function if exists bug14723| +drop procedure if exists bug14723| +/*!50003 create function bug14723() +returns bigint(20) +main_loop: begin +return 42; +end */;; +show create function bug14723;; +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug14723 CREATE DEFINER=`root`@`localhost` FUNCTION `bug14723`() RETURNS bigint(20) +main_loop: begin +return 42; +end latin1 latin1_swedish_ci latin1_swedish_ci +select bug14723();; +bug14723() +42 +/*!50003 create procedure bug14723() +main_loop: begin +select 42; +end */;; +show create procedure bug14723;; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +bug14723 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug14723`() +main_loop: begin +select 42; +end latin1 latin1_swedish_ci latin1_swedish_ci +call bug14723();; +42 +42 +drop function bug14723| +drop procedure bug14723| +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()| +a +0 +drop procedure bug14845| +drop procedure if exists bug13549_1| +drop procedure if exists bug13549_2| +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| +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| +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| +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| +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| +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| +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| +@@max_sp_recursion_depth +4 +select bug10100f(3)| +ERROR HY000: Recursive stored functions and triggers are not allowed +select bug10100f(6)| +ERROR HY000: Recursive stored functions and triggers are not allowed +call bug10100t(5)| +res +120 +call bug10100pt(1,5)| +a +4 +call bug10100pv(1,5)| +a +4 +update t3 set a=1| +call bug10100pd(1,5)| +level +1 +a +7 +a +7 +level +2 +a +13 +a +13 +level +3 +a +19 +a +19 +level +4 +a +25 +a +25 +a +25 +select * from t3| +a +25 +update t3 set a=1| +call bug10100pc(1,5)| +level +1 +lv +1 +level +2 +lv +2 +level +3 +lv +4 +level +4 +lv +7 +a +11 +select * from t3| +a +11 +set @@max_sp_recursion_depth=0| +select @@max_sp_recursion_depth| +@@max_sp_recursion_depth +0 +select bug10100f(5)| +ERROR HY000: Recursive stored functions and triggers are not allowed +call bug10100t(5)| +ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug10100p +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| +drop procedure if exists bug13729| +drop table if exists t3| +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()| +55 +55 +select * from t3| +s1 +1 +2 +drop procedure bug13729| +drop table t3| +drop procedure if exists bug14643_1| +drop procedure if exists bug14643_2| +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| +ERROR 42000: Undeclared variable: undefined_var +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| +ERROR 42000: Undeclared variable: undefined_var +drop procedure if exists bug14304| +drop table if exists t3, t4| +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()| +a +1 +2 +3 +drop procedure bug14304| +drop table t3, t4| +drop procedure if exists bug14376| +create procedure bug14376() +begin +declare x int default x; +end| +ERROR 42000: Undeclared variable: x +create procedure bug14376() +begin +declare x int default 42; +begin +declare x int default x; +select x; +end; +end| +call bug14376()| +x +42 +drop procedure bug14376| +create procedure bug14376(x int) +begin +declare x int default x; +select x; +end| +call bug14376(4711)| +x +4711 +drop procedure bug14376| +drop procedure if exists bug5967| +drop table if exists t3| +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| +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +call bug5967("a - stored procedure parameter")| +a +a - stored procedure parameter +Parameter takes precedence over table column +a - stored procedure parameter +Parameter takes precedence over table column in cursors +a - stored procedure parameter +A local variable takes precedence over parameter +a - local variable +A local variable takes precedence over parameter in cursors +a - local variable +A local variable in a nested compound statement takes precedence over a local variable in the outer statement +a - local variable in a nested compound statement +A local variable in a nested compound statement takes precedence over table column +a - local variable in a nested compound statement +A local variable in a nested compound statement takes precedence over table column in cursors +a - local variable in a nested compound statement +drop procedure bug5967| +drop procedure if exists bug13012| +create procedure bug13012() +BEGIN +REPAIR TABLE t1; +END| +call bug13012()| +Table Op Msg_type Msg_text +test.t1 repair status OK +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()| +Table Op Msg_type Msg_text +test.t1 repair status OK +test.t2 repair status OK +test.t3 repair status OK +test.v1 repair Error 'test.v1' is not of type 'BASE TABLE' +test.v1 repair status Operation failed +Table Op Msg_type Msg_text +test.t1 optimize status OK +test.t2 optimize status OK +test.t3 optimize status OK +test.v1 optimize Error 'test.v1' is not of type 'BASE TABLE' +test.v1 optimize status Operation failed +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status Table is already up to date +test.v1 analyze Error 'test.v1' is not of type 'BASE TABLE' +test.v1 analyze status Operation failed +call bug13012()| +Table Op Msg_type Msg_text +test.t1 repair status OK +test.t2 repair status OK +test.t3 repair status OK +test.v1 repair Error 'test.v1' is not of type 'BASE TABLE' +test.v1 repair status Operation failed +Table Op Msg_type Msg_text +test.t1 optimize status OK +test.t2 optimize status OK +test.t3 optimize status OK +test.v1 optimize Error 'test.v1' is not of type 'BASE TABLE' +test.v1 optimize status Operation failed +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status Table is already up to date +test.v1 analyze Error 'test.v1' is not of type 'BASE TABLE' +test.v1 analyze status Operation failed +call bug13012()| +Table Op Msg_type Msg_text +test.t1 repair status OK +test.t2 repair status OK +test.t3 repair status OK +test.v1 repair Error 'test.v1' is not of type 'BASE TABLE' +test.v1 repair status Operation failed +Table Op Msg_type Msg_text +test.t1 optimize status OK +test.t2 optimize status OK +test.t3 optimize status OK +test.v1 optimize Error 'test.v1' is not of type 'BASE TABLE' +test.v1 optimize status Operation failed +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status Table is already up to date +test.v1 analyze Error 'test.v1' is not of type 'BASE TABLE' +test.v1 analyze status Operation failed +drop procedure bug13012| +drop view v1| +select * from t1 order by data| +id data +aa 0 +aa 1 +aa 2 +aa 3 +aa 4 +aa 5 +aa 6 +aa 7 +aa 8 +aa 9 +drop schema if exists mysqltest1| +Warnings: +Note 1008 Can't drop database 'mysqltest1'; database doesn't exist +drop schema if exists mysqltest2| +Warnings: +Note 1008 Can't drop database 'mysqltest2'; database doesn't exist +drop schema if exists mysqltest3| +Warnings: +Note 1008 Can't drop database 'mysqltest3'; database doesn't exist +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| +call mysqltest1.p1(@rs)| +ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 +call mysqltest1.p1(@rs)| +ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 +call mysqltest1.p1(@rs)| +ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 +drop schema if exists mysqltest1| +drop schema if exists mysqltest2| +drop schema if exists mysqltest3| +use test| +drop table if exists t3| +drop procedure if exists bug15441| +create table t3 (id int not null primary key, county varchar(25))| +insert into t3 (id, county) values (1, 'York')| +create procedure bug15441(c varchar(25)) +begin +update t3 set id=2, county=value(c); +end| +call bug15441('county')| +ERROR 42S22: Unknown column 'c' in 'field list' +drop procedure bug15441| +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')| +id county +1 Yale +id county +2 NULL +drop table t3| +drop procedure bug15441| +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| +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| +ERROR 42000: Undeclared variable: v +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| +ERROR 42000: Undeclared variable: v +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| +ERROR 42000: Undeclared variable: v +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| +ERROR 42000: Undeclared variable: v +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| +ERROR 42000: Undeclared variable: v +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| +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()| +1 +Before NOT FOUND condition is triggered +2 +After NOT FOUND condtition is triggered +xid xdone +1 1 +call bug15231_3()| +Result +Missed it (correct) +Level Code Message +Warning 1366 Incorrect decimal value: 'zap' for column ``.``.`x` at row 0 +Result +Caught it (correct) +call bug15231_5()| +Result +Missed it (correct) +id +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| +drop procedure if exists bug15011| +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()| +Handler +Inner +drop procedure bug15011| +drop table t3| +drop procedure if exists bug17476| +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')| +date_format(t3.d, pDateFormat) count(*) +2005-01 4 +2005-02 2 +call bug17476('%Y-%m')| +date_format(t3.d, pDateFormat) count(*) +2005-01 4 +2005-02 2 +drop table t3| +drop procedure bug17476| +drop table if exists t3| +drop procedure if exists bug16887| +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()| +i +9 +i +8 +i +7 +i +6 +i +5 +i +4 +i +3 +i +2 +i +1 +i +0 +drop table t3| +drop procedure bug16887| +drop procedure if exists bug16474_1| +drop procedure if exists bug16474_2| +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| +drop procedure if exists bug14945| +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| +id +1 +drop table t3| +drop procedure bug14945| +create procedure bug16474_2(x int) +select id from t1 order by x, id| +call bug16474_1()| +id +a +b +c +call bug16474_2(1)| +id +a +b +c +call bug16474_2(2)| +id +a +b +c +drop procedure bug16474_1| +drop procedure bug16474_2| +set @x = 2| +select * from t1 order by @x, data| +id data +a 1 +c 2 +b 3 +delete from t1| +drop function if exists bug15728| +drop table if exists t3| +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()| +last_insert_id() +1 +select bug15728()| +bug15728() +1 +drop function bug15728| +drop table t3| +drop procedure if exists bug18787| +create procedure bug18787() +begin +declare continue handler for sqlexception begin end; +select no_such_function(); +end| +call bug18787()| +drop procedure bug18787| +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| +select schema_name from information_schema.schemata where +schema_name like 'bug18344%'| +schema_name +bug18344_012345678901 +bug18344_0123456789012 +select routine_name,routine_schema from information_schema.routines where +routine_schema like 'bug18344%'| +routine_name routine_schema +bug18344 bug18344_012345678901 +bug18344 bug18344_0123456789012 +bug18344_2 bug18344_012345678901 +bug18344_2 bug18344_0123456789012 +drop database bug18344_012345678901| +drop database bug18344_0123456789012| +select schema_name from information_schema.schemata where +schema_name like 'bug18344%'| +schema_name +select routine_name,routine_schema from information_schema.routines where +routine_schema like 'bug18344%'| +routine_name routine_schema +drop function if exists bug12472| +create function bug12472() returns int return (select count(*) from t1)| +create table t3 as select bug12472() as i| +show create table t3| +Table Create Table +t3 CREATE TABLE `t3` ( + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +select * from t3| +i +0 +drop table t3| +create view v1 as select bug12472() as j| +create table t3 as select * from v1| +show create table t3| +Table Create Table +t3 CREATE TABLE `t3` ( + `j` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +select * from t3| +j +0 +drop table t3| +drop view v1| +drop function bug12472| +DROP FUNCTION IF EXISTS bug18589_f1| +DROP PROCEDURE IF EXISTS bug18589_p1| +DROP PROCEDURE IF EXISTS bug18589_p2| +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| +SELECT bug18589_f1(REPEAT("a", 767))| +bug18589_f1(REPEAT("a", 767)) +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +SET @bug18589_v1 = ""| +CALL bug18589_p1(REPEAT("a", 767), @bug18589_v1)| +SELECT @bug18589_v1| +@bug18589_v1 +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +CALL bug18589_p2(REPEAT("a", 767))| +v +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +DROP FUNCTION bug18589_f1| +DROP PROCEDURE bug18589_p1| +DROP PROCEDURE bug18589_p2| +DROP FUNCTION IF EXISTS bug18037_f1| +DROP PROCEDURE IF EXISTS bug18037_p1| +DROP PROCEDURE IF EXISTS bug18037_p2| +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()| +bug18037_f1() +1 +CALL bug18037_p1()| +CALL bug18037_p2()| +1 +1 +DROP FUNCTION bug18037_f1| +DROP PROCEDURE bug18037_p1| +DROP PROCEDURE bug18037_p2| +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| +i mysqltest1.bug17199() +1 ok +2 ok +use mysqltest1| +create function bug18444(i int) returns int no sql deterministic return i + 1| +use test| +select mysqltest1.bug18444(i) from t3| +mysqltest1.bug18444(i) +2 +3 +drop database mysqltest1| +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| +Database Create Database +mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET koi8r COLLATE koi8r_general_ci */ +show create database mysqltest2| +Database Create Database +mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci */ +alter database mysqltest1 character set utf8| +use mysqltest2| +call mysqltest1.p1()| +show create database mysqltest1| +Database Create Database +mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET koi8r COLLATE koi8r_general_ci */ +show create database mysqltest2| +Database Create Database +mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci */ +drop database mysqltest1| +drop database mysqltest2| +use test| +drop table if exists t3| +drop procedure if exists bug15217| +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 | +call bug15217()| +concat('data was: /', var1, '/') +data was: /1/ +flush tables | +call bug15217()| +concat('data was: /', var1, '/') +data was: /1/ +drop table t3| +drop procedure bug15217| +DROP PROCEDURE IF EXISTS bug21013 | +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 | +DROP DATABASE IF EXISTS mysqltest1| +DROP DATABASE IF EXISTS mysqltest2| +CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8| +CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8| +use mysqltest1| +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 ""| +SHOW CREATE FUNCTION bug16211_f1| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8mb3 COLLATE utf8mb3_general_ci +RETURN "" latin1 latin1_swedish_ci utf8mb3_general_ci +SHOW CREATE FUNCTION bug16211_f2| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r COLLATE koi8r_general_ci +RETURN "" latin1 latin1_swedish_ci utf8mb3_general_ci +SHOW CREATE FUNCTION mysqltest2.bug16211_f3| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8mb3 COLLATE utf8mb3_general_ci +RETURN "" latin1 latin1_swedish_ci utf8mb3_general_ci +SHOW CREATE FUNCTION mysqltest2.bug16211_f4| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r COLLATE koi8r_general_ci +RETURN "" latin1 latin1_swedish_ci utf8mb3_general_ci +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| +dtd_identifier +char(10) +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"| +dtd_identifier +char(10) +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"| +dtd_identifier +char(10) +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"| +dtd_identifier +char(10) +SELECT CHARSET(bug16211_f1())| +CHARSET(bug16211_f1()) +utf8mb3 +SELECT CHARSET(bug16211_f2())| +CHARSET(bug16211_f2()) +koi8r +SELECT CHARSET(mysqltest2.bug16211_f3())| +CHARSET(mysqltest2.bug16211_f3()) +utf8mb3 +SELECT CHARSET(mysqltest2.bug16211_f4())| +CHARSET(mysqltest2.bug16211_f4()) +koi8r +ALTER DATABASE mysqltest1 CHARACTER SET cp1251| +ALTER DATABASE mysqltest2 CHARACTER SET cp1251| +SHOW CREATE FUNCTION bug16211_f1| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8mb3 COLLATE utf8mb3_general_ci +RETURN "" latin1 latin1_swedish_ci utf8mb3_general_ci +SHOW CREATE FUNCTION bug16211_f2| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r COLLATE koi8r_general_ci +RETURN "" latin1 latin1_swedish_ci utf8mb3_general_ci +SHOW CREATE FUNCTION mysqltest2.bug16211_f3| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8mb3 COLLATE utf8mb3_general_ci +RETURN "" latin1 latin1_swedish_ci utf8mb3_general_ci +SHOW CREATE FUNCTION mysqltest2.bug16211_f4| +Function sql_mode Create Function character_set_client collation_connection Database Collation +bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r COLLATE koi8r_general_ci +RETURN "" latin1 latin1_swedish_ci utf8mb3_general_ci +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| +dtd_identifier +char(10) +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"| +dtd_identifier +char(10) +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"| +dtd_identifier +char(10) +SELECT dtd_identifier +FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"| +dtd_identifier +char(10) +SELECT CHARSET(bug16211_f1())| +CHARSET(bug16211_f1()) +utf8mb3 +SELECT CHARSET(bug16211_f2())| +CHARSET(bug16211_f2()) +koi8r +SELECT CHARSET(mysqltest2.bug16211_f3())| +CHARSET(mysqltest2.bug16211_f3()) +utf8mb3 +SELECT CHARSET(mysqltest2.bug16211_f4())| +CHARSET(mysqltest2.bug16211_f4()) +koi8r +use test| +DROP DATABASE mysqltest1| +DROP DATABASE mysqltest2| +DROP DATABASE IF EXISTS mysqltest1| +CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8| +use mysqltest1| +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| +SET @v2 = 'b'| +SET @v3 = 'c'| +CALL bug16676_p1('a', @v2, @v3)| +CHARSET(p1) COLLATION(p1) +utf8mb3 utf8mb3_general_ci +CHARSET(p2) COLLATION(p2) +utf8mb3 utf8mb3_general_ci +CHARSET(p3) COLLATION(p3) +utf8mb3 utf8mb3_general_ci +CALL bug16676_p2('a', @v2, @v3)| +CHARSET(p1) COLLATION(p1) +koi8r koi8r_general_ci +CHARSET(p2) COLLATION(p2) +cp1251 cp1251_general_ci +CHARSET(p3) COLLATION(p3) +greek greek_general_ci +use test| +DROP DATABASE mysqltest1| +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| +create table t3 (a int)| +create table t4 (a int)| +insert into t3 values (1), (1), (2), (3)| +insert into t4 values (1), (1)| +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()| +statement failed +statement failed +statement after update +statement after update +select * from t3| +a +1 +1 +2 +3 +call bug8153_subselect()| +statement failed +statement failed +statement after update +statement after update +select * from t3| +a +1 +1 +2 +3 +drop procedure bug8153_subselect| +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()| +reachable code a1 +reachable code a1 +reachable code b1 +reachable code b1 +in continue handler +in continue handler +reachable code a2 +reachable code a2 +select * from t3| +a +1 +1 +2 +3 +call bug8153_subselect_a()| +reachable code a1 +reachable code a1 +reachable code b1 +reachable code b1 +in continue handler +in continue handler +reachable code a2 +reachable code a2 +select * from t3| +a +1 +1 +2 +3 +drop procedure bug8153_subselect_a| +drop procedure bug8153_subselect_b| +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()| +reachable code a1 +reachable code a1 +reachable code b1 +reachable code b1 +in continue handler +in continue handler +reachable code a2 +reachable code a2 +drop procedure bug8153_proc_a| +drop procedure bug8153_proc_b| +drop table t3| +drop table t4| +drop procedure if exists bug19862| +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| +bug19862(a) +2 +3 +SELECT * FROM t11| +a +1 +2 +DROP TABLE t11, t12| +DROP FUNCTION bug19862| +drop table if exists t3| +drop database if exists mysqltest1| +create table t3 (a int)| +insert into t3 (a) values (1), (2)| +create database mysqltest1| +use mysqltest1| +drop database mysqltest1| +select database()| +database() +NULL +select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2| +a +1 +use test| +drop table t3| +DROP PROCEDURE IF EXISTS bug16899_p1| +DROP FUNCTION IF EXISTS bug16899_f1| +CREATE DEFINER=longer_than_80_456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789@localhost PROCEDURE bug16899_p1() +BEGIN +SET @a = 1; +END| +ERROR HY000: String 'longer_than_80_4567890123456789012345678901234567890123456789012345...' is too long for user name (should be no longer than 128) +CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY +FUNCTION bug16899_f1() RETURNS INT +BEGIN +RETURN 1; +END| +ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY1...' is too long for host name (should be no longer than 255) +drop procedure if exists bug21416| +create procedure bug21416() show create procedure bug21416| +call bug21416()| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +bug21416 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21416`() +show create procedure bug21416 latin1 latin1_swedish_ci latin1_swedish_ci +drop procedure bug21416| +DROP PROCEDURE IF EXISTS bug21414| +CREATE PROCEDURE bug21414() SELECT 1| +FLUSH TABLES WITH READ LOCK| +DROP PROCEDURE bug21414| +ERROR HY000: Can't execute the query because you have a conflicting read lock +UNLOCK TABLES| +The following should succeed. +DROP PROCEDURE bug21414| +set names utf8| +drop database if exists това_е_дълго_име_за_база_данни_нали| +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')| +call това_е_дълго_име_за_база_данни_нали.това_е_процедура_Ñ_доÑта_дълго_име_нали_и_още_по_дълго()| +ERROR HY000: Failed to load routine това_е_дълго_име_за_база_данни_нали.това_е_процедура_Ñ_доÑта_дълго_име_нали_и_още_по_дълго (internal code -6). For more details, run SHOW WARNINGS +drop database това_е_дълго_име_за_база_данни_нали| +show warnings| +Level Code Message +Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2 +Error 1457 Failed to load routine това_е_дълго_име_за_база_данни_нали.това_е_процедура_Ñ_доÑта_дълго_име_нали_и_още_по_дълго (internal code -6). For more details, run SHOW WARNINGS +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' )| +DROP FUNCTION IF EXISTS bug21493| +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')| +bug21493('111111') +NULL +SELECT bug21493('222222')| +bug21493('222222') +CAD +SELECT bug21493(Member_ID) FROM t3| +bug21493(Member_ID) +NULL +CAD +CAD +CAD +CAD +CHF +DROP FUNCTION bug21493| +DROP TABLE t3,t4| +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| +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()| +func_20028_a() +0 +select func_20028_b()| +func_20028_b() +0 +select func_20028_c()| +ERROR 22012: Division by 0 +call proc_20028_a()| +Warnings: +Warning 1329 No data - zero rows fetched, selected, or processed +call proc_20028_b()| +Warnings: +Warning 1329 No data - zero rows fetched, selected, or processed +call proc_20028_c()| +ERROR 22012: Division by 0 +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()| +func_20028_a() +0 +select func_20028_b()| +func_20028_b() +0 +select func_20028_c()| +func_20028_c() +NULL +call proc_20028_a()| +Warnings: +Warning 1329 No data - zero rows fetched, selected, or processed +call proc_20028_b()| +Warnings: +Warning 1329 No data - zero rows fetched, selected, or processed +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| +drop procedure if exists proc_21462_a| +drop procedure if exists proc_21462_b| +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| +Called A +Called A +call proc_21462_a()| +Called A +Called A +call proc_21462_a(1)| +ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_a; expected 0, got 1 +call proc_21462_b| +ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_b; expected 1, got 0 +call proc_21462_b()| +ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_b; expected 1, got 0 +call proc_21462_b(1)| +Called B +Called B +drop procedure proc_21462_a| +drop procedure proc_21462_b| +drop table if exists t3| +drop procedure if exists proc_bug19733| +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| +DROP PROCEDURE IF EXISTS p1| +DROP VIEW IF EXISTS v1, v2| +DROP TABLE IF EXISTS t3, t4| +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| +CALL p1()| +t3_id_1 t3_id_2 t4_id +0 0 2 +1 NULL NULL +CALL p1()| +t3_id_1 t3_id_2 t4_id +0 0 2 +1 NULL NULL +DROP PROCEDURE p1| +DROP VIEW v1, v2| +DROP TABLE t3, t4| +End of 5.0 tests +Begin of 5.1 tests +drop function if exists pi; +create function pi() returns varchar(50) +return "pie, my favorite desert."; +Warnings: +Note 1585 This function 'pi' has the same name as a native function +SET @save_sql_mode=@@sql_mode; +SET SQL_MODE='IGNORE_SPACE'; +select pi(), pi (); +pi() pi () +3.141593 3.141593 +select test.pi(), test.pi (); +test.pi() test.pi () +pie, my favorite desert. pie, my favorite desert. +SET SQL_MODE=''; +select pi(), pi (); +pi() pi () +3.141593 3.141593 +select test.pi(), test.pi (); +test.pi() test.pi () +pie, my favorite desert. pie, my favorite desert. +SET @@sql_mode=@save_sql_mode; +drop function pi; +drop function if exists test.database; +drop function if exists test.current_user; +drop function if exists test.md5; +create database nowhere; +use nowhere; +drop database nowhere; +SET @save_sql_mode=@@sql_mode; +SET SQL_MODE='IGNORE_SPACE'; +select database(), database (); +database() database () +NULL NULL +select current_user(), current_user (); +current_user() current_user () +root@localhost root@localhost +select md5("aaa"), md5 ("aaa"); +md5("aaa") md5 ("aaa") +47bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 +SET SQL_MODE=''; +select database(), database (); +database() database () +NULL NULL +select current_user(), current_user (); +current_user() current_user () +root@localhost root@localhost +select md5("aaa"), md5 ("aaa"); +md5("aaa") md5 ("aaa") +47bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 +use test; +create function `database`() returns varchar(50) +return "Stored function database"; +Warnings: +Note 1585 This function 'database' has the same name as a native function +create function `current_user`() returns varchar(50) +return "Stored function current_user"; +Warnings: +Note 1585 This function 'current_user' has the same name as a native function +create function md5(x varchar(50)) returns varchar(50) +return "Stored function md5"; +Warnings: +Note 1585 This function 'md5' has the same name as a native function +SET SQL_MODE='IGNORE_SPACE'; +select database(), database (); +database() database () +test test +select current_user(), current_user (); +current_user() current_user () +root@localhost root@localhost +select md5("aaa"), md5 ("aaa"); +md5("aaa") md5 ("aaa") +47bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 +select test.database(), test.database (); +test.database() test.database () +Stored function database Stored function database +select test.current_user(), test.current_user (); +test.current_user() test.current_user () +Stored function current_user Stored function current_user +select test.md5("aaa"), test.md5 ("aaa"); +test.md5("aaa") test.md5 ("aaa") +Stored function md5 Stored function md5 +SET SQL_MODE=''; +select database(), database (); +database() database () +test test +select current_user(), current_user (); +current_user() current_user () +root@localhost root@localhost +select md5("aaa"), md5 ("aaa"); +md5("aaa") md5 ("aaa") +47bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 +select test.database(), test.database (); +test.database() test.database () +Stored function database Stored function database +select test.current_user(), test.current_user (); +test.current_user() test.current_user () +Stored function current_user Stored function current_user +select test.md5("aaa"), test.md5 ("aaa"); +test.md5("aaa") test.md5 ("aaa") +Stored function md5 Stored function md5 +SET @@sql_mode=@save_sql_mode; +drop function test.database; +drop function test.current_user; +drop function md5; +use test; +End of 5.1 tests +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| +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()| +ROW_COUNT() +7 +CALL bug23760_test_row_count()| +SELECT * FROM bug23760_log ORDER BY id| +id reason ammount +1 Test is working 7 +SET @save_max_sp_recursion= @@max_sp_recursion_depth| +SELECT @save_max_sp_recursion| +@save_max_sp_recursion +0 +SET max_sp_recursion_depth= 5| +SELECT @@max_sp_recursion_depth| +@@max_sp_recursion_depth +5 +CALL bug23760_test_row_count2(2)| +SELECT ROW_COUNT()| +ROW_COUNT() +16 +SELECT * FROM bug23760_log ORDER BY id| +id reason ammount +1 Test is working 7 +2 Test2 is working 7 +3 Test2 is working 7 +SELECT * FROM bug23760 ORDER by ID| +id num +1 2 +2 3 +3 3 +4 4 +5 5 +6 7 +7 10 +SET max_sp_recursion_depth= @save_max_sp_recursion| +SELECT bug23760_rc_test(123)| +bug23760_rc_test(123) +123 +INSERT INTO bug23760 (num) VALUES (13), (21), (34), (55)| +SELECT bug23760_rc_test(ROW_COUNT())| +bug23760_rc_test(ROW_COUNT()) +4 +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| +DROP PROCEDURE IF EXISTS bug24117| +DROP TABLE IF EXISTS t3| +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| +drop function if exists func_8407_a| +drop function if exists func_8407_b| +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| +Warnings: +Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead +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()| +func_8407_a() +NULL +select func_8407_b()| +func_8407_b() +1500 +drop function func_8407_a| +drop function func_8407_b| +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| +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| +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| +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)| +do something +do something +do something again +do something again +caught something +caught something +looping i +looping 4 +looping i +looping 3 +looping i +looping 2 +looping i +looping 1 +looping i +looping 0 +leaving handler +leaving handler +call proc_26503_ok_2(2)| +do something +do something +do something again +do something again +caught something +caught something +looping i +looping 4 +leaving handler +leaving handler +call proc_26503_ok_3(3)| +do something +do something +do something again +do something again +caught something +caught something +looping i +looping 4 +looping i +looping 3 +looping i +looping 2 +looping i +looping 1 +looping i +looping 0 +leaving handler +leaving handler +call proc_26503_ok_4(4)| +do something +do something +do something again +do something again +caught something +caught something +looping i +looping 4 +leaving handler +leaving handler +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| +DROP FUNCTION IF EXISTS bug25373| +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| +SUM(f2) bug25373(f1) +6.300000071525574 1 +15 2 +21.300000071525574 NULL +DROP FUNCTION bug25373| +DROP TABLE t3| +DROP DATABASE IF EXISTS mysqltest1| +DROP DATABASE IF EXISTS mysqltest2| +CREATE DATABASE mysqltest1| +CREATE DATABASE mysqltest2| +CREATE PROCEDURE mysqltest1.p1() +DROP DATABASE mysqltest2| +use mysqltest2| +CALL mysqltest1.p1()| +Warnings: +Note 1049 Unknown database 'mysqltest2' +SELECT DATABASE()| +DATABASE() +NULL +DROP DATABASE mysqltest1| +use test| +drop function if exists bug20777| +drop table if exists examplebug20777| +create function bug20777(f1 bigint unsigned) returns bigint unsigned +begin +set f1 = (f1 - 10); set f1 = (f1 + 10); +return f1; +end| +select bug20777(9223372036854775803) as '9223372036854775803 2**63-5'; +9223372036854775803 2**63-5 +9223372036854775803 +select bug20777(9223372036854775804) as '9223372036854775804 2**63-4'; +9223372036854775804 2**63-4 +9223372036854775804 +select bug20777(9223372036854775805) as '9223372036854775805 2**63-3'; +9223372036854775805 2**63-3 +9223372036854775805 +select bug20777(9223372036854775806) as '9223372036854775806 2**63-2'; +9223372036854775806 2**63-2 +9223372036854775806 +select bug20777(9223372036854775807) as '9223372036854775807 2**63-1'; +9223372036854775807 2**63-1 +9223372036854775807 +select bug20777(9223372036854775808) as '9223372036854775808 2**63+0'; +9223372036854775808 2**63+0 +9223372036854775808 +select bug20777(9223372036854775809) as '9223372036854775809 2**63+1'; +9223372036854775809 2**63+1 +9223372036854775809 +select bug20777(9223372036854775810) as '9223372036854775810 2**63+2'; +9223372036854775810 2**63+2 +9223372036854775810 +select bug20777(-9223372036854775808) as 'lower bounds signed bigint'; +ERROR 22003: BIGINT UNSIGNED value is out of range in 'f1@0 - 10' +select bug20777(9223372036854775807) as 'upper bounds signed bigint'; +upper bounds signed bigint +9223372036854775807 +select bug20777(0) as 'lower bounds unsigned bigint'; +ERROR 22003: BIGINT UNSIGNED value is out of range in 'f1@0 - 10' +select bug20777(18446744073709551615) as 'upper bounds unsigned bigint'; +upper bounds unsigned bigint +18446744073709551615 +select bug20777(18446744073709551616) as 'upper bounds unsigned bigint + 1'; +upper bounds unsigned bigint + 1 +18446744073709551615 +Warnings: +Warning 1264 Out of range value for column 'f1' at row 1 +select bug20777(-1) as 'lower bounds unsigned bigint - 1'; +ERROR 22003: BIGINT UNSIGNED value is out of range in 'f1@0 - 10' +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'; +Warnings: +Warning 1264 Out of range value for column 'f1' at row 1 +insert into examplebug20777 values (1, 9223372036854775806, 9223372036854775807, 223372036854775808, 9223372036854775809, 18446744073709551614, 18446744073709551615, 8446744073709551616); +show create table examplebug20777; +Table Create Table +examplebug20777 CREATE TABLE `examplebug20777` ( + `i` int(1) NOT NULL, + `2**63-2` bigint(20) unsigned DEFAULT NULL, + `2**63-1` bigint(20) unsigned DEFAULT NULL, + `2**63` bigint(20) unsigned DEFAULT NULL, + `2**63+1` bigint(20) unsigned DEFAULT NULL, + `2**64-2` bigint(20) unsigned DEFAULT NULL, + `2**64-1` bigint(20) unsigned DEFAULT NULL, + `2**64` bigint(20) unsigned DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +select * from examplebug20777 order by i; +i 2**63-2 2**63-1 2**63 2**63+1 2**64-2 2**64-1 2**64 +0 9223372036854775806 9223372036854775807 9223372036854775808 9223372036854775809 18446744073709551614 18446744073709551615 18446744073709551615 +1 9223372036854775806 9223372036854775807 223372036854775808 9223372036854775809 18446744073709551614 18446744073709551615 8446744073709551616 +drop table examplebug20777; +select bug20777(18446744073709551613)+1; +bug20777(18446744073709551613)+1 +18446744073709551614 +drop function bug20777; +DROP FUNCTION IF EXISTS bug5274_f1| +DROP FUNCTION IF EXISTS bug5274_f2| +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()| +bug5274_f2() +x +DROP FUNCTION bug5274_f1| +DROP FUNCTION bug5274_f2| +drop procedure if exists proc_21513| +create procedure proc_21513()`my_label`:BEGIN END| +show create procedure proc_21513| +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +proc_21513 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_21513`() +`my_label`:BEGIN END utf8mb3 utf8mb3_general_ci latin1_swedish_ci +drop procedure proc_21513| +End of 5.0 tests. +drop table t1,t2; +CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM; +CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb; +set @a=0; +CREATE function bug27354() RETURNS int not deterministic +begin +insert into t1 values (null); +set @a=@a+1; +return @a; +end| +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 */; +count(t_1.a) count(t_2.a) +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 ... */; +a b +1 -1 +2 -2 +3 -3 +select count(*) from t1 /* must be 3 */; +count(*) +3 +drop table t1,t2; +drop function bug27354; +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; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`metered`(`t1`.`a`) AS `metered` from `t1` utf8mb3 utf8mb3_general_ci +DROP VIEW v1; +DROP FUNCTION metered; +DROP TABLE t1; +SET @p1_p2_cnt= 2; +CREATE TABLE t1 (c1 INT); +CREATE VIEW v1 AS SELECT * FROM t1; +PREPARE s1 FROM 'SELECT c1 FROM v1'; +EXECUTE s1; +c1 +EXECUTE s1; +c1 +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| +CALL p1(2); +c1 +c1 +CALL p2(2); +c1 +c1 +c1 +c1 +c1 +c1 +SELECT f1(2); +f1(2) +0 +PREPARE s1 FROM 'SELECT f1(2)'; +EXECUTE s1; +f1(2) +0 +EXECUTE s1; +f1(2) +0 +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1; +drop database if exists mysqltest_db1; +create database mysqltest_db1; +create procedure mysqltest_db1.sp_bug28551() begin end; +call mysqltest_db1.sp_bug28551(); +show warnings; +Level Code Message +Note 1008 Can't drop database 'mysqltest_db1'; database doesn't exist +drop database mysqltest_db1; +drop database if exists mysqltest_db1; +drop table if exists test.t1; +create database mysqltest_db1; +use mysqltest_db1; +drop database mysqltest_db1; +create table test.t1 (id int); +insert into test.t1 (id) values (1); +create procedure test.sp_bug29050() begin select * from t1; end// +show warnings; +Level Code Message +call test.sp_bug29050(); +id +1 +show warnings; +Level Code Message +use test; +drop procedure sp_bug29050; +drop table t1; +SET NAMES latin1; +CREATE PROCEDURE p1() +BEGIN +DECLARE áâä INT; +SELECT áâä; +END| +CALL p1(); +áâä +NULL +set @@character_set_client=@save_character_set_client; +set @@character_set_results=@save_character_set_client; +DROP PROCEDURE p1; +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 +$$ +show create procedure proc_25411_a; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +proc_25411_a CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_a`() +begin +/* real comment */ +select 1; + select 2; +select 3; + select 4; + +end latin1 latin1_swedish_ci latin1_swedish_ci +call proc_25411_a(); +1 +1 +2 +2 +3 +3 +4 +4 +show create procedure proc_25411_b; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +proc_25411_b CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_b`( +/* real comment */ + p1 int, + p2 int + +) +begin +select p1, p2; +end latin1 latin1_swedish_ci latin1_swedish_ci +select name, param_list, body from mysql.proc where name like "%25411%" order by name; +name param_list body +proc_25411_a begin +/* real comment */ +select 1; + select 2; +select 3; + select 4; + +end +proc_25411_b +/* real comment */ + p1 int, + p2 int + + begin +select p1, p2; +end +proc_25411_c begin +select 1,2,3; +select 1 ,2 ,3; +select 1,2 ,3 ; +select 1 ,2 ,3 ; +select 1 ,2 ,3 ; +end +call proc_25411_b(10, 20); +p1 p2 +10 20 +show create procedure proc_25411_c; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +proc_25411_c CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_c`() +begin +select 1,2,3; +select 1 ,2 ,3; +select 1,2 ,3 ; +select 1 ,2 ,3 ; +select 1 ,2 ,3 ; +end latin1 latin1_swedish_ci latin1_swedish_ci +call proc_25411_c(); +1 2 3 +1 2 3 +1 2 3 +1 2 3 +1 2 3 +1 2 3 +1 2 3 +1 2 3 +1 2 3 +1 2 3 +drop procedure proc_25411_a; +drop procedure proc_25411_b; +drop procedure proc_25411_c; +drop procedure if exists proc_26302; +create procedure proc_26302() +select 1 /* testing */; +show create procedure proc_26302; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +proc_26302 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_26302`() +select 1 /* testing */ latin1 latin1_swedish_ci latin1_swedish_ci +select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES +where ROUTINE_NAME = "proc_26302"; +ROUTINE_NAME ROUTINE_DEFINITION +proc_26302 select 1 /* testing */ +drop procedure proc_26302; +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c1 c1 5 const 1 Using index +EXPLAIN SELECT * FROM t1 WHERE c1=f1(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index +EXPLAIN SELECT * FROM v1 WHERE c1=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c1 c1 5 const 1 Using index +EXPLAIN SELECT * FROM v1 WHERE c1=f1(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE c1=f2(10); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand()); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index +DROP VIEW v1; +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; +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| +create view v1 as select 1 as one, f1() as days; +connect bug29408, localhost, root,,*NO-ONE*; +connection bug29408; +show create view test.v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select 1 AS `one`,`f1`() AS `days` latin1 latin1_swedish_ci +select column_name from information_schema.columns +where table_name='v1' and table_schema='test'; +column_name +one +days +connection default; +disconnect bug29408; +drop view v1; +drop function f1; + +# Bug#13675. + +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP TABLE IF EXISTS t1; + +CREATE PROCEDURE p1(v DATETIME) CREATE TABLE t1 SELECT v; +CREATE PROCEDURE p2(v INT) CREATE TABLE t1 SELECT v; + +CALL p1(NOW()); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + +DROP TABLE t1; + +CALL p1('text'); +Warnings: +Warning 1265 Data truncated for column 'v' at row 0 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + +DROP TABLE t1; + +CALL p2(10); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + +DROP TABLE t1; + +CALL p2('text'); +Warnings: +Warning 1366 Incorrect integer value: 'text' for column ``.``.`v` at row 0 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci + +DROP TABLE t1; + +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +# +# Bug#31035. +# + +# +# - Prepare. +# + +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; + +# +# - Create required objects. +# + +CREATE TABLE t1(c1 INT); + +INSERT INTO t1 VALUES (1), (2), (3); + +CREATE FUNCTION f1() +RETURNS INT +NOT DETERMINISTIC +RETURN 1; + +CREATE FUNCTION f2(p INT) +RETURNS INT +NOT DETERMINISTIC +RETURN 1; + +CREATE FUNCTION f3() +RETURNS INT +DETERMINISTIC +RETURN 1; + +CREATE FUNCTION f4(p INT) +RETURNS INT +DETERMINISTIC +RETURN 1; + +# +# - Check. +# + +SELECT f1() AS a FROM t1 GROUP BY a; +a +1 + +SELECT f2(@a) AS a FROM t1 GROUP BY a; +a +1 + +SELECT f3() AS a FROM t1 GROUP BY a; +a +1 + +SELECT f4(0) AS a FROM t1 GROUP BY a; +a +1 + +SELECT f4(@a) AS a FROM t1 GROUP BY a; +a +1 + +# +# - Cleanup. +# + +DROP TABLE t1; +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP FUNCTION f3; +DROP FUNCTION f4; + +# +# Bug#31191. +# + +# +# - Prepare. +# + +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP FUNCTION IF EXISTS f1; + +# +# - Create required objects. +# + +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) +); + +INSERT INTO t1 (id, barcode) VALUES (1, 12345678); +INSERT INTO t1 (id, barcode) VALUES (2, 12345679); + +CREATE TABLE test.t2 ( +id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, +barcode BIGINT(11) UNSIGNED ZEROFILL NOT NULL, +PRIMARY KEY (id) +); + +INSERT INTO test.t2 (id, barcode) VALUES (1, 12345106708); +INSERT INTO test.t2 (id, barcode) VALUES (2, 12345106709); + +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); + +# +# - Check. +# + +SELECT DISTINCT t1.barcode, f1(t1.barcode) +FROM t1 +INNER JOIN t2 +ON f1(t1.barcode) = t2.barcode +WHERE t1.barcode=12345678; +barcode f1(t1.barcode) +12345678 12345106708 + +# +# - Cleanup. +# + +DROP TABLE t1; +DROP TABLE t2; +DROP FUNCTION f1; + +# +# Bug#31226. +# + +# +# - Prepare. +# + +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; + +# +# - Create required objects. +# + +CREATE TABLE t1(id INT); + +INSERT INTO t1 VALUES (1), (2), (3); + +CREATE FUNCTION f1() +RETURNS DATETIME +NOT DETERMINISTIC NO SQL +RETURN NOW(); + +# +# - Check. +# + +SELECT f1() FROM t1 GROUP BY 1; +f1() +<timestamp> + +# +# - Cleanup. +# + +DROP TABLE t1; +DROP FUNCTION f1; + +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; +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; +CALL db28318_b.t2(); +ERROR 42000: PROCEDURE db28318_b.t1 does not exist +DROP PROCEDURE db28318_a.t1; +DROP PROCEDURE db28318_b.t2; +DROP DATABASE db28318_a; +DROP DATABASE db28318_b; +use test; +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS bug29770; +CREATE TABLE t1(a int); +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| +CALL bug29770(); +SELECT @state, @exception; +@state @exception +run NULL +DROP TABLE t1; +DROP PROCEDURE bug29770; +use test; +drop table if exists t_33618; +drop procedure if exists proc_33618; +create table t_33618 (`a` int, unique(`a`), `b` varchar(30)) engine=myisam; +insert into t_33618 (`a`,`b`) values (1,'1'),(2,'2'); +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// +call proc_33618(20); +drop table t_33618; +drop procedure proc_33618; +# +# Bug#30787: Stored function ignores user defined alias. +# +use test; +drop function if exists func30787; +create table t1(f1 int); +insert into t1 values(1),(2); +create function func30787(p1 int) returns int +begin +return p1; +end | +select (select func30787(f1)) as ttt from t1; +ttt +1 +2 +drop function func30787; +drop table t1; +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(); +id +1 +2 +3 +4 +CALL test_sp(); +id +1 +2 +3 +4 +DROP PROCEDURE test_sp; +DROP TABLE t1; +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; +create procedure p1() +begin +select parent_control_name as c1 from v1; +end // +call p1(); +c1 +call p1(); +c1 +drop procedure p1; +drop function f1; +drop view v1; +drop table t1; +drop procedure if exists `p2` $ +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 $ +call `p2`('s s s s s s'); +drop procedure `p2`; +drop table if exists t1; +drop procedure if exists p1; +create procedure p1() begin select * from t1; end$ +call p1$ +ERROR 42S02: Table 'test.t1' doesn't exist +create table t1 (a integer)$ +call p1$ +a +alter table t1 add b integer$ +call p1$ +a +drop table t1; +drop procedure p1; +# ------------------------------------------------------------------ +# -- End of 5.0 tests +# ------------------------------------------------------------------ + +# +# Bug#20550. +# + +# +# - Prepare. +# + +DROP VIEW IF EXISTS v1; +DROP VIEW IF EXISTS v2; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; + +# +# - Create required objects. +# + +CREATE FUNCTION f1() RETURNS VARCHAR(65525) RETURN 'Hello'; + +CREATE FUNCTION f2() RETURNS TINYINT RETURN 1; + +CREATE VIEW v1 AS SELECT f1(); + +CREATE VIEW v2 AS SELECT f2(); + +# +# - Check. +# + +SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v1'; +DATA_TYPE +varchar + +SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v2'; +DATA_TYPE +tinyint + +# +# - Cleanup. +# + +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP VIEW v1; +DROP VIEW v2; + +# +# - Bug#24923: prepare. +# + +DROP FUNCTION IF EXISTS f1; + +# +# - Bug#24923: create required objects. +# + +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| + +# +# - Bug#24923: check. +# + +SELECT f1(1); +f1(1) +Very_long_enum_element_identifier + +SELECT f1(2); +f1(2) +Another_very_long_enum_element_identifier + +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(p INT) RETURNS enum('Very_long_enum_element_identifier','Another_very_long_enum_element_identifier') CHARSET latin1 COLLATE latin1_swedish_ci +BEGIN +CASE p +WHEN 1 THEN +RETURN 'Very_long_enum_element_identifier'; +ELSE +RETURN 'Another_very_long_enum_element_identifier'; +END CASE; +END latin1 latin1_swedish_ci latin1_swedish_ci +# +# - Bug#24923: cleanup. +# + +DROP FUNCTION f1; + +drop procedure if exists p; +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; +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; +name +p +drop procedure p; +CREATE DEFINER = 'root'@'localhost' PROCEDURE p1() +NOT DETERMINISTIC +CONTAINS SQL +SQL SECURITY DEFINER +COMMENT '' +BEGIN +SHOW TABLE STATUS like 't1'; +END;// +CREATE TABLE t1 (f1 INT); +CALL p1(); +CALL p1(); +CALL p1(); +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; +CREATE TABLE t1 ( f1 integer, primary key (f1)); +CREATE TABLE t2 LIKE t1; +CREATE TEMPORARY TABLE t3 LIKE t1; +CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ; +END| +CALL p1; +f1 +CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 ); +DROP TABLE t3; +CALL p1; +f1 +CALL p1; +f1 +DROP PROCEDURE p1; +DROP TABLE t1, t2; +DROP VIEW t3; +# +# Bug #46629: Item_in_subselect::val_int(): Assertion `0' +# on subquery inside a SP +# +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT, b INT PRIMARY KEY); +CREATE PROCEDURE p1 () +BEGIN +SELECT a FROM t1 A WHERE A.b IN (SELECT b FROM t2 AS B); +END| +CALL p1; +ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' +CALL p1; +ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' +DROP PROCEDURE p1; +DROP TABLE t1, t2; +# +# Bug#47627: SET @@{global.session}.local_variable in stored routine causes crash +# Bug#48626: Crash or lost connection using SET for declared variables with @@ +# +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +CREATE PROCEDURE p1() +BEGIN +DECLARE v INT DEFAULT 0; +SET @@SESSION.v= 10; +END// +ERROR HY000: Unknown system variable 'v' +CREATE PROCEDURE p2() +BEGIN +DECLARE v INT DEFAULT 0; +SET v= 10; +END// +call p2()// +CREATE PROCEDURE p3() +BEGIN +DECLARE v INT DEFAULT 0; +SELECT @@SESSION.v; +END// +ERROR HY000: Unknown system variable 'v' +CREATE PROCEDURE p4() +BEGIN +DECLARE v INT DEFAULT 0; +SET @@GLOBAL.v= 10; +END// +ERROR HY000: Unknown system variable 'v' +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// +CREATE PROCEDURE p6() +BEGIN +DECLARE v INT DEFAULT 0; +SET @@v= 0; +END// +ERROR HY000: Unknown system variable 'v' +SET @old_init_connect= @@GLOBAL.init_connect; +CALL p5(); +@@SESSION.IDENTITY +1 +@@GLOBAL.init_connect +SELECT 1 +init_connect +10 +SET @@GLOBAL.init_connect= @old_init_connect; +DROP PROCEDURE p2; +DROP PROCEDURE p5; +# +# Bug#11840395 (formerly known as bug#60347): +# The string "versiondata" seems +# to be 'leaking' into the schema name space +# +DROP DATABASE IF EXISTS mixedCaseDbName; +CREATE DATABASE mixedCaseDbName; +CREATE PROCEDURE mixedCaseDbName.tryMyProc() begin end| +CREATE FUNCTION mixedCaseDbName.tryMyFunc() returns text begin return 'IT WORKS'; end +| +call mixedCaseDbName.tryMyProc(); +select mixedCaseDbName.tryMyFunc(); +mixedCaseDbName.tryMyFunc() +IT WORKS +DROP DATABASE mixedCaseDbName; +# +# Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C +# +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); +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN +DECLARE a int; +-- SQL statement inside +SELECT 1 INTO a; +RETURN a; +END $ +SELECT COUNT(DISTINCT d) FROM t1, t2 WHERE a = c AND b = f1(); +COUNT(DISTINCT d) +2 +DROP FUNCTION f1; +DROP TABLE t1, t2; +# ------------------------------------------------------------------ +# -- End of 5.1 tests +# ------------------------------------------------------------------ +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t_non_existing; +DROP TABLE IF EXISTS t1; +CREATE FUNCTION f1() RETURNS INT +BEGIN +DECLARE v INT; +SELECT a INTO v FROM t_non_existing; +RETURN 1; +END| +CREATE TABLE t1 (a INT) ENGINE = myisam; +INSERT INTO t1 VALUES (1); +SELECT * FROM t1 WHERE a = f1(); +ERROR 42S02: Table 'test.t_non_existing' doesn't exist +DROP FUNCTION f1; +DROP TABLE t1; +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1(a INT, b CHAR) +BEGIN +IF a > 0 THEN +CALL p1(a-1, 'ab'); +ELSE +SELECT 1; +END IF; +END| +SET @save_max_sp_recursion= @@max_sp_recursion_depth; +SET @@max_sp_recursion_depth= 5; +CALL p1(4, 'a'); +1 +1 +Warnings: +Warning 1265 Data truncated for column 'b' at row 0 +Warning 1265 Data truncated for column 'b' at row 0 +Warning 1265 Data truncated for column 'b' at row 0 +Warning 1265 Data truncated for column 'b' at row 0 +SET @@max_sp_recursion_depth= @save_max_sp_recursion; +DROP PROCEDURE p1; +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1(a CHAR) +BEGIN +SELECT 1; +SELECT CAST('10 ' as UNSIGNED INTEGER); +SELECT 1; +END| +CALL p1('data truncated parameter'); +1 +1 +CAST('10 ' as UNSIGNED INTEGER) +10 +1 +1 +Warnings: +Warning 1265 Data truncated for column 'a' at row 0 +Note 1292 Truncated incorrect INTEGER value: '10 ' +DROP PROCEDURE p1; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +DROP PROCEDURE IF EXISTS p4; +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| +CALL p1(); +1 +1 +CAST('10 ' as UNSIGNED INTEGER) +10 +2 +2 +Warnings: +Note 1292 Truncated incorrect INTEGER value: '10 ' +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +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; +CREATE TABLE t1 (a CHAR(2)); +INSERT INTO t1 VALUES ('aa'); +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| +SELECT f1(); +f1() +a +Warnings: +Warning 1265 Data truncated for column 'f4()' at row 0 +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP FUNCTION f3; +DROP FUNCTION f4; +DROP TABLE t1; +# +# Bug#34197: CREATE PROCEDURE fails when COMMENT truncated in non +# strict SQL mode +# +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1 () +COMMENT +'12345678901234567890123456789012345678901234567890123456789012345678901234567890' +BEGIN +END; +SELECT comment FROM mysql.proc WHERE name = "p1"; +comment +12345678901234567890123456789012345678901234567890123456789012345678901234567890 +SELECT routine_comment FROM information_schema.routines WHERE routine_name = "p1"; +routine_comment +12345678901234567890123456789012345678901234567890123456789012345678901234567890 +DROP PROCEDURE p1; +# +# Bug #47313 assert in check_key_in_view during CALL procedure +# +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS t1, t2_unrelated; +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x); +CREATE VIEW t1 AS SELECT 10 AS f1; +# t1 refers to the view +CALL p1(1); +ERROR HY000: The target table t1 of the INSERT is not insertable-into +CREATE TEMPORARY TABLE t1 (f1 INT); +# t1 still refers to the view since it was inlined +CALL p1(2); +ERROR HY000: The target table t1 of the INSERT is not insertable-into +DROP VIEW t1; +# t1 now refers to the temporary table +CALL p1(3); +# Check which values were inserted into the temp table. +SELECT * FROM t1; +f1 +3 +DROP TEMPORARY TABLE t1; +DROP PROCEDURE p1; +# 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; +# Load the procedure into the sp cache +CALL p1(4); +ERROR HY000: The target table t1 of the INSERT is not insertable-into +CREATE TEMPORARY TABLE t1 (f1 int); +ALTER VIEW v2_unrelated AS SELECT 2 AS r1; +# Alter view causes the sp cache to be invalidated. +# Now t1 refers to the temporary table, not the view. +CALL p1(5); +# Check which values were inserted into the temp table. +SELECT * FROM t1; +f1 +5 +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); +# t1 refers to the temporary table +CALL p1(6); +CREATE VIEW t1 AS SELECT 10 AS f1; +# Create view causes the sp cache to be invalidated. +# t1 still refers to the temporary table since it shadows the view. +CALL p1(7); +DROP VIEW t1; +# Check which values were inserted into the temp table. +SELECT * FROM t1; +f1 +6 +7 +DROP TEMPORARY TABLE t1; +DROP PROCEDURE p1; +# +# Bug #11918 Can't use a declared variable in LIMIT clause +# +drop table if exists t1; +drop procedure if exists p1; +create table t1 (c1 int); +insert into t1 (c1) values (1), (2), (3), (4), (5); +create procedure p1() +begin +declare a integer; +declare b integer; +select * from t1 limit a, b; +end| +# How do we handle NULL limit values? +call p1(); +c1 +drop table t1; +create table t1 (a int); +insert into t1 (a) values (1), (2), (3), (4), (5); +# +# Do we correctly resolve identifiers in LIMIT? +# Since DROP and CREATE did not invalidate +# the SP cache, we can't test until +# we drop and re-create the procedure. +# +call p1(); +ERROR 42S22: Unknown column 'test.t1.c1' in 'field list' +# +# Drop and recreate the procedure, then repeat +# +drop procedure p1; +create procedure p1() +begin +declare a integer; +declare b integer; +select * from t1 limit a, b; +end| +# Stored procedure variables are resolved correctly in the LIMIT +call p1(); +a +drop table t1; +create table t1 (c1 int); +insert into t1 (c1) values (1), (2), (3), (4), (5); +drop procedure p1; +# Try to create a procedure that +# refers to non-existing variables. +create procedure p1(p1 integer, p2 integer) +select * from t1 limit a, b; +ERROR 42000: Undeclared variable: a +# +# Try to use data types not allowed in LIMIT +# +create procedure p1(p1 date, p2 date) select * from t1 limit p1, p2; +ERROR HY000: A variable of a non-integer based type in LIMIT clause +create procedure p1(p1 integer, p2 float) select * from t1 limit p1, p2; +ERROR HY000: A variable of a non-integer based type in LIMIT clause +create procedure p1(p1 integer, p2 char(1)) select * from t1 limit p1, p2; +ERROR HY000: A variable of a non-integer based type in LIMIT clause +create procedure p1(p1 varchar(5), p2 char(1)) select * from t1 limit p1, p2; +ERROR HY000: A variable of a non-integer based type in LIMIT clause +create procedure p1(p1 decimal, p2 decimal) select * from t1 limit p1, p2; +ERROR HY000: A variable of a non-integer based type in LIMIT clause +create procedure p1(p1 double, p2 double) select * from t1 limit p1, p2; +ERROR HY000: A variable of a non-integer based type in LIMIT clause +# +# Finally, test the valid case. +# +create procedure p1(p1 integer, p2 integer) +select * from t1 limit p1, p2; +call p1(NULL, NULL); +c1 +call p1(0, 0); +c1 +call p1(0, -1); +c1 +1 +2 +3 +4 +5 +call p1(-1, 0); +c1 +call p1(-1, -1); +c1 +call p1(0, 1); +c1 +1 +call p1(1, 0); +c1 +call p1(1, 5); +c1 +2 +3 +4 +5 +call p1(3, 2); +c1 +4 +5 +# Try to create a function that +# refers to non-existing variables. +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| +ERROR 42000: Undeclared variable: b +create function f1() +returns int +begin +declare a, b, c int; +set a = (select count(*) from t1 limit b, c); +return a; +end| +# How do we handle NULL limit values? +select f1(); +f1() +NULL +drop function f1; +# +# Try to use data types not allowed 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 HY000: A variable of a non-integer based type in LIMIT clause +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 HY000: A variable of a non-integer based type in LIMIT clause +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 HY000: A variable of a non-integer based type in LIMIT clause +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 HY000: A variable of a non-integer based type in LIMIT clause +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 HY000: A variable of a non-integer based type in LIMIT clause +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| +ERROR HY000: A variable of a non-integer based type in LIMIT clause +# +# Finally, test the valid case. +# +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| +select f1(0, 0); +f1(0, 0) +0 +select f1(0, -1); +f1(0, -1) +5 +select f1(-1, 0); +f1(-1, 0) +0 +select f1(-1, -1); +f1(-1, -1) +0 +select f1(0, 1); +f1(0, 1) +1 +select f1(1, 0); +f1(1, 0) +0 +select f1(1, 5); +f1(1, 5) +4 +select f1(3, 2); +f1(3, 2) +2 +# Cleanup +drop table t1; +drop procedure p1; +drop function f1; +# +# BUG#11766234: 59299: ASSERT (TABLE_REF->TABLE || TABLE_REF->VIEW) +# FAILS IN SET_FIELD_ITERATOR +# +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; + +CALL proc(); +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +CALL proc(); +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them + +DROP TABLE t1,t2; +DROP VIEW v1; +DROP PROCEDURE proc; + +# -- +# -- Bug 11765684 - 58674: SP-cache does not detect changes in +# -- pre-locking list caused by triggers +# --- +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t3; +DROP PROCEDURE IF EXISTS p1; +CREATE TABLE t1(a INT); +CREATE TABLE t2(a INT); +CREATE TABLE t3(a INT); +CREATE PROCEDURE p1() +INSERT INTO t1(a) VALUES (1); + +CREATE TRIGGER t1_ai AFTER INSERT ON t1 +FOR EACH ROW +INSERT INTO t2(a) VALUES (new.a); + +CALL p1(); + +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 +FOR EACH ROW +INSERT INTO t3(a) VALUES (new.a); + +CALL p1(); + +DROP TABLE t1, t2, t3; +DROP PROCEDURE p1; + + +# -- +# -- Bug#12652769 - 61470: case operator in stored routine retains old +# -- value of input parameter +# --- +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET utf8); +INSERT INTO t1 VALUES ('a'); +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| + +CALL p1('2011-04-03 05:14:10', 1); +x1 +2 +x2 +A +x3 +A +x4 +b +CALL p1('2011-04-03 05:14:11', 2); +x1 +2011-04-03 05:14:11 +x2 +A +x3 +A +x4 +b +CALL p1('2011-04-03 05:14:12', 2); +x1 +2011-04-03 05:14:12 +x2 +A +x3 +A +x4 +b +CALL p1('2011-04-03 05:14:13', 2); +x1 +2011-04-03 05:14:13 +x2 +A +x3 +A +x4 +b + +DROP TABLE t1; +DROP PROCEDURE p1; + +# +# Bug#12621017 - Crash if a sp variable is used in the +# limit clause of a set statement +# +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +CREATE TABLE t1 (c1 INT); +INSERT INTO t1 VALUES (1); +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| +CALL p1(); +CALL p2(); +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP TABLE t1; + +# Bug#13805127: Stored program cache produces wrong result in same THD + +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| + +CALL p1(1); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 +CALL p1(2); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +CALL p1(1); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 +DROP PROCEDURE p1; +# End of 5.5 test +FLUSH USER_STATISTICS; +CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; +CALL sp; +Table Op Msg_type Msg_text +test.non_existing_table optimize Error Table 'test.non_existing_table' doesn't exist +test.non_existing_table optimize status Operation failed +SELECT 1; +1 +1 +DROP PROCEDURE sp; +CREATE PROCEDURE sp() SET STATEMENT SQL_SELECT_LIMIT=0 FOR SHOW USER_STATISTICS; +CALL sp; +User Total_connections Concurrent_connections Connected_time Busy_time Cpu_time Bytes_received Bytes_sent Binlog_bytes_written Rows_read Rows_sent Rows_deleted Rows_inserted Rows_updated Select_commands Update_commands Other_commands Commit_transactions Rollback_transactions Denied_connections Lost_connections Access_denied Empty_queries Total_ssl_connections Max_statement_time_exceeded +SELECT 1; +1 +1 +DROP PROCEDURE sp; +# +# Bug#12663165 SP DEAD CODE REMOVAL DOESN'T UNDERSTAND CONTINUE HANDLERS +# +DROP FUNCTION IF EXISTS f1; +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 $ +SELECT f1(); +f1() +1 +DROP FUNCTION f1; +# ------------------------------------------------------------------ +# -- End of 5.1 tests +# ------------------------------------------------------------------ +# +# lp:993459 Execution of PS for a query with GROUP BY +# returns wrong result (see also mysql bug#13805127) +# + +# Bug#13805127: Stored program cache produces wrong result in same THD + +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| + +CALL p1(1); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 +CALL p1(2); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +CALL p1(1); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 +DROP PROCEDURE p1; + +MDEV-3900 Optimizer difference between MySQL and MariaDB with stored functions in WHERE clause of UPDATE or DELETE statements + +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%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_last 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +UPDATE t1 SET a = '+' WHERE daynum=tdn(); +SHOW STATUS LIKE '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 9 +Handler_read_last 0 +Handler_read_next 4097 +Handler_read_prev 0 +Handler_read_retry 0 +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +drop function tdn; +drop table t1; +# +# lp:1002157 : testing stored function +# bug#62125 result for null incorrectly yields 1292 warning. +# +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP FUNCTION IF EXISTS f3; +DROP FUNCTION IF EXISTS f4; +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;/ +SELECT f1() IS NULL; +f1() IS NULL +0 +SELECT f2() IS NULL; +f2() IS NULL +0 +SELECT f3() IS NULL; +f3() IS NULL +1 +SELECT f4() IS NULL; +f4() IS NULL +1 +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP FUNCTION f3; +DROP FUNCTION f4; + +Stored procedures and a condition handler in a nested procedure call +doesn't suppress the condition from being passed on to the calling +procedure + +drop procedure if exists p1; +drop procedure if exists p0; +create table t1 (id int); +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$$ +call p0(); +message +You should see this message and the warning that generated this +Level Code Message +Warning 1329 No data - zero rows fetched, selected, or processed +drop procedure p1; +drop procedure p0; +drop table t1; + +Test if stored procedures propagates errors + +create table t1 (id int primary key); +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$$ +call p1(); +ERROR 23000: Duplicate entry '2' for key 'PRIMARY' +show warnings; +Level Code Message +Error 1062 Duplicate entry '2' for key 'PRIMARY' +Note 4094 At line 5 in test.p1 +select * from t1; +id +1 +2 +call p2(); +Warnings: +Warning 1329 No data - zero rows fetched, selected, or processed +drop procedure p1; +drop procedure p2; +drop table t1; +# +# MDEV-4978 - Server cursor is broken with blobs in the select list, +# ORDER BY does not work +# +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'); +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| +CALL p1; +v1 +1 +v1 +2 +v1 +3 +v1 +4 +v1 +5 +v1 +6 +DROP PROCEDURE p1; +DROP TABLE t1; +# +# MDEV-10713: signal 11 error on multi-table update - crash in +# handler::increment_statistics or in make_select or assertion +# failure pfs_thread == ((PFS_thread*) pthread_getspecific((THR_PFS))) +# +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); +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 $$ +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; +# +# MDEV-16957: Server crashes in Field_iterator_natural_join::next +# upon 2nd execution of SP +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +CREATE PROCEDURE sp() SELECT * FROM t1 AS t1x JOIN t1 AS t1y USING (c); +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +alter table t1 add column c int; +CALL sp; +c a b a b +DROP PROCEDURE sp; +DROP TABLE t1; +# +# MDEV-17055: Server crashes in find_order_in_list upon +# 2nd (3rd) execution of SP with UPDATE +# +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE TABLE t2 (c INT); +CREATE PROCEDURE sp() UPDATE v1 SET a = 1 ORDER BY a, b LIMIT 1; +LOCK TABLE t2 READ; +CALL sp; +ERROR HY000: Table 'v1' was not locked with LOCK TABLES +UNLOCK TABLES; +CALL sp; +ERROR 42S22: Unknown column 'b' in 'order clause' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'order clause' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'order clause' +DROP PROCEDURE sp; +CREATE PROCEDURE sp() UPDATE v1 SET a = 1 WHERE a=1 and b=2; +LOCK TABLE t2 READ; +CALL sp; +ERROR HY000: Table 'v1' was not locked with LOCK TABLES +UNLOCK TABLES; +CALL sp; +ERROR 42S22: Unknown column 'b' in 'where clause' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'where clause' +CALL sp; +ERROR 42S22: Unknown column 'b' in 'where clause' +DROP PROCEDURE sp; +DROP VIEW v1; +DROP TABLE t1, t2; +# End of 5.5 test +# +# MDEV-7040: Crash in field_conv, memcpy_field_possible, part#2 +# +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; +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| +set @tmp_toc= @@table_open_cache; +set @tmp_tdc= @@table_definition_cache; +set global table_open_cache=10; +set global table_definition_cache=1; +Warnings: +Warning 1292 Truncated incorrect table_definition_cache value: '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; +# +# MDEV-11935: Queries in stored procedures with and +# EXISTS(SELECT * FROM VIEW) crashes and closes hte conneciton. +# +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 +; +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 +// +CALL SP_EXAMPLE_SELECT (); +ENTITY_UID +CALL SP_EXAMPLE_SELECT (); +ENTITY_UID +drop procedure SP_EXAMPLE_SELECT; +drop view ENTITY_ACCESS; +drop table ANY_TABLE, SECURITY_PATH; +# End of 10.0 test +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| +# +# MDEV-7023: Error 2027: Malformed packet and assertion +# `field_types == 0 || field_types[field_pos] == MYSQL_TYPE_INT24 || +#field_types[field_pos] == MYSQL_TYPE_LONG' failure in +#Protocol_text::store_long +# +create table t1 (i int); +create table t2 (i int); +create function f() returns int +begin +analyze insert into t1 values (1); +return 1; +end | +ERROR 0A000: Not allowed to return a result set from a function +create function f() returns int +begin +analyze insert t1 select * from t2; +return 1; +end | +ERROR 0A000: Not allowed to return a result set from a function +create function f() returns int +begin +analyze delete from t1; +return 1; +end | +ERROR 0A000: Not allowed to return a result set from a function +create function f() returns int +begin +analyze delete t1 from t1,t2; +return 1; +end | +ERROR 0A000: Not allowed to return a result set from a function +create function f() returns int +begin +analyze update t1 set i=1; +return 1; +end | +ERROR 0A000: Not allowed to return a result set from a function +create function f() returns int +begin +analyze update t1,t2 set i=1; +return 1; +end | +ERROR 0A000: Not allowed to return a result set from a function +create function f() returns int +begin +analyze replace t1 set i=1; +return 1; +end | +ERROR 0A000: Not allowed to return a result set from a function +create function f() returns int +begin +analyze replace t1 select * from t2; +return 1; +end | +ERROR 0A000: Not allowed to return a result set from a function +drop table t1,t2; +# +# MDEV-11584: GRANT inside an SP does not work well on 2nd execution +# +CREATE PROCEDURE sp1() +GRANT ALL PRIVILEGES ON *.* TO 'foo'@'%' IDENTIFIED BY 'pass'; +CALL sp1(); +CALL sp1(); +drop user 'foo'@'%'; +drop procedure sp1; +# +# MDEV-10972: Insert from select / view / union -- +# repeatable crash in 10.1, 10.2 Linux/Mac/Windows +# +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; +Warnings: +Note 1305 PROCEDURE test.p does not exist +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); +CALL p(); +ERROR 42S02: Table 'test.tmp_t' doesn't exist +create table tmp_t (id int null); +CALL p(); +drop procedure p; +drop view v; +drop table t, tmp_t; +# +# MDEV-13936: Server crashes in Time_and_counter_tracker::incr_loops +# +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 ); +REPLACE INTO v1 VALUES (f1()); +ERROR HY000: The target table v1 of the INSERT is not insertable-into +SET @aux = f1(); +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1; +# +# MDEV-14857: problem with 10.2.11 server crashing when +# executing stored procedure +# +SET max_sp_recursion_depth=10; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +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 || +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(); +a +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; +# +# MDEV-15347: Valgrind or ASAN errors in mysql_make_view on query +# from information_schema +# +CREATE VIEW v AS SELECT 1; +CREATE FUNCTION f() RETURNS INT RETURN 1; +SELECT * FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.PARAMETERS +UNION +SELECT * FROM INFORMATION_SCHEMA.TABLES JOIN INFORMATION_SCHEMA.PARAMETERS; +DROP FUNCTION f; +DROP VIEW v; +# +# MDEV-17963: Assertion `field_pos < field_count' failed in Protocol_text::store, +# Assertion `field_handlers == 0 || field_pos < field_count' +# +CREATE TABLE t1 (ct time); +INSERT INTO t1 VALUES ('16:11:28'); +CREATE FUNCTION f1 () RETURNS varchar(100) +BEGIN +DECLARE xxx varchar(100); +ANALYZE SELECT sum(ct) FROM t1 INTO xxx ; +RETURN xxx; +END| +ERROR 0A000: Not allowed to return a result set from a function +drop table t1; +#End of 10.1 tests +# +# MDEV-11081: CURSOR for query with GROUP BY +# +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); +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; +| +CALL p1(); +v_name v_total +a 3 +v_name v_total +b 2 +v_name v_total +c 1 +DROP PROCEDURE p1; +DROP TABLE t1; +# +# MDEV-13346: CURSOR a query with GROUP BY using derived table +# +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 +| +CALL p1(); +DROP PROCEDURE p1; +# +# MDEV-15057 Crash when using an unknown identifier as an SP parameter +# +CREATE OR REPLACE PROCEDURE p1 (a VARCHAR(10)) SELECT 1; +CALL p1(a); +ERROR 42S22: Unknown column 'a' in 'field list' +drop procedure p1; +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| +ERROR 42000: Undeclared variable: name2 +call p2(); +a +hello +drop procedure p1; +drop procedure p2; +# +# MDEV-15328: MariaDB 10.2.13 Crashes upon CALL PROCEDURE PARAM +# LAST_INSERT_ID () +# (part 1, part 2 is in query_cache.test) +# +CREATE PROCEDURE foo ( IN i INT UNSIGNED ) BEGIN END; +CALL foo( LAST_INSERT_ID() ); +DROP PROCEDURE foo; +# +# MDEV-15870 Using aggregate and window function in unexpected places can crash the server +# +CREATE PROCEDURE p1 (a TEXT) BEGIN END; +CALL p1(RANK() OVER (ORDER BY 1)); +ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause +CALL p1(ROW_NUMBER() OVER ()); +ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause +CALL p1(SUM(1)); +ERROR HY000: Invalid use of group function +DROP PROCEDURE p1; +# +# MDEV-16311 Server crash when using a NAME_CONST() with a CURSOR +# +SET sql_mode=STRICT_ALL_TABLES; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +BEGIN NOT ATOMIC +DECLARE a INT; +DECLARE c CURSOR FOR SELECT NAME_CONST('x','y') FROM t1; +OPEN c; +FETCH c INTO a; +CLOSE c; +END; +$$ +ERROR 22007: Incorrect integer value: 'y' for column ``.``.`a` at row 1 +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-24220: error when opening a table for the second call of SP +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE VIEW v1 AS SELECT MAX(a) as f FROM t1; +CREATE PROCEDURE p1() +BEGIN +SELECT * FROM v1; +END $ +CALL p1; +f +2 +ALTER TABLE t1 DROP a; +CALL p1; +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; +# +# BUG#30366310: USING A FUNCTION TO ASSIGN DEFAULT VALUES TO +# 2 OR MORE VARIABLES CRASHES SERVER +# +create function f1() returns bigint return now()-1| +create procedure p1() +begin +declare b, c bigint default f1(); +select b-c; +end| +call p1()| +b-c +0 +drop procedure p1| +drop function f1| +# +# MDEV-24827: MariaDB 10.5.5 crash (sig 11) during a SELECT +# +CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT); +CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, KEY idx_c2(c2)); +INSERT INTO t1 (c1, c2) SELECT seq, seq FROM seq_1_to_10000; +INSERT INTO t2 (c1, c2) SELECT seq, seq FROM seq_1_to_20000; +CREATE OR REPLACE PROCEDURE p1() +begin +DECLARE done INT DEFAULT FALSE; +DECLARE a INT; +DECLARE cur1 CURSOR FOR +SELECT t2.c1 AS c1 FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 +WHERE EXISTS (SELECT 1 FROM t1 WHERE c2 = -1) ORDER BY c1; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; +OPEN cur1; +read_loop: LOOP +FETCH cur1 INTO a; +IF done THEN +LEAVE read_loop; +END IF; +END LOOP; +CLOSE cur1; +END $ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; +DROP TABLE t2; +#End of 10.2 tests +# +# MDEV-12007 Allow ROW variables as a cursor FETCH target +# +# The cursor and the ROW variable in FETCH must have the same number of fields +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; +$$ +CALL p1(); +ERROR HY000: Incorrect number of FETCH variables +DROP PROCEDURE p1; +# Multiple ROW variables in FETCH +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; +$$ +CALL p1(); +ERROR 21000: Operand should contain 1 column(s) +DROP PROCEDURE p1; +# 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'); +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; +$$ +CALL p1(); +c +rec=(10,b10) +c +rec=(20,b20) +c +rec=(30,b30) +DROP PROCEDURE p1; +DROP TABLE t1; +# A ROW variable with a single field +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; +$$ +CALL p1(); +c +rec=(10) +c +rec=(20) +DROP PROCEDURE p1; +# +# MDEV-14228 MariaDB crashes with function +# +CREATE TABLE t1 (c VARCHAR(16), KEY(c)); +INSERT INTO t1 VALUES ('foo'); +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 $$ +SELECT f1(); +ERROR HY000: Illegal parameter data types row and varchar for operation '=' +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS VARCHAR(16) +BEGIN +DECLARE v ROW TYPE OF t1; +IF v = 'bar' THEN +RETURN 'eq'; +END IF; +RETURN 'ne'; +END $$ +SELECT f1(); +ERROR HY000: Illegal parameter data types row and varchar for operation '=' +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS VARCHAR(16) +BEGIN +DECLARE v ROW(a INT); +IF v = 'bar' THEN +RETURN 'eq'; +END IF; +RETURN 'ne'; +END $$ +SELECT f1(); +ERROR HY000: Illegal parameter data types row and varchar for operation '=' +DROP FUNCTION f1; +DROP TABLE t1; +BEGIN NOT ATOMIC +DECLARE v ROW(a INT); +SELECT v IN ('a','b'); +END $$ +ERROR HY000: Illegal parameter data types row and varchar for operation 'in' +BEGIN NOT ATOMIC +DECLARE v ROW(a INT); +SELECT 'a' IN (v,'b'); +END $$ +ERROR HY000: Illegal parameter data types varchar and row for operation 'in' +BEGIN NOT ATOMIC +DECLARE v ROW(a INT); +SELECT 'a' IN ('b',v); +END $$ +ERROR HY000: Illegal parameter data types varchar and row for operation 'in' +# +# MDEV-15112 Inconsistent evaluation of spvariable=0 in strict mode +# +SET sql_mode=STRICT_ALL_TABLES; +CREATE OR REPLACE TABLE t1 (e TIMESTAMP(6)); +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +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 +$$ +SELECT f1(e) FROM t1; +ERROR 22007: Truncated incorrect DECIMAL value: '2001-01-01 10:20:30.000000' +SELECT f2(e) FROM t1; +ERROR 22007: Truncated incorrect DECIMAL value: '2001-01-01 10:20:30.000000' +SELECT f3(e) FROM t1; +ERROR 22007: Truncated incorrect DECIMAL value: '2001-01-01 10:20:30.000000' +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP FUNCTION f3; +DROP TABLE t1; +# Test affected rows from an sp +create table t1 (a int); +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; +$$ +CALL p2(); +row_count() +2 +affected rows: 1 +affected rows: 5 +DROP PROCEDURE p1; +DROP PROCEDURE p2; +drop table t1; +# +# MDEV-15957 Unexpected "Data too long" when doing CREATE..SELECT with stored functions +# +CREATE TABLE t1 (a INT(3)); +INSERT INTO t1 VALUES (-999); +CREATE FUNCTION f1(a INT(3)) RETURNS INT(3) RETURN a; +CREATE TABLE t2 AS SELECT CONCAT(a) AS c1, CONCAT(COALESCE(a)) AS c2, CONCAT(f1(a)) AS c3 FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` varchar(11) DEFAULT NULL, + `c2` varchar(11) DEFAULT NULL, + `c3` varchar(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1,t2; +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET latin1 RETURN ''; +CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` tinytext DEFAULT NULL, + `c2` tinytext DEFAULT NULL, + `c3` varchar(255) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS TEXT CHARACTER SET latin1 RETURN ''; +CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` text DEFAULT NULL, + `c2` text DEFAULT NULL, + `c3` text DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS MEDIUMTEXT CHARACTER SET latin1 RETURN ''; +CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` mediumtext DEFAULT NULL, + `c2` mediumtext DEFAULT NULL, + `c3` mediumtext DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS LONGTEXT CHARACTER SET latin1 RETURN ''; +CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` longtext DEFAULT NULL, + `c2` longtext DEFAULT NULL, + `c3` longtext DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET utf8 RETURN ''; +CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` tinytext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + `c2` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + `c3` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS TEXT CHARACTER SET utf8 RETURN ''; +CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + `c2` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + `c3` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS MEDIUMTEXT CHARACTER SET utf8 RETURN ''; +CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + `c2` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + `c3` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS LONGTEXT CHARACTER SET utf8 RETURN ''; +CREATE TABLE t1 AS SELECT f1() AS c1, COALESCE(f1()) AS c2, CONCAT(f1()) AS c3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + `c2` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + `c3` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +DROP FUNCTION f1; +# +# MDEV-16036: Debug assertion failed in resignal on create +# temporary table +# +set @save_sql_mode= @@sql_mode; +set sql_mode='ORACLE'; +CREATE or replace procedure p4() +AS +CONTINUE HANDLER FOR SQLWARNING +BEGIN +NULL; +END; +EXIT HANDLER FOR OTHERS -- SQLEXCEPTION +BEGIN +GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT; +SELECT @sqlstate, @errno, @text; +SHOW WARNINGS; +RESIGNAL; -- cause DBG_ASSERT failed +END; +BEGIN +CREATE TEMPORARY TABLE IF NOT EXISTS t1(origine VARCHAR2(10) NOT NULL); +END +/ +call p4(); +call p4(); +@sqlstate @errno @text +42S01 1050 Table 't1' already exists +Level Code Message +Note 1050 Table 't1' already exists +Warnings: +Note 1050 Table 't1' already exists +drop procedure p4; +drop table t1; +set @@sql_mode=@save_sql_mode; +set @@global.userstat= @save_userstat; +# +# MDEV-17363 Compressed columns cannot be restored from dump +# COMPRESSED conflicted between data type and SP label, +# so it's not allowed as an SP label any more. +# +CREATE FUNCTION f1() RETURNS TEXT COMPRESSED +BEGIN +RETURN ''; +END; +$$ +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 STRICT_ALL_TABLES CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS text COMPRESSED CHARSET latin1 COLLATE latin1_swedish_ci +BEGIN +RETURN ''; +END latin1 latin1_swedish_ci latin1_swedish_ci +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS TEXT +COMPRESSED: +BEGIN +RETURN ''; +END; +$$ +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ': +BEGIN +RETURN ''; +END' at line 2 +# +# MDEV-28220: Assert failure in sp_head::~sp_head on parsing a syntax incorrect statement CREATE SEQUENCE ... RESTART inside CREATE PROCEDURE/CREATE FUNCTION + +# Specifying the RESTART clause for the statement CREATE SEQUENCE is a syntax error. +# Check that CREATE PROCEDURE doesn't crash server if the statement +# CREATE SEQUNCE ... RESTART is specified in its body. +# +CREATE PROCEDURE sp1() CREATE SEQUENCE s1 START WITH 300 INCREMENT BY 30 RESTART; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RESTART' at line 1 +# CREATE SEQUNCE ... RESTART and CREATE SEQUNCE ... RESTART WITH ... are +# handled by different grammar rules, so check the both cases. +CREATE PROCEDURE sp1() CREATE SEQUENCE s1 START WITH 300 INCREMENT BY 30 RESTART WITH 100; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RESTART' at line 1 +# End of 10.3 tests +# +# Start of 10.4 tests +# +# +# MDEV-19637 Crash on an SP variable assignment to a wrong subselect +# +BEGIN NOT ATOMIC +DECLARE a INT; +SET a=(SELECT 1 FROM DUAL UNION SELECT HIGH_PRIORITY 2 FROM DUAL); +END; +$$ +ERROR 42000: Incorrect usage/placement of 'HIGH_PRIORITY' +# +# MDEV-21173: Assertion `m_thd == __null' failed in sp_head::~sp_head +# +CREATE TABLE t1 (a INT); +CREATE PROCEDURE p1() SELECT 1 FROM t1 PROCEDURE ANALYSE( 10, (SELECT a FROM t1)); +ERROR 42000: PROCEDURE does not support subqueries or stored functions +DROP TABLE t1; +# +# MDEV-23902: MariaDB crash on calling function +# +CREATE FUNCTION f2 () RETURNS VARCHAR(1) +BEGIN +DECLARE rec1 ROW TYPE OF v1; +SELECT z INTO rec1 FROM v1; +RETURN 1; +END| +CREATE FUNCTION f1 () RETURNS VARCHAR(1) RETURN f2() ; +CREATE FUNCTION f3 () RETURNS VARCHAR(1) RETURN f_not_exist(); +CREATE VIEW v1 AS SELECT f3() z; +SELECT f1(); +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +# Check that crash doen't happen in case f3 completes with success. +DROP FUNCTION f3; +CREATE FUNCTION f3 () RETURNS VARCHAR(1) RETURN '!'; +SELECT f1(); +f1() +1 +# Clean up +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP FUNCTION f3; +DROP VIEW v1; +# End of 10.4 tests +# +# +# MDEV-29129: Performance regression starting in 10.6: unlimited "select order by limit" +# +CREATE TABLE t1 ( +lookupId int primary key, +value varchar(255) +); +insert into t1 select seq, seq from seq_1_to_100; +# Note: the function is intentionally NOT declared as DETERMINISTIC +CREATE FUNCTION f1(LOOKUPID_IN INT) RETURNS varchar(255) CHARSET utf8 +BEGIN +DECLARE LOOKUP_VALUE VARCHAR(255); +SET LOOKUP_VALUE = (SELECT value FROM t1 WHERE lookupId = LOOKUPID_IN); +set @counter=@counter+1; +RETURN LOOKUP_VALUE; +END; +// +create table t2 ( +col1 int, +col2 int +); +insert into t2 select mod(seq,100), seq from seq_1_to_1000; +explain +select f1(col1) from t2 order by col2 desc limit 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 Using filesort +set @counter=0; +select f1(col1) from t2 order by col2 desc limit 5; +f1(col1) +NULL +99 +98 +97 +96 +# Must show 5, not 1000: +select @counter; +@counter +5 +drop function f1; +drop table t1,t2; +# +# MDEV-31877: ASAN errors in Exec_time_tracker::get_cycles with innodb slow log verbosity +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2); +SET @tmp=@@log_slow_verbosity; +SET SESSION log_slow_verbosity= 'innodb'; +BEGIN NOT ATOMIC DECLARE r ROW TYPE OF t1 DEFAULT (SELECT * FROM t1); SELECT r.a; END $ +r.a +1 +SET SESSION log_slow_verbosity= @tmp; +DROP TABLE t1; +# +# MDEV-28129: MariaDB UAF issue at lex_end_nops(LEX*) +# +CREATE PROCEDURE sp() SELECT 1 INTO @; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +CREATE PROCEDURE sp() SET @=1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=1' at line 1 +CREATE PROCEDURE sp() SELECT @; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +# +# End of 10.7 tests +# |