2 # Basic stored PROCEDURE tests
4 # Test cases for bugs are added at the end. See template there.
6 # Some tests that require --error go into sp-error.test
7 # Tests that require inndb go into sp_trans.test
8 # Tests that check privilege and security issues go to sp-security.test.
9 # Tests that require multiple connections, except security/privilege tests,
11 # Tests that uses 'goto' to into sp-goto.test (currently disabled)
12 # Tests that destroys system tables (e.g. mysql.proc) for error testing
14 # Tests that require --with-geometry go into sp_gis.test
15 # Tests that require multibyte character sets, which are not always available,
16 # go into separate files (e.g. sp-ucs2.test)
22 # t1 and t2 are reused throughout the file, and dropped at the end.
23 # t3 and up are created and dropped when needed.
26 drop table if exists t1,t2,t3,t4;
27 drop view if exists v1;
28 drop procedure if exists p1;
29 drop procedure if exists p2;
30 drop function if exists f1;
31 drop function if exists f2;
34 id char(16) not null default '',
44 # Single statement, no params.
46 drop procedure if exists foo42;
48 create procedure foo42()
49 insert into test.t1 values ("foo", 42);
57 # Single statement, two IN params.
59 drop procedure if exists bar;
61 create procedure bar(x char(16), y int)
62 insert into test.t1 values (x, y);
67 # Don't drop procedure yet...
70 # Now for multiple statements...
75 drop procedure if exists empty|
77 create procedure empty()
84 # Scope test. This is legal (warnings might be possible in the future,
85 # but for the time being, we just accept it).
87 drop procedure if exists scope|
89 create procedure scope(a int, b float)
103 drop procedure if exists two|
105 create procedure two(x1 char(16), x2 char(16), y int)
107 insert into test.t1 values (x1, y);
108 insert into test.t1 values (x2, y);
111 call two("one", "two", 3)|
117 # Simple test of local variables and SET.
119 drop procedure if exists locset|
121 create procedure locset(x char(16), y int)
126 insert into test.t1 values (x, z2);
129 call locset("locset", 19)|
132 drop procedure locset|
135 # In some contexts local variables are not recognized
136 # (and in some, you have to qualify the identifier).
138 drop procedure if exists setcontext|
140 create procedure setcontext()
142 declare data int default 2;
144 insert into t1 (id, data) values ("foo", 1);
145 replace t1 set data = data, id = "bar";
146 update t1 set id = "kaka", data = 3 where t1.data = data;
150 select * from t1 order by data|
152 drop procedure setcontext|
156 create table t3 ( d date, i int, f double, s varchar(32) )|
159 drop procedure if exists nullset|
161 create procedure nullset()
166 declare ls varchar(32);
168 set ld = null, li = null, lf = null, ls = null;
169 insert into t3 values (ld, li, lf, ls);
171 insert into t3 (i, f, s) values ((ld is null), 1, "ld is null"),
172 ((li is null), 1, "li is null"),
173 ((li = 0), null, "li = 0"),
174 ((lf is null), 1, "lf is null"),
175 ((lf = 0), null, "lf = 0"),
176 ((ls is null), 1, "ls is null");
182 drop procedure nullset|
185 # The peculiar (non-standard) mixture of variables types in SET.
187 drop procedure if exists mixset|
189 create procedure mixset(x char(16), y int)
193 set @z = y, z = 666, max_join_size = 100;
194 insert into test.t1 values (x, z);
197 call mixset("mixset", 19)|
198 show variables like 'max_join_size'|
199 select id,data,@z from t1|
201 drop procedure mixset|
204 # Multiple CALL statements, one with OUT parameter.
206 drop procedure if exists zip|
208 create procedure zip(x char(16), y int)
215 # SET local variables and OUT parameter.
217 drop procedure if exists zap|
219 create procedure zap(x int, out y int)
231 # Top-level OUT parameter
240 drop procedure if exists c1|
242 create procedure c1(x int)
245 drop procedure if exists c2|
247 create procedure c2(s char(16), x int)
250 drop procedure if exists c3|
252 create procedure c3(x int, s char(16))
253 call c4("level", x, s)|
255 drop procedure if exists c4|
257 create procedure c4(l char(8), x int, s char(16))
258 insert into t1 values (concat(l,s), x)|
270 drop procedure if exists iotest|
272 create procedure iotest(x1 char(16), x2 char(16), y int)
275 insert into test.t1 values (x1, y);
279 drop procedure if exists inc2|
281 create procedure inc2(x char(16), y int)
284 insert into test.t1 values (x, y);
288 drop procedure if exists inc|
290 create procedure inc(inout io int)
293 call iotest("io1", "io2", 1)|
294 select * from t1 order by data desc|
296 drop procedure iotest|
299 # Propagating top-level @-vars
301 drop procedure if exists incr|
303 create procedure incr(inout x int)
316 # The expected result is:
320 drop procedure if exists cbv1|
322 create procedure cbv1()
324 declare y int default 3;
327 insert into test.t1 values ("cbv1", y);
331 drop procedure if exists cbv2|
333 create procedure cbv2(y1 int, inout y2 int)
336 insert into test.t1 values ("cbv2", y1);
340 select * from t1 order by data|
346 # Subselect arguments
348 insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)|
351 drop procedure if exists sub1|
353 create procedure sub1(id char(16), x int)
354 insert into test.t1 values (id, x)|
357 drop procedure if exists sub2|
359 create procedure sub2(id char(16))
362 set x = (select sum(t.i) from test.t2 t);
363 insert into test.t1 values (id, x);
367 drop procedure if exists sub3|
369 create function sub3(i int) returns int deterministic
372 call sub1("sub1a", (select 7))|
373 call sub1("sub1b", (select max(i) from t2))|
374 --error ER_OPERAND_COLUMNS
375 call sub1("sub1c", (select i,d from t2 limit 1))|
376 call sub1("sub1d", (select 1 from (select 1) a))|
378 select * from t1 order by id|
379 select sub3((select max(i) from t2))|
386 # Basic tests of the flow control constructs
388 # Just test on 'x'...
390 drop procedure if exists a0|
392 create procedure a0(x int)
395 insert into test.t1 values ("a0", x);
399 select * from t1 order by data desc|
404 # The same, but with a more traditional test.
406 drop procedure if exists a|
408 create procedure a(x int)
411 insert into test.t1 values ("a", x);
415 select * from t1 order by data desc|
422 drop procedure if exists b|
424 create procedure b(x int)
426 insert into test.t1 values (repeat("b",3), x);
428 until x = 0 end repeat|
431 select * from t1 order by data desc|
436 # Check that repeat isn't parsed the wrong way
438 drop procedure if exists b2|
440 create procedure b2(x int)
441 repeat(select 1 into outfile 'b2');
442 insert into test.t1 values (repeat("b2",3), x);
444 until x = 0 end repeat|
446 # We don't actually want to call it.
450 # Labelled WHILE with ITERATE (pointless really)
452 drop procedure if exists c|
454 create procedure c(x int)
456 insert into test.t1 values ("c", x);
459 insert into test.t1 values ("x", x);
463 select * from t1 order by data desc|
468 # Labelled WHILE with LEAVE
470 drop procedure if exists d|
472 create procedure d(x int)
474 insert into test.t1 values ("d", x);
477 insert into test.t1 values ("x", x);
486 # LOOP, with simple IF statement
488 drop procedure if exists e|
490 create procedure e(x int)
495 insert into test.t1 values ("e", x);
500 select * from t1 order by data desc|
505 # A full IF statement
507 drop procedure if exists f|
509 create procedure f(x int)
511 insert into test.t1 values ("f", 0);
513 insert into test.t1 values ("f", 1);
515 insert into test.t1 values ("f", 2);
521 select * from t1 order by data|
526 # This form of CASE is really just syntactic sugar for IF-ELSEIF-...
528 drop procedure if exists g|
530 create procedure g(x int)
533 insert into test.t1 values ("g", 0);
535 insert into test.t1 values ("g", 1);
537 insert into test.t1 values ("g", 2);
543 select * from t1 order by data|
550 drop procedure if exists h|
552 create procedure h(x int)
555 insert into test.t1 values ("h0", x);
557 insert into test.t1 values ("h1", x);
559 insert into test.t1 values ("h?", x);
565 select * from t1 order by data|
570 # It's actually possible to LEAVE a BEGIN-END block
572 drop procedure if exists i|
574 create procedure i(x int)
580 insert into test.t1 values ("i", x);
590 # SELECT with one of more result set sent back to the clinet
591 insert into t1 values ("foo", 3), ("bar", 19)|
592 insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)|
595 drop procedure if exists sel1|
597 create procedure sel1()
599 select * from t1 order by data;
606 drop procedure if exists sel2|
608 create procedure sel2()
610 select * from t1 order by data;
611 select * from t2 order by s;
619 # SELECT INTO local variables
621 drop procedure if exists into_test|
623 create procedure into_test(x char(16), y int)
625 insert into test.t1 values (x, y);
626 select id,data into x,y from test.t1 limit 1;
627 insert into test.t1 values (concat(x, "2"), y+2);
630 call into_test("into", 100)|
631 select * from t1 order by data|
633 drop procedure into_test|
636 # SELECT INTO with a mix of local and global variables
638 drop procedure if exists into_tes2|
640 create procedure into_test2(x char(16), y int)
642 insert into test.t1 values (x, y);
643 select id,data into x,@z from test.t1 limit 1;
644 insert into test.t1 values (concat(x, "2"), y+2);
647 call into_test2("into", 100)|
648 select id,data,@z from t1 order by data|
650 drop procedure into_test2|
653 # SELECT * INTO ... (bug test)
655 drop procedure if exists into_test3|
657 create procedure into_test3()
662 select * into x,y from test.t1 limit 1;
663 insert into test.t2 values (x, y, 0.0);
666 insert into t1 values ("into3", 19)|
667 # Two call needed for bug test
673 drop procedure into_test3|
676 # SELECT INTO with no data is a warning ("no data", which we will
677 # not see normally). When not caught, execution proceeds.
679 drop procedure if exists into_test4|
681 create procedure into_test4()
685 select data into x from test.t1 limit 1;
686 insert into test.t3 values ("into4", x);
690 create table t3 ( s char(16), d int)|
693 insert into t1 values ("i4", 77)|
698 drop procedure into_test4|
701 # These two (and the two procedures above) caused an assert() to fail in
702 # sql_base.cc:lock_tables() at some point.
704 drop procedure if exists into_outfile|
706 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
707 eval create procedure into_outfile(x char(16), y int)
709 insert into test.t1 values (x, y);
710 select * into outfile "$MYSQLTEST_VARDIR/tmp/spout" from test.t1;
711 insert into test.t1 values (concat(x, "2"), y+2);
714 # Check that file does not exists
716 --file_exists $MYSQLTEST_VARDIR/tmp/spout
717 call into_outfile("ofile", 1)|
718 --remove_file $MYSQLTEST_VARDIR/tmp/spout
720 drop procedure into_outfile|
723 drop procedure if exists into_dumpfile|
725 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
726 eval create procedure into_dumpfile(x char(16), y int)
728 insert into test.t1 values (x, y);
729 select * into dumpfile "$MYSQLTEST_VARDIR/tmp/spdump" from test.t1 limit 1;
730 insert into test.t1 values (concat(x, "2"), y+2);
733 # Check that file does not exists
735 --file_exists $MYSQLTEST_VARDIR/tmp/spdump
736 call into_dumpfile("dfile", 1)|
737 --remove_file $MYSQLTEST_VARDIR/tmp/spdump
739 drop procedure into_dumpfile|
742 drop procedure if exists create_select|
744 create procedure create_select(x char(16), y int)
746 insert into test.t1 values (x, y);
747 create temporary table test.t3 select * from test.t1;
748 insert into test.t3 values (concat(x, "2"), y+2);
751 call create_select("cs", 90)|
752 select * from t1, t3|
755 drop procedure create_select|
758 # A minimal, constant FUNCTION.
760 drop function if exists e|
762 create function e() returns double
763 return 2.7182818284590452354|
768 # A minimal function with one argument
770 drop function if exists inc|
772 create function inc(i int) returns int
775 select inc(1), inc(99), inc(-71)|
777 # A minimal function with two arguments
779 drop function if exists mul|
781 create function mul(x int, y int) returns int
784 select mul(1,1), mul(3,5), mul(4711, 666)|
786 # A minimal string function
788 drop function if exists append|
790 create function append(s1 char(8), s2 char(8)) returns char(16)
791 return concat(s1, s2)|
793 select append("foo", "bar")|
795 # A function with flow control
797 drop function if exists fac|
799 create function fac(n int unsigned) returns bigint unsigned
801 declare f bigint unsigned default 1;
810 select fac(1), fac(2), fac(5), fac(10)|
814 drop function if exists fun|
816 create function fun(d double, i int, u int unsigned) returns double
817 return mul(inc(i), fac(u)) / e()|
819 select fun(2.3, 3, 5)|
822 # Various function calls in differen statements
824 insert into t2 values (append("xxx", "yyy"), mul(4,3), e())|
825 insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))|
827 # Disable PS because double's give a bit different values
828 --disable_ps_protocol
829 select * from t2 where s = append("a", "b")|
830 select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2) order by i|
831 select * from t2 where d = e()|
832 select * from t2 order by i|
839 drop function append|
844 # CONDITIONs and HANDLERs
848 drop procedure if exists hndlr1|
850 create procedure hndlr1(val int)
852 declare x int default 0;
853 declare foo condition for 1136;
854 declare bar condition for sqlstate '42S98'; # Just for testing syntax
855 declare zip condition for sqlstate value '42S99'; # Just for testing syntax
856 declare continue handler for foo set x = 1;
858 insert into test.t1 values ("hndlr1", val, 2); # Too many values
860 insert into test.t1 values ("hndlr1", val); # This instead then
867 drop procedure hndlr1|
870 drop procedure if exists hndlr2|
872 create procedure hndlr2(val int)
874 declare x int default 0;
877 declare exit handler for sqlstate '21S01' set x = 1;
879 insert into test.t1 values ("hndlr2", val, 2); # Too many values
882 insert into test.t1 values ("hndlr2", x);
888 drop procedure hndlr2|
892 drop procedure if exists hndlr3|
894 create procedure hndlr3(val int)
896 declare x int default 0;
897 declare continue handler for sqlexception # Any error
910 insert into test.t1 values ("hndlr3", y, 2); # Too many values
912 insert into test.t1 values ("hndlr3", y);
921 drop procedure hndlr3|
924 # Variables might be uninitialized when using handlers
925 # (Otherwise the compiler can detect if a variable is not set, but
927 create table t3 ( id char(16), data int )|
930 drop procedure if exists hndlr4|
932 create procedure hndlr4()
934 declare x int default 0;
935 declare val int; # No default
936 declare continue handler for sqlstate '02000' set x=1;
938 select data into val from test.t3 where id='z' limit 1; # No hits
940 insert into test.t3 values ('z', val);
946 drop procedure hndlr4|
953 drop procedure if exists cur1|
955 create procedure cur1()
960 declare done int default 0;
961 declare c cursor for select * from test.t2;
962 declare continue handler for sqlstate '02000' set done = 1;
966 fetch c into a, b, c;
968 insert into test.t1 values (a, b+c);
970 until done end repeat;
974 insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)|
979 create table t3 ( s char(16), i int )|
982 drop procedure if exists cur2|
984 create procedure cur2()
986 declare done int default 0;
987 declare c1 cursor for select id,data from test.t1 order by id,data;
988 declare c2 cursor for select i from test.t2 order by i;
989 declare continue handler for sqlstate '02000' set done = 1;
998 fetch from c1 into a, b;
999 fetch next from c2 into c;
1002 insert into test.t3 values (a, b);
1004 insert into test.t3 values (a, c);
1008 until done end repeat;
1014 select * from t3 order by i,s|
1018 drop procedure cur2|
1021 # The few characteristics we parse
1023 drop procedure if exists chistics|
1025 create procedure chistics()
1029 sql security definer
1030 comment 'Characteristics procedure test'
1031 insert into t1 values ("chistics", 1)|
1033 show create procedure chistics|
1034 # Call it, just to make sure.
1038 alter procedure chistics sql security invoker|
1039 show create procedure chistics|
1040 drop procedure chistics|
1043 drop function if exists chistics|
1045 create function chistics() returns int
1048 sql security invoker
1049 comment 'Characteristics procedure test'
1052 show create function chistics|
1053 # Call it, just to make sure.
1055 alter function chistics
1057 comment 'Characteristics function test'|
1058 show create function chistics|
1059 drop function chistics|
1062 # Check mode settings
1063 insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)|
1065 set @@sql_mode = 'ANSI'|
1068 drop procedure if exists modes$
1070 create procedure modes(out c1 int, out c2 int)
1072 declare done int default 0;
1074 declare c cursor for select data from t1;
1075 declare continue handler for sqlstate '02000' set done = 1;
1077 select 1 || 2 into c1;
1085 until done end repeat;
1089 set @@sql_mode = ''|
1091 set sql_select_limit = 1|
1092 call modes(@c1, @c2)|
1093 set sql_select_limit = default|
1097 drop procedure modes|
1100 # Check that dropping a database without routines works.
1101 # (Dropping with routines is tested in sp-security.test)
1102 # First an empty db.
1103 create database sp_db1|
1104 drop database sp_db1|
1106 # Again, with a table.
1107 create database sp_db2|
1109 # Just put something in here...
1110 create table t3 ( s char(4), t int )|
1111 insert into t3 values ("abcd", 42), ("dcba", 666)|
1113 drop database sp_db2|
1115 # And yet again, with just a procedure.
1116 create database sp_db3|
1119 drop procedure if exists dummy|
1121 create procedure dummy(out x int)
1124 drop database sp_db3|
1125 # Check that it's gone
1126 select type,db,name from mysql.proc where db = 'sp_db3'|
1129 # ROW_COUNT() function after a CALL
1130 # We test the other cases here too, although it's not strictly SP specific
1132 drop procedure if exists rc|
1134 create procedure rc()
1137 insert into t1 values ("a", 1), ("b", 2), ("c", 3);
1142 --disable_ps_protocol
1143 update t1 set data=42 where id = "b";
1145 --enable_ps_protocol
1156 # Let us test how well new locking scheme works.
1159 # Let us prepare playground
1161 drop function if exists f0|
1162 drop function if exists f1|
1163 drop function if exists f2|
1164 drop function if exists f3|
1165 drop function if exists f4|
1166 drop function if exists f5|
1167 drop function if exists f6|
1168 drop function if exists f7|
1169 drop function if exists f8|
1170 drop function if exists f9|
1171 drop function if exists f10|
1172 drop function if exists f11|
1173 drop function if exists f12_1|
1174 drop function if exists f12_2|
1175 drop view if exists v0|
1176 drop view if exists v1|
1177 drop view if exists v2|
1181 insert into t1 values ("a", 1), ("b", 2) |
1182 insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
1184 # Test the simplest function using tables
1185 create function f1() returns int
1186 return (select sum(data) from t1)|
1188 # This should work too (and give 2 rows as result)
1189 select id, f1() from t1 order by id|
1191 # Function which uses two instances of table simultaneously
1192 create function f2() returns int
1193 return (select data from t1 where data <= (select sum(data) from t1) order by data limit 1)|
1195 select id, f2() from t1 order by id|
1197 # Function which uses the same table twice in different queries
1198 create function f3() returns int
1202 set n:= (select min(data) from t1);
1203 set m:= (select max(data) from t1);
1207 select id, f3() from t1 order by id|
1209 # Calling two functions using same table
1211 select id, f1(), f3() from t1 order by id|
1213 # Function which uses two different tables
1214 create function f4() returns double
1215 return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
1217 select s, f4() from t2 order by s|
1219 # Recursive functions which due to this recursion require simultaneous
1220 # access to several instance of the same table won't work
1221 create function f5(i int) returns int
1226 return (select count(*) from t1 where data = i);
1228 return (select count(*) + f5( i - 1) from t1 where data = i);
1232 # Since currently recursive functions are disallowed ER_SP_NO_RECURSION
1233 # error will be returned, once we will allow them error about
1234 # insufficient number of locked tables will be returned instead.
1235 --error ER_SP_NO_RECURSION
1237 --error ER_SP_NO_RECURSION
1240 # OTOH this should work
1241 create function f6() returns int
1245 return (select count(*) from t1 where data <= f7() and data <= n);
1247 create function f7() returns int
1248 return (select sum(data) from t1 where data <= f1())|
1250 select id, f6() from t1 order by id|
1253 # Let us test how new locking work with views
1255 # The most trivial view
1256 create view v1 (a) as select f1()|
1258 select id, a from t1, v1 order by id|
1259 select * from v1, v1 as v|
1260 # A bit more complex construction
1261 create view v2 (a) as select a*10 from v1|
1263 select id, a from t1, v2 order by id|
1264 select * from v1, v2|
1266 # Nice example where the same view is used on
1267 # on different expression levels
1268 create function f8 () returns int
1269 return (select count(*) from v2)|
1271 select *, f8() from v1|
1273 # Let us test what will happen if function is missing
1275 --error ER_VIEW_INVALID
1278 # And what will happen if we have recursion which involves
1279 # views and functions ?
1280 create function f1() returns int
1281 return (select sum(data) from t1) + (select sum(data) from v1)|
1282 --error ER_SP_NO_RECURSION
1284 --error ER_SP_NO_RECURSION
1286 --error ER_SP_NO_RECURSION
1288 # Back to the normal cases
1290 create function f1() returns int
1291 return (select sum(data) from t1)|
1293 # Let us also test some weird cases where no real tables is used
1294 create function f0() returns int
1295 return (select * from (select 100) as r)|
1297 select *, f0() from (select 1) as t|
1298 create view v0 as select f0()|
1300 select *, f0() from v0|
1303 # Let us test how well prelocking works with explicit LOCK TABLES.
1305 lock tables t1 read, t1 as t11 read|
1306 # These should work well
1308 select id, f3() from t1 as t11 order by id|
1309 # Degenerate cases work too :)
1312 select *, f0() from v0, (select 123) as d1|
1313 # But these should not !
1314 --error ER_TABLE_NOT_LOCKED
1315 select id, f3() from t1|
1316 --error ER_TABLE_NOT_LOCKED
1320 # Let us test how LOCK TABLES which implicitly depends on functions
1322 lock tables v2 read, mysql.proc read|
1325 # These should not work as we have too little instances of tables locked
1326 --error ER_TABLE_NOT_LOCKED
1327 select * from v1, t1|
1328 --error ER_TABLE_NOT_LOCKED
1332 # Tests for handling of temporary tables in functions.
1334 # Unlike for permanent tables we should be able to create, use
1335 # and drop such tables in functions.
1337 # Simplest function using temporary table. It is also test case for bug
1338 # #12198 "Temporary table aliasing does not work inside stored functions"
1339 create function f9() returns int
1342 drop temporary table if exists t3;
1343 create temporary table t3 (id int);
1344 insert into t3 values (1), (2), (3);
1345 set a:= (select count(*) from t3);
1346 set b:= (select count(*) from t3 t3_alias);
1349 # This will emit warning as t3 was not existing before.
1351 select f9() from t1 limit 1|
1353 # Function which uses both temporary and permanent tables.
1354 create function f10() returns int
1356 drop temporary table if exists t3;
1357 create temporary table t3 (id int);
1358 insert into t3 select id from t4;
1359 return (select count(*) from t3);
1361 # Check that we don't ignore completely tables used in function
1362 --error ER_NO_SUCH_TABLE
1364 create table t4 as select 1 as id|
1367 # Practical cases which we don't handle well (yet)
1369 # Function which does not work because of well-known and documented
1370 # limitation of MySQL. We can't use the several instances of the
1371 # same temporary table in statement.
1372 create function f11() returns int
1374 drop temporary table if exists t3;
1375 create temporary table t3 (id int);
1376 insert into t3 values (1), (2), (3);
1377 return (select count(*) from t3 as a, t3 as b);
1379 --error ER_CANT_REOPEN_TABLE
1381 --error ER_CANT_REOPEN_TABLE
1382 select f11() from t1|
1383 # Test that using a single table instance at a time works
1384 create function f12_1() returns int
1386 drop temporary table if exists t3;
1387 create temporary table t3 (id int);
1388 insert into t3 values (1), (2), (3);
1391 create function f12_2() returns int
1392 return (select count(*) from t3)|
1394 drop temporary table t3|
1396 select f12_1() from t1 limit 1|
1411 drop function f12_1|
1412 drop function f12_2|
1420 # End of non-bug tests
1424 # Some "real" examples
1430 drop table if exists t3|
1432 create table t3 (n int unsigned not null primary key, f bigint unsigned)|
1435 drop procedure if exists ifac|
1437 create procedure ifac(n int unsigned)
1439 declare i int unsigned default 1;
1442 set n = 20; # bigint overflow otherwise
1446 insert into test.t3 values (i, fac(i));
1455 --replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
1456 show function status like '%f%'|
1457 drop procedure ifac|
1459 --replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
1460 show function status like '%f%'|
1466 drop table if exists t3|
1470 i int unsigned not null primary key,
1471 p bigint unsigned not null
1474 insert into t3 values
1475 ( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13),
1476 ( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31),
1477 (10, 37), (11, 41), (12, 43), (13, 47), (14, 53),
1478 (15, 59), (16, 61), (17, 67), (18, 71), (19, 73),
1479 (20, 79), (21, 83), (22, 89), (23, 97), (24, 101),
1480 (25, 103), (26, 107), (27, 109), (28, 113), (29, 127),
1481 (30, 131), (31, 137), (32, 139), (33, 149), (34, 151),
1482 (35, 157), (36, 163), (37, 167), (38, 173), (39, 179),
1483 (40, 181), (41, 191), (42, 193), (43, 197), (44, 199)|
1486 drop procedure if exists opp|
1488 create procedure opp(n bigint unsigned, out pp bool)
1491 declare b, s bigint unsigned default 0;
1498 set b = b+200, s = 0;
1501 declare p bigint unsigned;
1503 select t.p into p from test.t3 t where t.i = s;
1508 if mod(n, b+p) = 0 then
1519 drop procedure if exists ip|
1521 create procedure ip(m int unsigned)
1523 declare p bigint unsigned;
1524 declare i int unsigned;
1530 declare pp bool default 0;
1534 insert into test.t3 values (i, p);
1541 show create procedure opp|
1542 --replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
1543 show procedure status where name like '%p%' and db='test'|
1545 # This isn't the fastest way in the world to compute prime numbers, so
1546 # don't be too ambitious. ;-)
1548 # We don't want to select the entire table here, just pick a few
1550 # The expected result is:
1556 select * from t3 where i=45 or i=100 or i=199|
1560 --replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
1561 show procedure status where name like '%p%' and db='test'|
1569 drop procedure if exists bar|
1571 create procedure bar(x char(16), y int)
1572 comment "111111111111" sql security invoker
1573 insert into test.t1 values (x, y)|
1574 --replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
1575 show procedure status like 'bar'|
1576 alter procedure bar comment "2222222222" sql security definer|
1577 alter procedure bar comment "3333333333"|
1578 alter procedure bar|
1579 show create procedure bar|
1580 --replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
1581 show procedure status like 'bar'|
1588 drop procedure if exists p1|
1590 create procedure p1 ()
1591 select (select s1 from t3) from t3|
1593 create table t3 (s1 int)|
1596 insert into t3 values (1)|
1605 drop function if exists foo|
1607 create function `foo` () returns int
1610 drop function `foo`|
1613 # Implicit LOCK/UNLOCK TABLES for table access in functions
1617 drop function if exists t1max|
1619 create function t1max() returns int
1622 select max(data) into x from t1;
1626 insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)|
1628 drop function t1max|
1631 v char(16) not null primary key,
1632 c int unsigned not null
1635 create function getcount(s char(16)) returns int
1639 select count(*) into x from t3 where v = s;
1641 insert into t3 values (s, 1);
1643 update t3 set c = c+1 where v = s;
1647 select * from t1 where data = getcount("bar")|
1649 select getcount("zip")|
1650 select getcount("zip")|
1652 select getcount(id) from t1 where data = 3|
1653 select getcount(id) from t1 where data = 5|
1656 drop function getcount|
1659 # Test cases for different combinations of condition handlers in nested
1660 # begin-end blocks in stored procedures.
1662 # Note that the standard specifies that the most specific handler should
1663 # be triggered even if it's an outer handler masked by a less specific
1664 # handler in an inner block.
1665 # Note also that '02000' is more specific than NOT FOUND; there might be
1666 # other '02xxx' states, even if we currently do not issue them in any
1667 # situation (e.g. '02001').
1669 # The combinations we test are these:
1672 # errcode sqlstate not found sqlwarning sqlexception
1673 # Outer +------------+------------+------------+------------+------------+
1674 #errcode | h_ee (i) | h_es (o) | h_en (o) | h_ew (o) | h_ex (o) |
1675 #sqlstate | h_se (i) | h_ss (i) | h_sn (o) | h_sw (o) | h_sx (o) |
1676 #not found | h_ne (i) | h_ns (i) | h_nn (i) | | |
1677 #sqlwarning | h_we (i) | h_ws (i) | | h_ww (i) | |
1678 #sqlexception | h_xe (i) | h_xs (i) | | | h_xx (i) |
1679 # +------------+---------------------------------------------------+
1681 # (i) means that the inner handler is the one that should be invoked,
1682 # (o) means that the outer handler should be invoked.
1684 # ('not found', 'sqlwarning' and 'sqlexception' are mutually exclusive, hence
1685 # no tests for those combinations.)
1689 drop table if exists t3|
1690 drop procedure if exists h_ee|
1691 drop procedure if exists h_es|
1692 drop procedure if exists h_en|
1693 drop procedure if exists h_ew|
1694 drop procedure if exists h_ex|
1695 drop procedure if exists h_se|
1696 drop procedure if exists h_ss|
1697 drop procedure if exists h_sn|
1698 drop procedure if exists h_sw|
1699 drop procedure if exists h_sx|
1700 drop procedure if exists h_ne|
1701 drop procedure if exists h_ns|
1702 drop procedure if exists h_nn|
1703 drop procedure if exists h_we|
1704 drop procedure if exists h_ws|
1705 drop procedure if exists h_ww|
1706 drop procedure if exists h_xe|
1707 drop procedure if exists h_xs|
1708 drop procedure if exists h_xx|
1711 # smallint - to get out of range warnings
1712 # primary key - to get constraint errors
1713 create table t3 (a smallint primary key)|
1715 insert into t3 (a) values (1)|
1717 create procedure h_ee()
1720 declare continue handler for 1062 -- ER_DUP_ENTRY
1721 select 'Outer (bad)' as 'h_ee';
1724 declare continue handler for 1062 -- ER_DUP_ENTRY
1725 select 'Inner (good)' as 'h_ee';
1727 insert into t3 values (1);
1731 create procedure h_es()
1734 declare continue handler for 1062 -- ER_DUP_ENTRY
1735 select 'Outer (good)' as 'h_es';
1738 -- integrity constraint violation
1739 declare continue handler for sqlstate '23000'
1740 select 'Inner (bad)' as 'h_es';
1742 insert into t3 values (1);
1746 create procedure h_en()
1749 declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
1750 select 'Outer (good)' as 'h_en';
1754 declare continue handler for sqlstate '02000' -- no data
1755 select 'Inner (bad)' as 'h_en';
1757 select a into x from t3 where a = 42;
1761 create procedure h_ew()
1764 declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
1765 select 'Outer (good)' as 'h_ew';
1768 declare continue handler for sqlwarning
1769 select 'Inner (bad)' as 'h_ew';
1771 insert into t3 values (123456789012);
1774 insert into t3 values (1);
1777 create procedure h_ex()
1780 declare continue handler for 1062 -- ER_DUP_ENTRY
1781 select 'Outer (good)' as 'h_ex';
1784 declare continue handler for sqlexception
1785 select 'Inner (bad)' as 'h_ex';
1787 insert into t3 values (1);
1791 create procedure h_se()
1794 -- integrity constraint violation
1795 declare continue handler for sqlstate '23000'
1796 select 'Outer (bad)' as 'h_se';
1799 declare continue handler for 1062 -- ER_DUP_ENTRY
1800 select 'Inner (good)' as 'h_se';
1802 insert into t3 values (1);
1806 create procedure h_ss()
1809 -- integrity constraint violation
1810 declare continue handler for sqlstate '23000'
1811 select 'Outer (bad)' as 'h_ss';
1814 -- integrity constraint violation
1815 declare continue handler for sqlstate '23000'
1816 select 'Inner (good)' as 'h_ss';
1818 insert into t3 values (1);
1822 create procedure h_sn()
1825 -- Note: '02000' is more specific than NOT FOUND ;
1826 -- there might be other not found states
1827 declare continue handler for sqlstate '02000' -- no data
1828 select 'Outer (good)' as 'h_sn';
1832 declare continue handler for not found
1833 select 'Inner (bad)' as 'h_sn';
1835 select a into x from t3 where a = 42;
1839 create procedure h_sw()
1842 -- data exception - numeric value out of range
1843 declare continue handler for sqlstate '22003'
1844 select 'Outer (good)' as 'h_sw';
1847 declare continue handler for sqlwarning
1848 select 'Inner (bad)' as 'h_sw';
1850 insert into t3 values (123456789012);
1853 insert into t3 values (1);
1856 create procedure h_sx()
1859 -- integrity constraint violation
1860 declare continue handler for sqlstate '23000'
1861 select 'Outer (good)' as 'h_sx';
1864 declare continue handler for sqlexception
1865 select 'Inner (bad)' as 'h_sx';
1867 insert into t3 values (1);
1871 create procedure h_ne()
1874 declare continue handler for not found
1875 select 'Outer (bad)' as 'h_ne';
1879 declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
1880 select 'Inner (good)' as 'h_ne';
1882 select a into x from t3 where a = 42;
1886 create procedure h_ns()
1889 declare continue handler for not found
1890 select 'Outer (bad)' as 'h_ns';
1894 declare continue handler for sqlstate '02000' -- no data
1895 select 'Inner (good)' as 'h_ns';
1897 select a into x from t3 where a = 42;
1901 create procedure h_nn()
1904 declare continue handler for not found
1905 select 'Outer (bad)' as 'h_nn';
1909 declare continue handler for not found
1910 select 'Inner (good)' as 'h_nn';
1912 select a into x from t3 where a = 42;
1916 create procedure h_we()
1919 declare continue handler for sqlwarning
1920 select 'Outer (bad)' as 'h_we';
1923 declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
1924 select 'Inner (good)' as 'h_we';
1926 insert into t3 values (123456789012);
1929 insert into t3 values (1);
1932 create procedure h_ws()
1935 declare continue handler for sqlwarning
1936 select 'Outer (bad)' as 'h_ws';
1939 -- data exception - numeric value out of range
1940 declare continue handler for sqlstate '22003'
1941 select 'Inner (good)' as 'h_ws';
1943 insert into t3 values (123456789012);
1946 insert into t3 values (1);
1949 create procedure h_ww()
1952 declare continue handler for sqlwarning
1953 select 'Outer (bad)' as 'h_ww';
1956 declare continue handler for sqlwarning
1957 select 'Inner (good)' as 'h_ww';
1959 insert into t3 values (123456789012);
1962 insert into t3 values (1);
1965 create procedure h_xe()
1968 declare continue handler for sqlexception
1969 select 'Outer (bad)' as 'h_xe';
1972 declare continue handler for 1062 -- ER_DUP_ENTRY
1973 select 'Inner (good)' as 'h_xe';
1975 insert into t3 values (1);
1979 create procedure h_xs()
1982 declare continue handler for sqlexception
1983 select 'Outer (bad)' as 'h_xs';
1986 -- integrity constraint violation
1987 declare continue handler for sqlstate '23000'
1988 select 'Inner (good)' as 'h_xs';
1990 insert into t3 values (1);
1994 create procedure h_xx()
1997 declare continue handler for sqlexception
1998 select 'Outer (bad)' as 'h_xx';
2001 declare continue handler for sqlexception
2002 select 'Inner (good)' as 'h_xx';
2004 insert into t3 values (1);
2029 drop procedure h_ee|
2030 drop procedure h_es|
2031 drop procedure h_en|
2032 drop procedure h_ew|
2033 drop procedure h_ex|
2034 drop procedure h_se|
2035 drop procedure h_ss|
2036 drop procedure h_sn|
2037 drop procedure h_sw|
2038 drop procedure h_sx|
2039 drop procedure h_ne|
2040 drop procedure h_ns|
2041 drop procedure h_nn|
2042 drop procedure h_we|
2043 drop procedure h_ws|
2044 drop procedure h_ww|
2045 drop procedure h_xe|
2046 drop procedure h_xs|
2047 drop procedure h_xx|
2051 # Test cases for old bugs
2058 drop procedure if exists bug822|
2060 create procedure bug822(a_id char(16), a_data int)
2063 select count(*) into n from t1 where id = a_id and data = a_data;
2065 insert into t1 (id, data) values (a_id, a_data);
2070 call bug822('foo', 42)|
2071 call bug822('foo', 42)|
2072 call bug822('bar', 666)|
2073 select * from t1 order by data|
2075 drop procedure bug822|
2081 drop procedure if exists bug1495|
2083 create procedure bug1495()
2087 select data into x from t1 order by id limit 1;
2089 insert into t1 values ("less", x-10);
2091 insert into t1 values ("more", x+10);
2095 insert into t1 values ('foo', 12)|
2097 delete from t1 where id='foo'|
2098 insert into t1 values ('bar', 7)|
2100 delete from t1 where id='bar'|
2101 select * from t1 order by data|
2103 drop procedure bug1495|
2109 drop procedure if exists bug1547|
2111 create procedure bug1547(s char(16))
2115 select data into x from t1 where s = id limit 1;
2117 insert into t1 values ("less", x-10);
2119 insert into t1 values ("more", x+10);
2123 insert into t1 values ("foo", 12), ("bar", 7)|
2124 call bug1547("foo")|
2125 call bug1547("bar")|
2126 select * from t1 order by id|
2128 drop procedure bug1547|
2134 drop table if exists t70|
2136 create table t70 (s1 int,s2 int)|
2137 insert into t70 values (1,2)|
2140 drop procedure if exists bug1656|
2142 create procedure bug1656(out p1 int, out p2 int)
2143 select * into p1, p1 from t70|
2145 call bug1656(@1, @2)|
2148 drop procedure bug1656|
2153 create table t3(a int)|
2156 drop procedure if exists bug1862|
2158 create procedure bug1862()
2160 insert into t3 values(2);
2165 # the second call caused a segmentation
2169 drop procedure bug1862|
2175 drop procedure if exists bug1874|
2177 create procedure bug1874()
2181 select max(data) into x from t1;
2182 insert into t2 values ("max", x, 0);
2183 select min(data) into x from t1;
2184 insert into t2 values ("min", x, 0);
2185 select sum(data) into x from t1;
2186 insert into t2 values ("sum", x, 0);
2187 select avg(data) into y from t1;
2188 insert into t2 values ("avg", 0, y);
2191 insert into t1 (data) values (3), (1), (5), (9), (4)|
2193 select * from t2 order by i|
2196 drop procedure bug1874|
2202 drop procedure if exists bug2260|
2204 create procedure bug2260()
2207 declare c1 cursor for select data from t1;
2208 declare continue handler for not found set @x2 = 1;
2218 drop procedure bug2260|
2221 # BUG#2267 "Lost connect if stored procedure has SHOW FUNCTION STATUS"
2224 drop procedure if exists bug2267_1|
2226 create procedure bug2267_1()
2228 show procedure status where db='test';
2232 drop procedure if exists bug2267_2|
2234 create procedure bug2267_2()
2236 show function status where db='test';
2240 drop procedure if exists bug2267_3|
2242 create procedure bug2267_3()
2244 show create procedure bug2267_1;
2248 drop procedure if exists bug2267_4|
2249 drop function if exists bug2267_4|
2251 create procedure bug2267_4()
2253 show create function bug2267_4;
2255 create function bug2267_4() returns int return 100|
2257 --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
2259 --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
2264 drop procedure bug2267_1|
2265 drop procedure bug2267_2|
2266 drop procedure bug2267_3|
2267 drop procedure bug2267_4|
2268 drop function bug2267_4|
2274 drop procedure if exists bug2227|
2276 create procedure bug2227(x int)
2278 declare y float default 2.6;
2279 declare z char(16) default "zzz";
2281 select 1.3, x, y, 42, z;
2285 drop procedure bug2227|
2288 # BUG#2614 "Stored procedure with INSERT ... SELECT that does not
2289 # contain any tables crashes server"
2292 drop procedure if exists bug2614|
2294 create procedure bug2614()
2296 drop table if exists t3;
2297 create table t3 (id int default '0' not null);
2298 insert into t3 select 12;
2299 insert into t3 select * from t3;
2307 drop procedure bug2614|
2313 drop function if exists bug2674|
2315 create function bug2674() returns int
2316 return @@sort_buffer_size|
2318 set @osbs = @@sort_buffer_size|
2319 set @@sort_buffer_size = 262000|
2321 drop function bug2674|
2322 set @@sort_buffer_size = @osbs|
2328 drop procedure if exists bug3259_1 |
2330 create procedure bug3259_1 () begin end|
2332 drop procedure if exists BUG3259_2 |
2334 create procedure BUG3259_2 () begin end|
2336 drop procedure if exists Bug3259_3 |
2338 create procedure Bug3259_3 () begin end|
2347 drop procedure bUg3259_1|
2348 drop procedure BuG3259_2|
2349 drop procedure BUG3259_3|
2355 drop function if exists bug2772|
2357 create function bug2772() returns char(10) character set latin2
2361 drop function bug2772|
2367 drop procedure if exists bug2776_1|
2369 create procedure bug2776_1(out x int)
2378 drop procedure if exists bug2776_2|
2380 create procedure bug2776_2(out x int)
2382 declare v int default 42;
2393 drop procedure bug2776_1|
2394 drop procedure bug2776_2|
2399 create table t3 (s1 smallint)|
2401 insert into t3 values (123456789012)|
2404 drop procedure if exists bug2780|
2406 create procedure bug2780()
2408 declare exit handler for sqlwarning set @x = 1;
2411 insert into t3 values (123456789012);
2412 insert into t3 values (0);
2419 drop procedure bug2780|
2425 create table t3 (content varchar(10) )|
2426 insert into t3 values ("test1")|
2427 insert into t3 values ("test2")|
2428 create table t4 (f1 int, rc int, t3 int)|
2431 drop procedure if exists bug1863|
2433 create procedure bug1863(in1 int)
2436 declare ind int default 0;
2441 declare rc int default 0;
2442 declare continue handler for 1065 set rc = 1;
2444 drop temporary table if exists temp_t1;
2445 create temporary table temp_t1 (
2446 f1 int auto_increment, f2 varchar(20), primary key (f1)
2449 insert into temp_t1 (f2) select content from t3;
2451 select f2 into t3 from temp_t1 where f1 = 10;
2454 insert into t4 values (1, rc, t3);
2457 insert into t4 values (2, rc, t3);
2465 drop procedure bug1863|
2466 drop temporary table temp_t1;
2474 OrderID int not null,
2476 primary key (OrderID)
2480 MarketID int not null,
2483 primary key (MarketID)
2486 insert t3 (OrderID,MarketID) values (1,1)|
2487 insert t3 (OrderID,MarketID) values (2,2)|
2488 insert t4 (MarketID,Market,Status) values (1,"MarketID One","A")|
2489 insert t4 (MarketID,Market,Status) values (2,"MarketID Two","A")|
2492 drop procedure if exists bug2656_1|
2494 create procedure bug2656_1()
2499 ON o.MarketID != 1 and o.MarketID = m.MarketID;
2503 drop procedure if exists bug2656_2|
2505 create procedure bug2656_2()
2512 m.MarketID != 1 and m.MarketID = o.MarketID;
2520 drop procedure bug2656_1|
2521 drop procedure bug2656_2|
2529 drop procedure if exists bug3426|
2531 create procedure bug3426(in_time int unsigned, out x int)
2533 if in_time is null then
2534 set @stamped_time=10;
2537 set @stamped_time=in_time;
2542 # so that from_unixtime() has a deterministic result
2543 set time_zone='+03:00';
2545 call bug3426(1000, @i)|
2546 select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
2547 call bug3426(NULL, @i)|
2548 select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
2550 alter procedure bug3426 sql security invoker|
2551 call bug3426(NULL, @i)|
2552 select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
2553 call bug3426(1000, @i)|
2554 select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time|
2556 drop procedure bug3426|
2562 id int unsigned auto_increment not null primary key,
2565 fulltext (title,body)
2568 insert into t3 (title,body) values
2569 ('MySQL Tutorial','DBMS stands for DataBase ...'),
2570 ('How To Use MySQL Well','After you went through a ...'),
2571 ('Optimizing MySQL','In this tutorial we will show ...'),
2572 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
2573 ('MySQL vs. YourSQL','In the following database comparison ...'),
2574 ('MySQL Security','When configured properly, MySQL ...')|
2577 drop procedure if exists bug3734 |
2579 create procedure bug3734 (param1 varchar(100))
2580 select * from t3 where match (title,body) against (param1)|
2582 call bug3734('database')|
2583 call bug3734('Security')|
2585 drop procedure bug3734|
2592 drop procedure if exists bug3863|
2594 create procedure bug3863()
2607 drop procedure bug3863|
2614 id int(10) unsigned not null default 0,
2615 rid int(10) unsigned not null default 0,
2618 unique key rid (rid, id)
2622 drop procedure if exists bug2460_1|
2624 create procedure bug2460_1(in v int)
2626 ( select n0.id from t3 as n0 where n0.id = v )
2628 ( select n0.id from t3 as n0, t3 as n1
2629 where n0.id = n1.rid and n1.id = v )
2631 ( select n0.id from t3 as n0, t3 as n1, t3 as n2
2632 where n0.id = n1.rid and n1.id = n2.rid and n2.id = v );
2637 insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')|
2642 drop procedure if exists bug2460_2|
2644 create procedure bug2460_2()
2646 drop table if exists t3;
2647 create temporary table t3 (s1 int);
2648 insert into t3 select 1 union select 1;
2655 drop procedure bug2460_1|
2656 drop procedure bug2460_2|
2663 set @@sql_mode = ''|
2665 drop procedure if exists bug2564_1|
2667 create procedure bug2564_1()
2668 comment 'Joe''s procedure'
2669 insert into `t1` values ("foo", 1)|
2671 set @@sql_mode = 'ANSI_QUOTES'|
2673 drop procedure if exists bug2564_2|
2675 create procedure bug2564_2()
2676 insert into "t1" values ('foo', 1)|
2679 set @@sql_mode = ''$
2681 drop function if exists bug2564_3$
2683 create function bug2564_3(x int, y int) returns int
2686 set @@sql_mode = 'ANSI'$
2688 drop function if exists bug2564_4$
2690 create function bug2564_4(x int, y int) returns int
2694 set @@sql_mode = ''|
2695 show create procedure bug2564_1|
2696 show create procedure bug2564_2|
2697 show create function bug2564_3|
2698 show create function bug2564_4|
2700 drop procedure bug2564_1|
2701 drop procedure bug2564_2|
2702 drop function bug2564_3|
2703 drop function bug2564_4|
2709 drop function if exists bug3132|
2711 create function bug3132(s char(20)) returns char(50)
2712 return concat('Hello, ', s, '!')|
2714 select bug3132('Bob') union all select bug3132('Judy')|
2715 drop function bug3132|
2721 drop procedure if exists bug3843|
2723 create procedure bug3843()
2726 # Testing for packets out of order
2731 drop procedure bug3843|
2736 create table t3 ( s1 char(10) )|
2737 insert into t3 values ('a'), ('b')|
2740 drop procedure if exists bug3368|
2742 create procedure bug3368(v char(10))
2744 select group_concat(v) from t3;
2749 drop procedure bug3368|
2755 create table t3 (f1 int, f2 int)|
2756 insert into t3 values (1,1)|
2759 drop procedure if exists bug4579_1|
2761 create procedure bug4579_1 ()
2765 select f1 into sf1 from t3 where f1=1 and f2=1;
2766 update t3 set f2 = f2 + 1 where f1=1 and f2=1;
2771 drop procedure if exists bug4579_2|
2773 create procedure bug4579_2 ()
2781 drop procedure bug4579_1|
2782 drop procedure bug4579_2|
2786 # BUG#2773: Function's data type ignored in stored procedures
2789 drop procedure if exists bug2773|
2792 create function bug2773() returns int return null|
2793 create table t3 as select bug2773()|
2794 show create table t3|
2796 drop function bug2773|
2799 # BUG#3788: Stored procedure packet error
2802 drop procedure if exists bug3788|
2805 create function bug3788() returns date return cast("2005-03-04" as date)|
2807 drop function bug3788|
2809 create function bug3788() returns binary(1) return 5|
2811 drop function bug3788|
2817 create table t3 (f1 int, f2 int, f3 int)|
2818 insert into t3 values (1,1,1)|
2821 drop procedure if exists bug4726|
2823 create procedure bug4726()
2825 declare tmp_o_id INT;
2826 declare tmp_d_id INT default 1;
2828 while tmp_d_id <= 2 do
2830 select f1 into tmp_o_id from t3 where f2=1 and f3=1;
2831 set tmp_d_id = tmp_d_id + 1;
2840 drop procedure bug4726|
2847 --disable_parsing # Don't know if HANDLER commands can work with SPs, or at all..
2848 create table t3 (s1 int)|
2849 insert into t3 values (3), (4)|
2852 drop procedure if exists bug4318|
2854 create procedure bug4318()
2855 handler t3 read next|
2858 # Expect no results, as tables are closed, but there shouldn't be any errors
2863 drop procedure bug4318|
2868 # BUG#4902: Stored procedure with SHOW WARNINGS leads to packet error
2870 # Added tests for most other show commands we could find too.
2871 # (Skipping those already tested, and the ones depending on optional handlers.)
2873 # Note: This will return a large number of results of different formats,
2874 # which makes it impossible to filter with --replace_column.
2875 # It's possible that some of these are not deterministic across
2876 # platforms. If so, just remove the offending command.
2879 drop procedure if exists bug4902|
2881 create procedure bug4902()
2883 show charset like 'foo';
2884 show collation like 'foo';
2886 show create table t1;
2887 show create database test;
2888 show databases like 'foo';
2890 show columns from t1;
2892 show open tables like 'foo';
2893 # Removed because result will differ in embedded mode.
2895 show status like 'foo';
2896 show tables like 'foo';
2897 show variables like 'foo';
2901 --replace_regex /table_id: [0-9]+/table_id: #/
2903 show storage engines|
2912 drop procedure bug4902|
2918 drop procedure if exists bug4904|
2920 create procedure bug4904()
2922 declare continue handler for sqlstate 'HY000' begin end;
2924 create table t2 as select * from t3;
2930 drop procedure bug4904|
2932 create table t3 (s1 char character set latin1, s2 char character set latin2)|
2935 drop procedure if exists bug4904|
2937 create procedure bug4904 ()
2939 declare continue handler for sqlstate 'HY000' begin end;
2941 select s1 from t3 union select s2 from t3;
2946 drop procedure bug4904|
2953 drop procedure if exists bug336|
2955 create procedure bug336(out y int)
2958 set x = (select sum(t.data) from test.t1 t);
2962 insert into t1 values ("a", 2), ("b", 3)|
2966 drop procedure bug336|
2972 drop procedure if exists bug3157|
2974 create procedure bug3157()
2976 if exists(select * from t1) then
2979 if (select count(*) from t1) then
2985 insert into t1 values ("a", 1)|
2989 drop procedure bug3157|
2992 # BUG#5251: mysql changes creation time of a procedure/function when altering
2995 drop procedure if exists bug5251|
2997 create procedure bug5251()
3001 select created into @c1 from mysql.proc
3002 where db='test' and name='bug5251'|
3004 alter procedure bug5251 comment 'foobar'|
3005 select count(*) from mysql.proc
3006 where db='test' and name='bug5251' and created = @c1|
3008 drop procedure bug5251|
3011 # BUG#5279: Stored procedure packets out of order if CHECKSUM TABLE
3014 drop procedure if exists bug5251|
3016 create procedure bug5251()
3021 drop procedure bug5251|
3024 # BUG#5287: Stored procedure crash if leave outside loop
3027 drop procedure if exists bug5287|
3029 create procedure bug5287(param1 int)
3032 declare c cursor for select 5;
3041 drop procedure bug5287|
3045 # BUG#5307: Stored procedure allows statement after BEGIN ... END
3048 drop procedure if exists bug5307|
3050 create procedure bug5307()
3056 drop procedure bug5307|
3059 # BUG#5258: Stored procedure modified date is 0000-00-00
3060 # (This was a design flaw)
3062 drop procedure if exists bug5258|
3064 create procedure bug5258()
3069 drop procedure if exists bug5258_aux|
3071 create procedure bug5258_aux()
3073 declare c, m char(19);
3075 select created,modified into c,m from mysql.proc where name = 'bug5258';
3085 drop procedure bug5258|
3086 drop procedure bug5258_aux|
3089 # BUG#4487: Stored procedure connection aborted if uninitialized char
3092 drop function if exists bug4487|
3094 create function bug4487() returns char
3101 drop function bug4487|
3105 # BUG#4941: Stored procedure crash fetching null value into variable.
3108 drop procedure if exists bug4941|
3111 drop procedure if exists bug4941|
3113 create procedure bug4941(out x int)
3115 declare c cursor for select i from t2 limit 1;
3121 insert into t2 values (null, null, null)|
3126 drop procedure bug4941|
3129 # BUG#4905: Stored procedure doesn't clear for "Rows affected"
3132 drop procedure if exists bug4905|
3135 create table t3 (s1 int,primary key (s1))|
3138 drop procedure if exists bug4905|
3140 create procedure bug4905()
3143 declare continue handler for sqlstate '23000' set v = 5;
3145 insert into t3 values (1);
3156 drop procedure bug4905|
3160 # BUG#6022: Stored procedure shutdown problem with self-calling function.
3163 --disable_parsing # until we implement support for recursive stored functions.
3165 drop function if exists bug6022|
3169 drop function if exists bug6022|
3171 create function bug6022(x int) returns int
3176 return bug6022(x-1);
3181 drop function bug6022|
3185 # BUG#6029: Stored procedure specific handlers should have priority
3188 drop procedure if exists bug6029|
3192 drop procedure if exists bug6029|
3194 create procedure bug6029()
3196 declare exit handler for 1136 select '1136';
3197 declare exit handler for sqlstate '23000' select 'sqlstate 23000';
3198 declare continue handler for sqlexception select 'sqlexception';
3200 insert into t3 values (1);
3201 insert into t3 values (1,2);
3204 create table t3 (s1 int, primary key (s1))|
3205 insert into t3 values (1)|
3210 drop procedure bug6029|
3214 # BUG#8540: Local variable overrides an alias
3217 drop procedure if exists bug8540|
3220 create procedure bug8540()
3222 declare x int default 1;
3223 select x as y, x+0 as z;
3227 drop procedure bug8540|
3230 # BUG#6642: Stored procedure crash if expression with set function
3232 create table t3 (s1 int)|
3235 drop procedure if exists bug6642|
3238 create procedure bug6642()
3239 select abs(count(s1)) from t3|
3243 drop procedure bug6642|
3246 # BUG#7013: Stored procedure crash if group by ... with rollup
3248 insert into t3 values (0),(1)|
3250 drop procedure if exists bug7013|
3252 create procedure bug7013()
3253 select s1,count(s1) from t3 group by s1 with rollup|
3256 drop procedure bug7013|
3259 # BUG#7743: 'Lost connection to MySQL server during query' on Stored Procedure
3262 drop table if exists t4|
3265 a mediumint(8) unsigned not null auto_increment,
3266 b smallint(5) unsigned not null,
3267 c char(32) not null,
3269 ) engine=myisam default charset=latin1|
3270 insert into t4 values (1, 2, 'oneword')|
3271 insert into t4 values (2, 2, 'anotherword')|
3274 drop procedure if exists bug7743|
3276 create procedure bug7743 ( searchstring char(28) )
3278 declare var mediumint(8) unsigned;
3279 select a into var from t4 where b = 2 and c = binary searchstring limit 1;
3283 call bug7743("oneword")|
3284 call bug7743("OneWord")|
3285 call bug7743("anotherword")|
3286 call bug7743("AnotherWord")|
3287 drop procedure bug7743|
3291 # BUG#7992: SELECT .. INTO variable .. within Stored Procedure crashes
3295 insert into t3 values(1)|
3296 drop procedure if exists bug7992_1|
3297 drop procedure if exists bug7992_2|
3298 create procedure bug7992_1()
3301 select max(s1)+1 into i from t3;
3303 create procedure bug7992_2()
3304 insert into t3 (s1) select max(t4.s1)+1 from t3 as t4|
3311 drop procedure bug7992_1|
3312 drop procedure bug7992_2|
3316 # BUG#8116: calling simple stored procedure twice in a row results
3319 create table t3 ( userid bigint(20) not null default 0 )|
3322 drop procedure if exists bug8116|
3324 create procedure bug8116(in _userid int)
3325 select * from t3 where userid = _userid|
3329 drop procedure bug8116|
3333 # BUG#6857: current_time() in STORED PROCEDURES
3336 drop procedure if exists bug6857|
3338 create procedure bug6857(counter int)
3341 declare plus bool default 0;
3343 set t0 = current_time();
3344 while counter > 0 do
3345 set counter = counter - 1;
3347 set t1 = current_time();
3354 # QQ: This is currently disabled. Not only does it slow down a normal test
3355 # run, it makes running with valgrind (or similar tools) extremely
3357 # Make sure this takes at least one second on all machines in all builds.
3358 # 30000 makes it about 3 seconds on an old 1.1GHz linux.
3359 #call bug6857(300000)|
3361 drop procedure bug6857|
3364 # BUG#8757: Stored Procedures: Scope of Begin and End Statements do not
3367 drop procedure if exists bug8757|
3369 create procedure bug8757()
3372 declare c1 cursor for select data from t1 limit 1;
3376 declare c2 cursor for select i from t2 limit 1;
3391 insert into t1 values ("x", 1)|
3392 insert into t2 values ("y", 2, 0.0)|
3398 drop procedure bug8757|
3402 # BUG#8762: Stored Procedures: Inconsistent behavior
3403 # of DROP PROCEDURE IF EXISTS statement.
3405 drop procedure if exists bug8762|
3408 drop procedure if exists bug8762; create procedure bug8762() begin end|
3410 drop procedure if exists bug8762; create procedure bug8762() begin end|
3411 drop procedure bug8762|
3415 # BUG#5240: Stored procedure crash if function has cursor declaration
3418 drop function if exists bug5240|
3420 create function bug5240 () returns int
3423 declare c cursor for select data from t1 limit 1;
3432 insert into t1 values ("answer", 42)|
3433 select id, bug5240() from t1|
3434 drop function bug5240|
3437 # BUG#7992: rolling back temporary Item tree changes in SP
3440 drop procedure if exists p1|
3442 create table t3(id int)|
3443 insert into t3 values(1)|
3444 create procedure bug7992()
3447 select max(id)+1 into i from t3;
3452 drop procedure bug7992|
3457 # BUG#8849: problem with insert statement with table alias's
3459 # Rolling back changes to AND/OR structure of ON and WHERE clauses in SP
3464 lpitnumber int(11) default null,
3465 lrecordtype int(11) default null
3469 lbsiid int(11) not null default '0',
3470 ltradingmodeid int(11) not null default '0',
3471 ltradingareaid int(11) not null default '0',
3472 csellingprice decimal(19,4) default null,
3473 primary key (lbsiid,ltradingmodeid,ltradingareaid)
3477 lbsiid int(11) not null default '0',
3478 ltradingareaid int(11) not null default '0',
3479 primary key (lbsiid,ltradingareaid)
3483 drop procedure if exists bug8849|
3485 create procedure bug8849()
3492 select distinct t3.lpitnumber, t4.ltradingareaid
3495 t3.lpitnumber = t4.lbsiid
3496 and t3.lrecordtype = 1
3497 left join t4 as price01 on
3498 price01.lbsiid = t4.lbsiid and
3499 price01.ltradingmodeid = 1 and
3500 t4.ltradingareaid = price01.ltradingareaid;
3506 drop procedure bug8849|
3507 drop tables t3,t4,t5|
3510 # BUG#8937: Stored Procedure: AVG() works as SUM() in SELECT ... INTO statement
3513 drop procedure if exists bug8937|
3515 create procedure bug8937()
3517 declare s,x,y,z int;
3520 select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1;
3522 select avg(data) into a from t1;
3527 insert into t1 (data) values (1), (2), (3), (4), (6)|
3530 drop procedure bug8937|
3535 # BUG#6900: Stored procedure inner handler ignored
3536 # BUG#9074: STORED PROC: The scope of every handler declared is not
3540 drop procedure if exists bug6900|
3541 drop procedure if exists bug9074|
3542 drop procedure if exists bug6900_9074|
3545 create table t3 (w char unique, x char)|
3546 insert into t3 values ('a', 'b')|
3548 create procedure bug6900()
3550 declare exit handler for sqlexception select '1';
3553 declare exit handler for sqlexception select '2';
3555 insert into t3 values ('x', 'y', 'z');
3559 create procedure bug9074()
3561 declare x1, x2, x3, x4, x5, x6 int default 0;
3564 declare continue handler for sqlstate '23000' set x5 = 1;
3566 insert into t3 values ('a', 'b');
3572 declare continue handler for sqlstate '23000' set x1 = 1;
3574 insert into t3 values ('a', 'b');
3579 declare exit handler for sqlstate '23000' set x3 = 1;
3582 insert into t3 values ('a','b');
3587 select x1, x2, x3, x4, x5, x6;
3590 create procedure bug6900_9074(z int)
3592 declare exit handler for sqlstate '23000' select '23000';
3595 declare exit handler for sqlexception select 'sqlexception';
3598 insert into t3 values ('a', 'b');
3600 insert into t3 values ('x', 'y', 'z');
3607 call bug6900_9074(0)|
3608 call bug6900_9074(1)|
3610 drop procedure bug6900|
3611 drop procedure bug9074|
3612 drop procedure bug6900_9074|
3617 # BUG#7185: Stored procedure crash if identifier is AVG
3620 drop procedure if exists avg|
3622 create procedure avg ()
3631 # BUG#6129: Stored procedure won't display @@sql_mode value
3634 drop procedure if exists bug6129|
3636 set @old_mode= @@sql_mode;
3637 set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO";
3638 create procedure bug6129()
3641 set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"|
3643 set @@sql_mode= "NO_ZERO_IN_DATE"|
3645 set @@sql_mode=@old_mode;
3647 drop procedure bug6129|
3651 # BUG#9856: Stored procedures: crash if handler for sqlexception, not found
3654 drop procedure if exists bug9856|
3656 create procedure bug9856()
3659 declare c cursor for select data from t1;
3660 declare exit handler for sqlexception, not found select '16';
3670 drop procedure bug9856|
3674 # BUG##9674: Stored Procs: Using declared vars in algebric operation causes
3678 drop procedure if exists bug9674_1|
3679 drop procedure if exists bug9674_2|
3681 create procedure bug9674_1(out arg int)
3683 declare temp_in1 int default 0;
3684 declare temp_fl1 int default 0;
3687 set temp_fl1 = temp_in1/10;
3691 create procedure bug9674_2()
3693 declare v int default 100;
3698 call bug9674_1(@sptmp)|
3699 call bug9674_1(@sptmp)|
3703 drop procedure bug9674_1|
3704 drop procedure bug9674_2|
3708 # BUG#9598: stored procedure call within stored procedure overwrites IN variable
3711 drop procedure if exists bug9598_1|
3712 drop procedure if exists bug9598_2|
3714 create procedure bug9598_1(in var_1 char(16),
3715 out var_2 integer, out var_3 integer)
3721 create procedure bug9598_2(in v1 char(16),
3727 select v1,v2,v3,v4,v5;
3728 call bug9598_1(v1,@tmp1,@tmp2);
3729 select v1,v2,v3,v4,v5;
3732 call bug9598_2('Test',2,3,4,5)|
3733 select @tmp1, @tmp2|
3735 drop procedure bug9598_1|
3736 drop procedure bug9598_2|
3740 # BUG#9902: Crash with simple stored function using user defined variables
3743 drop procedure if exists bug9902|
3745 create function bug9902() returns int(11)
3751 set @qcs1 = @@query_cache_size|
3752 set global query_cache_size = 100000|
3754 insert into t1 values ("qc", 42)|
3755 select bug9902() from t1|
3756 select bug9902() from t1|
3759 set global query_cache_size = @qcs1|
3761 drop function bug9902|
3765 # BUG#9102: Stored proccedures: function which returns blob causes crash
3768 drop function if exists bug9102|
3770 create function bug9102() returns blob return 'a'|
3772 drop function bug9102|
3776 # BUG#7648: Stored procedure crash when invoking a function that returns a bit
3779 drop function if exists bug7648|
3781 create function bug7648() returns bit(8) return 'a'|
3783 drop function bug7648|
3787 # BUG#9775: crash if create function that returns enum or set
3790 drop function if exists bug9775|
3792 create function bug9775(v1 char(1)) returns enum('a','b') return v1|
3793 select bug9775('a'),bug9775('b'),bug9775('c')|
3794 drop function bug9775|
3795 create function bug9775(v1 int) returns enum('a','b') return v1|
3796 select bug9775(1),bug9775(2),bug9775(3)|
3797 drop function bug9775|
3799 create function bug9775(v1 char(1)) returns set('a','b') return v1|
3800 select bug9775('a'),bug9775('b'),bug9775('a,b'),bug9775('c')|
3801 drop function bug9775|
3802 create function bug9775(v1 int) returns set('a','b') return v1|
3803 select bug9775(1),bug9775(2),bug9775(3),bug9775(4)|
3804 drop function bug9775|
3808 # BUG#8861: If Return is a YEAR data type, value is not shown in year format
3811 drop function if exists bug8861|
3813 create function bug8861(v1 int) returns year return v1|
3815 set @x = bug8861(05)|
3817 drop function bug8861|
3821 # BUG#9004: Inconsistent behaviour of SP re. warnings
3824 drop procedure if exists bug9004_1|
3825 drop procedure if exists bug9004_2|
3827 create procedure bug9004_1(x char(16))
3829 insert into t1 values (x, 42);
3830 insert into t1 values (x, 17);
3832 create procedure bug9004_2(x char(16))
3835 # Truncation warnings expected...
3836 call bug9004_1('12345678901234567')|
3837 call bug9004_2('12345678901234567890')|
3840 drop procedure bug9004_1|
3841 drop procedure bug9004_2|
3844 # BUG#7293: Stored procedure crash with soundex
3847 drop procedure if exists bug7293|
3849 insert into t1 values ('secret', 0)|
3850 create procedure bug7293(p1 varchar(100))
3852 if exists (select id from t1 where soundex(p1)=soundex(id)) then
3856 call bug7293('secret')|
3857 call bug7293 ('secrete')|
3858 drop procedure bug7293|
3863 # BUG#9841: Unexpected read lock when trying to update a view in a
3867 drop procedure if exists bug9841|
3868 drop view if exists v1|
3871 create view v1 as select * from t1, t2 where id = s|
3872 create procedure bug9841 ()
3873 update v1 set data = 10|
3877 drop procedure bug9841|
3881 # BUG#5963 subqueries in SET/IF
3884 drop procedure if exists bug5963|
3887 create procedure bug5963_1 () begin declare v int; set v = (select s1 from t3); select v; end;|
3888 create table t3 (s1 int)|
3889 insert into t3 values (5)|
3892 drop procedure bug5963_1|
3895 create procedure bug5963_2 (cfk_value int)
3897 if cfk_value in (select cpk from t3) then
3902 create table t3 (cpk int)|
3903 insert into t3 values (1)|
3906 drop procedure bug5963_2|
3911 # BUG#9559: Functions: Numeric Operations using -ve value gives incorrect
3915 drop function if exists bug9559|
3917 create function bug9559()
3926 drop function bug9559|
3930 # BUG#10961: Stored procedures: crash if select * from dual
3933 drop procedure if exists bug10961|
3935 # "select * from dual" results in an error, so the cursor will not open
3936 create procedure bug10961()
3940 declare c cursor for select * from dual;
3941 declare continue handler for sqlexception select x;
3954 drop procedure bug10961|
3957 # BUG #6866: Second call of a stored procedure using a view with on expressions
3961 DROP PROCEDURE IF EXISTS bug6866|
3964 DROP VIEW IF EXISTS tv|
3965 DROP TABLE IF EXISTS tt1,tt2,tt3|
3967 CREATE TABLE tt1 (a1 int, a2 int, a3 int, data varchar(10))|
3968 CREATE TABLE tt2 (a2 int, data2 varchar(10))|
3969 CREATE TABLE tt3 (a3 int, data3 varchar(10))|
3971 INSERT INTO tt1 VALUES (1, 1, 4, 'xx')|
3973 INSERT INTO tt2 VALUES (1, 'a')|
3974 INSERT INTO tt2 VALUES (2, 'b')|
3975 INSERT INTO tt2 VALUES (3, 'c')|
3977 INSERT INTO tt3 VALUES (4, 'd')|
3978 INSERT INTO tt3 VALUES (5, 'e')|
3979 INSERT INTO tt3 VALUES (6, 'f')|
3982 SELECT tt1.*, tt2.data2, tt3.data3
3983 FROM tt1 INNER JOIN tt2 ON tt1.a2 = tt2.a2
3984 LEFT JOIN tt3 ON tt1.a3 = tt3.a3
3985 ORDER BY tt1.a1, tt2.a2, tt3.a3|
3987 CREATE PROCEDURE bug6866 (_a1 int)
3989 SELECT * FROM tv WHERE a1 = _a1;
3996 DROP PROCEDURE bug6866;
3999 DROP TABLE tt1, tt2, tt3|
4002 # BUG#10136: items cleunup
4005 DROP PROCEDURE IF EXISTS bug10136|
4007 create table t3 ( name char(5) not null primary key, val float not null)|
4008 insert into t3 values ('aaaaa', 1), ('bbbbb', 2), ('ccccc', 3)|
4009 create procedure bug10136()
4011 declare done int default 3;
4015 set done = done - 1;
4016 until done <= 0 end repeat;
4022 drop procedure bug10136|
4026 # BUG#11529: crash server after use stored procedure
4029 drop procedure if exists bug11529|
4031 create procedure bug11529()
4033 declare c cursor for select id, data from t1 where data in (10,13);
4037 declare vid char(16);
4039 declare exit handler for not found begin end;
4042 fetch c into vid, vdata;
4048 insert into t1 values
4058 drop procedure bug11529|
4062 # BUG#6063: Stored procedure labels are subject to restrictions (partial)
4063 # BUG#7088: Stored procedures: labels won't work if character set is utf8
4066 drop procedure if exists bug6063|
4067 drop procedure if exists bug7088_1|
4068 drop procedure if exists bug7088_2|
4071 --disable_parsing # temporarily disabled until Bar fixes BUG#11986
4072 create procedure bug6063()
4075 # QQ Known bug: this will not show the label correctly.
4076 show create procedure bug6063|
4078 set character set utf8|
4079 create procedure bug7088_1()
4080 label1: begin end label1|
4081 create procedure bug7088_2()
4085 set character set default|
4086 show create procedure bug7088_1|
4087 show create procedure bug7088_2|
4089 drop procedure bug6063|
4090 drop procedure bug7088_1|
4091 drop procedure bug7088_2|
4095 # BUG#9565: "Wrong locking in stored procedure if a sub-sequent procedure
4099 drop procedure if exists bug9565_sub|
4100 drop procedure if exists bug9565|
4102 create procedure bug9565_sub()
4106 create procedure bug9565()
4108 insert into t1 values ("one", 1);
4113 drop procedure bug9565_sub|
4114 drop procedure bug9565|
4118 # BUG#9538: SProc: Creation fails if we try to SET system variable
4119 # using @@var_name in proc
4122 drop procedure if exists bug9538|
4124 create procedure bug9538()
4125 set @@sort_buffer_size = 1000000|
4127 set @x = @@sort_buffer_size|
4128 set @@sort_buffer_size = 2000000|
4129 select @@sort_buffer_size|
4131 select @@sort_buffer_size|
4132 set @@sort_buffer_size = @x|
4134 drop procedure bug9538|
4138 # BUG#8692: Cursor fetch of empty string
4141 drop procedure if exists bug8692|
4143 create table t3 (c1 varchar(5), c2 char(5), c3 enum('one','two'), c4 text, c5 blob, c6 char(5), c7 varchar(5))|
4144 insert into t3 values ('', '', '', '', '', '', NULL)|
4146 create procedure bug8692()
4148 declare v1 VARCHAR(10);
4149 declare v2 VARCHAR(10);
4150 declare v3 VARCHAR(10);
4151 declare v4 VARCHAR(10);
4152 declare v5 VARCHAR(10);
4153 declare v6 VARCHAR(10);
4154 declare v7 VARCHAR(10);
4155 declare c8692 cursor for select c1,c2,c3,c4,c5,c6,c7 from t3;
4157 fetch c8692 into v1,v2,v3,v4,v5,v6,v7;
4158 select v1, v2, v3, v4, v5, v6, v7;
4162 drop procedure bug8692|
4166 # Bug#10055 "Using stored function with information_schema causes empty
4170 drop function if exists bug10055|
4172 create function bug10055(v char(255)) returns char(255) return lower(v)|
4173 # This select should not crash server and should return all fields in t1
4174 select t.column_name, bug10055(t.column_name)
4175 from information_schema.columns as t
4176 where t.table_schema = 'test' and t.table_name = 't1'|
4177 drop function bug10055|
4180 # Bug #12297 "SP crashes the server if data inserted inside a lon loop"
4181 # The test for memleak bug, so actually there is no way to test it
4182 # from the suite. The test below could be used to check SP memory
4183 # consumption by passing large input parameter.
4187 drop procedure if exists bug12297|
4190 create procedure bug12297(lim int)
4194 insert into t1(id,data)
4202 drop procedure bug12297|
4205 # Bug #11247 "Stored procedures: Function calls in long loops leak memory"
4206 # One more memleak bug test. One could use this test to check that the memory
4207 # isn't leaking by increasing the input value for p_bug11247.
4211 drop function if exists f_bug11247|
4212 drop procedure if exists p_bug11247|
4215 create function f_bug11247(param int)
4219 create procedure p_bug11247(lim int)
4221 declare v int default 0;
4224 set v= f_bug11247(v);
4228 call p_bug11247(10)|
4229 drop function f_bug11247|
4230 drop procedure p_bug11247|
4232 # BUG#12168: "'DECLARE CONTINUE HANDLER FOR NOT FOUND ...' in conditional
4233 # handled incorrectly"
4236 drop procedure if exists bug12168|
4237 drop table if exists t3, t4|
4240 create table t3 (a int)|
4241 insert into t3 values (1),(2),(3),(4)|
4243 create table t4 (a int)|
4245 create procedure bug12168(arg1 char(1))
4247 declare b, c integer;
4250 declare c1 cursor for select a from t3 where a % 2;
4251 declare continue handler for not found set b = 1;
4260 insert into t4 values (c);
4267 declare c2 cursor for select a from t3 where not a % 2;
4268 declare continue handler for not found set b = 1;
4277 insert into t4 values (c);
4297 drop procedure if exists bug12168|
4300 # Bug #11333 "Stored Procedure: Memory blow up on repeated SELECT ... INTO
4302 # One more memleak bug. Use the test to check memory consumption.
4306 drop table if exists t3|
4307 drop procedure if exists bug11333|
4310 create table t3 (c1 char(128))|
4312 insert into t3 values
4313 ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')|
4316 create procedure bug11333(i int)
4318 declare tmp varchar(128);
4321 select c1 into tmp from t3
4322 where c1 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
4330 drop procedure bug11333|
4334 # BUG#9048: Creating a function with char binary IN parameter fails
4337 drop function if exists bug9048|
4339 create function bug9048(f1 char binary) returns char
4341 set f1= concat( 'hello', f1 );
4344 drop function bug9048|
4346 # This was disabled in 5.1.12. See bug #20701
4347 # When collation support in SP is implemented, then this test should
4350 --error ER_NOT_SUPPORTED_YET
4351 create function bug9048(f1 char binary) returns char binary
4353 set f1= concat( 'hello', f1 );
4357 # Bug #12849 Stored Procedure: Crash on procedure call with CHAR type
4362 drop procedure if exists bug12849_1|
4364 create procedure bug12849_1(inout x char) select x into x|
4366 call bug12849_1(@var)|
4368 drop procedure bug12849_1|
4371 drop procedure if exists bug12849_2|
4373 create procedure bug12849_2(inout foo varchar(15))
4375 select concat(foo, foo) INTO foo;
4378 call bug12849_2(@var)|
4380 drop procedure bug12849_2|
4383 # BUG#13133: Local variables in stored procedures are not initialized correctly.
4386 drop procedure if exists bug131333|
4387 drop function if exists bug131333|
4389 create procedure bug131333()
4405 create function bug131333()
4423 drop procedure bug131333|
4424 drop function bug131333|
4427 # BUG#12379: PROCEDURE with HANDLER calling FUNCTION with error get
4431 drop function if exists bug12379|
4432 drop procedure if exists bug12379_1|
4433 drop procedure if exists bug12379_2|
4434 drop procedure if exists bug12379_3|
4435 drop table if exists t3|
4438 create table t3 (c1 char(1) primary key not null)|
4440 create function bug12379()
4443 insert into t3 values('X');
4444 insert into t3 values('X');
4448 create procedure bug12379_1()
4450 declare exit handler for sqlexception select 42;
4454 create procedure bug12379_2()
4456 declare exit handler for sqlexception begin end;
4460 create procedure bug12379_3()
4465 --error ER_DUP_ENTRY
4468 # statement-based binlogging will show warning which row-based won't;
4469 # so we hide it (this warning is already tested in rpl_stm_sp.test)
4476 --error ER_DUP_ENTRY
4480 drop function bug12379|
4481 drop procedure bug12379_1|
4482 drop procedure bug12379_2|
4483 drop procedure bug12379_3|
4487 # Bug #13124 Stored Procedure using SELECT INTO crashes server
4491 drop procedure if exists bug13124|
4493 create procedure bug13124()
4499 drop procedure bug13124|
4502 # Bug #12979 Stored procedures: crash if inout decimal parameter
4505 # check NULL inout parameters processing
4508 drop procedure if exists bug12979_1|
4510 create procedure bug12979_1(inout d decimal(5)) set d = d / 2|
4511 set @bug12979_user_var = NULL|
4512 call bug12979_1(@bug12979_user_var)|
4513 drop procedure bug12979_1|
4515 # check NULL local variables processing
4518 drop procedure if exists bug12979_2|
4520 create procedure bug12979_2()
4522 declare internal_var decimal(5);
4523 set internal_var= internal_var / 2;
4524 select internal_var;
4527 drop procedure bug12979_2|
4531 # BUG#6127: Stored procedure handlers within handlers don't work
4534 drop table if exists t3|
4535 drop procedure if exists bug6127|
4537 create table t3 (s1 int unique)|
4540 set sql_mode='traditional'|
4542 create procedure bug6127()
4544 declare continue handler for sqlstate '23000'
4546 declare continue handler for sqlstate '22003'
4547 insert into t3 values (0);
4549 insert into t3 values (1000000000000000);
4552 insert into t3 values (1);
4553 insert into t3 values (1);
4558 --error ER_DUP_ENTRY
4563 drop procedure bug6127|
4567 # BUG#12589: Assert when creating temp. table from decimal stored procedure
4571 drop procedure if exists bug12589_1|
4572 drop procedure if exists bug12589_2|
4573 drop procedure if exists bug12589_3|
4575 create procedure bug12589_1()
4577 declare spv1 decimal(3,3);
4581 create temporary table tm1 as select spv1;
4582 show create table tm1;
4583 drop temporary table tm1;
4586 create procedure bug12589_2()
4588 declare spv1 decimal(6,3);
4591 create temporary table tm1 as select spv1;
4592 show create table tm1;
4593 drop temporary table tm1;
4596 create procedure bug12589_3()
4598 declare spv1 decimal(6,3);
4601 create temporary table tm1 as select spv1;
4602 show create table tm1;
4603 drop temporary table tm1;
4606 # Note: The type of the field will match the value, not the declared
4607 # type of the variable. (This is a type checking issue which
4608 # might be changed later.)
4610 # Warning expected from "set spv1 = 'test'", the value is set to decimal "0".
4615 drop procedure bug12589_1|
4616 drop procedure bug12589_2|
4617 drop procedure bug12589_3|
4620 # BUG#7049: Stored procedure CALL errors are ignored
4623 drop table if exists t3|
4624 drop procedure if exists bug7049_1|
4625 drop procedure if exists bug7049_2|
4626 drop procedure if exists bug7049_3|
4627 drop procedure if exists bug7049_4|
4628 drop function if exists bug7049_1|
4629 drop function if exists bug7049_2|
4632 create table t3 ( x int unique )|
4634 create procedure bug7049_1()
4636 insert into t3 values (42);
4637 insert into t3 values (42);
4640 create procedure bug7049_2()
4642 declare exit handler for sqlexception
4643 select 'Caught it' as 'Result';
4646 select 'Missed it' as 'Result';
4649 create procedure bug7049_3()
4652 create procedure bug7049_4()
4654 declare exit handler for sqlexception
4655 select 'Caught it' as 'Result';
4658 select 'Missed it' as 'Result';
4661 create function bug7049_1()
4664 insert into t3 values (42);
4665 insert into t3 values (42);
4669 create function bug7049_2()
4672 declare x int default 0;
4673 declare continue handler for sqlexception
4676 set x = bug7049_1();
4688 drop procedure bug7049_1|
4689 drop procedure bug7049_2|
4690 drop procedure bug7049_3|
4691 drop procedure bug7049_4|
4692 drop function bug7049_1|
4693 drop function bug7049_2|
4697 # BUG#13941: replace() string fuction behaves badly inside stored procedure
4698 # (BUG#13914: IFNULL is returning garbage in stored procedure)
4701 drop function if exists bug13941|
4702 drop procedure if exists bug13941|
4705 create function bug13941(p_input_str text)
4708 declare p_output_str text;
4710 set p_output_str = p_input_str;
4712 set p_output_str = replace(p_output_str, 'xyzzy', 'plugh');
4713 set p_output_str = replace(p_output_str, 'test', 'prova');
4714 set p_output_str = replace(p_output_str, 'this', 'questo');
4715 set p_output_str = replace(p_output_str, ' a ', 'una ');
4716 set p_output_str = replace(p_output_str, 'is', '');
4718 return p_output_str;
4721 create procedure bug13941(out sout varchar(128))
4724 set sout = ifnull(sout, 'DEF');
4727 # Note: The bug showed different behaviour in different types of builds,
4728 # giving garbage results in some, and seemingly working in others.
4729 # Running with valgrind (or purify) is the safe way to check that it's
4730 # really working correctly.
4731 select bug13941('this is a test')|
4735 drop function bug13941|
4736 drop procedure bug13941|
4740 # BUG#13095: Cannot create VIEWs in prepared statements
4746 DROP PROCEDURE IF EXISTS bug13095;
4747 DROP TABLE IF EXISTS bug13095_t1;
4748 DROP VIEW IF EXISTS bug13095_v1;
4753 CREATE PROCEDURE bug13095(tbl_name varchar(32))
4756 CONCAT("CREATE TABLE ", tbl_name, "(stuff char(15))");
4758 PREPARE stmt FROM @str;
4762 CONCAT("INSERT INTO ", tbl_name, " VALUES('row1'),('row2'),('row3')" );
4764 PREPARE stmt FROM @str;
4768 CONCAT("CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM ", tbl_name);
4770 PREPARE stmt FROM @str;
4773 SELECT * FROM bug13095_v1;
4776 "DROP VIEW bug13095_v1";
4778 PREPARE stmt FROM @str;
4784 CALL bug13095('bug13095_t1');
4787 DROP PROCEDURE IF EXISTS bug13095;
4788 DROP VIEW IF EXISTS bug13095_v1;
4789 DROP TABLE IF EXISTS bug13095_t1;
4795 # BUG#1473: Dumping of stored functions seems to cause corruption in
4799 drop function if exists bug14723|
4800 drop procedure if exists bug14723|
4804 /*!50003 create function bug14723()
4809 show create function bug14723;;
4812 /*!50003 create procedure bug14723()
4816 show create procedure bug14723;;
4821 drop function bug14723|
4822 drop procedure bug14723|
4825 # Bug#14845 "mysql_stmt_fetch returns MYSQL_NO_DATA when COUNT(*) is 0"
4826 # Check that when fetching from a cursor, COUNT(*) works properly.
4828 create procedure bug14845()
4830 declare a char(255);
4831 declare done int default 0;
4832 declare c cursor for select count(*) from t1 where 1 = 0;
4833 declare continue handler for sqlstate '02000' set done = 1;
4840 until done end repeat;
4844 drop procedure bug14845|
4847 # BUG#13549 "Server crash with nested stored procedures".
4848 # Server should not crash when during execution of stored procedure
4849 # we have to parse trigger/function definition and this new trigger/
4850 # function has more local variables declared than invoking stored
4851 # procedure and last of these variables is used in argument of NOT
4855 drop procedure if exists bug13549_1|
4856 drop procedure if exists bug13549_2|
4858 CREATE PROCEDURE `bug13549_2`()
4862 CREATE PROCEDURE `bug13549_1`()
4864 declare done int default 0;
4868 drop procedure bug13549_2|
4869 drop procedure bug13549_1|
4872 # BUG#10100: function (and stored procedure?) recursivity problem
4875 drop function if exists bug10100f|
4876 drop procedure if exists bug10100p|
4877 drop procedure if exists bug10100t|
4878 drop procedure if exists bug10100pt|
4879 drop procedure if exists bug10100pv|
4880 drop procedure if exists bug10100pd|
4881 drop procedure if exists bug10100pc|
4883 # routines with simple recursion
4884 create function bug10100f(prm int) returns int
4887 return prm * bug10100f(prm - 1);
4891 create procedure bug10100p(prm int, inout res int)
4893 set res = res * prm;
4895 call bug10100p(prm - 1, res);
4898 create procedure bug10100t(prm int)
4902 call bug10100p(prm, res);
4906 # a procedure which use tables and recursion
4907 create table t3 (a int)|
4908 insert into t3 values (0)|
4909 create view v1 as select a from t3|
4910 create procedure bug10100pt(level int, lim int)
4913 update t3 set a=level;
4915 call bug10100pt(level+1, lim);
4921 create procedure bug10100pv(level int, lim int)
4924 update v1 set a=level;
4926 call bug10100pv(level+1, lim);
4931 # dynamic sql & recursion
4932 prepare stmt2 from "select * from t3;"|
4933 create procedure bug10100pd(level int, lim int)
4937 prepare stmt1 from "update t3 set a=a+2";
4944 deallocate prepare stmt1;
4947 call bug10100pd(level+1, lim);
4952 # cursor & recursion
4953 create procedure bug10100pc(level int, lim int)
4956 declare c cursor for select a from t3;
4962 update t3 set a=level+lv;
4964 call bug10100pc(level+1, lim);
4971 set @@max_sp_recursion_depth=4|
4972 select @@max_sp_recursion_depth|
4973 -- error ER_SP_NO_RECURSION
4974 select bug10100f(3)|
4975 -- error ER_SP_NO_RECURSION
4976 select bug10100f(6)|
4978 call bug10100pt(1,5)|
4979 call bug10100pv(1,5)|
4981 call bug10100pd(1,5)|
4984 call bug10100pc(1,5)|
4986 set @@max_sp_recursion_depth=0|
4987 select @@max_sp_recursion_depth|
4988 -- error ER_SP_NO_RECURSION
4989 select bug10100f(5)|
4990 -- error ER_SP_RECURSION_LIMIT
4993 #end of the stack checking
4994 deallocate prepare stmt2|
4996 drop function bug10100f|
4997 drop procedure bug10100p|
4998 drop procedure bug10100t|
4999 drop procedure bug10100pt|
5000 drop procedure bug10100pv|
5001 drop procedure bug10100pd|
5002 drop procedure bug10100pc|
5006 # BUG#13729: Stored procedures: packet error after exception handled
5009 drop procedure if exists bug13729|
5010 drop table if exists t3|
5013 create table t3 (s1 int, primary key (s1))|
5015 insert into t3 values (1),(2)|
5017 create procedure bug13729()
5019 declare continue handler for sqlexception select 55;
5021 update t3 set s1 = 1;
5025 # Used to cause Packets out of order
5028 drop procedure bug13729|
5032 # BUG#14643: Stored Procedure: Continuing after failed var. initialization
5036 drop procedure if exists bug14643_1|
5037 drop procedure if exists bug14643_2|
5040 create procedure bug14643_1()
5042 declare continue handler for sqlexception select 'boo' as 'Handler';
5045 declare v int default undefined_var;
5050 select v, isnull(v);
5055 create procedure bug14643_2()
5057 declare continue handler for sqlexception select 'boo' as 'Handler';
5066 select undefined_var;
5072 drop procedure bug14643_1|
5073 drop procedure bug14643_2|
5076 # BUG#14304: auto_increment field incorrect set in SP
5079 drop procedure if exists bug14304|
5080 drop table if exists t3, t4|
5083 create table t3(a int primary key auto_increment)|
5084 create table t4(a int primary key auto_increment)|
5086 create procedure bug14304()
5088 insert into t3 set a=null;
5089 insert into t4 set a=null;
5090 insert into t4 set a=null;
5091 insert into t4 set a=null;
5092 insert into t4 set a=null;
5093 insert into t4 set a=null;
5094 insert into t4 select null as a;
5096 insert into t3 set a=null;
5097 insert into t3 set a=null;
5104 drop procedure bug14304|
5108 # BUG#14376: MySQL crash on scoped variable (re)initialization
5111 drop procedure if exists bug14376|
5114 create procedure bug14376()
5116 declare x int default x;
5119 # Not the error we want, but that's what we got for now...
5120 --error ER_BAD_FIELD_ERROR
5122 drop procedure bug14376|
5124 create procedure bug14376()
5126 declare x int default 42;
5129 declare x int default x;
5137 drop procedure bug14376|
5139 create procedure bug14376(x int)
5141 declare x int default x;
5146 call bug14376(4711)|
5148 drop procedure bug14376|
5151 # Bug#5967 "Stored procedure declared variable used instead of column"
5152 # The bug should be fixed later.
5153 # Test precedence of names of parameters, variable declarations,
5154 # variable declarations in nested compound statements, table columns,
5155 # table columns in cursor declarations.
5156 # According to the standard, table columns take precedence over
5157 # variable declarations. In MySQL 5.0 it's vice versa.
5161 drop procedure if exists bug5967|
5162 drop table if exists t3|
5164 create table t3 (a varchar(255))|
5165 insert into t3 (a) values ("a - table column")|
5166 create procedure bug5967(a varchar(255))
5168 declare i varchar(255);
5169 declare c cursor for select a from t3;
5171 select a from t3 into i;
5172 select i as 'Parameter takes precedence over table column'; open c;
5175 select i as 'Parameter takes precedence over table column in cursors';
5177 declare a varchar(255) default 'a - local variable';
5178 declare c1 cursor for select a from t3;
5179 select a as 'A local variable takes precedence over parameter';
5183 select i as 'A local variable takes precedence over parameter in cursors';
5185 declare a varchar(255) default 'a - local variable in a nested compound statement';
5186 declare c2 cursor for select a from t3;
5187 select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement';
5188 select a from t3 into i;
5189 select i as 'A local variable in a nested compound statement takes precedence over table column';
5193 select i as 'A local variable in a nested compound statement takes precedence over table column in cursors';
5197 call bug5967("a - stored procedure parameter")|
5198 drop procedure bug5967|
5201 # Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server"
5203 --let $backupdir = $MYSQLTEST_VARDIR/tmp/
5205 --remove_file $backupdir/t1.frm
5207 --remove_file $backupdir/t1.MYD
5210 drop procedure if exists bug13012|
5211 # Disable warnings also for BACKUP/RESTORE: they are deprecated.
5212 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
5213 eval create procedure bug13012()
5216 BACKUP TABLE t1 to '$backupdir';
5218 RESTORE TABLE t1 FROM '$backupdir';
5223 --remove_file $backupdir/t1.frm
5224 --remove_file $backupdir/t1.MYD
5226 drop procedure bug13012|
5228 create view v1 as select * from t1|
5229 create procedure bug13012()
5231 REPAIR TABLE t1,t2,t3,v1;
5232 OPTIMIZE TABLE t1,t2,t3,v1;
5233 ANALYZE TABLE t1,t2,t3,v1;
5238 drop procedure bug13012|
5240 select * from t1 order by data|
5243 # A test case for Bug#15392 "Server crashes during prepared statement
5244 # execute": make sure that stored procedure check for error conditions
5245 # properly and do not continue execution if an error has been set.
5247 # It's necessary to use several DBs because in the original code
5248 # the successful return of mysql_change_db overrode the error from
5250 drop schema if exists mysqltest1|
5251 drop schema if exists mysqltest2|
5252 drop schema if exists mysqltest3|
5253 create schema mysqltest1|
5254 create schema mysqltest2|
5255 create schema mysqltest3|
5258 create procedure mysqltest1.p1 (out prequestid varchar(100))
5260 call mysqltest2.p2('call mysqltest3.p3(1, 2)');
5263 create procedure mysqltest2.p2(in psql text)
5267 prepare lstatement from @lsql;
5269 deallocate prepare lstatement;
5272 create procedure mysqltest3.p3(in p1 int)
5277 --error ER_SP_WRONG_NO_OF_ARGS
5278 call mysqltest1.p1(@rs)|
5279 --error ER_SP_WRONG_NO_OF_ARGS
5280 call mysqltest1.p1(@rs)|
5281 --error ER_SP_WRONG_NO_OF_ARGS
5282 call mysqltest1.p1(@rs)|
5283 drop schema if exists mysqltest1|
5284 drop schema if exists mysqltest2|
5285 drop schema if exists mysqltest3|
5289 # Bug#15441 "Running SP causes Server to Crash": check that an SP variable
5290 # can not be used in VALUES() function.
5293 drop table if exists t3|
5294 drop procedure if exists bug15441|
5296 create table t3 (id int not null primary key, county varchar(25))|
5297 insert into t3 (id, county) values (1, 'York')|
5299 # First check that a stored procedure that refers to a parameter in VALUES()
5300 # function won't parse.
5302 create procedure bug15441(c varchar(25))
5304 update t3 set id=2, county=values(c);
5306 --error ER_BAD_FIELD_ERROR
5307 call bug15441('county')|
5308 drop procedure bug15441|
5310 # Now check the case when there is an ambiguity between column names
5311 # and stored procedure parameters: the parser shall resolve the argument
5312 # of VALUES() function to the column name.
5314 # It's hard to deduce what county refers to in every case (INSERT statement):
5315 # 1st county refers to the column
5316 # 2nd county refers to the procedure parameter
5317 # 3d and 4th county refers to the column, again, but
5318 # for 4th county it has the value of SP parameter
5320 # In UPDATE statement, just check that values() function returns NULL for
5321 # non- INSERT...UPDATE statements, as stated in the manual.
5323 create procedure bug15441(county varchar(25))
5325 declare c varchar(25) default "hello";
5327 insert into t3 (id, county) values (1, county)
5328 on duplicate key update county= values(county);
5331 update t3 set id=2, county=values(id);
5334 call bug15441('Yale')|
5336 drop procedure bug15441|
5339 # BUG#14498: Stored procedures: hang if undefined variable and exception
5342 drop procedure if exists bug14498_1|
5343 drop procedure if exists bug14498_2|
5344 drop procedure if exists bug14498_3|
5345 drop procedure if exists bug14498_4|
5346 drop procedure if exists bug14498_5|
5349 create procedure bug14498_1()
5351 declare continue handler for sqlexception select 'error' as 'Handler';
5354 select 'yes' as 'v';
5358 select 'done' as 'End';
5361 create procedure bug14498_2()
5363 declare continue handler for sqlexception select 'error' as 'Handler';
5366 select 'yes' as 'v';
5368 select 'done' as 'End';
5371 create procedure bug14498_3()
5373 declare continue handler for sqlexception select 'error' as 'Handler';
5376 select 'maybe' as 'v';
5378 select 'done' as 'End';
5381 create procedure bug14498_4()
5383 declare continue handler for sqlexception select 'error' as 'Handler';
5393 select 'done' as 'End';
5396 create procedure bug14498_5()
5398 declare continue handler for sqlexception select 'error' as 'Handler';
5408 select 'done' as 'End';
5417 drop procedure bug14498_1|
5418 drop procedure bug14498_2|
5419 drop procedure bug14498_3|
5420 drop procedure bug14498_4|
5421 drop procedure bug14498_5|
5424 # BUG#15231: Stored procedure bug with not found condition handler
5427 drop table if exists t3|
5428 drop procedure if exists bug15231_1|
5429 drop procedure if exists bug15231_2|
5430 drop procedure if exists bug15231_3|
5431 drop procedure if exists bug15231_4|
5434 create table t3 (id int not null)|
5436 create procedure bug15231_1()
5438 declare xid integer;
5439 declare xdone integer default 0;
5440 declare continue handler for not found set xdone = 1;
5443 call bug15231_2(xid);
5447 create procedure bug15231_2(inout ioid integer)
5449 select "Before NOT FOUND condition is triggered" as '1';
5450 select id into ioid from t3 where id=ioid;
5451 select "After NOT FOUND condtition is triggered" as '2';
5453 if ioid is null then
5458 create procedure bug15231_3()
5460 declare exit handler for sqlwarning
5461 select 'Caught it (wrong)' as 'Result';
5466 create procedure bug15231_4()
5468 declare x decimal(2,1);
5471 select 'Missed it (correct)' as 'Result';
5477 drop table if exists t3|
5478 drop procedure if exists bug15231_1|
5479 drop procedure if exists bug15231_2|
5480 drop procedure if exists bug15231_3|
5481 drop procedure if exists bug15231_4|
5485 # BUG#15011: error handler in nested block not activated
5488 drop procedure if exists bug15011|
5491 create table t3 (c1 int primary key)|
5493 insert into t3 values (1)|
5495 create procedure bug15011()
5498 declare continue handler for 1062
5499 select 'Outer' as 'Handler';
5502 declare continue handler for 1062
5503 select 'Inner' as 'Handler';
5505 insert into t3 values (1);
5511 drop procedure bug15011|
5516 # BUG#17476: Stored procedure not returning data when it is called first
5517 # time per connection
5520 drop procedure if exists bug17476|
5523 create table t3 ( d date )|
5524 insert into t3 values
5525 ( '2005-01-01' ), ( '2005-01-02' ), ( '2005-01-03' ),
5526 ( '2005-01-04' ), ( '2005-02-01' ), ( '2005-02-02' )|
5528 create procedure bug17476(pDateFormat varchar(10))
5529 select date_format(t3.d, pDateFormat), count(*)
5531 group by date_format(t3.d, pDateFormat)|
5533 call bug17476('%Y-%m')|
5534 call bug17476('%Y-%m')|
5537 drop procedure bug17476|
5541 # BUG#16887: Cursor causes server segfault
5544 drop table if exists t3|
5545 drop procedure if exists bug16887|
5548 create table t3 ( c varchar(1) )|
5550 insert into t3 values
5551 (' '),('.'),(';'),(','),('-'),('_'),('('),(')'),('/'),('\\')|
5553 create procedure bug16887()
5555 declare i int default 10;
5560 declare breakchar varchar(1);
5561 declare done int default 0;
5562 declare t3_cursor cursor for select c from t3;
5563 declare continue handler for not found set done = 1;
5575 fetch t3_cursor into breakchar;
5591 drop procedure bug16887|
5594 # BUG#16474: SP crashed MySQL
5595 # (when using "order by localvar", where 'localvar' is just that.
5598 drop procedure if exists bug16474_1|
5599 drop procedure if exists bug16474_2|
5603 insert into t1 values ('c', 2), ('b', 3), ('a', 1)|
5605 create procedure bug16474_1()
5609 select id from t1 order by x, id;
5613 # BUG#14945: Truncate table doesn't reset the auto_increment counter
5616 drop procedure if exists bug14945|
5618 create table t3 (id int not null auto_increment primary key)|
5619 create procedure bug14945() deterministic truncate t3|
5620 insert into t3 values (null)|
5622 insert into t3 values (null)|
5625 drop procedure bug14945|
5627 # This does NOT order by column index; variable is an expression.
5628 create procedure bug16474_2(x int)
5629 select id from t1 order by x, id|
5634 drop procedure bug16474_1|
5635 drop procedure bug16474_2|
5637 # For reference: user variables are expressions too and do not affect ordering.
5639 select * from t1 order by @x, data|
5645 # BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
5647 # The solution is not to reset last_insert_id on enter to sub-statement.
5650 drop function if exists bug15728|
5651 drop table if exists t3|
5655 id int not null auto_increment,
5658 create function bug15728() returns int(11)
5659 return last_insert_id()|
5661 insert into t3 values (0)|
5662 select last_insert_id()|
5665 drop function bug15728|
5670 # BUG#18787: Server crashed when calling a stored procedure containing
5671 # a misnamed function
5674 drop procedure if exists bug18787|
5676 create procedure bug18787()
5678 declare continue handler for sqlexception begin end;
5680 select no_such_function();
5684 drop procedure bug18787|
5688 # BUG#18344: DROP DATABASE does not drop associated routines
5689 # (... if the database name is longer than 21 characters)
5691 # 1234567890123456789012
5692 create database bug18344_012345678901|
5693 use bug18344_012345678901|
5694 create procedure bug18344() begin end|
5695 create procedure bug18344_2() begin end|
5697 create database bug18344_0123456789012|
5698 use bug18344_0123456789012|
5699 create procedure bug18344() begin end|
5700 create procedure bug18344_2() begin end|
5704 select schema_name from information_schema.schemata where
5705 schema_name like 'bug18344%'|
5706 select routine_name,routine_schema from information_schema.routines where
5707 routine_schema like 'bug18344%'|
5709 drop database bug18344_012345678901|
5710 drop database bug18344_0123456789012|
5712 # Should be nothing left.
5713 select schema_name from information_schema.schemata where
5714 schema_name like 'bug18344%'|
5715 select routine_name,routine_schema from information_schema.routines where
5716 routine_schema like 'bug18344%'|
5720 # BUG#12472/BUG#15137 'CREATE TABLE ... SELECT ... which explicitly or
5721 # implicitly uses stored function gives "Table not locked" error'.
5724 drop function if exists bug12472|
5726 create function bug12472() returns int return (select count(*) from t1)|
5727 # Check case when function is used directly
5728 create table t3 as select bug12472() as i|
5729 show create table t3|
5732 # Check case when function is used indirectly through view
5733 create view v1 as select bug12472() as j|
5734 create table t3 as select * from v1|
5735 show create table t3|
5739 drop function bug12472|
5743 # BUG#18587: Function that accepts and returns TEXT garbles data if longer than
5750 DROP FUNCTION IF EXISTS bug18589_f1|
5751 DROP PROCEDURE IF EXISTS bug18589_p1|
5752 DROP PROCEDURE IF EXISTS bug18589_p2|
5755 CREATE FUNCTION bug18589_f1(arg TEXT) RETURNS TEXT
5757 RETURN CONCAT(arg, "");
5760 CREATE PROCEDURE bug18589_p1(arg TEXT, OUT ret TEXT)
5762 SET ret = CONCAT(arg, "");
5765 CREATE PROCEDURE bug18589_p2(arg TEXT)
5768 CALL bug18589_p1(arg, v);
5774 SELECT bug18589_f1(REPEAT("a", 767))|
5776 SET @bug18589_v1 = ""|
5777 CALL bug18589_p1(REPEAT("a", 767), @bug18589_v1)|
5778 SELECT @bug18589_v1|
5780 CALL bug18589_p2(REPEAT("a", 767))|
5784 DROP FUNCTION bug18589_f1|
5785 DROP PROCEDURE bug18589_p1|
5786 DROP PROCEDURE bug18589_p2|
5790 # BUG#18037: Server crash when returning system variable in stored procedures
5791 # BUG#19633: Stack corruption in fix_fields()/THD::rollback_item_tree_changes()
5797 DROP FUNCTION IF EXISTS bug18037_f1|
5798 DROP PROCEDURE IF EXISTS bug18037_p1|
5799 DROP PROCEDURE IF EXISTS bug18037_p2|
5804 CREATE FUNCTION bug18037_f1() RETURNS INT
5809 CREATE PROCEDURE bug18037_p1()
5811 DECLARE v INT DEFAULT @@server_id;
5814 CREATE PROCEDURE bug18037_p2()
5824 SELECT bug18037_f1()|
5830 DROP FUNCTION bug18037_f1|
5831 DROP PROCEDURE bug18037_p1|
5832 DROP PROCEDURE bug18037_p2|
5835 # Bug#17199: "Table not found" error occurs if the query contains a call
5836 # to a function from another database.
5837 # See also ps.test for an additional test case for this bug.
5840 create table t3 (i int)|
5841 insert into t3 values (1), (2)|
5842 create database mysqltest1|
5844 create function bug17199() returns varchar(2) deterministic return 'ok'|
5846 select *, mysqltest1.bug17199() from t3|
5848 # Bug#18444: Fully qualified stored function names don't work correctly
5849 # in select statements
5852 create function bug18444(i int) returns int no sql deterministic return i + 1|
5854 select mysqltest1.bug18444(i) from t3|
5855 drop database mysqltest1|
5857 # Check that current database has no influence to a stored procedure
5859 create database mysqltest1 charset=utf8|
5860 create database mysqltest2 charset=utf8|
5861 create procedure mysqltest1.p1()
5863 -- alters the default collation of database test
5864 alter database character set koi8r;
5868 show create database mysqltest1|
5869 show create database mysqltest2|
5870 alter database mysqltest1 character set utf8|
5872 call mysqltest1.p1()|
5873 show create database mysqltest1|
5874 show create database mysqltest2|
5875 drop database mysqltest1|
5876 drop database mysqltest2|
5878 # Restore the old environemnt
5881 # Bug#15217 "Using a SP cursor on a table created with PREPARE fails with
5882 # weird error". Check that the code that is supposed to work at
5883 # the first execution of a stored procedure actually works for
5887 drop table if exists t3|
5888 drop procedure if exists bug15217|
5890 create table t3 as select 1|
5891 create procedure bug15217()
5893 declare var1 char(255);
5894 declare cur1 cursor for select * from t3;
5896 fetch cur1 into var1;
5897 select concat('data was: /', var1, '/');
5900 # Returns expected result
5903 # Returns error with garbage as column name
5906 drop procedure bug15217|
5910 # BUG#21013: Performance Degrades when importing data that uses
5911 # Trigger and Stored Procedure
5913 # This is a performance and memory leak test. Run with large number
5914 # passed to bug21013() procedure.
5917 DROP PROCEDURE IF EXISTS bug21013 |
5920 CREATE PROCEDURE bug21013(IN lim INT)
5922 DECLARE i INT DEFAULT 0;
5924 SET @b = LOCATE(_latin1'b', @a, 1);
5929 SET @a = _latin2"aaaaaaaaaa" |
5932 DROP PROCEDURE bug21013 |
5936 # BUG#16211: Stored function return type for strings is ignored
5939 # Prepare: create database with fixed, pre-defined character set.
5942 DROP DATABASE IF EXISTS mysqltest1|
5943 DROP DATABASE IF EXISTS mysqltest2|
5946 CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
5947 CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8|
5953 # - Create two stored functions -- with and without explicit CHARSET-clause
5956 CREATE FUNCTION bug16211_f1() RETURNS CHAR(10)
5959 CREATE FUNCTION bug16211_f2() RETURNS CHAR(10) CHARSET koi8r
5962 CREATE FUNCTION mysqltest2.bug16211_f3() RETURNS CHAR(10)
5965 CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r
5968 # - Check that CHARSET-clause is specified for the second function;
5970 SHOW CREATE FUNCTION bug16211_f1|
5971 SHOW CREATE FUNCTION bug16211_f2|
5973 SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
5974 SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
5976 SELECT dtd_identifier
5977 FROM INFORMATION_SCHEMA.ROUTINES
5978 WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
5980 SELECT dtd_identifier
5981 FROM INFORMATION_SCHEMA.ROUTINES
5982 WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
5984 SELECT dtd_identifier
5985 FROM INFORMATION_SCHEMA.ROUTINES
5986 WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
5988 SELECT dtd_identifier
5989 FROM INFORMATION_SCHEMA.ROUTINES
5990 WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
5992 SELECT CHARSET(bug16211_f1())|
5993 SELECT CHARSET(bug16211_f2())|
5995 SELECT CHARSET(mysqltest2.bug16211_f3())|
5996 SELECT CHARSET(mysqltest2.bug16211_f4())|
5998 # - Alter database character set.
6000 ALTER DATABASE mysqltest1 CHARACTER SET cp1251|
6001 ALTER DATABASE mysqltest2 CHARACTER SET cp1251|
6003 # - Check that CHARSET-clause has not changed.
6005 SHOW CREATE FUNCTION bug16211_f1|
6006 SHOW CREATE FUNCTION bug16211_f2|
6008 SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
6009 SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
6011 SELECT dtd_identifier
6012 FROM INFORMATION_SCHEMA.ROUTINES
6013 WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
6015 SELECT dtd_identifier
6016 FROM INFORMATION_SCHEMA.ROUTINES
6017 WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
6019 SELECT dtd_identifier
6020 FROM INFORMATION_SCHEMA.ROUTINES
6021 WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
6023 SELECT dtd_identifier
6024 FROM INFORMATION_SCHEMA.ROUTINES
6025 WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
6027 SELECT CHARSET(bug16211_f1())|
6028 SELECT CHARSET(bug16211_f2())|
6030 SELECT CHARSET(mysqltest2.bug16211_f3())|
6031 SELECT CHARSET(mysqltest2.bug16211_f4())|
6037 DROP DATABASE mysqltest1|
6038 DROP DATABASE mysqltest2|
6042 # BUG#16676: Database CHARSET not used for stored procedures
6045 # Prepare: create database with fixed, pre-defined character set.
6048 DROP DATABASE IF EXISTS mysqltest1|
6051 CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
6057 # - Create two stored procedures -- with and without explicit CHARSET-clause;
6059 CREATE PROCEDURE bug16676_p1(
6064 SELECT CHARSET(p1), COLLATION(p1);
6065 SELECT CHARSET(p2), COLLATION(p2);
6066 SELECT CHARSET(p3), COLLATION(p3);
6069 CREATE PROCEDURE bug16676_p2(
6070 IN p1 CHAR(10) CHARSET koi8r,
6071 INOUT p2 CHAR(10) CHARSET cp1251,
6072 OUT p3 CHAR(10) CHARSET greek)
6074 SELECT CHARSET(p1), COLLATION(p1);
6075 SELECT CHARSET(p2), COLLATION(p2);
6076 SELECT CHARSET(p3), COLLATION(p3);
6079 # - Call procedures.
6084 CALL bug16676_p1('a', @v2, @v3)|
6085 CALL bug16676_p2('a', @v2, @v3)|
6091 DROP DATABASE mysqltest1|
6093 # BUG#8153: Stored procedure with subquery and continue handler, wrong result
6097 drop table if exists t3|
6098 drop table if exists t4|
6099 drop procedure if exists bug8153_subselect|
6100 drop procedure if exists bug8153_subselect_a|
6101 drop procedure if exists bug8153_subselect_b|
6102 drop procedure if exists bug8153_proc_a|
6103 drop procedure if exists bug8153_proc_b|
6106 create table t3 (a int)|
6107 create table t4 (a int)|
6108 insert into t3 values (1), (1), (2), (3)|
6109 insert into t4 values (1), (1)|
6111 ## Testing the use case reported in Bug#8153
6113 create procedure bug8153_subselect()
6115 declare continue handler for sqlexception
6117 select 'statement failed';
6119 update t3 set a=a+1 where (select a from t4 where a=1) is null;
6120 select 'statement after update';
6123 call bug8153_subselect()|
6126 call bug8153_subselect()|
6129 drop procedure bug8153_subselect|
6131 ## Testing a subselect with a non local handler
6133 create procedure bug8153_subselect_a()
6135 declare continue handler for sqlexception
6137 select 'in continue handler';
6140 select 'reachable code a1';
6141 call bug8153_subselect_b();
6142 select 'reachable code a2';
6145 create procedure bug8153_subselect_b()
6147 select 'reachable code b1';
6148 update t3 set a=a+1 where (select a from t4 where a=1) is null;
6149 select 'unreachable code b2';
6152 call bug8153_subselect_a()|
6155 call bug8153_subselect_a()|
6158 drop procedure bug8153_subselect_a|
6159 drop procedure bug8153_subselect_b|
6161 ## Testing extra use cases, found while investigating
6162 ## This is related to BUG#18787, with a non local handler
6164 create procedure bug8153_proc_a()
6166 declare continue handler for sqlexception
6168 select 'in continue handler';
6171 select 'reachable code a1';
6172 call bug8153_proc_b();
6173 select 'reachable code a2';
6176 create procedure bug8153_proc_b()
6178 select 'reachable code b1';
6179 select no_such_function();
6180 select 'unreachable code b2';
6183 call bug8153_proc_a()|
6185 drop procedure bug8153_proc_a|
6186 drop procedure bug8153_proc_b|
6191 # BUG#19862: Sort with filesort by function evaluates function twice
6194 drop procedure if exists bug19862|
6196 CREATE TABLE t11 (a INT)|
6197 CREATE TABLE t12 (a INT)|
6198 CREATE FUNCTION bug19862(x INT) RETURNS INT
6200 INSERT INTO t11 VALUES (x);
6203 INSERT INTO t12 VALUES (1), (2)|
6204 SELECT bug19862(a) FROM t12 ORDER BY 1|
6206 DROP TABLE t11, t12|
6207 DROP FUNCTION bug19862|
6210 # Bug#21002 "Derived table not selecting from a "real" table fails in JOINs"
6212 # A regression caused by the fix for Bug#18444: for derived tables we should
6213 # set an empty string as the current database. They do not belong to any
6214 # database and must be usable even if there is no database
6217 drop table if exists t3|
6218 drop database if exists mysqltest1|
6220 create table t3 (a int)|
6221 insert into t3 (a) values (1), (2)|
6223 create database mysqltest1|
6225 drop database mysqltest1|
6227 # No current database
6230 select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2|
6235 # Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause.
6240 DROP PROCEDURE IF EXISTS bug16899_p1|
6241 DROP FUNCTION IF EXISTS bug16899_f1|
6244 --error ER_WRONG_STRING_LENGTH
6245 CREATE DEFINER=1234567890abcdefGHIKL@localhost PROCEDURE bug16899_p1()
6250 --error ER_WRONG_STRING_LENGTH
6251 CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY
6252 FUNCTION bug16899_f1() RETURNS INT
6259 # BUG#21416: SP: Recursion level higher than zero needed for non-recursive call
6262 drop procedure if exists bug21416|
6264 create procedure bug21416() show create procedure bug21416|
6266 drop procedure bug21416|
6270 # BUG#21414: SP: Procedure undroppable, to some extent
6273 DROP PROCEDURE IF EXISTS bug21414|
6276 CREATE PROCEDURE bug21414() SELECT 1|
6278 FLUSH TABLES WITH READ LOCK|
6280 --error ER_CANT_UPDATE_WITH_READLOCK
6281 DROP PROCEDURE bug21414|
6285 --echo The following should succeed.
6286 DROP PROCEDURE bug21414|
6290 # BUG#21311: Possible stack overrun if SP has non-latin1 name
6294 drop database if exists това_е_дълго_име_за_база_данни_нали|
6296 create database това_е_дълго_име_за_база_данни_нали|
6297 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')|
6298 --error ER_SP_PROC_TABLE_CORRUPT
6299 call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()|
6300 drop database това_е_дълго_име_за_база_данни_нали|
6304 # BUG#21493: Crash on the second call of a procedure containing
6305 # a select statement that uses an IN aggregating subquery
6309 Member_ID varchar(15) NOT NULL,
6310 PRIMARY KEY (Member_ID)
6314 ID int(10) unsigned NOT NULL auto_increment,
6315 Member_ID varchar(15) NOT NULL default '',
6316 Action varchar(12) NOT NULL,
6317 Action_Date datetime NOT NULL,
6318 Track varchar(15) default NULL,
6319 User varchar(12) default NULL,
6320 Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update
6323 KEY Action (Action),
6324 KEY Action_Date (Action_Date)
6328 INSERT INTO t3(Member_ID) VALUES
6329 ('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')|
6331 INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES
6332 ('111111', 'Disenrolled', '2006-03-01', 'CAD' ),
6333 ('111111', 'Enrolled', '2006-03-01', 'CAD' ),
6334 ('111111', 'Disenrolled', '2006-07-03', 'CAD' ),
6335 ('222222', 'Enrolled', '2006-03-07', 'CAD' ),
6336 ('222222', 'Enrolled', '2006-03-07', 'CHF' ),
6337 ('222222', 'Disenrolled', '2006-08-02', 'CHF' ),
6338 ('333333', 'Enrolled', '2006-03-01', 'CAD' ),
6339 ('333333', 'Disenrolled', '2006-03-01', 'CAD' ),
6340 ('444444', 'Enrolled', '2006-03-01', 'CAD' ),
6341 ('555555', 'Disenrolled', '2006-03-01', 'CAD' ),
6342 ('555555', 'Enrolled', '2006-07-21', 'CAD' ),
6343 ('555555', 'Disenrolled', '2006-03-01', 'CHF' ),
6344 ('666666', 'Enrolled', '2006-02-09', 'CAD' ),
6345 ('666666', 'Enrolled', '2006-05-12', 'CHF' ),
6346 ('666666', 'Disenrolled', '2006-06-01', 'CAD' )|
6349 DROP FUNCTION IF EXISTS bug21493|
6352 CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45)
6354 DECLARE tracks VARCHAR(45);
6355 SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4
6356 WHERE Member_ID=paramMember AND Action='Enrolled' AND
6357 (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4
6358 WHERE Member_ID=paramMember GROUP BY Track);
6362 SELECT bug21493('111111')|
6363 SELECT bug21493('222222')|
6365 SELECT bug21493(Member_ID) FROM t3|
6367 DROP FUNCTION bug21493|
6371 # Bug#20028 Function with select return no data
6375 drop function if exists func_20028_a|
6376 drop function if exists func_20028_b|
6377 drop function if exists func_20028_c|
6378 drop procedure if exists proc_20028_a|
6379 drop procedure if exists proc_20028_b|
6380 drop procedure if exists proc_20028_c|
6381 drop table if exists table_20028|
6384 create table table_20028 (i int)|
6386 SET @save_sql_mode=@@sql_mode|
6390 create function func_20028_a() returns integer
6392 declare temp integer;
6393 select i into temp from table_20028 limit 1;
6394 return ifnull(temp, 0);
6397 create function func_20028_b() returns integer
6399 return func_20028_a();
6402 create function func_20028_c() returns integer
6404 declare div_zero integer;
6405 set SQL_MODE='TRADITIONAL';
6406 select 1/0 into div_zero;
6410 create procedure proc_20028_a()
6412 declare temp integer;
6413 select i into temp from table_20028 limit 1;
6416 create procedure proc_20028_b()
6418 call proc_20028_a();
6421 create procedure proc_20028_c()
6423 declare div_zero integer;
6424 set SQL_MODE='TRADITIONAL';
6425 select 1/0 into div_zero;
6428 select func_20028_a()|
6429 select func_20028_b()|
6430 --error ER_DIVISION_BY_ZERO
6431 select func_20028_c()|
6432 call proc_20028_a()|
6433 call proc_20028_b()|
6434 --error ER_DIVISION_BY_ZERO
6435 call proc_20028_c()|
6437 SET sql_mode='TRADITIONAL'|
6439 drop function func_20028_a|
6440 drop function func_20028_b|
6441 drop function func_20028_c|
6442 drop procedure proc_20028_a|
6443 drop procedure proc_20028_b|
6444 drop procedure proc_20028_c|
6446 create function func_20028_a() returns integer
6448 declare temp integer;
6449 select i into temp from table_20028 limit 1;
6450 return ifnull(temp, 0);
6453 create function func_20028_b() returns integer
6455 return func_20028_a();
6458 create function func_20028_c() returns integer
6460 declare div_zero integer;
6462 select 1/0 into div_zero;
6466 create procedure proc_20028_a()
6468 declare temp integer;
6469 select i into temp from table_20028 limit 1;
6472 create procedure proc_20028_b()
6474 call proc_20028_a();
6477 create procedure proc_20028_c()
6479 declare div_zero integer;
6481 select 1/0 into div_zero;
6484 select func_20028_a()|
6485 select func_20028_b()|
6486 select func_20028_c()|
6487 call proc_20028_a()|
6488 call proc_20028_b()|
6489 call proc_20028_c()|
6491 SET @@sql_mode=@save_sql_mode|
6493 drop function func_20028_a|
6494 drop function func_20028_b|
6495 drop function func_20028_c|
6496 drop procedure proc_20028_a|
6497 drop procedure proc_20028_b|
6498 drop procedure proc_20028_c|
6499 drop table table_20028|
6502 # Bug#21462 Stored procedures with no arguments require parenthesis
6506 drop procedure if exists proc_21462_a|
6507 drop procedure if exists proc_21462_b|
6510 create procedure proc_21462_a()
6515 create procedure proc_21462_b(x int)
6521 call proc_21462_a()|
6522 -- error ER_SP_WRONG_NO_OF_ARGS
6523 call proc_21462_a(1)|
6525 -- error ER_SP_WRONG_NO_OF_ARGS
6527 -- error ER_SP_WRONG_NO_OF_ARGS
6528 call proc_21462_b()|
6529 call proc_21462_b(1)|
6531 drop procedure proc_21462_a|
6532 drop procedure proc_21462_b|
6536 # Bug#19733 "Repeated alter, or repeated create/drop, fails"
6537 # Check that CREATE/DROP INDEX is re-execution friendly.
6540 drop table if exists t3|
6541 drop procedure if exists proc_bug19733|
6543 create table t3 (s1 int)|
6545 create procedure proc_bug19733()
6547 declare v int default 0;
6549 create index i on t3 (s1);
6555 call proc_bug19733()|
6556 call proc_bug19733()|
6557 call proc_bug19733()|
6559 drop procedure proc_bug19733|
6564 # BUG#20492: Subsequent calls to stored procedure yeild incorrect
6565 # result if join is used
6567 # Optimized ON expression in join wasn't properly saved for reuse.
6570 DROP PROCEDURE IF EXISTS p1|
6571 DROP VIEW IF EXISTS v1, v2|
6572 DROP TABLE IF EXISTS t3, t4|
6575 CREATE TABLE t3 (t3_id INT)|
6577 INSERT INTO t3 VALUES (0)|
6578 INSERT INTO t3 VALUES (1)|
6580 CREATE TABLE t4 (t4_id INT)|
6582 INSERT INTO t4 VALUES (2)|
6585 SELECT t3.t3_id, t4.t4_id
6586 FROM t3 JOIN t4 ON t3.t3_id = 0|
6589 SELECT t3.t3_id AS t3_id_1, v1.t3_id AS t3_id_2, v1.t4_id
6590 FROM t3 LEFT JOIN v1 ON t3.t3_id = 0|
6592 CREATE PROCEDURE p1() SELECT * FROM v2|
6594 # Results should not differ.
6602 --echo End of 5.0 tests
6604 --echo Begin of 5.1 tests
6607 # BUG#18239: Possible to overload internal functions with stored functions
6613 drop function if exists pi;
6616 create function pi() returns varchar(50)
6617 return "pie, my favorite desert.";
6619 SET @save_sql_mode=@@sql_mode;
6621 SET SQL_MODE='IGNORE_SPACE';
6625 # Non deterministic warnings from db_load_routine
6627 select test.pi(), test.pi ();
6634 # Non deterministic warnings from db_load_routine
6636 select test.pi(), test.pi ();
6639 SET @@sql_mode=@save_sql_mode;
6645 # BUG#22619: Spaces considered harmful
6649 drop function if exists test.database;
6650 drop function if exists test.current_user;
6651 drop function if exists test.md5;
6654 create database nowhere;
6656 drop database nowhere;
6658 SET @save_sql_mode=@@sql_mode;
6660 SET SQL_MODE='IGNORE_SPACE';
6662 select database(), database ();
6663 select current_user(), current_user ();
6664 select md5("aaa"), md5 ("aaa");
6668 select database(), database ();
6669 select current_user(), current_user ();
6670 select md5("aaa"), md5 ("aaa");
6674 create function `database`() returns varchar(50)
6675 return "Stored function database";
6677 create function `current_user`() returns varchar(50)
6678 return "Stored function current_user";
6680 create function md5(x varchar(50)) returns varchar(50)
6681 return "Stored function md5";
6683 SET SQL_MODE='IGNORE_SPACE';
6685 select database(), database ();
6686 select current_user(), current_user ();
6687 select md5("aaa"), md5 ("aaa");
6689 # Non deterministic warnings from db_load_routine
6691 select test.database(), test.database ();
6692 select test.current_user(), test.current_user ();
6693 select test.md5("aaa"), test.md5 ("aaa");
6698 select database(), database ();
6699 select current_user(), current_user ();
6700 select md5("aaa"), md5 ("aaa");
6702 # Non deterministic warnings from db_load_routine
6704 select test.database(), test.database ();
6705 select test.current_user(), test.current_user ();
6706 select test.md5("aaa"), test.md5 ("aaa");
6709 SET @@sql_mode=@save_sql_mode;
6711 drop function test.database;
6712 drop function test.current_user;
6719 --echo End of 5.1 tests
6722 # BUG#23760: ROW_COUNT() and store procedure not owrking together
6725 DROP TABLE IF EXISTS bug23760|
6726 DROP TABLE IF EXISTS bug23760_log|
6727 DROP PROCEDURE IF EXISTS bug23760_update_log|
6728 DROP PROCEDURE IF EXISTS bug23760_test_row_count|
6729 DROP FUNCTION IF EXISTS bug23760_rc_test|
6731 CREATE TABLE bug23760 (
6732 id INT NOT NULL AUTO_INCREMENT ,
6737 CREATE TABLE bug23760_log (
6738 id INT NOT NULL AUTO_INCREMENT ,
6739 reason VARCHAR(50)NULL ,
6740 ammount INT NOT NULL ,
6744 CREATE PROCEDURE bug23760_update_log(r Varchar(50), a INT)
6746 INSERT INTO bug23760_log (reason, ammount) VALUES(r, a);
6749 CREATE PROCEDURE bug23760_test_row_count()
6751 UPDATE bug23760 SET num = num + 1;
6752 CALL bug23760_update_log('Test is working', ROW_COUNT());
6753 UPDATE bug23760 SET num = num - 1;
6757 CREATE PROCEDURE bug23760_test_row_count2(level INT)
6760 UPDATE bug23760 SET num = num + 1;
6761 CALL bug23760_update_log('Test2 is working', ROW_COUNT());
6762 CALL bug23760_test_row_count2(level - 1);
6766 CREATE FUNCTION bug23760_rc_test(in_var INT) RETURNS INT RETURN in_var|
6768 INSERT INTO bug23760 (num) VALUES (0), (1), (1), (2), (3), (5), (8)|
6771 CALL bug23760_test_row_count()|
6772 SELECT * FROM bug23760_log ORDER BY id|
6774 SET @save_max_sp_recursion= @@max_sp_recursion_depth|
6775 SELECT @save_max_sp_recursion|
6776 SET max_sp_recursion_depth= 5|
6777 SELECT @@max_sp_recursion_depth|
6778 CALL bug23760_test_row_count2(2)|
6780 SELECT * FROM bug23760_log ORDER BY id|
6781 SELECT * FROM bug23760 ORDER by ID|
6782 SET max_sp_recursion_depth= @save_max_sp_recursion|
6784 SELECT bug23760_rc_test(123)|
6785 INSERT INTO bug23760 (num) VALUES (13), (21), (34), (55)|
6786 SELECT bug23760_rc_test(ROW_COUNT())|
6788 DROP TABLE bug23760, bug23760_log|
6789 DROP PROCEDURE bug23760_update_log|
6790 DROP PROCEDURE bug23760_test_row_count|
6791 DROP PROCEDURE bug23760_test_row_count2|
6792 DROP FUNCTION bug23760_rc_test|
6795 # BUG#24117: server crash on a FETCH with a cursor on a table which is not in
6800 DROP PROCEDURE IF EXISTS bug24117|
6801 DROP TABLE IF EXISTS t3|
6803 CREATE TABLE t3(c1 ENUM('abc'))|
6804 INSERT INTO t3 VALUES('abc')|
6805 CREATE PROCEDURE bug24117()
6807 DECLARE t3c1 ENUM('abc');
6808 DECLARE mycursor CURSOR FOR SELECT c1 FROM t3;
6811 FETCH mycursor INTO t3c1;
6815 DROP PROCEDURE bug24117|
6819 # Bug#8407(Stored functions/triggers ignore exception handler)
6823 drop function if exists func_8407_a|
6824 drop function if exists func_8407_b|
6827 create function func_8407_a() returns int
6831 declare continue handler for sqlexception
6835 select 1 from no_such_view limit 1 into x;
6840 create function func_8407_b() returns int
6842 declare x int default 0;
6844 declare continue handler for sqlstate '42S02'
6849 case (select 1 from no_such_view limit 1)
6850 when 1 then set x:= x+1;
6851 when 2 then set x:= x+2;
6859 select func_8407_a()|
6860 select func_8407_b()|
6862 drop function func_8407_a|
6863 drop function func_8407_b|
6866 # Bug#26503 (Illegal SQL exception handler code causes the server to crash)
6870 drop table if exists table_26503|
6871 drop procedure if exists proc_26503_ok_1|
6872 drop procedure if exists proc_26503_ok_2|
6873 drop procedure if exists proc_26503_ok_3|
6874 drop procedure if exists proc_26503_ok_4|
6877 create table table_26503(a int unique)|
6879 create procedure proc_26503_ok_1(v int)
6881 declare i int default 5;
6883 declare continue handler for sqlexception
6885 select 'caught something';
6890 select 'looping', i;
6895 select 'leaving handler';
6898 select 'do something';
6899 insert into table_26503 values (v);
6900 select 'do something again';
6901 insert into table_26503 values (v);
6904 create procedure proc_26503_ok_2(v int)
6906 declare i int default 5;
6908 declare continue handler for sqlexception
6910 select 'caught something';
6915 select 'looping', i;
6920 select 'leaving handler';
6923 select 'do something';
6924 insert into table_26503 values (v);
6925 select 'do something again';
6926 insert into table_26503 values (v);
6929 ## The outer retry label should not prevent using the inner label.
6931 create procedure proc_26503_ok_3(v int)
6933 declare i int default 5;
6937 declare continue handler for sqlexception
6939 select 'caught something';
6944 select 'looping', i;
6949 select 'leaving handler';
6952 select 'do something';
6953 insert into table_26503 values (v);
6954 select 'do something again';
6955 insert into table_26503 values (v);
6959 ## The outer retry label should not prevent using the inner label.
6961 create procedure proc_26503_ok_4(v int)
6963 declare i int default 5;
6967 declare continue handler for sqlexception
6969 select 'caught something';
6974 select 'looping', i;
6979 select 'leaving handler';
6982 select 'do something';
6983 insert into table_26503 values (v);
6984 select 'do something again';
6985 insert into table_26503 values (v);
6989 call proc_26503_ok_1(1)|
6990 call proc_26503_ok_2(2)|
6991 call proc_26503_ok_3(3)|
6992 call proc_26503_ok_4(4)|
6994 drop table table_26503|
6995 drop procedure proc_26503_ok_1|
6996 drop procedure proc_26503_ok_2|
6997 drop procedure proc_26503_ok_3|
6998 drop procedure proc_26503_ok_4|
7001 # Bug#25373: Stored functions wasn't compared correctly which leads to a wrong
7005 DROP FUNCTION IF EXISTS bug25373|
7007 CREATE FUNCTION bug25373(p1 INTEGER) RETURNS INTEGER
7008 LANGUAGE SQL DETERMINISTIC
7010 CREATE TABLE t3 (f1 INT, f2 FLOAT)|
7011 INSERT INTO t3 VALUES (1, 3.4), (1, 2), (1, 0.9), (2, 8), (2, 7)|
7012 SELECT SUM(f2), bug25373(f1) FROM t3 GROUP BY bug25373(f1) WITH ROLLUP|
7013 DROP FUNCTION bug25373|
7018 # BUG#25082: Default database change on trigger execution breaks replication.
7020 # As it turned out, this bug has actually two bugs. So, here we have two test
7021 # cases -- one in sp.test, the other in sp-security.test.
7025 # Test case 1: error on dropping the current database.
7031 DROP DATABASE IF EXISTS mysqltest1|
7032 DROP DATABASE IF EXISTS mysqltest2|
7035 CREATE DATABASE mysqltest1|
7036 CREATE DATABASE mysqltest2|
7040 CREATE PROCEDURE mysqltest1.p1()
7041 DROP DATABASE mysqltest2|
7045 CALL mysqltest1.p1()|
7051 DROP DATABASE mysqltest1|
7057 # Bug#20777: Function w BIGINT UNSIGNED shows diff. behaviour --ps-protocol
7059 drop function if exists bug20777|
7060 drop table if exists examplebug20777|
7062 create function bug20777(f1 bigint unsigned) returns bigint unsigned
7064 set f1 = (f1 - 10); set f1 = (f1 + 10);
7068 select bug20777(9223372036854775803) as '9223372036854775803 2**63-5';
7069 select bug20777(9223372036854775804) as '9223372036854775804 2**63-4';
7070 select bug20777(9223372036854775805) as '9223372036854775805 2**63-3';
7071 select bug20777(9223372036854775806) as '9223372036854775806 2**63-2';
7072 select bug20777(9223372036854775807) as '9223372036854775807 2**63-1';
7073 select bug20777(9223372036854775808) as '9223372036854775808 2**63+0';
7074 select bug20777(9223372036854775809) as '9223372036854775809 2**63+1';
7075 select bug20777(9223372036854775810) as '9223372036854775810 2**63+2';
7076 select bug20777(-9223372036854775808) as 'lower bounds signed bigint';
7077 select bug20777(9223372036854775807) as 'upper bounds signed bigint';
7078 select bug20777(0) as 'lower bounds unsigned bigint';
7079 select bug20777(18446744073709551615) as 'upper bounds unsigned bigint';
7080 select bug20777(18446744073709551616) as 'upper bounds unsigned bigint + 1';
7081 select bug20777(-1) as 'lower bounds unsigned bigint - 1';
7083 create table examplebug20777 as select
7085 bug20777(9223372036854775806) as '2**63-2',
7086 bug20777(9223372036854775807) as '2**63-1',
7087 bug20777(9223372036854775808) as '2**63',
7088 bug20777(9223372036854775809) as '2**63+1',
7089 bug20777(18446744073709551614) as '2**64-2',
7090 bug20777(18446744073709551615) as '2**64-1',
7091 bug20777(18446744073709551616) as '2**64',
7093 bug20777(-1) as '-1';
7094 insert into examplebug20777 values (1, 9223372036854775806, 9223372036854775807, 223372036854775808, 9223372036854775809, 18446744073709551614, 18446744073709551615, 8446744073709551616, 0, -1);
7095 show create table examplebug20777;
7096 select * from examplebug20777 order by i;
7098 drop table examplebug20777;
7099 select bug20777(18446744073709551613)+1;
7100 drop function bug20777;
7105 # BUG#5274: Stored procedure crash if length of CHAR variable too great.
7111 DROP FUNCTION IF EXISTS bug5274_f1|
7112 DROP FUNCTION IF EXISTS bug5274_f2|
7117 CREATE FUNCTION bug5274_f1(p1 CHAR) RETURNS CHAR
7118 RETURN CONCAT(p1, p1)|
7120 CREATE FUNCTION bug5274_f2() RETURNS CHAR
7122 DECLARE v1 INT DEFAULT 0;
7123 DECLARE v2 CHAR DEFAULT 'x';
7127 SET v2 = bug5274_f1(v2);
7133 SELECT bug5274_f2()|
7137 DROP FUNCTION bug5274_f1|
7138 DROP FUNCTION bug5274_f2|
7141 # Bug#21513 (SP having body starting with quoted label rendered unusable)
7144 drop procedure if exists proc_21513|
7147 create procedure proc_21513()`my_label`:BEGIN END|
7148 show create procedure proc_21513|
7150 drop procedure proc_21513|
7153 --echo End of 5.0 tests.
7156 # BUG#NNNN: New bug synopsis
7159 #drop procedure if exists bugNNNN|
7161 #create procedure bugNNNN...
7163 # Add bugs above this line. Use existing tables t1 and t2 when
7164 # practical, or create table t3,t4 etc temporarily (and drop them).
7165 # NOTE: The delimiter is `|`, and not `;`. It is changed to `;`
7166 # at the end of the file!
7172 # Disable warnings to allow test run without InnoDB
7174 CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM;
7175 CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb;
7180 CREATE function bug27354() RETURNS int not deterministic
7182 insert into t1 values (null);
7188 update t2 set b=1 where a=bug27354();
7189 select count(t_1.a),count(t_2.a) from t1 as t_1, t2 as t_2 /* must be 0,0 */;
7190 insert into t2 values (1,1),(2,2),(3,3);
7191 update t2 set b=-b where a=bug27354();
7192 select * from t2 /* must return 1,-1 ... */;
7193 select count(*) from t1 /* must be 3 */;
7197 drop function bug27354;
7200 # Bug #28605: SHOW CREATE VIEW with views using stored_procedures no longer
7203 CREATE TABLE t1 (a INT);
7204 INSERT INTO t1 VALUES (1),(2);
7206 CREATE FUNCTION metered(a INT) RETURNS INT RETURN 12;
7208 CREATE VIEW v1 AS SELECT test.metered(a) as metered FROM t1;
7210 SHOW CREATE VIEW v1;
7213 DROP FUNCTION metered;
7217 # Bug#29834: Accessing a view column by name in SP/PS causes a memory leak.
7219 # This is leak test. Run with large number assigned to $execute_cnt,
7220 # $p1_cnt, $p2_cnt, @p1_p2_cnt, $f1_normal_cnt or $f1_prep_cnt variables.
7223 let $execute_cnt= 2;
7227 let $f1_normal_cnt= 2;
7228 let $f1_prep_cnt= 2;
7230 CREATE TABLE t1 (c1 INT);
7231 CREATE VIEW v1 AS SELECT * FROM t1;
7233 PREPARE s1 FROM 'SELECT c1 FROM v1';
7234 while ($execute_cnt)
7242 CREATE PROCEDURE p1(IN loops BIGINT(19) UNSIGNED)
7246 SET loops = loops - 1;
7250 CREATE PROCEDURE p2(IN loops BIGINT(19) UNSIGNED)
7254 CALL p1(@p1_p2_cnt);
7255 SET loops = loops - 1;
7259 CREATE FUNCTION f1(loops INT UNSIGNED)
7264 SELECT c1 INTO tmp FROM v1;
7265 SET loops = loops - 1;
7272 eval CALL p1($p1_cnt);
7273 eval CALL p2($p2_cnt);
7275 eval SELECT f1($f1_normal_cnt);
7277 eval PREPARE s1 FROM 'SELECT f1($f1_prep_cnt)';
7288 # Bug#28551 "The warning 'No database selected' is reported when calling
7289 # stored procedures"
7292 drop database if exists mysqltest_db1;
7294 create database mysqltest_db1;
7295 create procedure mysqltest_db1.sp_bug28551() begin end;
7296 call mysqltest_db1.sp_bug28551();
7298 drop database mysqltest_db1;
7300 # Bug#29050 Creation of a legal stored procedure fails if a database is not
7304 drop database if exists mysqltest_db1;
7305 drop table if exists test.t1;
7307 create database mysqltest_db1;
7309 # For the sake of its side effect
7310 drop database mysqltest_db1;
7311 # Now we have no current database selected.
7312 create table test.t1 (id int);
7313 insert into test.t1 (id) values (1);
7315 create procedure test.sp_bug29050() begin select * from t1; end//
7318 call test.sp_bug29050();
7320 # Restore the old current database
7322 drop procedure sp_bug29050;
7326 # Bug #30120 SP with local variables with non-ASCII names crashes server.
7333 CREATE PROCEDURE p1()
7347 # Bug#25411 (trigger code truncated)
7351 drop procedure if exists proc_25411_a;
7352 drop procedure if exists proc_25411_b;
7353 drop procedure if exists proc_25411_c;
7358 create procedure proc_25411_a()
7364 /*!00000 select 4; */
7365 /*!99999 select 5; */
7369 create procedure proc_25411_b(
7380 create procedure proc_25411_c()
7382 select 1/*!,2*//*!00000,3*//*!99999,4*/;
7383 select 1/*! ,2*//*!00000 ,3*//*!99999 ,4*/;
7384 select 1/*!,2 *//*!00000,3 *//*!99999,4 */;
7385 select 1/*! ,2 *//*!00000 ,3 *//*!99999 ,4 */;
7386 select 1 /*!,2*/ /*!00000,3*/ /*!99999,4*/ ;
7392 show create procedure proc_25411_a;
7393 call proc_25411_a();
7395 show create procedure proc_25411_b;
7396 select name, param_list, body from mysql.proc where name like "%25411%";
7397 call proc_25411_b(10, 20);
7399 show create procedure proc_25411_c;
7400 call proc_25411_c();
7402 drop procedure proc_25411_a;
7403 drop procedure proc_25411_b;
7404 drop procedure proc_25411_c;
7408 # Bug#26302 (MySQL server cuts off trailing "*/" from comments in SP/func)
7412 drop procedure if exists proc_26302;
7415 create procedure proc_26302()
7416 select 1 /* testing */;
7418 show create procedure proc_26302;
7420 select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES
7421 where ROUTINE_NAME = "proc_26302";
7423 drop procedure proc_26302;
7426 # Bug #29338: no optimization for stored functions with a trivial body
7427 # always returning constant.
7430 CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2;
7431 CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3;
7433 CREATE TABLE t1 (c1 INT, INDEX(c1));
7435 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
7437 CREATE VIEW v1 AS SELECT c1 FROM t1;
7439 EXPLAIN SELECT * FROM t1 WHERE c1=1;
7440 EXPLAIN SELECT * FROM t1 WHERE c1=f1();
7442 EXPLAIN SELECT * FROM v1 WHERE c1=1;
7443 EXPLAIN SELECT * FROM v1 WHERE c1=f1();
7445 EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
7446 EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
7447 EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand());
7456 # Bug#29408 Cannot find view in columns table if the selection contains a function
7460 create function f1()
7464 sql security definer
7474 create view v1 as select 1 as one, f1() as days;
7476 connect (bug29408, localhost, root,,*NO-ONE*);
7477 connection bug29408;
7479 show create view test.v1;
7480 select column_name from information_schema.columns
7481 where table_name='v1' and table_schema='test';
7484 disconnect bug29408;
7489 # Bug#13675: DATETIME/DATE type in store proc param seems to be converted as
7498 DROP PROCEDURE IF EXISTS p1;
7499 DROP PROCEDURE IF EXISTS p2;
7501 DROP TABLE IF EXISTS t1;
7506 CREATE PROCEDURE p1(v DATETIME) CREATE TABLE t1 SELECT v;
7508 CREATE PROCEDURE p2(v INT) CREATE TABLE t1 SELECT v;
7512 SHOW CREATE TABLE t1;
7519 SHOW CREATE TABLE t1;
7526 SHOW CREATE TABLE t1;
7533 SHOW CREATE TABLE t1;
7542 ###########################################################################
7545 # Bug#31035: select from function, group by result crasher.
7548 ###########################################################################
7565 DROP TABLE IF EXISTS t1;
7566 DROP FUNCTION IF EXISTS f1;
7567 DROP FUNCTION IF EXISTS f2;
7568 DROP FUNCTION IF EXISTS f3;
7569 DROP FUNCTION IF EXISTS f4;
7575 --echo # - Create required objects.
7580 CREATE TABLE t1(c1 INT);
7584 INSERT INTO t1 VALUES (1), (2), (3);
7588 CREATE FUNCTION f1()
7595 CREATE FUNCTION f2(p INT)
7602 CREATE FUNCTION f3()
7609 CREATE FUNCTION f4(p INT)
7622 # Not deterministic function, no arguments.
7624 SELECT f1() AS a FROM t1 GROUP BY a;
7628 # Not deterministic function, non-constant argument.
7630 SELECT f2(@a) AS a FROM t1 GROUP BY a;
7634 # Deterministic function, no arguments.
7636 SELECT f3() AS a FROM t1 GROUP BY a;
7640 # Deterministic function, constant argument.
7642 SELECT f4(0) AS a FROM t1 GROUP BY a;
7646 # Deterministic function, non-constant argument.
7648 SELECT f4(@a) AS a FROM t1 GROUP BY a;
7666 ###########################################################################
7669 # Bug#31191: JOIN in combination with stored function crashes the server.
7672 ###########################################################################
7687 DROP TABLE IF EXISTS t1;
7688 DROP TABLE IF EXISTS t2;
7689 DROP FUNCTION IF EXISTS f1;
7695 --echo # - Create required objects.
7701 id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
7702 barcode INT(8) UNSIGNED ZEROFILL nOT NULL,
7704 UNIQUE KEY barcode (barcode)
7709 INSERT INTO t1 (id, barcode) VALUES (1, 12345678);
7710 INSERT INTO t1 (id, barcode) VALUES (2, 12345679);
7714 CREATE TABLE test.t2 (
7715 id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
7716 barcode BIGINT(11) UNSIGNED ZEROFILL NOT NULL,
7722 INSERT INTO test.t2 (id, barcode) VALUES (1, 12345106708);
7723 INSERT INTO test.t2 (id, barcode) VALUES (2, 12345106709);
7727 CREATE FUNCTION f1(p INT(8))
7728 RETURNS BIGINT(11) UNSIGNED
7730 RETURN FLOOR(p/1000)*1000000 + 100000 + FLOOR((p MOD 1000)/10)*100 + (p MOD 10);
7740 SELECT DISTINCT t1.barcode, f1(t1.barcode)
7743 ON f1(t1.barcode) = t2.barcode
7744 WHERE t1.barcode=12345678;
7760 ###########################################################################
7763 # Bug#31226: Group by function crashes mysql.
7766 ###########################################################################
7781 DROP TABLE IF EXISTS t1;
7782 DROP FUNCTION IF EXISTS f1;
7788 --echo # - Create required objects.
7793 CREATE TABLE t1(id INT);
7797 INSERT INTO t1 VALUES (1), (2), (3);
7801 CREATE FUNCTION f1()
7803 NOT DETERMINISTIC NO SQL
7814 --replace_column 1 <timestamp>
7815 SELECT f1() FROM t1 GROUP BY 1;
7830 ###########################################################################
7833 # Bug#28318 (CREATE FUNCTION (UDF) requires a schema)
7837 DROP PROCEDURE IF EXISTS db28318_a.t1;
7838 DROP PROCEDURE IF EXISTS db28318_b.t2;
7839 DROP DATABASE IF EXISTS db28318_a;
7840 DROP DATABASE IF EXISTS db28318_b;
7843 CREATE DATABASE db28318_a;
7844 CREATE DATABASE db28318_b;
7846 CREATE PROCEDURE db28318_a.t1() SELECT "db28318_a.t1";
7847 CREATE PROCEDURE db28318_b.t2() CALL t1();
7851 # In db28318_b.t2, t1 refers to db28318_b.t1
7852 --error ER_SP_DOES_NOT_EXIST
7853 CALL db28318_b.t2();
7855 DROP PROCEDURE db28318_a.t1;
7856 DROP PROCEDURE db28318_b.t2;
7857 DROP DATABASE db28318_a;
7858 DROP DATABASE db28318_b;
7861 ###########################################################################
7864 # Bug#29770 Two handlers are allowed to catch an error in an stored procedure.
7868 DROP TABLE IF EXISTS t1;
7869 DROP PROCEDURE IF EXISTS bug29770;
7872 CREATE TABLE t1(a int);
7874 CREATE PROCEDURE bug29770()
7876 DECLARE CONTINUE HANDLER FOR SQLSTATE '42S22' SET @state:= 'run';
7877 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @exception:= 'run';
7882 SELECT @state, @exception;
7884 DROP PROCEDURE bug29770;
7887 # Bug#33618 Crash in sp_rcontext
7893 drop table if exists t_33618;
7894 drop procedure if exists proc_33618;
7897 create table t_33618 (`a` int, unique(`a`), `b` varchar(30)) engine=myisam;
7898 insert into t_33618 (`a`,`b`) values (1,'1'),(2,'2');
7902 create procedure proc_33618(num int)
7904 declare count1 int default '0';
7905 declare vb varchar(30);
7906 declare last_row int;
7911 declare cur1 cursor for select `a` from t_33618;
7912 declare continue handler for not found set last_row = 1;
7918 declare exit handler for 1062 begin end;
7920 if (last_row = 1) then
7933 call proc_33618(20);
7936 drop procedure proc_33618;
7939 --echo # Bug#30787: Stored function ignores user defined alias.
7943 drop function if exists func30787;
7945 create table t1(f1 int);
7946 insert into t1 values(1),(2);
7948 create function func30787(p1 int) returns int
7953 select (select func30787(f1)) as ttt from t1;
7954 drop function func30787;
7958 # Bug #33811: Call to stored procedure with SELECT * / RIGHT JOIN fails
7959 # after the first time
7961 CREATE TABLE t1 (id INT);
7962 INSERT INTO t1 VALUES (1),(2),(3),(4);
7964 CREATE PROCEDURE test_sp()
7965 SELECT t1.* FROM t1 RIGHT JOIN t1 t2 ON t1.id=t2.id;
7970 DROP PROCEDURE test_sp;
7974 ###########################################################################
7976 # Bug#38291 memory corruption and server crash with view/sp/function
7979 create table t1(c1 INT);
7980 create function f1(p1 int) returns varchar(32)
7982 create view v1 as select f1(c1) as parent_control_name from t1;
7985 create procedure p1()
7987 select parent_control_name as c1 from v1;
8000 # Bug#38469 invalid memory read and/or crash with utf8 text field, stored procedure, uservar
8004 drop procedure if exists `p2` $
8006 create procedure `p2`(in `a` text charset utf8)
8008 declare `pos` int default 1;
8009 declare `str` text charset utf8;
8011 select substr(`str`, `pos`+ 1 ) into `str`;
8014 call `p2`('s s s s s s');
8015 drop procedure `p2`;
8018 # Bug#38823: Invalid memory access when a SP statement does wildcard expansion
8022 drop table if exists t1;
8023 drop procedure if exists p1;
8027 create procedure p1() begin select * from t1; end$
8028 --error ER_NO_SUCH_TABLE
8030 create table t1 (a integer)$
8032 alter table t1 add b integer;
8039 --echo # ------------------------------------------------------------------
8040 --echo # -- End of 5.0 tests
8041 --echo # ------------------------------------------------------------------
8043 ###########################################################################
8046 # Bug#20550: Stored function: wrong RETURN type metadata when used in a VIEW.
8049 ###########################################################################
8066 DROP VIEW IF EXISTS v1;
8067 DROP VIEW IF EXISTS v2;
8068 DROP FUNCTION IF EXISTS f1;
8069 DROP FUNCTION IF EXISTS f2;
8075 --echo # - Create required objects.
8080 CREATE FUNCTION f1() RETURNS VARCHAR(65525) RETURN 'Hello';
8084 CREATE FUNCTION f2() RETURNS TINYINT RETURN 1;
8088 CREATE VIEW v1 AS SELECT f1();
8092 CREATE VIEW v2 AS SELECT f2();
8102 SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v1';
8106 SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v2';
8123 ###########################################################################
8126 # Bug#24923: Functions with ENUM issues.
8129 ###########################################################################
8132 --echo # - Bug#24923: prepare.
8138 DROP FUNCTION IF EXISTS f1;
8144 --echo # - Bug#24923: create required objects.
8151 CREATE FUNCTION f1(p INT)
8152 RETURNS ENUM ('Very_long_enum_element_identifier',
8153 'Another_very_long_enum_element_identifier')
8157 RETURN 'Very_long_enum_element_identifier';
8159 RETURN 'Another_very_long_enum_element_identifier';
8168 --echo # - Bug#24923: check.
8181 SHOW CREATE FUNCTION f1;
8184 --echo # - Bug#24923: cleanup.
8193 ###########################################################################
8196 # Bug#32633 Can not create any routine if SQL_MODE=no_engine_substitution
8198 # Ensure that when new SQL modes are introduced, they are also added to
8199 # the mysql.proc table.
8203 drop procedure if exists p;
8205 set @old_mode= @@sql_mode;
8206 set @@sql_mode= pow(2,32)-1;
8207 select @@sql_mode into @full_mode;
8208 create procedure p() begin end;
8211 set @@sql_mode= @old_mode;
8212 # Rename SQL modes that differ in name between the server and the table definition.
8213 select replace(@full_mode, '?', 'NOT_USED') into @full_mode;
8214 select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode;
8215 select name from mysql.proc where name = 'p' and sql_mode = @full_mode;
8219 # Bug#43962 "Packets out of order" calling a SHOW TABLE STATUS
8222 CREATE DEFINER = 'root'@'localhost' PROCEDURE p1()
8225 SQL SECURITY DEFINER
8228 SHOW TABLE STATUS like 't1';
8233 CREATE TABLE t1 (f1 INT);
8234 --disable_result_log
8246 # Bug#47649 crash during CALL procedure
8248 CREATE TABLE t1 ( f1 integer, primary key (f1));
8249 CREATE TABLE t2 LIKE t1;
8250 CREATE TEMPORARY TABLE t3 LIKE t1;
8252 CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ;
8255 --error ER_CANT_REOPEN_TABLE
8257 CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 );
8266 --echo # Bug #46629: Item_in_subselect::val_int(): Assertion `0'
8267 --echo # on subquery inside a SP
8269 CREATE TABLE t1(a INT);
8270 CREATE TABLE t2(a INT, b INT PRIMARY KEY);
8273 CREATE PROCEDURE p1 ()
8275 SELECT a FROM t1 A WHERE A.b IN (SELECT b FROM t2 AS B);
8278 --error ER_BAD_FIELD_ERROR
8280 --error ER_BAD_FIELD_ERROR
8286 --echo # Bug#47627: SET @@{global.session}.local_variable in stored routine causes crash
8287 --echo # Bug#48626: Crash or lost connection using SET for declared variables with @@
8291 DROP PROCEDURE IF EXISTS p1;
8292 DROP PROCEDURE IF EXISTS p2;
8293 DROP PROCEDURE IF EXISTS p3;
8298 --error ER_UNKNOWN_SYSTEM_VARIABLE
8299 CREATE PROCEDURE p1()
8301 DECLARE v INT DEFAULT 0;
8302 SET @@SESSION.v= 10;
8305 CREATE PROCEDURE p2()
8307 DECLARE v INT DEFAULT 0;
8312 --error ER_UNKNOWN_SYSTEM_VARIABLE
8313 CREATE PROCEDURE p3()
8315 DECLARE v INT DEFAULT 0;
8319 --error ER_UNKNOWN_SYSTEM_VARIABLE
8320 CREATE PROCEDURE p4()
8322 DECLARE v INT DEFAULT 0;
8326 CREATE PROCEDURE p5()
8328 DECLARE init_connect INT DEFAULT 0;
8329 SET init_connect= 10;
8330 SET @@GLOBAL.init_connect= 'SELECT 1';
8331 SET @@SESSION.IDENTITY= 1;
8332 SELECT @@SESSION.IDENTITY;
8333 SELECT @@GLOBAL.init_connect;
8334 SELECT init_connect;
8337 --error ER_UNKNOWN_SYSTEM_VARIABLE
8338 CREATE PROCEDURE p6()
8340 DECLARE v INT DEFAULT 0;
8346 SET @old_init_connect= @@GLOBAL.init_connect;
8348 SET @@GLOBAL.init_connect= @old_init_connect;
8355 --echo # Bug#11840395 (formerly known as bug#60347):
8356 --echo # The string "versiondata" seems
8357 --echo # to be 'leaking' into the schema name space
8360 DROP DATABASE IF EXISTS mixedCaseDbName;
8362 CREATE DATABASE mixedCaseDbName;
8364 CREATE PROCEDURE mixedCaseDbName.tryMyProc() begin end|
8365 CREATE FUNCTION mixedCaseDbName.tryMyFunc() returns text begin return 'IT WORKS'; end
8368 call mixedCaseDbName.tryMyProc();
8369 select mixedCaseDbName.tryMyFunc();
8370 DROP DATABASE mixedCaseDbName;
8374 --echo # Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C
8377 CREATE TABLE t1 (a INT, b INT, KEY(b));
8378 CREATE TABLE t2 (c INT, d INT, KEY(c));
8379 INSERT INTO t1 VALUES (1,1),(1,1),(1,2);
8380 INSERT INTO t2 VALUES (1,1),(1,2);
8384 CREATE FUNCTION f1() RETURNS INT DETERMINISTIC
8387 -- SQL statement inside
8394 SELECT COUNT(DISTINCT d) FROM t1, t2 WHERE a = c AND b = f1();
8401 --echo # Bug#12663165 SP DEAD CODE REMOVAL DOESN'T UNDERSTAND CONTINUE HANDLERS
8405 DROP FUNCTION IF EXISTS f1;
8409 CREATE FUNCTION f1() RETURNS INT
8411 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
8413 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN f1();
8415 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN f1();
8423 # This used to cause an assertion.
8429 --echo # ------------------------------------------------------------------
8430 --echo # -- End of 5.1 tests
8431 --echo # ------------------------------------------------------------------
8434 --echo # Bug#13805127: Stored program cache produces wrong result in same THD
8439 CREATE PROCEDURE p1(x INT UNSIGNED)
8441 SELECT c1, t2.c2, count(c3)
8444 SELECT 3 as c2 FROM dual WHERE x = 1
8446 SELECT 2 FROM dual WHERE x = 1 OR x = 2
8449 SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
8451 SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
8453 SELECT '2012-03-01 01:00:00', 2, 1 FROM dual