1use test; 2drop table if exists t1,t2,t3,t4; 3drop view if exists v1; 4drop procedure if exists p1; 5drop procedure if exists p2; 6drop function if exists f1; 7drop function if exists f2; 8create table t1 ( 9id char(16) not null default '', 10data int not null 11); 12create table t2 ( 13s char(16), 14i int, 15d double 16); 17drop procedure if exists foo42; 18create procedure foo42() 19insert into test.t1 values ("foo", 42); 20call foo42(); 21select * from t1; 22id data 23foo 42 24delete from t1; 25drop procedure foo42; 26drop procedure if exists bar; 27create procedure bar(x char(16), y int) 28insert into test.t1 values (x, y); 29call bar("bar", 666); 30select * from t1; 31id data 32bar 666 33delete from t1; 34drop procedure if exists empty| 35create procedure empty() 36begin 37end| 38call empty()| 39drop procedure empty| 40drop procedure if exists scope| 41create procedure scope(a int, b float) 42begin 43declare b int; 44declare c float; 45begin 46declare c int; 47end; 48end| 49drop procedure scope| 50drop procedure if exists two| 51create procedure two(x1 char(16), x2 char(16), y int) 52begin 53insert into test.t1 values (x1, y); 54insert into test.t1 values (x2, y); 55end| 56call two("one", "two", 3)| 57select * from t1| 58id data 59one 3 60two 3 61delete from t1| 62drop procedure two| 63drop procedure if exists locset| 64create procedure locset(x char(16), y int) 65begin 66declare z1, z2 int; 67set z1 = y; 68set z2 = z1+2; 69insert into test.t1 values (x, z2); 70end| 71call locset("locset", 19)| 72select * from t1| 73id data 74locset 21 75delete from t1| 76drop procedure locset| 77drop procedure if exists setcontext| 78create procedure setcontext() 79begin 80declare data int default 2; 81insert into t1 (id, data) values ("foo", 1); 82replace t1 set data = data, id = "bar"; 83update t1 set id = "kaka", data = 3 where t1.data = data; 84end| 85call setcontext()| 86select * from t1 order by data| 87id data 88foo 1 89kaka 3 90delete from t1| 91drop procedure setcontext| 92create table t3 ( d date, i int, f double, s varchar(32) )| 93drop procedure if exists nullset| 94create procedure nullset() 95begin 96declare ld date; 97declare li int; 98declare lf double; 99declare ls varchar(32); 100set ld = null, li = null, lf = null, ls = null; 101insert into t3 values (ld, li, lf, ls); 102insert into t3 (i, f, s) values ((ld is null), 1, "ld is null"), 103((li is null), 1, "li is null"), 104((li = 0), null, "li = 0"), 105((lf is null), 1, "lf is null"), 106((lf = 0), null, "lf = 0"), 107((ls is null), 1, "ls is null"); 108end| 109call nullset()| 110select * from t3| 111d i f s 112NULL NULL NULL NULL 113NULL 1 1 ld is null 114NULL 1 1 li is null 115NULL NULL NULL li = 0 116NULL 1 1 lf is null 117NULL NULL NULL lf = 0 118NULL 1 1 ls is null 119drop table t3| 120drop procedure nullset| 121drop procedure if exists mixset| 122create procedure mixset(x char(16), y int) 123begin 124declare z int; 125set @z = y, z = 666, max_join_size = 100; 126insert into test.t1 values (x, z); 127end| 128call mixset("mixset", 19)| 129show variables like 'max_join_size'| 130Variable_name Value 131max_join_size 100 132select id,data,@z from t1| 133id data @z 134mixset 666 19 135delete from t1| 136drop procedure mixset| 137drop procedure if exists zip| 138create procedure zip(x char(16), y int) 139begin 140declare z int; 141call zap(y, z); 142call bar(x, z); 143end| 144drop procedure if exists zap| 145create procedure zap(x int, out y int) 146begin 147declare z int; 148set z = x+1, y = z; 149end| 150call zip("zip", 99)| 151select * from t1| 152id data 153zip 100 154delete from t1| 155drop procedure zip| 156drop procedure bar| 157call zap(7, @zap)| 158select @zap| 159@zap 1608 161drop procedure zap| 162drop procedure if exists c1| 163create procedure c1(x int) 164call c2("c", x)| 165drop procedure if exists c2| 166create procedure c2(s char(16), x int) 167call c3(x, s)| 168drop procedure if exists c3| 169create procedure c3(x int, s char(16)) 170call c4("level", x, s)| 171drop procedure if exists c4| 172create procedure c4(l char(8), x int, s char(16)) 173insert into t1 values (concat(l,s), x)| 174call c1(42)| 175select * from t1| 176id data 177levelc 42 178delete from t1| 179drop procedure c1| 180drop procedure c2| 181drop procedure c3| 182drop procedure c4| 183drop procedure if exists iotest| 184create procedure iotest(x1 char(16), x2 char(16), y int) 185begin 186call inc2(x2, y); 187insert into test.t1 values (x1, y); 188end| 189drop procedure if exists inc2| 190create procedure inc2(x char(16), y int) 191begin 192call inc(y); 193insert into test.t1 values (x, y); 194end| 195drop procedure if exists inc| 196create procedure inc(inout io int) 197set io = io + 1| 198call iotest("io1", "io2", 1)| 199select * from t1 order by data desc| 200id data 201io2 2 202io1 1 203delete from t1| 204drop procedure iotest| 205drop procedure inc2| 206drop procedure if exists incr| 207create procedure incr(inout x int) 208call inc(x)| 209select @zap| 210@zap 2118 212call incr(@zap)| 213select @zap| 214@zap 2159 216drop procedure inc| 217drop procedure incr| 218drop procedure if exists cbv1| 219create procedure cbv1() 220begin 221declare y int default 3; 222call cbv2(y+1, y); 223insert into test.t1 values ("cbv1", y); 224end| 225drop procedure if exists cbv2| 226create procedure cbv2(y1 int, inout y2 int) 227begin 228set y2 = 4711; 229insert into test.t1 values ("cbv2", y1); 230end| 231call cbv1()| 232select * from t1 order by data| 233id data 234cbv2 4 235cbv1 4711 236delete from t1| 237drop procedure cbv1| 238drop procedure cbv2| 239insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)| 240drop procedure if exists sub1| 241create procedure sub1(id char(16), x int) 242insert into test.t1 values (id, x)| 243drop procedure if exists sub2| 244create procedure sub2(id char(16)) 245begin 246declare x int; 247set x = (select sum(t.i) from test.t2 t); 248insert into test.t1 values (id, x); 249end| 250drop procedure if exists sub3| 251create function sub3(i int) returns int deterministic 252return i+1| 253call sub1("sub1a", (select 7))| 254call sub1("sub1b", (select max(i) from t2))| 255call sub1("sub1c", (select i,d from t2 limit 1))| 256ERROR 21000: Operand should contain 1 column(s) 257call sub1("sub1d", (select 1 from (select 1) a))| 258call sub2("sub2")| 259select * from t1 order by id| 260id data 261sub1a 7 262sub1b 3 263sub1d 1 264sub2 6 265select sub3((select max(i) from t2))| 266sub3((select max(i) from t2)) 2674 268drop procedure sub1| 269drop procedure sub2| 270drop function sub3| 271delete from t1| 272delete from t2| 273drop procedure if exists a0| 274create procedure a0(x int) 275while x do 276set x = x-1; 277insert into test.t1 values ("a0", x); 278end while| 279call a0(3)| 280select * from t1 order by data desc| 281id data 282a0 2 283a0 1 284a0 0 285delete from t1| 286drop procedure a0| 287drop procedure if exists a| 288create procedure a(x int) 289while x > 0 do 290set x = x-1; 291insert into test.t1 values ("a", x); 292end while| 293call a(3)| 294select * from t1 order by data desc| 295id data 296a 2 297a 1 298a 0 299delete from t1| 300drop procedure a| 301drop procedure if exists b| 302create procedure b(x int) 303repeat 304insert into test.t1 values (repeat("b",3), x); 305set x = x-1; 306until x = 0 end repeat| 307call b(3)| 308select * from t1 order by data desc| 309id data 310bbb 3 311bbb 2 312bbb 1 313delete from t1| 314drop procedure b| 315drop procedure if exists b2| 316create procedure b2(x int) 317repeat(select 1 into outfile 'b2'); 318insert into test.t1 values (repeat("b2",3), x); 319set x = x-1; 320until x = 0 end repeat| 321drop procedure b2| 322drop procedure if exists c| 323create procedure c(x int) 324hmm: while x > 0 do 325insert into test.t1 values ("c", x); 326set x = x-1; 327iterate hmm; 328insert into test.t1 values ("x", x); 329end while hmm| 330call c(3)| 331select * from t1 order by data desc| 332id data 333c 3 334c 2 335c 1 336delete from t1| 337drop procedure c| 338drop procedure if exists d| 339create procedure d(x int) 340hmm: while x > 0 do 341insert into test.t1 values ("d", x); 342set x = x-1; 343leave hmm; 344insert into test.t1 values ("x", x); 345end while| 346call d(3)| 347select * from t1| 348id data 349d 3 350delete from t1| 351drop procedure d| 352drop procedure if exists e| 353create procedure e(x int) 354foo: loop 355if x = 0 then 356leave foo; 357end if; 358insert into test.t1 values ("e", x); 359set x = x-1; 360end loop foo| 361call e(3)| 362select * from t1 order by data desc| 363id data 364e 3 365e 2 366e 1 367delete from t1| 368drop procedure e| 369drop procedure if exists f| 370create procedure f(x int) 371if x < 0 then 372insert into test.t1 values ("f", 0); 373elseif x = 0 then 374insert into test.t1 values ("f", 1); 375else 376insert into test.t1 values ("f", 2); 377end if| 378call f(-2)| 379call f(0)| 380call f(4)| 381select * from t1 order by data| 382id data 383f 0 384f 1 385f 2 386delete from t1| 387drop procedure f| 388drop procedure if exists g| 389create procedure g(x int) 390case 391when x < 0 then 392insert into test.t1 values ("g", 0); 393when x = 0 then 394insert into test.t1 values ("g", 1); 395else 396insert into test.t1 values ("g", 2); 397end case| 398call g(-42)| 399call g(0)| 400call g(1)| 401select * from t1 order by data| 402id data 403g 0 404g 1 405g 2 406delete from t1| 407drop procedure g| 408drop procedure if exists h| 409create procedure h(x int) 410case x 411when 0 then 412insert into test.t1 values ("h0", x); 413when 1 then 414insert into test.t1 values ("h1", x); 415else 416insert into test.t1 values ("h?", x); 417end case| 418call h(0)| 419call h(1)| 420call h(17)| 421select * from t1 order by data| 422id data 423h0 0 424h1 1 425h? 17 426delete from t1| 427drop procedure h| 428drop procedure if exists i| 429create procedure i(x int) 430foo: 431begin 432if x = 0 then 433leave foo; 434end if; 435insert into test.t1 values ("i", x); 436end foo| 437call i(0)| 438call i(3)| 439select * from t1| 440id data 441i 3 442delete from t1| 443drop procedure i| 444insert into t1 values ("foo", 3), ("bar", 19)| 445insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)| 446drop procedure if exists sel1| 447create procedure sel1() 448begin 449select * from t1 order by data; 450end| 451call sel1()| 452id data 453foo 3 454bar 19 455drop procedure sel1| 456drop procedure if exists sel2| 457create procedure sel2() 458begin 459select * from t1 order by data; 460select * from t2 order by s; 461end| 462call sel2()| 463id data 464foo 3 465bar 19 466s i d 467x 9 4.1 468y -1 19.2 469z 3 2.2 470drop procedure sel2| 471delete from t1| 472delete from t2| 473drop procedure if exists into_test| 474create procedure into_test(x char(16), y int) 475begin 476insert into test.t1 values (x, y); 477select id,data into x,y from test.t1 limit 1; 478insert into test.t1 values (concat(x, "2"), y+2); 479end| 480call into_test("into", 100)| 481select * from t1 order by data| 482id data 483into 100 484into2 102 485delete from t1| 486drop procedure into_test| 487drop procedure if exists into_tes2| 488create procedure into_test2(x char(16), y int) 489begin 490insert into test.t1 values (x, y); 491select id,data into x,@z from test.t1 limit 1; 492insert into test.t1 values (concat(x, "2"), y+2); 493end| 494call into_test2("into", 100)| 495select id,data,@z from t1 order by data| 496id data @z 497into 100 100 498into2 102 100 499delete from t1| 500drop procedure into_test2| 501drop procedure if exists into_test3| 502create procedure into_test3() 503begin 504declare x char(16); 505declare y int; 506select * into x,y from test.t1 limit 1; 507insert into test.t2 values (x, y, 0.0); 508end| 509insert into t1 values ("into3", 19)| 510call into_test3()| 511call into_test3()| 512select * from t2| 513s i d 514into3 19 0 515into3 19 0 516delete from t1| 517delete from t2| 518drop procedure into_test3| 519drop procedure if exists into_test4| 520create procedure into_test4() 521begin 522declare x int; 523select data into x from test.t1 limit 1; 524insert into test.t3 values ("into4", x); 525end| 526delete from t1| 527create table t3 ( s char(16), d int)| 528call into_test4()| 529select * from t3| 530s d 531into4 NULL 532insert into t1 values ("i4", 77)| 533call into_test4()| 534select * from t3| 535s d 536into4 NULL 537into4 77 538delete from t1| 539drop table t3| 540drop procedure into_test4| 541drop procedure if exists into_outfile| 542create procedure into_outfile(x char(16), y int) 543begin 544insert into test.t1 values (x, y); 545select * into outfile "MYSQLTEST_VARDIR/tmp/spout" from test.t1; 546insert into test.t1 values (concat(x, "2"), y+2); 547end| 548call into_outfile("ofile", 1)| 549delete from t1| 550drop procedure into_outfile| 551drop procedure if exists into_dumpfile| 552create procedure into_dumpfile(x char(16), y int) 553begin 554insert into test.t1 values (x, y); 555select * into dumpfile "MYSQLTEST_VARDIR/tmp/spdump" from test.t1 limit 1; 556insert into test.t1 values (concat(x, "2"), y+2); 557end| 558call into_dumpfile("dfile", 1)| 559delete from t1| 560drop procedure into_dumpfile| 561drop procedure if exists create_select| 562create procedure create_select(x char(16), y int) 563begin 564insert into test.t1 values (x, y); 565create temporary table test.t3 select * from test.t1; 566insert into test.t3 values (concat(x, "2"), y+2); 567end| 568call create_select("cs", 90)| 569select * from t1, t3| 570id data id data 571cs 90 cs 90 572cs 90 cs2 92 573drop table t3| 574delete from t1| 575drop procedure create_select| 576drop function if exists e| 577create function e() returns double 578return 2.7182818284590452354| 579set @e = e()| 580select e(), @e| 581e() @e 5822.718281828459045 2.718281828459045 583drop function if exists inc| 584create function inc(i int) returns int 585return i+1| 586select inc(1), inc(99), inc(-71)| 587inc(1) inc(99) inc(-71) 5882 100 -70 589drop function if exists mul| 590create function mul(x int, y int) returns int 591return x*y| 592select mul(1,1), mul(3,5), mul(4711, 666)| 593mul(1,1) mul(3,5) mul(4711, 666) 5941 15 3137526 595drop function if exists append| 596create function append(s1 char(8), s2 char(8)) returns char(16) 597return concat(s1, s2)| 598select append("foo", "bar")| 599append("foo", "bar") 600foobar 601drop function if exists fac| 602create function fac(n int unsigned) returns bigint unsigned 603begin 604declare f bigint unsigned default 1; 605while n > 1 do 606set f = f * n; 607set n = n - 1; 608end while; 609return f; 610end| 611select fac(1), fac(2), fac(5), fac(10)| 612fac(1) fac(2) fac(5) fac(10) 6131 2 120 3628800 614drop function if exists fun| 615create function fun(d double, i int, u int unsigned) returns double 616return mul(inc(i), fac(u)) / e()| 617select fun(2.3, 3, 5)| 618fun(2.3, 3, 5) 619176.58213176229233 620insert into t2 values (append("xxx", "yyy"), mul(4,3), e())| 621insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))| 622select * from t2 where s = append("a", "b")| 623s i d 624ab 24 1324.3659882171924 625select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2) order by i| 626s i d 627xxxyyy 12 2.718281828459045 628ab 24 1324.3659882171924 629select * from t2 where d = e()| 630s i d 631xxxyyy 12 2.718281828459045 632select * from t2 order by i| 633s i d 634xxxyyy 12 2.718281828459045 635ab 24 1324.3659882171924 636delete from t2| 637drop function e| 638drop function inc| 639drop function mul| 640drop function append| 641drop function fun| 642drop procedure if exists hndlr1| 643create procedure hndlr1(val int) 644begin 645declare x int default 0; 646declare foo condition for 1136; 647declare bar condition for sqlstate '42S98'; # Just for testing syntax 648declare zip condition for sqlstate value '42S99'; # Just for testing syntax 649declare continue handler for foo set x = 1; 650insert into test.t1 values ("hndlr1", val, 2); # Too many values 651if (x) then 652insert into test.t1 values ("hndlr1", val); # This instead then 653end if; 654end| 655call hndlr1(42)| 656select * from t1| 657id data 658hndlr1 42 659delete from t1| 660drop procedure hndlr1| 661drop procedure if exists hndlr2| 662create procedure hndlr2(val int) 663begin 664declare x int default 0; 665begin 666declare exit handler for sqlstate '21S01' set x = 1; 667insert into test.t1 values ("hndlr2", val, 2); # Too many values 668end; 669insert into test.t1 values ("hndlr2", x); 670end| 671call hndlr2(42)| 672select * from t1| 673id data 674hndlr2 1 675delete from t1| 676drop procedure hndlr2| 677drop procedure if exists hndlr3| 678create procedure hndlr3(val int) 679begin 680declare x int default 0; 681declare continue handler for sqlexception # Any error 682begin 683declare z int; 684set z = 2 * val; 685set x = 1; 686end; 687if val < 10 then 688begin 689declare y int; 690set y = val + 10; 691insert into test.t1 values ("hndlr3", y, 2); # Too many values 692if x then 693insert into test.t1 values ("hndlr3", y); 694end if; 695end; 696end if; 697end| 698call hndlr3(3)| 699select * from t1| 700id data 701hndlr3 13 702delete from t1| 703drop procedure hndlr3| 704create table t3 ( id char(16), data int )| 705drop procedure if exists hndlr4| 706create procedure hndlr4() 707begin 708declare x int default 0; 709declare val int; # No default 710declare continue handler for sqlstate '02000' set x=1; 711select data into val from test.t3 where id='z' limit 1; # No hits 712insert into test.t3 values ('z', val); 713end| 714call hndlr4()| 715select * from t3| 716id data 717z NULL 718drop table t3| 719drop procedure hndlr4| 720drop procedure if exists cur1| 721create procedure cur1() 722begin 723declare a char(16); 724declare b int; 725declare c double; 726declare done int default 0; 727declare c cursor for select * from test.t2; 728declare continue handler for sqlstate '02000' set done = 1; 729open c; 730repeat 731fetch c into a, b, c; 732if not done then 733insert into test.t1 values (a, b+c); 734end if; 735until done end repeat; 736close c; 737end| 738insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)| 739call cur1()| 740Warnings: 741Error 1329 No data - zero rows fetched, selected, or processed 742select * from t1| 743id data 744foo 40 745bar 15 746zap 663 747drop procedure cur1| 748create table t3 ( s char(16), i int )| 749drop procedure if exists cur2| 750create procedure cur2() 751begin 752declare done int default 0; 753declare c1 cursor for select id,data from test.t1 order by id,data; 754declare c2 cursor for select i from test.t2 order by i; 755declare continue handler for sqlstate '02000' set done = 1; 756open c1; 757open c2; 758repeat 759begin 760declare a char(16); 761declare b,c int; 762fetch from c1 into a, b; 763fetch next from c2 into c; 764if not done then 765if b < c then 766insert into test.t3 values (a, b); 767else 768insert into test.t3 values (a, c); 769end if; 770end if; 771end; 772until done end repeat; 773close c1; 774close c2; 775end| 776call cur2()| 777Warnings: 778Error 1329 No data - zero rows fetched, selected, or processed 779select * from t3 order by i,s| 780s i 781bar 3 782foo 40 783zap 663 784delete from t1| 785delete from t2| 786drop table t3| 787drop procedure cur2| 788drop procedure if exists chistics| 789create procedure chistics() 790language sql 791modifies sql data 792not deterministic 793sql security definer 794comment 'Characteristics procedure test' 795 insert into t1 values ("chistics", 1)| 796show create procedure chistics| 797Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 798chistics CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`() 799 MODIFIES SQL DATA 800 COMMENT 'Characteristics procedure test' 801insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci 802call chistics()| 803select * from t1| 804id data 805chistics 1 806delete from t1| 807alter procedure chistics sql security invoker| 808show create procedure chistics| 809Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 810chistics CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`() 811 MODIFIES SQL DATA 812 SQL SECURITY INVOKER 813 COMMENT 'Characteristics procedure test' 814insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci 815drop procedure chistics| 816drop function if exists chistics| 817create function chistics() returns int 818language sql 819deterministic 820sql security invoker 821comment 'Characteristics procedure test' 822 return 42| 823show create function chistics| 824Function sql_mode Create Function character_set_client collation_connection Database Collation 825chistics CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11) 826 DETERMINISTIC 827 SQL SECURITY INVOKER 828 COMMENT 'Characteristics procedure test' 829return 42 latin1 latin1_swedish_ci latin1_swedish_ci 830select chistics()| 831chistics() 83242 833alter function chistics 834no sql 835comment 'Characteristics function test'| 836show create function chistics| 837Function sql_mode Create Function character_set_client collation_connection Database Collation 838chistics CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11) 839 NO SQL 840 DETERMINISTIC 841 SQL SECURITY INVOKER 842 COMMENT 'Characteristics function test' 843return 42 latin1 latin1_swedish_ci latin1_swedish_ci 844drop function chistics| 845insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)| 846set @@sql_mode = 'ANSI'| 847drop procedure if exists modes$ 848create procedure modes(out c1 int, out c2 int) 849begin 850declare done int default 0; 851declare x int; 852declare c cursor for select data from t1; 853declare continue handler for sqlstate '02000' set done = 1; 854select 1 || 2 into c1; 855set c2 = 0; 856open c; 857repeat 858fetch c into x; 859if not done then 860set c2 = c2 + 1; 861end if; 862until done end repeat; 863close c; 864end$ 865set @@sql_mode = ''| 866set sql_select_limit = 1| 867call modes(@c1, @c2)| 868Warnings: 869Error 1329 No data - zero rows fetched, selected, or processed 870set sql_select_limit = default| 871select @c1, @c2| 872@c1 @c2 87312 3 874delete from t1| 875drop procedure modes| 876create database sp_db1| 877drop database sp_db1| 878create database sp_db2| 879use sp_db2| 880create table t3 ( s char(4), t int )| 881insert into t3 values ("abcd", 42), ("dcba", 666)| 882use test| 883drop database sp_db2| 884create database sp_db3| 885use sp_db3| 886drop procedure if exists dummy| 887create procedure dummy(out x int) 888set x = 42| 889use test| 890drop database sp_db3| 891select type,db,name from mysql.proc where db = 'sp_db3'| 892type db name 893drop procedure if exists rc| 894create procedure rc() 895begin 896delete from t1; 897insert into t1 values ("a", 1), ("b", 2), ("c", 3); 898end| 899call rc()| 900select row_count()| 901row_count() 9023 903update t1 set data=42 where id = "b"; 904select row_count()| 905row_count() 9061 907delete from t1| 908select row_count()| 909row_count() 9103 911delete from t1| 912select row_count()| 913row_count() 9140 915select * from t1| 916id data 917select row_count()| 918row_count() 919-1 920drop procedure rc| 921drop function if exists f0| 922drop function if exists f1| 923drop function if exists f2| 924drop function if exists f3| 925drop function if exists f4| 926drop function if exists f5| 927drop function if exists f6| 928drop function if exists f7| 929drop function if exists f8| 930drop function if exists f9| 931drop function if exists f10| 932drop function if exists f11| 933drop function if exists f12_1| 934drop function if exists f12_2| 935drop view if exists v0| 936drop view if exists v1| 937drop view if exists v2| 938delete from t1| 939delete from t2| 940insert into t1 values ("a", 1), ("b", 2) | 941insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) | 942create function f1() returns int 943return (select sum(data) from t1)| 944select f1()| 945f1() 9463 947select id, f1() from t1 order by id| 948id f1() 949a 3 950b 3 951create function f2() returns int 952return (select data from t1 where data <= (select sum(data) from t1) order by data limit 1)| 953select f2()| 954f2() 9551 956select id, f2() from t1 order by id| 957id f2() 958a 1 959b 1 960create function f3() returns int 961begin 962declare n int; 963declare m int; 964set n:= (select min(data) from t1); 965set m:= (select max(data) from t1); 966return n < m; 967end| 968select f3()| 969f3() 9701 971select id, f3() from t1 order by id| 972id f3() 973a 1 974b 1 975select f1(), f3()| 976f1() f3() 9773 1 978select id, f1(), f3() from t1 order by id| 979id f1() f3() 980a 3 1 981b 3 1 982create function f4() returns double 983return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| 984select f4()| 985f4() 9862 987select s, f4() from t2 order by s| 988s f4() 989a 2 990b 2 991c 2 992create function f5(i int) returns int 993begin 994if i <= 0 then 995return 0; 996elseif i = 1 then 997return (select count(*) from t1 where data = i); 998else 999return (select count(*) + f5( i - 1) from t1 where data = i); 1000end if; 1001end| 1002select f5(1)| 1003f5(1) 10041 1005select f5(2)| 1006ERROR HY000: Recursive stored functions and triggers are not allowed. 1007select f5(3)| 1008ERROR HY000: Recursive stored functions and triggers are not allowed. 1009create function f6() returns int 1010begin 1011declare n int; 1012set n:= f1(); 1013return (select count(*) from t1 where data <= f7() and data <= n); 1014end| 1015create function f7() returns int 1016return (select sum(data) from t1 where data <= f1())| 1017select f6()| 1018f6() 10192 1020select id, f6() from t1 order by id| 1021id f6() 1022a 2 1023b 2 1024create view v1 (a) as select f1()| 1025select * from v1| 1026a 10273 1028select id, a from t1, v1 order by id| 1029id a 1030a 3 1031b 3 1032select * from v1, v1 as v| 1033a a 10343 3 1035create view v2 (a) as select a*10 from v1| 1036select * from v2| 1037a 103830 1039select id, a from t1, v2 order by id| 1040id a 1041a 30 1042b 30 1043select * from v1, v2| 1044a a 10453 30 1046create function f8 () returns int 1047return (select count(*) from v2)| 1048select *, f8() from v1| 1049a f8() 10503 1 1051drop function f1| 1052select * from v1| 1053ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 1054create function f1() returns int 1055return (select sum(data) from t1) + (select sum(data) from v1)| 1056select f1()| 1057ERROR HY000: Recursive stored functions and triggers are not allowed. 1058select * from v1| 1059ERROR HY000: Recursive stored functions and triggers are not allowed. 1060select * from v2| 1061ERROR HY000: Recursive stored functions and triggers are not allowed. 1062drop function f1| 1063create function f1() returns int 1064return (select sum(data) from t1)| 1065create function f0() returns int 1066return (select * from (select 100) as r)| 1067select f0()| 1068f0() 1069100 1070select *, f0() from (select 1) as t| 10711 f0() 10721 100 1073create view v0 as select f0()| 1074select * from v0| 1075f0() 1076100 1077select *, f0() from v0| 1078f0() f0() 1079100 100 1080lock tables t1 read, t1 as t11 read| 1081select f3()| 1082f3() 10831 1084select id, f3() from t1 as t11 order by id| 1085id f3() 1086a 1 1087b 1 1088select f0()| 1089f0() 1090100 1091select * from v0| 1092ERROR HY000: Table 'v0' was not locked with LOCK TABLES 1093select *, f0() from v0, (select 123) as d1| 1094ERROR HY000: Table 'v0' was not locked with LOCK TABLES 1095select id, f3() from t1| 1096ERROR HY000: Table 't1' was not locked with LOCK TABLES 1097select f4()| 1098ERROR HY000: Table 't2' was not locked with LOCK TABLES 1099unlock tables| 1100lock tables v2 read, mysql.proc read| 1101select * from v2| 1102a 110330 1104select * from v1| 1105a 11063 1107select * from v1, t1| 1108ERROR HY000: Table 't1' was not locked with LOCK TABLES 1109select f4()| 1110ERROR HY000: Table 't2' was not locked with LOCK TABLES 1111unlock tables| 1112create function f9() returns int 1113begin 1114declare a, b int; 1115drop temporary table if exists t3; 1116create temporary table t3 (id int); 1117insert into t3 values (1), (2), (3); 1118set a:= (select count(*) from t3); 1119set b:= (select count(*) from t3 t3_alias); 1120return a + b; 1121end| 1122select f9()| 1123f9() 11246 1125select f9() from t1 limit 1| 1126f9() 11276 1128create function f10() returns int 1129begin 1130drop temporary table if exists t3; 1131create temporary table t3 (id int); 1132insert into t3 select id from t4; 1133return (select count(*) from t3); 1134end| 1135select f10()| 1136ERROR 42S02: Table 'test.t4' doesn't exist 1137create table t4 as select 1 as id| 1138select f10()| 1139f10() 11401 1141create function f11() returns int 1142begin 1143drop temporary table if exists t3; 1144create temporary table t3 (id int); 1145insert into t3 values (1), (2), (3); 1146return (select count(*) from t3 as a, t3 as b); 1147end| 1148select f11()| 1149ERROR HY000: Can't reopen table: 'a' 1150select f11() from t1| 1151ERROR HY000: Can't reopen table: 'a' 1152create function f12_1() returns int 1153begin 1154drop temporary table if exists t3; 1155create temporary table t3 (id int); 1156insert into t3 values (1), (2), (3); 1157return f12_2(); 1158end| 1159create function f12_2() returns int 1160return (select count(*) from t3)| 1161drop temporary table t3| 1162select f12_1()| 1163f12_1() 11643 1165select f12_1() from t1 limit 1| 1166f12_1() 11673 1168drop function f0| 1169drop function f1| 1170drop function f2| 1171drop function f3| 1172drop function f4| 1173drop function f5| 1174drop function f6| 1175drop function f7| 1176drop function f8| 1177drop function f9| 1178drop function f10| 1179drop function f11| 1180drop function f12_1| 1181drop function f12_2| 1182drop view v0| 1183drop view v1| 1184drop view v2| 1185truncate table t1 | 1186truncate table t2 | 1187drop table t4| 1188drop table if exists t3| 1189create table t3 (n int unsigned not null primary key, f bigint unsigned)| 1190drop procedure if exists ifac| 1191create procedure ifac(n int unsigned) 1192begin 1193declare i int unsigned default 1; 1194if n > 20 then 1195set n = 20; # bigint overflow otherwise 1196end if; 1197while i <= n do 1198begin 1199insert into test.t3 values (i, fac(i)); 1200set i = i + 1; 1201end; 1202end while; 1203end| 1204call ifac(20)| 1205select * from t3| 1206n f 12071 1 12082 2 12093 6 12104 24 12115 120 12126 720 12137 5040 12148 40320 12159 362880 121610 3628800 121711 39916800 121812 479001600 121913 6227020800 122014 87178291200 122115 1307674368000 122216 20922789888000 122317 355687428096000 122418 6402373705728000 122519 121645100408832000 122620 2432902008176640000 1227drop table t3| 1228show function status like '%f%'| 1229Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1230test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1231drop procedure ifac| 1232drop function fac| 1233show function status like '%f%'| 1234Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1235drop table if exists t3| 1236create table t3 ( 1237i int unsigned not null primary key, 1238p bigint unsigned not null 1239)| 1240insert into t3 values 1241( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), 1242( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), 1243(10, 37), (11, 41), (12, 43), (13, 47), (14, 53), 1244(15, 59), (16, 61), (17, 67), (18, 71), (19, 73), 1245(20, 79), (21, 83), (22, 89), (23, 97), (24, 101), 1246(25, 103), (26, 107), (27, 109), (28, 113), (29, 127), 1247(30, 131), (31, 137), (32, 139), (33, 149), (34, 151), 1248(35, 157), (36, 163), (37, 167), (38, 173), (39, 179), 1249(40, 181), (41, 191), (42, 193), (43, 197), (44, 199)| 1250drop procedure if exists opp| 1251create procedure opp(n bigint unsigned, out pp bool) 1252begin 1253declare r double; 1254declare b, s bigint unsigned default 0; 1255set r = sqrt(n); 1256again: 1257loop 1258if s = 45 then 1259set b = b+200, s = 0; 1260else 1261begin 1262declare p bigint unsigned; 1263select t.p into p from test.t3 t where t.i = s; 1264if b+p > r then 1265set pp = 1; 1266leave again; 1267end if; 1268if mod(n, b+p) = 0 then 1269set pp = 0; 1270leave again; 1271end if; 1272set s = s+1; 1273end; 1274end if; 1275end loop; 1276end| 1277drop procedure if exists ip| 1278create procedure ip(m int unsigned) 1279begin 1280declare p bigint unsigned; 1281declare i int unsigned; 1282set i=45, p=201; 1283while i < m do 1284begin 1285declare pp bool default 0; 1286call opp(p, pp); 1287if pp then 1288insert into test.t3 values (i, p); 1289set i = i+1; 1290end if; 1291set p = p+2; 1292end; 1293end while; 1294end| 1295show create procedure opp| 1296Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1297opp CREATE DEFINER=`root`@`localhost` PROCEDURE `opp`(n bigint unsigned, out pp bool) 1298begin 1299declare r double; 1300declare b, s bigint unsigned default 0; 1301set r = sqrt(n); 1302again: 1303loop 1304if s = 45 then 1305set b = b+200, s = 0; 1306else 1307begin 1308declare p bigint unsigned; 1309select t.p into p from test.t3 t where t.i = s; 1310if b+p > r then 1311set pp = 1; 1312leave again; 1313end if; 1314if mod(n, b+p) = 0 then 1315set pp = 0; 1316leave again; 1317end if; 1318set s = s+1; 1319end; 1320end if; 1321end loop; 1322end latin1 latin1_swedish_ci latin1_swedish_ci 1323show procedure status where name like '%p%' and db='test'| 1324Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1325test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1326test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1327call ip(200)| 1328select * from t3 where i=45 or i=100 or i=199| 1329i p 133045 211 1331100 557 1332199 1229 1333drop table t3| 1334drop procedure opp| 1335drop procedure ip| 1336show procedure status where name like '%p%' and db='test'| 1337Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1338drop procedure if exists bar| 1339create procedure bar(x char(16), y int) 1340comment "111111111111" sql security invoker 1341insert into test.t1 values (x, y)| 1342show procedure status like 'bar'| 1343Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1344test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER 111111111111 latin1 latin1_swedish_ci latin1_swedish_ci 1345alter procedure bar comment "2222222222" sql security definer| 1346alter procedure bar comment "3333333333"| 1347alter procedure bar| 1348show create procedure bar| 1349Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1350bar CREATE DEFINER=`root`@`localhost` PROCEDURE `bar`(x char(16), y int) 1351 COMMENT '3333333333' 1352insert into test.t1 values (x, y) latin1 latin1_swedish_ci latin1_swedish_ci 1353show procedure status like 'bar'| 1354Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1355test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER 3333333333 latin1 latin1_swedish_ci latin1_swedish_ci 1356drop procedure bar| 1357drop procedure if exists p1| 1358create procedure p1 () 1359select (select s1 from t3) from t3| 1360create table t3 (s1 int)| 1361call p1()| 1362(select s1 from t3) 1363insert into t3 values (1)| 1364call p1()| 1365(select s1 from t3) 13661 1367drop procedure p1| 1368drop table t3| 1369drop function if exists foo| 1370create function `foo` () returns int 1371return 5| 1372select `foo` ()| 1373`foo` () 13745 1375drop function `foo`| 1376drop function if exists t1max| 1377create function t1max() returns int 1378begin 1379declare x int; 1380select max(data) into x from t1; 1381return x; 1382end| 1383insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)| 1384select t1max()| 1385t1max() 13865 1387drop function t1max| 1388create table t3 ( 1389v char(16) not null primary key, 1390c int unsigned not null 1391)| 1392create function getcount(s char(16)) returns int 1393begin 1394declare x int; 1395select count(*) into x from t3 where v = s; 1396if x = 0 then 1397insert into t3 values (s, 1); 1398else 1399update t3 set c = c+1 where v = s; 1400end if; 1401return x; 1402end| 1403select * from t1 where data = getcount("bar")| 1404id data 1405zap 1 1406select * from t3| 1407v c 1408bar 4 1409select getcount("zip")| 1410getcount("zip") 14110 1412select getcount("zip")| 1413getcount("zip") 14141 1415select * from t3| 1416v c 1417bar 4 1418zip 2 1419select getcount(id) from t1 where data = 3| 1420getcount(id) 14210 1422select getcount(id) from t1 where data = 5| 1423getcount(id) 14241 1425select * from t3| 1426v c 1427bar 4 1428zip 3 1429foo 1 1430drop table t3| 1431drop function getcount| 1432drop table if exists t3| 1433drop procedure if exists h_ee| 1434drop procedure if exists h_es| 1435drop procedure if exists h_en| 1436drop procedure if exists h_ew| 1437drop procedure if exists h_ex| 1438drop procedure if exists h_se| 1439drop procedure if exists h_ss| 1440drop procedure if exists h_sn| 1441drop procedure if exists h_sw| 1442drop procedure if exists h_sx| 1443drop procedure if exists h_ne| 1444drop procedure if exists h_ns| 1445drop procedure if exists h_nn| 1446drop procedure if exists h_we| 1447drop procedure if exists h_ws| 1448drop procedure if exists h_ww| 1449drop procedure if exists h_xe| 1450drop procedure if exists h_xs| 1451drop procedure if exists h_xx| 1452create table t3 (a smallint primary key)| 1453insert into t3 (a) values (1)| 1454create procedure h_ee() 1455deterministic 1456begin 1457declare continue handler for 1062 -- ER_DUP_ENTRY 1458select 'Outer (bad)' as 'h_ee'; 1459begin 1460declare continue handler for 1062 -- ER_DUP_ENTRY 1461select 'Inner (good)' as 'h_ee'; 1462insert into t3 values (1); 1463end; 1464end| 1465create procedure h_es() 1466deterministic 1467begin 1468declare continue handler for 1062 -- ER_DUP_ENTRY 1469select 'Outer (good)' as 'h_es'; 1470begin 1471-- integrity constraint violation 1472declare continue handler for sqlstate '23000' 1473 select 'Inner (bad)' as 'h_es'; 1474insert into t3 values (1); 1475end; 1476end| 1477create procedure h_en() 1478deterministic 1479begin 1480declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA 1481select 'Outer (good)' as 'h_en'; 1482begin 1483declare x int; 1484declare continue handler for sqlstate '02000' -- no data 1485select 'Inner (bad)' as 'h_en'; 1486select a into x from t3 where a = 42; 1487end; 1488end| 1489create procedure h_ew() 1490deterministic 1491begin 1492declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE 1493select 'Outer (good)' as 'h_ew'; 1494begin 1495declare continue handler for sqlwarning 1496select 'Inner (bad)' as 'h_ew'; 1497insert into t3 values (123456789012); 1498end; 1499delete from t3; 1500insert into t3 values (1); 1501end| 1502create procedure h_ex() 1503deterministic 1504begin 1505declare continue handler for 1062 -- ER_DUP_ENTRY 1506select 'Outer (good)' as 'h_ex'; 1507begin 1508declare continue handler for sqlexception 1509select 'Inner (bad)' as 'h_ex'; 1510insert into t3 values (1); 1511end; 1512end| 1513create procedure h_se() 1514deterministic 1515begin 1516-- integrity constraint violation 1517declare continue handler for sqlstate '23000' 1518select 'Outer (bad)' as 'h_se'; 1519begin 1520declare continue handler for 1062 -- ER_DUP_ENTRY 1521select 'Inner (good)' as 'h_se'; 1522insert into t3 values (1); 1523end; 1524end| 1525create procedure h_ss() 1526deterministic 1527begin 1528-- integrity constraint violation 1529declare continue handler for sqlstate '23000' 1530select 'Outer (bad)' as 'h_ss'; 1531begin 1532-- integrity constraint violation 1533declare continue handler for sqlstate '23000' 1534select 'Inner (good)' as 'h_ss'; 1535insert into t3 values (1); 1536end; 1537end| 1538create procedure h_sn() 1539deterministic 1540begin 1541-- Note: '02000' is more specific than NOT FOUND ; 1542-- there might be other not found states 1543declare continue handler for sqlstate '02000' -- no data 1544select 'Outer (good)' as 'h_sn'; 1545begin 1546declare x int; 1547declare continue handler for not found 1548select 'Inner (bad)' as 'h_sn'; 1549select a into x from t3 where a = 42; 1550end; 1551end| 1552create procedure h_sw() 1553deterministic 1554begin 1555-- data exception - numeric value out of range 1556declare continue handler for sqlstate '22003' 1557 select 'Outer (good)' as 'h_sw'; 1558begin 1559declare continue handler for sqlwarning 1560select 'Inner (bad)' as 'h_sw'; 1561insert into t3 values (123456789012); 1562end; 1563delete from t3; 1564insert into t3 values (1); 1565end| 1566create procedure h_sx() 1567deterministic 1568begin 1569-- integrity constraint violation 1570declare continue handler for sqlstate '23000' 1571select 'Outer (good)' as 'h_sx'; 1572begin 1573declare continue handler for sqlexception 1574select 'Inner (bad)' as 'h_sx'; 1575insert into t3 values (1); 1576end; 1577end| 1578create procedure h_ne() 1579deterministic 1580begin 1581declare continue handler for not found 1582select 'Outer (bad)' as 'h_ne'; 1583begin 1584declare x int; 1585declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA 1586select 'Inner (good)' as 'h_ne'; 1587select a into x from t3 where a = 42; 1588end; 1589end| 1590create procedure h_ns() 1591deterministic 1592begin 1593declare continue handler for not found 1594select 'Outer (bad)' as 'h_ns'; 1595begin 1596declare x int; 1597declare continue handler for sqlstate '02000' -- no data 1598select 'Inner (good)' as 'h_ns'; 1599select a into x from t3 where a = 42; 1600end; 1601end| 1602create procedure h_nn() 1603deterministic 1604begin 1605declare continue handler for not found 1606select 'Outer (bad)' as 'h_nn'; 1607begin 1608declare x int; 1609declare continue handler for not found 1610select 'Inner (good)' as 'h_nn'; 1611select a into x from t3 where a = 42; 1612end; 1613end| 1614create procedure h_we() 1615deterministic 1616begin 1617declare continue handler for sqlwarning 1618select 'Outer (bad)' as 'h_we'; 1619begin 1620declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE 1621select 'Inner (good)' as 'h_we'; 1622insert into t3 values (123456789012); 1623end; 1624delete from t3; 1625insert into t3 values (1); 1626end| 1627create procedure h_ws() 1628deterministic 1629begin 1630declare continue handler for sqlwarning 1631select 'Outer (bad)' as 'h_ws'; 1632begin 1633-- data exception - numeric value out of range 1634declare continue handler for sqlstate '22003' 1635 select 'Inner (good)' as 'h_ws'; 1636insert into t3 values (123456789012); 1637end; 1638delete from t3; 1639insert into t3 values (1); 1640end| 1641create procedure h_ww() 1642deterministic 1643begin 1644declare continue handler for sqlwarning 1645select 'Outer (bad)' as 'h_ww'; 1646begin 1647declare continue handler for sqlwarning 1648select 'Inner (good)' as 'h_ww'; 1649insert into t3 values (123456789012); 1650end; 1651delete from t3; 1652insert into t3 values (1); 1653end| 1654create procedure h_xe() 1655deterministic 1656begin 1657declare continue handler for sqlexception 1658select 'Outer (bad)' as 'h_xe'; 1659begin 1660declare continue handler for 1062 -- ER_DUP_ENTRY 1661select 'Inner (good)' as 'h_xe'; 1662insert into t3 values (1); 1663end; 1664end| 1665create procedure h_xs() 1666deterministic 1667begin 1668declare continue handler for sqlexception 1669select 'Outer (bad)' as 'h_xs'; 1670begin 1671-- integrity constraint violation 1672declare continue handler for sqlstate '23000' 1673 select 'Inner (good)' as 'h_xs'; 1674insert into t3 values (1); 1675end; 1676end| 1677create procedure h_xx() 1678deterministic 1679begin 1680declare continue handler for sqlexception 1681select 'Outer (bad)' as 'h_xx'; 1682begin 1683declare continue handler for sqlexception 1684select 'Inner (good)' as 'h_xx'; 1685insert into t3 values (1); 1686end; 1687end| 1688call h_ee()| 1689h_ee 1690Inner (good) 1691Warnings: 1692Error 1062 Duplicate entry '1' for key 'PRIMARY' 1693call h_es()| 1694h_es 1695Outer (good) 1696Warnings: 1697Error 1062 Duplicate entry '1' for key 'PRIMARY' 1698call h_en()| 1699h_en 1700Outer (good) 1701Warnings: 1702Warning 1329 No data - zero rows fetched, selected, or processed 1703call h_ew()| 1704h_ew 1705Outer (good) 1706call h_ex()| 1707h_ex 1708Outer (good) 1709Warnings: 1710Error 1062 Duplicate entry '1' for key 'PRIMARY' 1711call h_se()| 1712h_se 1713Inner (good) 1714Warnings: 1715Error 1062 Duplicate entry '1' for key 'PRIMARY' 1716call h_ss()| 1717h_ss 1718Inner (good) 1719Warnings: 1720Error 1062 Duplicate entry '1' for key 'PRIMARY' 1721call h_sn()| 1722h_sn 1723Outer (good) 1724Warnings: 1725Warning 1329 No data - zero rows fetched, selected, or processed 1726call h_sw()| 1727h_sw 1728Outer (good) 1729call h_sx()| 1730h_sx 1731Outer (good) 1732Warnings: 1733Error 1062 Duplicate entry '1' for key 'PRIMARY' 1734call h_ne()| 1735h_ne 1736Inner (good) 1737Warnings: 1738Warning 1329 No data - zero rows fetched, selected, or processed 1739call h_ns()| 1740h_ns 1741Inner (good) 1742Warnings: 1743Warning 1329 No data - zero rows fetched, selected, or processed 1744call h_nn()| 1745h_nn 1746Inner (good) 1747Warnings: 1748Warning 1329 No data - zero rows fetched, selected, or processed 1749call h_we()| 1750h_we 1751Inner (good) 1752call h_ws()| 1753h_ws 1754Inner (good) 1755call h_ww()| 1756h_ww 1757Inner (good) 1758call h_xe()| 1759h_xe 1760Inner (good) 1761Warnings: 1762Error 1062 Duplicate entry '1' for key 'PRIMARY' 1763call h_xs()| 1764h_xs 1765Inner (good) 1766Warnings: 1767Error 1062 Duplicate entry '1' for key 'PRIMARY' 1768call h_xx()| 1769h_xx 1770Inner (good) 1771Warnings: 1772Error 1062 Duplicate entry '1' for key 'PRIMARY' 1773drop table t3| 1774drop procedure h_ee| 1775drop procedure h_es| 1776drop procedure h_en| 1777drop procedure h_ew| 1778drop procedure h_ex| 1779drop procedure h_se| 1780drop procedure h_ss| 1781drop procedure h_sn| 1782drop procedure h_sw| 1783drop procedure h_sx| 1784drop procedure h_ne| 1785drop procedure h_ns| 1786drop procedure h_nn| 1787drop procedure h_we| 1788drop procedure h_ws| 1789drop procedure h_ww| 1790drop procedure h_xe| 1791drop procedure h_xs| 1792drop procedure h_xx| 1793drop procedure if exists bug822| 1794create procedure bug822(a_id char(16), a_data int) 1795begin 1796declare n int; 1797select count(*) into n from t1 where id = a_id and data = a_data; 1798if n = 0 then 1799insert into t1 (id, data) values (a_id, a_data); 1800end if; 1801end| 1802delete from t1| 1803call bug822('foo', 42)| 1804call bug822('foo', 42)| 1805call bug822('bar', 666)| 1806select * from t1 order by data| 1807id data 1808foo 42 1809bar 666 1810delete from t1| 1811drop procedure bug822| 1812drop procedure if exists bug1495| 1813create procedure bug1495() 1814begin 1815declare x int; 1816select data into x from t1 order by id limit 1; 1817if x > 10 then 1818insert into t1 values ("less", x-10); 1819else 1820insert into t1 values ("more", x+10); 1821end if; 1822end| 1823insert into t1 values ('foo', 12)| 1824call bug1495()| 1825delete from t1 where id='foo'| 1826insert into t1 values ('bar', 7)| 1827call bug1495()| 1828delete from t1 where id='bar'| 1829select * from t1 order by data| 1830id data 1831less 2 1832more 17 1833delete from t1| 1834drop procedure bug1495| 1835drop procedure if exists bug1547| 1836create procedure bug1547(s char(16)) 1837begin 1838declare x int; 1839select data into x from t1 where s = id limit 1; 1840if x > 10 then 1841insert into t1 values ("less", x-10); 1842else 1843insert into t1 values ("more", x+10); 1844end if; 1845end| 1846insert into t1 values ("foo", 12), ("bar", 7)| 1847call bug1547("foo")| 1848call bug1547("bar")| 1849select * from t1 order by id| 1850id data 1851bar 7 1852foo 12 1853less 2 1854more 17 1855delete from t1| 1856drop procedure bug1547| 1857drop table if exists t70| 1858create table t70 (s1 int,s2 int)| 1859insert into t70 values (1,2)| 1860drop procedure if exists bug1656| 1861create procedure bug1656(out p1 int, out p2 int) 1862select * into p1, p1 from t70| 1863call bug1656(@1, @2)| 1864select @1, @2| 1865@1 @2 18662 NULL 1867drop table t70| 1868drop procedure bug1656| 1869create table t3(a int)| 1870drop procedure if exists bug1862| 1871create procedure bug1862() 1872begin 1873insert into t3 values(2); 1874flush tables; 1875end| 1876call bug1862()| 1877call bug1862()| 1878select * from t3| 1879a 18802 18812 1882drop table t3| 1883drop procedure bug1862| 1884drop procedure if exists bug1874| 1885create procedure bug1874() 1886begin 1887declare x int; 1888declare y double; 1889select max(data) into x from t1; 1890insert into t2 values ("max", x, 0); 1891select min(data) into x from t1; 1892insert into t2 values ("min", x, 0); 1893select sum(data) into x from t1; 1894insert into t2 values ("sum", x, 0); 1895select avg(data) into y from t1; 1896insert into t2 values ("avg", 0, y); 1897end| 1898insert into t1 (data) values (3), (1), (5), (9), (4)| 1899call bug1874()| 1900select * from t2 order by i| 1901s i d 1902avg 0 4.4 1903min 1 0 1904max 9 0 1905sum 22 0 1906delete from t1| 1907delete from t2| 1908drop procedure bug1874| 1909drop procedure if exists bug2260| 1910create procedure bug2260() 1911begin 1912declare v1 int; 1913declare c1 cursor for select data from t1; 1914declare continue handler for not found set @x2 = 1; 1915open c1; 1916fetch c1 into v1; 1917set @x2 = 2; 1918close c1; 1919end| 1920call bug2260()| 1921Warnings: 1922Error 1329 No data - zero rows fetched, selected, or processed 1923select @x2| 1924@x2 19252 1926drop procedure bug2260| 1927drop procedure if exists bug2267_1| 1928create procedure bug2267_1() 1929begin 1930show procedure status where db='test'; 1931end| 1932drop procedure if exists bug2267_2| 1933create procedure bug2267_2() 1934begin 1935show function status where db='test'; 1936end| 1937drop procedure if exists bug2267_3| 1938create procedure bug2267_3() 1939begin 1940show create procedure bug2267_1; 1941end| 1942drop procedure if exists bug2267_4| 1943drop function if exists bug2267_4| 1944create procedure bug2267_4() 1945begin 1946show create function bug2267_4; 1947end| 1948create function bug2267_4() returns int return 100| 1949call bug2267_1()| 1950Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1951test bug2267_1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1952test bug2267_2 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1953test bug2267_3 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1954test bug2267_4 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1955call bug2267_2()| 1956Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1957test bug2267_4 FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1958call bug2267_3()| 1959Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1960bug2267_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2267_1`() 1961begin 1962show procedure status where db='test'; 1963end latin1 latin1_swedish_ci latin1_swedish_ci 1964call bug2267_4()| 1965Function sql_mode Create Function character_set_client collation_connection Database Collation 1966bug2267_4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2267_4`() RETURNS int(11) 1967return 100 latin1 latin1_swedish_ci latin1_swedish_ci 1968drop procedure bug2267_1| 1969drop procedure bug2267_2| 1970drop procedure bug2267_3| 1971drop procedure bug2267_4| 1972drop function bug2267_4| 1973drop procedure if exists bug2227| 1974create procedure bug2227(x int) 1975begin 1976declare y float default 2.6; 1977declare z char(16) default "zzz"; 1978select 1.3, x, y, 42, z; 1979end| 1980call bug2227(9)| 19811.3 x y 42 z 19821.3 9 2.6 42 zzz 1983drop procedure bug2227| 1984drop procedure if exists bug2614| 1985create procedure bug2614() 1986begin 1987drop table if exists t3; 1988create table t3 (id int default '0' not null); 1989insert into t3 select 12; 1990insert into t3 select * from t3; 1991end| 1992call bug2614()| 1993call bug2614()| 1994drop table t3| 1995drop procedure bug2614| 1996drop function if exists bug2674| 1997create function bug2674() returns int 1998return @@sort_buffer_size| 1999set @osbs = @@sort_buffer_size| 2000set @@sort_buffer_size = 262000| 2001select bug2674()| 2002bug2674() 2003262000 2004drop function bug2674| 2005set @@sort_buffer_size = @osbs| 2006drop procedure if exists bug3259_1 | 2007create procedure bug3259_1 () begin end| 2008drop procedure if exists BUG3259_2 | 2009create procedure BUG3259_2 () begin end| 2010drop procedure if exists Bug3259_3 | 2011create procedure Bug3259_3 () begin end| 2012call BUG3259_1()| 2013call BUG3259_1()| 2014call bug3259_2()| 2015call Bug3259_2()| 2016call bug3259_3()| 2017call bUG3259_3()| 2018drop procedure bUg3259_1| 2019drop procedure BuG3259_2| 2020drop procedure BUG3259_3| 2021drop function if exists bug2772| 2022create function bug2772() returns char(10) character set latin2 2023return 'a'| 2024select bug2772()| 2025bug2772() 2026a 2027drop function bug2772| 2028drop procedure if exists bug2776_1| 2029create procedure bug2776_1(out x int) 2030begin 2031declare v int; 2032set v = default; 2033set x = v; 2034end| 2035drop procedure if exists bug2776_2| 2036create procedure bug2776_2(out x int) 2037begin 2038declare v int default 42; 2039set v = default; 2040set x = v; 2041end| 2042set @x = 1| 2043call bug2776_1(@x)| 2044select @x| 2045@x 2046NULL 2047call bug2776_2(@x)| 2048select @x| 2049@x 205042 2051drop procedure bug2776_1| 2052drop procedure bug2776_2| 2053create table t3 (s1 smallint)| 2054insert into t3 values (123456789012)| 2055Warnings: 2056Warning 1264 Out of range value for column 's1' at row 1 2057drop procedure if exists bug2780| 2058create procedure bug2780() 2059begin 2060declare exit handler for sqlwarning set @x = 1; 2061set @x = 0; 2062insert into t3 values (123456789012); 2063insert into t3 values (0); 2064end| 2065call bug2780()| 2066Warnings: 2067Warning 1264 Out of range value for column 's1' at row 1 2068select @x| 2069@x 20701 2071select * from t3| 2072s1 207332767 207432767 2075drop procedure bug2780| 2076drop table t3| 2077create table t3 (content varchar(10) )| 2078insert into t3 values ("test1")| 2079insert into t3 values ("test2")| 2080create table t4 (f1 int, rc int, t3 int)| 2081drop procedure if exists bug1863| 2082create procedure bug1863(in1 int) 2083begin 2084declare ind int default 0; 2085declare t1 int; 2086declare t2 int; 2087declare t3 int; 2088declare rc int default 0; 2089declare continue handler for 1065 set rc = 1; 2090drop temporary table if exists temp_t1; 2091create temporary table temp_t1 ( 2092f1 int auto_increment, f2 varchar(20), primary key (f1) 2093); 2094insert into temp_t1 (f2) select content from t3; 2095select f2 into t3 from temp_t1 where f1 = 10; 2096if (rc) then 2097insert into t4 values (1, rc, t3); 2098end if; 2099insert into t4 values (2, rc, t3); 2100end| 2101call bug1863(10)| 2102call bug1863(10)| 2103select * from t4| 2104f1 rc t3 21052 0 NULL 21062 0 NULL 2107drop procedure bug1863| 2108drop temporary table temp_t1; 2109drop table t3, t4| 2110create table t3 ( 2111OrderID int not null, 2112MarketID int, 2113primary key (OrderID) 2114)| 2115create table t4 ( 2116MarketID int not null, 2117Market varchar(60), 2118Status char(1), 2119primary key (MarketID) 2120)| 2121insert t3 (OrderID,MarketID) values (1,1)| 2122insert t3 (OrderID,MarketID) values (2,2)| 2123insert t4 (MarketID,Market,Status) values (1,"MarketID One","A")| 2124insert t4 (MarketID,Market,Status) values (2,"MarketID Two","A")| 2125drop procedure if exists bug2656_1| 2126create procedure bug2656_1() 2127begin 2128select 2129m.Market 2130from t4 m JOIN t3 o 2131ON o.MarketID != 1 and o.MarketID = m.MarketID; 2132end | 2133drop procedure if exists bug2656_2| 2134create procedure bug2656_2() 2135begin 2136select 2137m.Market 2138from 2139t4 m, t3 o 2140where 2141m.MarketID != 1 and m.MarketID = o.MarketID; 2142end | 2143call bug2656_1()| 2144Market 2145MarketID Two 2146call bug2656_1()| 2147Market 2148MarketID Two 2149call bug2656_2()| 2150Market 2151MarketID Two 2152call bug2656_2()| 2153Market 2154MarketID Two 2155drop procedure bug2656_1| 2156drop procedure bug2656_2| 2157drop table t3, t4| 2158drop procedure if exists bug3426| 2159create procedure bug3426(in_time int unsigned, out x int) 2160begin 2161if in_time is null then 2162set @stamped_time=10; 2163set x=1; 2164else 2165set @stamped_time=in_time; 2166set x=2; 2167end if; 2168end| 2169set time_zone='+03:00'; 2170call bug3426(1000, @i)| 2171select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| 2172@i time 21732 01-01-1970 03:16:40 2174call bug3426(NULL, @i)| 2175select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| 2176@i time 21771 01-01-1970 03:00:10 2178alter procedure bug3426 sql security invoker| 2179call bug3426(NULL, @i)| 2180select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| 2181@i time 21821 01-01-1970 03:00:10 2183call bug3426(1000, @i)| 2184select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| 2185@i time 21862 01-01-1970 03:16:40 2187drop procedure bug3426| 2188create table t3 ( 2189id int unsigned auto_increment not null primary key, 2190title VARCHAR(200), 2191body text, 2192fulltext (title,body) 2193)| 2194insert into t3 (title,body) values 2195('MySQL Tutorial','DBMS stands for DataBase ...'), 2196('How To Use MySQL Well','After you went through a ...'), 2197('Optimizing MySQL','In this tutorial we will show ...'), 2198('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 2199('MySQL vs. YourSQL','In the following database comparison ...'), 2200('MySQL Security','When configured properly, MySQL ...')| 2201drop procedure if exists bug3734 | 2202create procedure bug3734 (param1 varchar(100)) 2203select * from t3 where match (title,body) against (param1)| 2204call bug3734('database')| 2205id title body 22065 MySQL vs. YourSQL In the following database comparison ... 22071 MySQL Tutorial DBMS stands for DataBase ... 2208call bug3734('Security')| 2209id title body 22106 MySQL Security When configured properly, MySQL ... 2211drop procedure bug3734| 2212drop table t3| 2213drop procedure if exists bug3863| 2214create procedure bug3863() 2215begin 2216set @a = 0; 2217while @a < 5 do 2218set @a = @a + 1; 2219end while; 2220end| 2221call bug3863()| 2222select @a| 2223@a 22245 2225call bug3863()| 2226select @a| 2227@a 22285 2229drop procedure bug3863| 2230create table t3 ( 2231id int(10) unsigned not null default 0, 2232rid int(10) unsigned not null default 0, 2233msg text not null, 2234primary key (id), 2235unique key rid (rid, id) 2236)| 2237drop procedure if exists bug2460_1| 2238create procedure bug2460_1(in v int) 2239begin 2240( select n0.id from t3 as n0 where n0.id = v ) 2241union 2242( select n0.id from t3 as n0, t3 as n1 2243where n0.id = n1.rid and n1.id = v ) 2244union 2245( select n0.id from t3 as n0, t3 as n1, t3 as n2 2246where n0.id = n1.rid and n1.id = n2.rid and n2.id = v ); 2247end| 2248call bug2460_1(2)| 2249id 2250call bug2460_1(2)| 2251id 2252insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')| 2253call bug2460_1(2)| 2254id 22552 22561 2257call bug2460_1(2)| 2258id 22592 22601 2261drop procedure if exists bug2460_2| 2262create procedure bug2460_2() 2263begin 2264drop table if exists t3; 2265create temporary table t3 (s1 int); 2266insert into t3 select 1 union select 1; 2267end| 2268call bug2460_2()| 2269call bug2460_2()| 2270select * from t3| 2271s1 22721 2273drop procedure bug2460_1| 2274drop procedure bug2460_2| 2275drop table t3| 2276set @@sql_mode = ''| 2277drop procedure if exists bug2564_1| 2278create procedure bug2564_1() 2279comment 'Joe''s procedure' 2280 insert into `t1` values ("foo", 1)| 2281set @@sql_mode = 'ANSI_QUOTES'| 2282drop procedure if exists bug2564_2| 2283create procedure bug2564_2() 2284insert into "t1" values ('foo', 1)| 2285set @@sql_mode = ''$ 2286drop function if exists bug2564_3$ 2287create function bug2564_3(x int, y int) returns int 2288return x || y$ 2289set @@sql_mode = 'ANSI'$ 2290drop function if exists bug2564_4$ 2291create function bug2564_4(x int, y int) returns int 2292return x || y$ 2293set @@sql_mode = ''| 2294show create procedure bug2564_1| 2295Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 2296bug2564_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2564_1`() 2297 COMMENT 'Joe''s procedure' 2298insert into `t1` values ("foo", 1) latin1 latin1_swedish_ci latin1_swedish_ci 2299show create procedure bug2564_2| 2300Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 2301bug2564_2 ANSI_QUOTES CREATE DEFINER="root"@"localhost" PROCEDURE "bug2564_2"() 2302insert into "t1" values ('foo', 1) latin1 latin1_swedish_ci latin1_swedish_ci 2303show create function bug2564_3| 2304Function sql_mode Create Function character_set_client collation_connection Database Collation 2305bug2564_3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2564_3`(x int, y int) RETURNS int(11) 2306return x || y latin1 latin1_swedish_ci latin1_swedish_ci 2307show create function bug2564_4| 2308Function sql_mode Create Function character_set_client collation_connection Database Collation 2309bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI CREATE DEFINER="root"@"localhost" FUNCTION "bug2564_4"(x int, y int) RETURNS int(11) 2310return x || y latin1 latin1_swedish_ci latin1_swedish_ci 2311drop procedure bug2564_1| 2312drop procedure bug2564_2| 2313drop function bug2564_3| 2314drop function bug2564_4| 2315drop function if exists bug3132| 2316create function bug3132(s char(20)) returns char(50) 2317return concat('Hello, ', s, '!')| 2318select bug3132('Bob') union all select bug3132('Judy')| 2319bug3132('Bob') 2320Hello, Bob! 2321Hello, Judy! 2322drop function bug3132| 2323drop procedure if exists bug3843| 2324create procedure bug3843() 2325analyze table t1| 2326call bug3843()| 2327Table Op Msg_type Msg_text 2328test.t1 analyze status OK 2329call bug3843()| 2330Table Op Msg_type Msg_text 2331test.t1 analyze status Table is already up to date 2332select 1+2| 23331+2 23343 2335drop procedure bug3843| 2336create table t3 ( s1 char(10) )| 2337insert into t3 values ('a'), ('b')| 2338drop procedure if exists bug3368| 2339create procedure bug3368(v char(10)) 2340begin 2341select group_concat(v) from t3; 2342end| 2343call bug3368('x')| 2344group_concat(v) 2345x,x 2346call bug3368('yz')| 2347group_concat(v) 2348yz,yz 2349drop procedure bug3368| 2350drop table t3| 2351create table t3 (f1 int, f2 int)| 2352insert into t3 values (1,1)| 2353drop procedure if exists bug4579_1| 2354create procedure bug4579_1 () 2355begin 2356declare sf1 int; 2357select f1 into sf1 from t3 where f1=1 and f2=1; 2358update t3 set f2 = f2 + 1 where f1=1 and f2=1; 2359call bug4579_2(); 2360end| 2361drop procedure if exists bug4579_2| 2362create procedure bug4579_2 () 2363begin 2364end| 2365call bug4579_1()| 2366call bug4579_1()| 2367call bug4579_1()| 2368drop procedure bug4579_1| 2369drop procedure bug4579_2| 2370drop table t3| 2371drop procedure if exists bug2773| 2372create function bug2773() returns int return null| 2373create table t3 as select bug2773()| 2374show create table t3| 2375Table Create Table 2376t3 CREATE TABLE `t3` ( 2377 `bug2773()` int(11) DEFAULT NULL 2378) ENGINE=MyISAM DEFAULT CHARSET=latin1 2379drop table t3| 2380drop function bug2773| 2381drop procedure if exists bug3788| 2382create function bug3788() returns date return cast("2005-03-04" as date)| 2383select bug3788()| 2384bug3788() 23852005-03-04 2386drop function bug3788| 2387create function bug3788() returns binary(1) return 5| 2388select bug3788()| 2389bug3788() 23905 2391drop function bug3788| 2392create table t3 (f1 int, f2 int, f3 int)| 2393insert into t3 values (1,1,1)| 2394drop procedure if exists bug4726| 2395create procedure bug4726() 2396begin 2397declare tmp_o_id INT; 2398declare tmp_d_id INT default 1; 2399while tmp_d_id <= 2 do 2400begin 2401select f1 into tmp_o_id from t3 where f2=1 and f3=1; 2402set tmp_d_id = tmp_d_id + 1; 2403end; 2404end while; 2405end| 2406call bug4726()| 2407call bug4726()| 2408call bug4726()| 2409drop procedure bug4726| 2410drop table t3| 2411drop procedure if exists bug4902| 2412create procedure bug4902() 2413begin 2414show charset like 'foo'; 2415show collation like 'foo'; 2416show create table t1; 2417show create database test; 2418show databases like 'foo'; 2419show errors; 2420show columns from t1; 2421show keys from t1; 2422show open tables like 'foo'; 2423# Removed because result will differ in embedded mode. 2424#show privileges; 2425show status like 'foo'; 2426show tables like 'foo'; 2427show variables like 'foo'; 2428show warnings; 2429end| 2430call bug4902()| 2431Charset Description Default collation Maxlen 2432Collation Charset Id Default Compiled Sortlen 2433Table Create Table 2434t1 CREATE TABLE `t1` ( 2435 `id` char(16) NOT NULL DEFAULT '', 2436 `data` int(11) NOT NULL 2437) ENGINE=MyISAM DEFAULT CHARSET=latin1 2438Database Create Database 2439test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ 2440Database (foo) 2441Level Code Message 2442Field Type Null Key Default Extra 2443id char(16) NO 2444data int(11) NO NULL 2445Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2446Database Table In_use Name_locked 2447Variable_name Value 2448Tables_in_test (foo) 2449Variable_name Value 2450Level Code Message 2451call bug4902()| 2452Charset Description Default collation Maxlen 2453Collation Charset Id Default Compiled Sortlen 2454Table Create Table 2455t1 CREATE TABLE `t1` ( 2456 `id` char(16) NOT NULL DEFAULT '', 2457 `data` int(11) NOT NULL 2458) ENGINE=MyISAM DEFAULT CHARSET=latin1 2459Database Create Database 2460test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ 2461Database (foo) 2462Level Code Message 2463Field Type Null Key Default Extra 2464id char(16) NO 2465data int(11) NO NULL 2466Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2467Database Table In_use Name_locked 2468Variable_name Value 2469Tables_in_test (foo) 2470Variable_name Value 2471Level Code Message 2472drop procedure bug4902| 2473drop procedure if exists bug4904| 2474create procedure bug4904() 2475begin 2476declare continue handler for sqlstate 'HY000' begin end; 2477create table t2 as select * from t3; 2478end| 2479call bug4904()| 2480ERROR 42S02: Table 'test.t3' doesn't exist 2481drop procedure bug4904| 2482create table t3 (s1 char character set latin1, s2 char character set latin2)| 2483drop procedure if exists bug4904| 2484create procedure bug4904 () 2485begin 2486declare continue handler for sqlstate 'HY000' begin end; 2487select s1 from t3 union select s2 from t3; 2488end| 2489call bug4904()| 2490Warnings: 2491Error 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin2_general_ci,IMPLICIT) for operation 'UNION' 2492drop procedure bug4904| 2493drop table t3| 2494drop procedure if exists bug336| 2495create procedure bug336(out y int) 2496begin 2497declare x int; 2498set x = (select sum(t.data) from test.t1 t); 2499set y = x; 2500end| 2501insert into t1 values ("a", 2), ("b", 3)| 2502call bug336(@y)| 2503select @y| 2504@y 25055 2506delete from t1| 2507drop procedure bug336| 2508drop procedure if exists bug3157| 2509create procedure bug3157() 2510begin 2511if exists(select * from t1) then 2512set @n= @n + 1; 2513end if; 2514if (select count(*) from t1) then 2515set @n= @n + 1; 2516end if; 2517end| 2518set @n = 0| 2519insert into t1 values ("a", 1)| 2520call bug3157()| 2521select @n| 2522@n 25232 2524delete from t1| 2525drop procedure bug3157| 2526drop procedure if exists bug5251| 2527create procedure bug5251() 2528begin 2529end| 2530select created into @c1 from mysql.proc 2531where db='test' and name='bug5251'| 2532alter procedure bug5251 comment 'foobar'| 2533select count(*) from mysql.proc 2534where db='test' and name='bug5251' and created = @c1| 2535count(*) 25361 2537drop procedure bug5251| 2538drop procedure if exists bug5251| 2539create procedure bug5251() 2540checksum table t1| 2541call bug5251()| 2542Table Checksum 2543test.t1 0 2544call bug5251()| 2545Table Checksum 2546test.t1 0 2547drop procedure bug5251| 2548drop procedure if exists bug5287| 2549create procedure bug5287(param1 int) 2550label1: 2551begin 2552declare c cursor for select 5; 2553loop 2554if param1 >= 0 then 2555leave label1; 2556end if; 2557end loop; 2558end| 2559call bug5287(1)| 2560drop procedure bug5287| 2561drop procedure if exists bug5307| 2562create procedure bug5307() 2563begin 2564end; set @x = 3| 2565call bug5307()| 2566select @x| 2567@x 25683 2569drop procedure bug5307| 2570drop procedure if exists bug5258| 2571create procedure bug5258() 2572begin 2573end| 2574drop procedure if exists bug5258_aux| 2575create procedure bug5258_aux() 2576begin 2577declare c, m char(19); 2578select created,modified into c,m from mysql.proc where name = 'bug5258'; 2579if c = m then 2580select 'Ok'; 2581else 2582select c, m; 2583end if; 2584end| 2585call bug5258_aux()| 2586Ok 2587Ok 2588drop procedure bug5258| 2589drop procedure bug5258_aux| 2590drop function if exists bug4487| 2591create function bug4487() returns char 2592begin 2593declare v char; 2594return v; 2595end| 2596select bug4487()| 2597bug4487() 2598NULL 2599drop function bug4487| 2600drop procedure if exists bug4941| 2601drop procedure if exists bug4941| 2602create procedure bug4941(out x int) 2603begin 2604declare c cursor for select i from t2 limit 1; 2605open c; 2606fetch c into x; 2607close c; 2608end| 2609insert into t2 values (null, null, null)| 2610set @x = 42| 2611call bug4941(@x)| 2612select @x| 2613@x 2614NULL 2615delete from t1| 2616drop procedure bug4941| 2617drop procedure if exists bug4905| 2618create table t3 (s1 int,primary key (s1))| 2619drop procedure if exists bug4905| 2620create procedure bug4905() 2621begin 2622declare v int; 2623declare continue handler for sqlstate '23000' set v = 5; 2624insert into t3 values (1); 2625end| 2626call bug4905()| 2627select row_count()| 2628row_count() 26291 2630call bug4905()| 2631Warnings: 2632Error 1062 Duplicate entry '1' for key 'PRIMARY' 2633select row_count()| 2634row_count() 2635-1 2636call bug4905()| 2637Warnings: 2638Error 1062 Duplicate entry '1' for key 'PRIMARY' 2639select row_count()| 2640row_count() 2641-1 2642select * from t3| 2643s1 26441 2645drop procedure bug4905| 2646drop table t3| 2647drop procedure if exists bug6029| 2648drop procedure if exists bug6029| 2649create procedure bug6029() 2650begin 2651declare exit handler for 1136 select '1136'; 2652declare exit handler for sqlstate '23000' select 'sqlstate 23000'; 2653declare continue handler for sqlexception select 'sqlexception'; 2654insert into t3 values (1); 2655insert into t3 values (1,2); 2656end| 2657create table t3 (s1 int, primary key (s1))| 2658insert into t3 values (1)| 2659call bug6029()| 2660sqlstate 23000 2661sqlstate 23000 2662Warnings: 2663Error 1062 Duplicate entry '1' for key 'PRIMARY' 2664delete from t3| 2665call bug6029()| 26661136 26671136 2668Warnings: 2669Error 1136 Column count doesn't match value count at row 1 2670drop procedure bug6029| 2671drop table t3| 2672drop procedure if exists bug8540| 2673create procedure bug8540() 2674begin 2675declare x int default 1; 2676select x as y, x+0 as z; 2677end| 2678call bug8540()| 2679y z 26801 1 2681drop procedure bug8540| 2682create table t3 (s1 int)| 2683drop procedure if exists bug6642| 2684create procedure bug6642() 2685select abs(count(s1)) from t3| 2686call bug6642()| 2687abs(count(s1)) 26880 2689call bug6642()| 2690abs(count(s1)) 26910 2692drop procedure bug6642| 2693insert into t3 values (0),(1)| 2694drop procedure if exists bug7013| 2695create procedure bug7013() 2696select s1,count(s1) from t3 group by s1 with rollup| 2697call bug7013()| 2698s1 count(s1) 26990 1 27001 1 2701NULL 2 2702call bug7013()| 2703s1 count(s1) 27040 1 27051 1 2706NULL 2 2707drop procedure bug7013| 2708drop table if exists t4| 2709create table t4 ( 2710a mediumint(8) unsigned not null auto_increment, 2711b smallint(5) unsigned not null, 2712c char(32) not null, 2713primary key (a) 2714) engine=myisam default charset=latin1| 2715insert into t4 values (1, 2, 'oneword')| 2716insert into t4 values (2, 2, 'anotherword')| 2717drop procedure if exists bug7743| 2718create procedure bug7743 ( searchstring char(28) ) 2719begin 2720declare var mediumint(8) unsigned; 2721select a into var from t4 where b = 2 and c = binary searchstring limit 1; 2722select var; 2723end| 2724call bug7743("oneword")| 2725var 27261 2727call bug7743("OneWord")| 2728var 2729NULL 2730Warnings: 2731Warning 1329 No data - zero rows fetched, selected, or processed 2732call bug7743("anotherword")| 2733var 27342 2735call bug7743("AnotherWord")| 2736var 2737NULL 2738Warnings: 2739Warning 1329 No data - zero rows fetched, selected, or processed 2740drop procedure bug7743| 2741drop table t4| 2742delete from t3| 2743insert into t3 values(1)| 2744drop procedure if exists bug7992_1| 2745Warnings: 2746Note 1305 PROCEDURE test.bug7992_1 does not exist 2747drop procedure if exists bug7992_2| 2748Warnings: 2749Note 1305 PROCEDURE test.bug7992_2 does not exist 2750create procedure bug7992_1() 2751begin 2752declare i int; 2753select max(s1)+1 into i from t3; 2754end| 2755create procedure bug7992_2() 2756insert into t3 (s1) select max(t4.s1)+1 from t3 as t4| 2757call bug7992_1()| 2758call bug7992_1()| 2759call bug7992_2()| 2760call bug7992_2()| 2761drop procedure bug7992_1| 2762drop procedure bug7992_2| 2763drop table t3| 2764create table t3 ( userid bigint(20) not null default 0 )| 2765drop procedure if exists bug8116| 2766create procedure bug8116(in _userid int) 2767select * from t3 where userid = _userid| 2768call bug8116(42)| 2769userid 2770call bug8116(42)| 2771userid 2772drop procedure bug8116| 2773drop table t3| 2774drop procedure if exists bug6857| 2775create procedure bug6857() 2776begin 2777declare t0, t1 int; 2778declare plus bool default 0; 2779set t0 = unix_timestamp(); 2780select sleep(1.1); 2781set t1 = unix_timestamp(); 2782if t1 > t0 then 2783set plus = 1; 2784end if; 2785select plus; 2786end| 2787call bug6857()| 2788sleep(1.1) 27890 2790plus 27911 2792drop procedure bug6857| 2793drop procedure if exists bug8757| 2794create procedure bug8757() 2795begin 2796declare x int; 2797declare c1 cursor for select data from t1 limit 1; 2798begin 2799declare y int; 2800declare c2 cursor for select i from t2 limit 1; 2801open c2; 2802fetch c2 into y; 2803close c2; 2804select 2,y; 2805end; 2806open c1; 2807fetch c1 into x; 2808close c1; 2809select 1,x; 2810end| 2811delete from t1| 2812delete from t2| 2813insert into t1 values ("x", 1)| 2814insert into t2 values ("y", 2, 0.0)| 2815call bug8757()| 28162 y 28172 2 28181 x 28191 1 2820delete from t1| 2821delete from t2| 2822drop procedure bug8757| 2823drop procedure if exists bug8762| 2824drop procedure if exists bug8762; create procedure bug8762() begin end| 2825drop procedure if exists bug8762; create procedure bug8762() begin end| 2826drop procedure bug8762| 2827drop function if exists bug5240| 2828create function bug5240 () returns int 2829begin 2830declare x int; 2831declare c cursor for select data from t1 limit 1; 2832open c; 2833fetch c into x; 2834close c; 2835return x; 2836end| 2837delete from t1| 2838insert into t1 values ("answer", 42)| 2839select id, bug5240() from t1| 2840id bug5240() 2841answer 42 2842drop function bug5240| 2843drop procedure if exists p1| 2844create table t3(id int)| 2845insert into t3 values(1)| 2846create procedure bug7992() 2847begin 2848declare i int; 2849select max(id)+1 into i from t3; 2850end| 2851call bug7992()| 2852call bug7992()| 2853drop procedure bug7992| 2854drop table t3| 2855create table t3 ( 2856lpitnumber int(11) default null, 2857lrecordtype int(11) default null 2858)| 2859create table t4 ( 2860lbsiid int(11) not null default '0', 2861ltradingmodeid int(11) not null default '0', 2862ltradingareaid int(11) not null default '0', 2863csellingprice decimal(19,4) default null, 2864primary key (lbsiid,ltradingmodeid,ltradingareaid) 2865)| 2866create table t5 ( 2867lbsiid int(11) not null default '0', 2868ltradingareaid int(11) not null default '0', 2869primary key (lbsiid,ltradingareaid) 2870)| 2871drop procedure if exists bug8849| 2872create procedure bug8849() 2873begin 2874insert into t5 2875( 2876t5.lbsiid, 2877t5.ltradingareaid 2878) 2879select distinct t3.lpitnumber, t4.ltradingareaid 2880from 2881t4 join t3 on 2882t3.lpitnumber = t4.lbsiid 2883and t3.lrecordtype = 1 2884left join t4 as price01 on 2885price01.lbsiid = t4.lbsiid and 2886price01.ltradingmodeid = 1 and 2887t4.ltradingareaid = price01.ltradingareaid; 2888end| 2889call bug8849()| 2890call bug8849()| 2891call bug8849()| 2892drop procedure bug8849| 2893drop tables t3,t4,t5| 2894drop procedure if exists bug8937| 2895create procedure bug8937() 2896begin 2897declare s,x,y,z int; 2898declare a float; 2899select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1; 2900select s,x,y,z; 2901select avg(data) into a from t1; 2902select a; 2903end| 2904delete from t1| 2905insert into t1 (data) values (1), (2), (3), (4), (6)| 2906call bug8937()| 2907s x y z 290816 3 1 6 2909a 29103.2 2911drop procedure bug8937| 2912delete from t1| 2913drop procedure if exists bug6900| 2914drop procedure if exists bug9074| 2915drop procedure if exists bug6900_9074| 2916create table t3 (w char unique, x char)| 2917insert into t3 values ('a', 'b')| 2918create procedure bug6900() 2919begin 2920declare exit handler for sqlexception select '1'; 2921begin 2922declare exit handler for sqlexception select '2'; 2923insert into t3 values ('x', 'y', 'z'); 2924end; 2925end| 2926create procedure bug9074() 2927begin 2928declare x1, x2, x3, x4, x5, x6 int default 0; 2929begin 2930declare continue handler for sqlstate '23000' set x5 = 1; 2931insert into t3 values ('a', 'b'); 2932set x6 = 1; 2933end; 2934begin1_label: 2935begin 2936declare continue handler for sqlstate '23000' set x1 = 1; 2937insert into t3 values ('a', 'b'); 2938set x2 = 1; 2939begin2_label: 2940begin 2941declare exit handler for sqlstate '23000' set x3 = 1; 2942set x4= 1; 2943insert into t3 values ('a','b'); 2944set x4= 0; 2945end begin2_label; 2946end begin1_label; 2947select x1, x2, x3, x4, x5, x6; 2948end| 2949create procedure bug6900_9074(z int) 2950begin 2951declare exit handler for sqlstate '23000' select '23000'; 2952begin 2953declare exit handler for sqlexception select 'sqlexception'; 2954if z = 1 then 2955insert into t3 values ('a', 'b'); 2956else 2957insert into t3 values ('x', 'y', 'z'); 2958end if; 2959end; 2960end| 2961call bug6900()| 29622 29632 2964Warnings: 2965Error 1136 Column count doesn't match value count at row 1 2966call bug9074()| 2967x1 x2 x3 x4 x5 x6 29681 1 1 1 1 1 2969Warnings: 2970Error 1062 Duplicate entry 'a' for key 'w' 2971call bug6900_9074(0)| 2972sqlexception 2973sqlexception 2974Warnings: 2975Error 1136 Column count doesn't match value count at row 1 2976call bug6900_9074(1)| 297723000 297823000 2979Warnings: 2980Error 1062 Duplicate entry 'a' for key 'w' 2981drop procedure bug6900| 2982drop procedure bug9074| 2983drop procedure bug6900_9074| 2984drop table t3| 2985drop procedure if exists avg| 2986create procedure avg () 2987begin 2988end| 2989call avg ()| 2990drop procedure avg| 2991drop procedure if exists bug6129| 2992set @old_mode= @@sql_mode; 2993set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO"; 2994create procedure bug6129() 2995select @@sql_mode| 2996call bug6129()| 2997@@sql_mode 2998ERROR_FOR_DIVISION_BY_ZERO 2999set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"| 3000call bug6129()| 3001@@sql_mode 3002ERROR_FOR_DIVISION_BY_ZERO 3003set @@sql_mode= "NO_ZERO_IN_DATE"| 3004call bug6129()| 3005@@sql_mode 3006ERROR_FOR_DIVISION_BY_ZERO 3007set @@sql_mode=@old_mode; 3008drop procedure bug6129| 3009drop procedure if exists bug9856| 3010create procedure bug9856() 3011begin 3012declare v int; 3013declare c cursor for select data from t1; 3014declare exit handler for sqlexception, not found select '16'; 3015open c; 3016fetch c into v; 3017select v; 3018end| 3019delete from t1| 3020call bug9856()| 302116 302216 3023Warnings: 3024Error 1329 No data - zero rows fetched, selected, or processed 3025call bug9856()| 302616 302716 3028Warnings: 3029Error 1329 No data - zero rows fetched, selected, or processed 3030drop procedure bug9856| 3031drop procedure if exists bug9674_1| 3032drop procedure if exists bug9674_2| 3033create procedure bug9674_1(out arg int) 3034begin 3035declare temp_in1 int default 0; 3036declare temp_fl1 int default 0; 3037set temp_in1 = 100; 3038set temp_fl1 = temp_in1/10; 3039set arg = temp_fl1; 3040end| 3041create procedure bug9674_2() 3042begin 3043declare v int default 100; 3044select v/10; 3045end| 3046call bug9674_1(@sptmp)| 3047call bug9674_1(@sptmp)| 3048select @sptmp| 3049@sptmp 305010 3051call bug9674_2()| 3052v/10 305310.0000 3054call bug9674_2()| 3055v/10 305610.0000 3057drop procedure bug9674_1| 3058drop procedure bug9674_2| 3059drop procedure if exists bug9598_1| 3060drop procedure if exists bug9598_2| 3061create procedure bug9598_1(in var_1 char(16), 3062out var_2 integer, out var_3 integer) 3063begin 3064set var_2 = 50; 3065set var_3 = 60; 3066end| 3067create procedure bug9598_2(in v1 char(16), 3068in v2 integer, 3069in v3 integer, 3070in v4 integer, 3071in v5 integer) 3072begin 3073select v1,v2,v3,v4,v5; 3074call bug9598_1(v1,@tmp1,@tmp2); 3075select v1,v2,v3,v4,v5; 3076end| 3077call bug9598_2('Test',2,3,4,5)| 3078v1 v2 v3 v4 v5 3079Test 2 3 4 5 3080v1 v2 v3 v4 v5 3081Test 2 3 4 5 3082select @tmp1, @tmp2| 3083@tmp1 @tmp2 308450 60 3085drop procedure bug9598_1| 3086drop procedure bug9598_2| 3087drop procedure if exists bug9902| 3088create function bug9902() returns int(11) 3089begin 3090set @x = @x + 1; 3091return @x; 3092end| 3093set @qcs1 = @@query_cache_size| 3094set global query_cache_size = 102400| 3095set @x = 1| 3096insert into t1 values ("qc", 42)| 3097select bug9902() from t1| 3098bug9902() 30992 3100select bug9902() from t1| 3101bug9902() 31023 3103select @x| 3104@x 31053 3106set global query_cache_size = @qcs1| 3107delete from t1| 3108drop function bug9902| 3109drop function if exists bug9102| 3110create function bug9102() returns blob return 'a'| 3111select bug9102()| 3112bug9102() 3113a 3114drop function bug9102| 3115drop function if exists bug7648| 3116create function bug7648() returns bit(8) return 'a'| 3117select bug7648()| 3118bug7648() 3119a 3120drop function bug7648| 3121drop function if exists bug9775| 3122create function bug9775(v1 char(1)) returns enum('a','b') return v1| 3123select bug9775('a'),bug9775('b'),bug9775('c')| 3124bug9775('a') bug9775('b') bug9775('c') 3125a b 3126Warnings: 3127Warning 1265 Data truncated for column 'bug9775('c')' at row 1 3128drop function bug9775| 3129create function bug9775(v1 int) returns enum('a','b') return v1| 3130select bug9775(1),bug9775(2),bug9775(3)| 3131bug9775(1) bug9775(2) bug9775(3) 3132a b 3133Warnings: 3134Warning 1265 Data truncated for column 'bug9775(3)' at row 1 3135drop function bug9775| 3136create function bug9775(v1 char(1)) returns set('a','b') return v1| 3137select bug9775('a'),bug9775('b'),bug9775('a,b'),bug9775('c')| 3138bug9775('a') bug9775('b') bug9775('a,b') bug9775('c') 3139a b a 3140Warnings: 3141Warning 1265 Data truncated for column 'v1' at row 1 3142Warning 1265 Data truncated for column 'bug9775('c')' at row 1 3143drop function bug9775| 3144create function bug9775(v1 int) returns set('a','b') return v1| 3145select bug9775(1),bug9775(2),bug9775(3),bug9775(4)| 3146bug9775(1) bug9775(2) bug9775(3) bug9775(4) 3147a b a,b 3148Warnings: 3149Warning 1265 Data truncated for column 'bug9775(4)' at row 1 3150drop function bug9775| 3151drop function if exists bug8861| 3152create function bug8861(v1 int) returns year return v1| 3153select bug8861(05)| 3154bug8861(05) 31552005 3156set @x = bug8861(05)| 3157select @x| 3158@x 31592005 3160drop function bug8861| 3161drop procedure if exists bug9004_1| 3162drop procedure if exists bug9004_2| 3163create procedure bug9004_1(x char(16)) 3164begin 3165insert into t1 values (x, 42); 3166insert into t1 values (x, 17); 3167end| 3168create procedure bug9004_2(x char(16)) 3169call bug9004_1(x)| 3170call bug9004_1('12345678901234567')| 3171Warnings: 3172Warning 1265 Data truncated for column 'x' at row 1 3173call bug9004_2('12345678901234567890')| 3174Warnings: 3175Warning 1265 Data truncated for column 'x' at row 1 3176delete from t1| 3177drop procedure bug9004_1| 3178drop procedure bug9004_2| 3179drop procedure if exists bug7293| 3180insert into t1 values ('secret', 0)| 3181create procedure bug7293(p1 varchar(100)) 3182begin 3183if exists (select id from t1 where soundex(p1)=soundex(id)) then 3184select 'yes'; 3185end if; 3186end;| 3187call bug7293('secret')| 3188yes 3189yes 3190call bug7293 ('secrete')| 3191yes 3192yes 3193drop procedure bug7293| 3194delete from t1| 3195drop procedure if exists bug9841| 3196drop view if exists v1| 3197create view v1 as select * from t1, t2 where id = s| 3198create procedure bug9841 () 3199update v1 set data = 10| 3200call bug9841()| 3201drop view v1| 3202drop procedure bug9841| 3203drop procedure if exists bug5963| 3204create procedure bug5963_1 () begin declare v int; set v = (select s1 from t3); select v; end;| 3205create table t3 (s1 int)| 3206insert into t3 values (5)| 3207call bug5963_1()| 3208v 32095 3210call bug5963_1()| 3211v 32125 3213drop procedure bug5963_1| 3214drop table t3| 3215create procedure bug5963_2 (cfk_value int) 3216begin 3217if cfk_value in (select cpk from t3) then 3218set @x = 5; 3219end if; 3220end; 3221| 3222create table t3 (cpk int)| 3223insert into t3 values (1)| 3224call bug5963_2(1)| 3225call bug5963_2(1)| 3226drop procedure bug5963_2| 3227drop table t3| 3228drop function if exists bug9559| 3229create function bug9559() 3230returns int 3231begin 3232set @y = -6/2; 3233return @y; 3234end| 3235select bug9559()| 3236bug9559() 3237-3 3238drop function bug9559| 3239drop procedure if exists bug10961| 3240create procedure bug10961() 3241begin 3242declare v char; 3243declare x int; 3244declare c cursor for select * from dual; 3245declare continue handler for sqlexception select x; 3246set x = 1; 3247open c; 3248set x = 2; 3249fetch c into v; 3250set x = 3; 3251close c; 3252end| 3253call bug10961()| 3254x 32551 3256x 32572 3258x 32593 3260Warnings: 3261Error 1326 Cursor is not open 3262call bug10961()| 3263x 32641 3265x 32662 3267x 32683 3269Warnings: 3270Error 1326 Cursor is not open 3271drop procedure bug10961| 3272DROP PROCEDURE IF EXISTS bug6866| 3273DROP VIEW IF EXISTS tv| 3274Warnings: 3275Note 1051 Unknown table 'test.tv' 3276DROP TABLE IF EXISTS tt1,tt2,tt3| 3277Warnings: 3278Note 1051 Unknown table 'tt1' 3279Note 1051 Unknown table 'tt2' 3280Note 1051 Unknown table 'tt3' 3281CREATE TABLE tt1 (a1 int, a2 int, a3 int, data varchar(10))| 3282CREATE TABLE tt2 (a2 int, data2 varchar(10))| 3283CREATE TABLE tt3 (a3 int, data3 varchar(10))| 3284INSERT INTO tt1 VALUES (1, 1, 4, 'xx')| 3285INSERT INTO tt2 VALUES (1, 'a')| 3286INSERT INTO tt2 VALUES (2, 'b')| 3287INSERT INTO tt2 VALUES (3, 'c')| 3288INSERT INTO tt3 VALUES (4, 'd')| 3289INSERT INTO tt3 VALUES (5, 'e')| 3290INSERT INTO tt3 VALUES (6, 'f')| 3291CREATE VIEW tv AS 3292SELECT tt1.*, tt2.data2, tt3.data3 3293FROM tt1 INNER JOIN tt2 ON tt1.a2 = tt2.a2 3294LEFT JOIN tt3 ON tt1.a3 = tt3.a3 3295ORDER BY tt1.a1, tt2.a2, tt3.a3| 3296CREATE PROCEDURE bug6866 (_a1 int) 3297BEGIN 3298SELECT * FROM tv WHERE a1 = _a1; 3299END| 3300CALL bug6866(1)| 3301a1 a2 a3 data data2 data3 33021 1 4 xx a d 3303CALL bug6866(1)| 3304a1 a2 a3 data data2 data3 33051 1 4 xx a d 3306CALL bug6866(1)| 3307a1 a2 a3 data data2 data3 33081 1 4 xx a d 3309DROP PROCEDURE bug6866; 3310DROP VIEW tv| 3311DROP TABLE tt1, tt2, tt3| 3312DROP PROCEDURE IF EXISTS bug10136| 3313create table t3 ( name char(5) not null primary key, val float not null)| 3314insert into t3 values ('aaaaa', 1), ('bbbbb', 2), ('ccccc', 3)| 3315create procedure bug10136() 3316begin 3317declare done int default 3; 3318repeat 3319select * from t3; 3320set done = done - 1; 3321until done <= 0 end repeat; 3322end| 3323call bug10136()| 3324name val 3325aaaaa 1 3326bbbbb 2 3327ccccc 3 3328name val 3329aaaaa 1 3330bbbbb 2 3331ccccc 3 3332name val 3333aaaaa 1 3334bbbbb 2 3335ccccc 3 3336call bug10136()| 3337name val 3338aaaaa 1 3339bbbbb 2 3340ccccc 3 3341name val 3342aaaaa 1 3343bbbbb 2 3344ccccc 3 3345name val 3346aaaaa 1 3347bbbbb 2 3348ccccc 3 3349call bug10136()| 3350name val 3351aaaaa 1 3352bbbbb 2 3353ccccc 3 3354name val 3355aaaaa 1 3356bbbbb 2 3357ccccc 3 3358name val 3359aaaaa 1 3360bbbbb 2 3361ccccc 3 3362drop procedure bug10136| 3363drop table t3| 3364drop procedure if exists bug11529| 3365create procedure bug11529() 3366begin 3367declare c cursor for select id, data from t1 where data in (10,13); 3368open c; 3369begin 3370declare vid char(16); 3371declare vdata int; 3372declare exit handler for not found begin end; 3373while true do 3374fetch c into vid, vdata; 3375end while; 3376end; 3377close c; 3378end| 3379insert into t1 values 3380('Name1', 10), 3381('Name2', 11), 3382('Name3', 12), 3383('Name4', 13), 3384('Name5', 14)| 3385call bug11529()| 3386Warnings: 3387Error 1329 No data - zero rows fetched, selected, or processed 3388call bug11529()| 3389Warnings: 3390Error 1329 No data - zero rows fetched, selected, or processed 3391delete from t1| 3392drop procedure bug11529| 3393set character set utf8| 3394drop procedure if exists bug6063| 3395drop procedure if exists bug7088_1| 3396drop procedure if exists bug7088_2| 3397create procedure bug6063() 3398begin 3399lâbel: begin end; 3400label: begin end; 3401label1: begin end; 3402end| 3403create procedure bug7088_1() 3404label1: begin end label1| 3405create procedure bug7088_2() 3406läbel1: begin end| 3407call bug6063()| 3408call bug7088_1()| 3409call bug7088_2()| 3410set character set default| 3411show create procedure bug6063| 3412Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 3413bug6063 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug6063`() 3414begin 3415l�bel: begin end; 3416label: begin end; 3417label1: begin end; 3418end utf8 latin1_swedish_ci latin1_swedish_ci 3419show create procedure bug7088_1| 3420Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 3421bug7088_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug7088_1`() 3422label1: begin end label1 utf8 latin1_swedish_ci latin1_swedish_ci 3423show create procedure bug7088_2| 3424Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 3425bug7088_2 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug7088_2`() 3426l�bel1: begin end utf8 latin1_swedish_ci latin1_swedish_ci 3427drop procedure bug6063| 3428drop procedure bug7088_1| 3429drop procedure bug7088_2| 3430drop procedure if exists bug9565_sub| 3431drop procedure if exists bug9565| 3432create procedure bug9565_sub() 3433begin 3434select * from t1; 3435end| 3436create procedure bug9565() 3437begin 3438insert into t1 values ("one", 1); 3439call bug9565_sub(); 3440end| 3441call bug9565()| 3442id data 3443one 1 3444delete from t1| 3445drop procedure bug9565_sub| 3446drop procedure bug9565| 3447drop procedure if exists bug9538| 3448create procedure bug9538() 3449set @@sort_buffer_size = 1000000| 3450set @x = @@sort_buffer_size| 3451set @@sort_buffer_size = 2000000| 3452select @@sort_buffer_size| 3453@@sort_buffer_size 34542000000 3455call bug9538()| 3456select @@sort_buffer_size| 3457@@sort_buffer_size 34581000000 3459set @@sort_buffer_size = @x| 3460drop procedure bug9538| 3461drop procedure if exists bug8692| 3462create table t3 (c1 varchar(5), c2 char(5), c3 enum('one','two'), c4 text, c5 blob, c6 char(5), c7 varchar(5))| 3463insert into t3 values ('', '', '', '', '', '', NULL)| 3464Warnings: 3465Warning 1265 Data truncated for column 'c3' at row 1 3466create procedure bug8692() 3467begin 3468declare v1 VARCHAR(10); 3469declare v2 VARCHAR(10); 3470declare v3 VARCHAR(10); 3471declare v4 VARCHAR(10); 3472declare v5 VARCHAR(10); 3473declare v6 VARCHAR(10); 3474declare v7 VARCHAR(10); 3475declare c8692 cursor for select c1,c2,c3,c4,c5,c6,c7 from t3; 3476open c8692; 3477fetch c8692 into v1,v2,v3,v4,v5,v6,v7; 3478select v1, v2, v3, v4, v5, v6, v7; 3479end| 3480call bug8692()| 3481v1 v2 v3 v4 v5 v6 v7 3482 NULL 3483drop procedure bug8692| 3484drop table t3| 3485drop function if exists bug10055| 3486create function bug10055(v char(255)) returns char(255) return lower(v)| 3487select t.column_name, bug10055(t.column_name) 3488from information_schema.columns as t 3489where t.table_schema = 'test' and t.table_name = 't1'| 3490column_name bug10055(t.column_name) 3491id id 3492data data 3493drop function bug10055| 3494drop procedure if exists bug12297| 3495create procedure bug12297(lim int) 3496begin 3497set @x = 0; 3498repeat 3499insert into t1(id,data) 3500values('aa', @x); 3501set @x = @x + 1; 3502until @x >= lim 3503end repeat; 3504end| 3505call bug12297(10)| 3506drop procedure bug12297| 3507drop function if exists f_bug11247| 3508drop procedure if exists p_bug11247| 3509create function f_bug11247(param int) 3510returns int 3511return param + 1| 3512create procedure p_bug11247(lim int) 3513begin 3514declare v int default 0; 3515while v < lim do 3516set v= f_bug11247(v); 3517end while; 3518end| 3519call p_bug11247(10)| 3520drop function f_bug11247| 3521drop procedure p_bug11247| 3522drop procedure if exists bug12168| 3523drop table if exists t3, t4| 3524create table t3 (a int)| 3525insert into t3 values (1),(2),(3),(4)| 3526create table t4 (a int)| 3527create procedure bug12168(arg1 char(1)) 3528begin 3529declare b, c integer; 3530if arg1 = 'a' then 3531begin 3532declare c1 cursor for select a from t3 where a % 2; 3533declare continue handler for not found set b = 1; 3534set b = 0; 3535open c1; 3536c1_repeat: repeat 3537fetch c1 into c; 3538if (b = 1) then 3539leave c1_repeat; 3540end if; 3541insert into t4 values (c); 3542until b = 1 3543end repeat; 3544end; 3545end if; 3546if arg1 = 'b' then 3547begin 3548declare c2 cursor for select a from t3 where not a % 2; 3549declare continue handler for not found set b = 1; 3550set b = 0; 3551open c2; 3552c2_repeat: repeat 3553fetch c2 into c; 3554if (b = 1) then 3555leave c2_repeat; 3556end if; 3557insert into t4 values (c); 3558until b = 1 3559end repeat; 3560end; 3561end if; 3562end| 3563call bug12168('a')| 3564Warnings: 3565Error 1329 No data - zero rows fetched, selected, or processed 3566select * from t4| 3567a 35681 35693 3570truncate t4| 3571call bug12168('b')| 3572Warnings: 3573Error 1329 No data - zero rows fetched, selected, or processed 3574select * from t4| 3575a 35762 35774 3578truncate t4| 3579call bug12168('a')| 3580Warnings: 3581Error 1329 No data - zero rows fetched, selected, or processed 3582select * from t4| 3583a 35841 35853 3586truncate t4| 3587call bug12168('b')| 3588Warnings: 3589Error 1329 No data - zero rows fetched, selected, or processed 3590select * from t4| 3591a 35922 35934 3594truncate t4| 3595drop table t3, t4| 3596drop procedure if exists bug12168| 3597drop table if exists t3| 3598drop procedure if exists bug11333| 3599create table t3 (c1 char(128))| 3600insert into t3 values 3601('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')| 3602create procedure bug11333(i int) 3603begin 3604declare tmp varchar(128); 3605set @x = 0; 3606repeat 3607select c1 into tmp from t3 3608where c1 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'; 3609set @x = @x + 1; 3610until @x >= i 3611end repeat; 3612end| 3613call bug11333(10)| 3614drop procedure bug11333| 3615drop table t3| 3616drop function if exists bug9048| 3617create function bug9048(f1 char binary) returns char 3618begin 3619set f1= concat( 'hello', f1 ); 3620return f1; 3621end| 3622drop function bug9048| 3623create function bug9048(f1 char binary) returns char binary 3624begin 3625set f1= concat( 'hello', f1 ); 3626return f1; 3627end| 3628ERROR 42000: This version of MySQL doesn't yet support 'return value collation' 3629drop procedure if exists bug12849_1| 3630create procedure bug12849_1(inout x char) select x into x| 3631set @var='a'| 3632call bug12849_1(@var)| 3633select @var| 3634@var 3635a 3636drop procedure bug12849_1| 3637drop procedure if exists bug12849_2| 3638create procedure bug12849_2(inout foo varchar(15)) 3639begin 3640select concat(foo, foo) INTO foo; 3641end| 3642set @var='abcd'| 3643call bug12849_2(@var)| 3644select @var| 3645@var 3646abcdabcd 3647drop procedure bug12849_2| 3648drop procedure if exists bug131333| 3649drop function if exists bug131333| 3650create procedure bug131333() 3651begin 3652begin 3653declare a int; 3654select a; 3655set a = 1; 3656select a; 3657end; 3658begin 3659declare b int; 3660select b; 3661end; 3662end| 3663create function bug131333() 3664returns int 3665begin 3666begin 3667declare a int; 3668set a = 1; 3669end; 3670begin 3671declare b int; 3672return b; 3673end; 3674end| 3675call bug131333()| 3676a 3677NULL 3678a 36791 3680b 3681NULL 3682select bug131333()| 3683bug131333() 3684NULL 3685drop procedure bug131333| 3686drop function bug131333| 3687drop function if exists bug12379| 3688drop procedure if exists bug12379_1| 3689drop procedure if exists bug12379_2| 3690drop procedure if exists bug12379_3| 3691drop table if exists t3| 3692create table t3 (c1 char(1) primary key not null)| 3693create function bug12379() 3694returns integer 3695begin 3696insert into t3 values('X'); 3697insert into t3 values('X'); 3698return 0; 3699end| 3700create procedure bug12379_1() 3701begin 3702declare exit handler for sqlexception select 42; 3703select bug12379(); 3704END| 3705create procedure bug12379_2() 3706begin 3707declare exit handler for sqlexception begin end; 3708select bug12379(); 3709end| 3710create procedure bug12379_3() 3711begin 3712select bug12379(); 3713end| 3714select bug12379()| 3715ERROR 23000: Duplicate entry 'X' for key 'PRIMARY' 3716select 1| 37171 37181 3719call bug12379_1()| 3720bug12379() 372142 372242 3723select 2| 37242 37252 3726call bug12379_2()| 3727bug12379() 3728select 3| 37293 37303 3731call bug12379_3()| 3732ERROR 23000: Duplicate entry 'X' for key 'PRIMARY' 3733select 4| 37344 37354 3736drop function bug12379| 3737drop procedure bug12379_1| 3738drop procedure bug12379_2| 3739drop procedure bug12379_3| 3740drop table t3| 3741drop procedure if exists bug13124| 3742create procedure bug13124() 3743begin 3744declare y integer; 3745set @x=y; 3746end| 3747call bug13124()| 3748drop procedure bug13124| 3749drop procedure if exists bug12979_1| 3750create procedure bug12979_1(inout d decimal(5)) set d = d / 2| 3751set @bug12979_user_var = NULL| 3752call bug12979_1(@bug12979_user_var)| 3753drop procedure bug12979_1| 3754drop procedure if exists bug12979_2| 3755create procedure bug12979_2() 3756begin 3757declare internal_var decimal(5); 3758set internal_var= internal_var / 2; 3759select internal_var; 3760end| 3761call bug12979_2()| 3762internal_var 3763NULL 3764drop procedure bug12979_2| 3765drop table if exists t3| 3766drop procedure if exists bug6127| 3767create table t3 (s1 int unique)| 3768set @sm=@@sql_mode| 3769set sql_mode='traditional'| 3770create procedure bug6127() 3771begin 3772declare continue handler for sqlstate '23000' 3773 begin 3774declare continue handler for sqlstate '22003' 3775 insert into t3 values (0); 3776insert into t3 values (1000000000000000); 3777end; 3778insert into t3 values (1); 3779insert into t3 values (1); 3780end| 3781call bug6127()| 3782select * from t3| 3783s1 37840 37851 3786call bug6127()| 3787ERROR 23000: Duplicate entry '0' for key 's1' 3788select * from t3| 3789s1 37900 37911 3792set sql_mode=@sm| 3793drop table t3| 3794drop procedure bug6127| 3795drop procedure if exists bug12589_1| 3796drop procedure if exists bug12589_2| 3797drop procedure if exists bug12589_3| 3798create procedure bug12589_1() 3799begin 3800declare spv1 decimal(3,3); 3801set spv1= 123.456; 3802set spv1 = 'test'; 3803create temporary table tm1 as select spv1; 3804show create table tm1; 3805drop temporary table tm1; 3806end| 3807create procedure bug12589_2() 3808begin 3809declare spv1 decimal(6,3); 3810set spv1= 123.456; 3811create temporary table tm1 as select spv1; 3812show create table tm1; 3813drop temporary table tm1; 3814end| 3815create procedure bug12589_3() 3816begin 3817declare spv1 decimal(6,3); 3818set spv1= -123.456; 3819create temporary table tm1 as select spv1; 3820show create table tm1; 3821drop temporary table tm1; 3822end| 3823call bug12589_1()| 3824Table Create Table 3825tm1 CREATE TEMPORARY TABLE `tm1` ( 3826 `spv1` decimal(3,3) DEFAULT NULL 3827) ENGINE=MyISAM DEFAULT CHARSET=latin1 3828call bug12589_2()| 3829Table Create Table 3830tm1 CREATE TEMPORARY TABLE `tm1` ( 3831 `spv1` decimal(6,3) DEFAULT NULL 3832) ENGINE=MyISAM DEFAULT CHARSET=latin1 3833call bug12589_3()| 3834Table Create Table 3835tm1 CREATE TEMPORARY TABLE `tm1` ( 3836 `spv1` decimal(6,3) DEFAULT NULL 3837) ENGINE=MyISAM DEFAULT CHARSET=latin1 3838drop procedure bug12589_1| 3839drop procedure bug12589_2| 3840drop procedure bug12589_3| 3841drop table if exists t3| 3842drop procedure if exists bug7049_1| 3843drop procedure if exists bug7049_2| 3844drop procedure if exists bug7049_3| 3845drop procedure if exists bug7049_4| 3846drop function if exists bug7049_1| 3847drop function if exists bug7049_2| 3848create table t3 ( x int unique )| 3849create procedure bug7049_1() 3850begin 3851insert into t3 values (42); 3852insert into t3 values (42); 3853end| 3854create procedure bug7049_2() 3855begin 3856declare exit handler for sqlexception 3857select 'Caught it' as 'Result'; 3858call bug7049_1(); 3859select 'Missed it' as 'Result'; 3860end| 3861create procedure bug7049_3() 3862call bug7049_1()| 3863create procedure bug7049_4() 3864begin 3865declare exit handler for sqlexception 3866select 'Caught it' as 'Result'; 3867call bug7049_3(); 3868select 'Missed it' as 'Result'; 3869end| 3870create function bug7049_1() 3871returns int 3872begin 3873insert into t3 values (42); 3874insert into t3 values (42); 3875return 42; 3876end| 3877create function bug7049_2() 3878returns int 3879begin 3880declare x int default 0; 3881declare continue handler for sqlexception 3882set x = 1; 3883set x = bug7049_1(); 3884return x; 3885end| 3886call bug7049_2()| 3887Result 3888Caught it 3889Warnings: 3890Error 1062 Duplicate entry '42' for key 'x' 3891select * from t3| 3892x 389342 3894delete from t3| 3895call bug7049_4()| 3896Result 3897Caught it 3898Warnings: 3899Error 1062 Duplicate entry '42' for key 'x' 3900select * from t3| 3901x 390242 3903select bug7049_2()| 3904bug7049_2() 39051 3906Warnings: 3907Error 1062 Duplicate entry '42' for key 'x' 3908drop table t3| 3909drop procedure bug7049_1| 3910drop procedure bug7049_2| 3911drop procedure bug7049_3| 3912drop procedure bug7049_4| 3913drop function bug7049_1| 3914drop function bug7049_2| 3915drop function if exists bug13941| 3916drop procedure if exists bug13941| 3917create function bug13941(p_input_str text) 3918returns text 3919begin 3920declare p_output_str text; 3921set p_output_str = p_input_str; 3922set p_output_str = replace(p_output_str, 'xyzzy', 'plugh'); 3923set p_output_str = replace(p_output_str, 'test', 'prova'); 3924set p_output_str = replace(p_output_str, 'this', 'questo'); 3925set p_output_str = replace(p_output_str, ' a ', 'una '); 3926set p_output_str = replace(p_output_str, 'is', ''); 3927return p_output_str; 3928end| 3929create procedure bug13941(out sout varchar(128)) 3930begin 3931set sout = 'Local'; 3932set sout = ifnull(sout, 'DEF'); 3933end| 3934select bug13941('this is a test')| 3935bug13941('this is a test') 3936questo una prova 3937call bug13941(@a)| 3938select @a| 3939@a 3940Local 3941drop function bug13941| 3942drop procedure bug13941| 3943DROP PROCEDURE IF EXISTS bug13095; 3944DROP TABLE IF EXISTS bug13095_t1; 3945DROP VIEW IF EXISTS bug13095_v1; 3946CREATE PROCEDURE bug13095(tbl_name varchar(32)) 3947BEGIN 3948SET @str = 3949CONCAT("CREATE TABLE ", tbl_name, "(stuff char(15))"); 3950SELECT @str; 3951PREPARE stmt FROM @str; 3952EXECUTE stmt; 3953SET @str = 3954CONCAT("INSERT INTO ", tbl_name, " VALUES('row1'),('row2'),('row3')" ); 3955SELECT @str; 3956PREPARE stmt FROM @str; 3957EXECUTE stmt; 3958SET @str = 3959CONCAT("CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM ", tbl_name); 3960SELECT @str; 3961PREPARE stmt FROM @str; 3962EXECUTE stmt; 3963SELECT * FROM bug13095_v1; 3964SET @str = 3965"DROP VIEW bug13095_v1"; 3966SELECT @str; 3967PREPARE stmt FROM @str; 3968EXECUTE stmt; 3969END| 3970CALL bug13095('bug13095_t1'); 3971@str 3972CREATE TABLE bug13095_t1(stuff char(15)) 3973@str 3974INSERT INTO bug13095_t1 VALUES('row1'),('row2'),('row3') 3975@str 3976CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM bug13095_t1 3977c1 3978row1 3979row2 3980row3 3981@str 3982DROP VIEW bug13095_v1 3983DROP PROCEDURE IF EXISTS bug13095; 3984DROP VIEW IF EXISTS bug13095_v1; 3985DROP TABLE IF EXISTS bug13095_t1; 3986drop function if exists bug14723| 3987drop procedure if exists bug14723| 3988/*!50003 create function bug14723() 3989returns bigint(20) 3990main_loop: begin 3991return 42; 3992end */;; 3993show create function bug14723;; 3994Function sql_mode Create Function character_set_client collation_connection Database Collation 3995bug14723 CREATE DEFINER=`root`@`localhost` FUNCTION `bug14723`() RETURNS bigint(20) 3996main_loop: begin 3997return 42; 3998end latin1 latin1_swedish_ci latin1_swedish_ci 3999select bug14723();; 4000bug14723() 400142 4002/*!50003 create procedure bug14723() 4003main_loop: begin 4004select 42; 4005end */;; 4006show create procedure bug14723;; 4007Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 4008bug14723 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug14723`() 4009main_loop: begin 4010select 42; 4011end latin1 latin1_swedish_ci latin1_swedish_ci 4012call bug14723();; 401342 401442 4015drop function bug14723| 4016drop procedure bug14723| 4017create procedure bug14845() 4018begin 4019declare a char(255); 4020declare done int default 0; 4021declare c cursor for select count(*) from t1 where 1 = 0; 4022declare continue handler for sqlstate '02000' set done = 1; 4023open c; 4024repeat 4025fetch c into a; 4026if not done then 4027select a; 4028end if; 4029until done end repeat; 4030close c; 4031end| 4032call bug14845()| 4033a 40340 4035Warnings: 4036Error 1329 No data - zero rows fetched, selected, or processed 4037drop procedure bug14845| 4038drop procedure if exists bug13549_1| 4039drop procedure if exists bug13549_2| 4040CREATE PROCEDURE `bug13549_2`() 4041begin 4042call bug13549_1(); 4043end| 4044CREATE PROCEDURE `bug13549_1`() 4045begin 4046declare done int default 0; 4047set done= not done; 4048end| 4049CALL bug13549_2()| 4050drop procedure bug13549_2| 4051drop procedure bug13549_1| 4052drop function if exists bug10100f| 4053drop procedure if exists bug10100p| 4054drop procedure if exists bug10100t| 4055drop procedure if exists bug10100pt| 4056drop procedure if exists bug10100pv| 4057drop procedure if exists bug10100pd| 4058drop procedure if exists bug10100pc| 4059create function bug10100f(prm int) returns int 4060begin 4061if prm > 1 then 4062return prm * bug10100f(prm - 1); 4063end if; 4064return 1; 4065end| 4066create procedure bug10100p(prm int, inout res int) 4067begin 4068set res = res * prm; 4069if prm > 1 then 4070call bug10100p(prm - 1, res); 4071end if; 4072end| 4073create procedure bug10100t(prm int) 4074begin 4075declare res int; 4076set res = 1; 4077call bug10100p(prm, res); 4078select res; 4079end| 4080create table t3 (a int)| 4081insert into t3 values (0)| 4082create view v1 as select a from t3| 4083create procedure bug10100pt(level int, lim int) 4084begin 4085if level < lim then 4086update t3 set a=level; 4087FLUSH TABLES; 4088call bug10100pt(level+1, lim); 4089else 4090select * from t3; 4091end if; 4092end| 4093create procedure bug10100pv(level int, lim int) 4094begin 4095if level < lim then 4096update v1 set a=level; 4097FLUSH TABLES; 4098call bug10100pv(level+1, lim); 4099else 4100select * from v1; 4101end if; 4102end| 4103prepare stmt2 from "select * from t3;"| 4104create procedure bug10100pd(level int, lim int) 4105begin 4106if level < lim then 4107select level; 4108prepare stmt1 from "update t3 set a=a+2"; 4109execute stmt1; 4110FLUSH TABLES; 4111execute stmt1; 4112FLUSH TABLES; 4113execute stmt1; 4114FLUSH TABLES; 4115deallocate prepare stmt1; 4116execute stmt2; 4117select * from t3; 4118call bug10100pd(level+1, lim); 4119else 4120execute stmt2; 4121end if; 4122end| 4123create procedure bug10100pc(level int, lim int) 4124begin 4125declare lv int; 4126declare c cursor for select a from t3; 4127open c; 4128if level < lim then 4129select level; 4130fetch c into lv; 4131select lv; 4132update t3 set a=level+lv; 4133FLUSH TABLES; 4134call bug10100pc(level+1, lim); 4135else 4136select * from t3; 4137end if; 4138close c; 4139end| 4140set @@max_sp_recursion_depth=4| 4141select @@max_sp_recursion_depth| 4142@@max_sp_recursion_depth 41434 4144select bug10100f(3)| 4145ERROR HY000: Recursive stored functions and triggers are not allowed. 4146select bug10100f(6)| 4147ERROR HY000: Recursive stored functions and triggers are not allowed. 4148call bug10100t(5)| 4149res 4150120 4151call bug10100pt(1,5)| 4152a 41534 4154call bug10100pv(1,5)| 4155a 41564 4157update t3 set a=1| 4158call bug10100pd(1,5)| 4159level 41601 4161a 41627 4163a 41647 4165level 41662 4167a 416813 4169a 417013 4171level 41723 4173a 417419 4175a 417619 4177level 41784 4179a 418025 4181a 418225 4183a 418425 4185select * from t3| 4186a 418725 4188update t3 set a=1| 4189call bug10100pc(1,5)| 4190level 41911 4192lv 41931 4194level 41952 4196lv 41972 4198level 41993 4200lv 42014 4202level 42034 4204lv 42057 4206a 420711 4208select * from t3| 4209a 421011 4211set @@max_sp_recursion_depth=0| 4212select @@max_sp_recursion_depth| 4213@@max_sp_recursion_depth 42140 4215select bug10100f(5)| 4216ERROR HY000: Recursive stored functions and triggers are not allowed. 4217call bug10100t(5)| 4218ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug10100p 4219deallocate prepare stmt2| 4220drop function bug10100f| 4221drop procedure bug10100p| 4222drop procedure bug10100t| 4223drop procedure bug10100pt| 4224drop procedure bug10100pv| 4225drop procedure bug10100pd| 4226drop procedure bug10100pc| 4227drop view v1| 4228drop procedure if exists bug13729| 4229drop table if exists t3| 4230create table t3 (s1 int, primary key (s1))| 4231insert into t3 values (1),(2)| 4232create procedure bug13729() 4233begin 4234declare continue handler for sqlexception select 55; 4235update t3 set s1 = 1; 4236end| 4237call bug13729()| 423855 423955 4240Warnings: 4241Error 1062 Duplicate entry '1' for key 'PRIMARY' 4242select * from t3| 4243s1 42441 42452 4246drop procedure bug13729| 4247drop table t3| 4248drop procedure if exists bug14643_1| 4249drop procedure if exists bug14643_2| 4250create procedure bug14643_1() 4251begin 4252declare continue handler for sqlexception select 'boo' as 'Handler'; 4253begin 4254declare v int default undefined_var; 4255if v = 1 then 4256select 1; 4257else 4258select v, isnull(v); 4259end if; 4260end; 4261end| 4262create procedure bug14643_2() 4263begin 4264declare continue handler for sqlexception select 'boo' as 'Handler'; 4265case undefined_var 4266when 1 then 4267select 1; 4268else 4269select 2; 4270end case; 4271select undefined_var; 4272end| 4273call bug14643_1()| 4274Handler 4275boo 4276v isnull(v) 4277NULL 1 4278Warnings: 4279Error 1054 Unknown column 'undefined_var' in 'field list' 4280call bug14643_2()| 4281Handler 4282boo 4283Handler 4284boo 4285Warnings: 4286Error 1054 Unknown column 'undefined_var' in 'field list' 4287drop procedure bug14643_1| 4288drop procedure bug14643_2| 4289drop procedure if exists bug14304| 4290drop table if exists t3, t4| 4291create table t3(a int primary key auto_increment)| 4292create table t4(a int primary key auto_increment)| 4293create procedure bug14304() 4294begin 4295insert into t3 set a=null; 4296insert into t4 set a=null; 4297insert into t4 set a=null; 4298insert into t4 set a=null; 4299insert into t4 set a=null; 4300insert into t4 set a=null; 4301insert into t4 select null as a; 4302insert into t3 set a=null; 4303insert into t3 set a=null; 4304select * from t3; 4305end| 4306call bug14304()| 4307a 43081 43092 43103 4311drop procedure bug14304| 4312drop table t3, t4| 4313drop procedure if exists bug14376| 4314create procedure bug14376() 4315begin 4316declare x int default x; 4317end| 4318call bug14376()| 4319ERROR 42S22: Unknown column 'x' in 'field list' 4320drop procedure bug14376| 4321create procedure bug14376() 4322begin 4323declare x int default 42; 4324begin 4325declare x int default x; 4326select x; 4327end; 4328end| 4329call bug14376()| 4330x 433142 4332drop procedure bug14376| 4333create procedure bug14376(x int) 4334begin 4335declare x int default x; 4336select x; 4337end| 4338call bug14376(4711)| 4339x 43404711 4341drop procedure bug14376| 4342drop procedure if exists bug5967| 4343drop table if exists t3| 4344create table t3 (a varchar(255))| 4345insert into t3 (a) values ("a - table column")| 4346create procedure bug5967(a varchar(255)) 4347begin 4348declare i varchar(255); 4349declare c cursor for select a from t3; 4350select a; 4351select a from t3 into i; 4352select i as 'Parameter takes precedence over table column'; open c; 4353fetch c into i; 4354close c; 4355select i as 'Parameter takes precedence over table column in cursors'; 4356begin 4357declare a varchar(255) default 'a - local variable'; 4358declare c1 cursor for select a from t3; 4359select a as 'A local variable takes precedence over parameter'; 4360open c1; 4361fetch c1 into i; 4362close c1; 4363select i as 'A local variable takes precedence over parameter in cursors'; 4364begin 4365declare a varchar(255) default 'a - local variable in a nested compound statement'; 4366declare c2 cursor for select a from t3; 4367select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement'; 4368select a from t3 into i; 4369select i as 'A local variable in a nested compound statement takes precedence over table column'; 4370open c2; 4371fetch c2 into i; 4372close c2; 4373select i as 'A local variable in a nested compound statement takes precedence over table column in cursors'; 4374end; 4375end; 4376end| 4377call bug5967("a - stored procedure parameter")| 4378a 4379a - stored procedure parameter 4380Parameter takes precedence over table column 4381a - stored procedure parameter 4382Parameter takes precedence over table column in cursors 4383a - stored procedure parameter 4384A local variable takes precedence over parameter 4385a - local variable 4386A local variable takes precedence over parameter in cursors 4387a - local variable 4388A local variable in a nested compound statement takes precedence over a local variable in the outer statement 4389a - local variable in a nested compound statement 4390A local variable in a nested compound statement takes precedence over table column 4391a - local variable in a nested compound statement 4392A local variable in a nested compound statement takes precedence over table column in cursors 4393a - local variable in a nested compound statement 4394drop procedure bug5967| 4395drop procedure if exists bug13012| 4396create procedure bug13012() 4397BEGIN 4398REPAIR TABLE t1; 4399END| 4400call bug13012()| 4401Table Op Msg_type Msg_text 4402test.t1 repair status OK 4403drop procedure bug13012| 4404create view v1 as select * from t1| 4405create procedure bug13012() 4406BEGIN 4407REPAIR TABLE t1,t2,t3,v1; 4408OPTIMIZE TABLE t1,t2,t3,v1; 4409ANALYZE TABLE t1,t2,t3,v1; 4410END| 4411call bug13012()| 4412Table Op Msg_type Msg_text 4413test.t1 repair status OK 4414test.t2 repair status OK 4415test.t3 repair status OK 4416test.v1 repair Error 'test.v1' is not BASE TABLE 4417test.v1 repair status Operation failed 4418Table Op Msg_type Msg_text 4419test.t1 optimize status OK 4420test.t2 optimize status OK 4421test.t3 optimize status OK 4422test.v1 optimize Error 'test.v1' is not BASE TABLE 4423test.v1 optimize status Operation failed 4424Table Op Msg_type Msg_text 4425test.t1 analyze status Table is already up to date 4426test.t2 analyze status Table is already up to date 4427test.t3 analyze status Table is already up to date 4428test.v1 analyze Error 'test.v1' is not BASE TABLE 4429test.v1 analyze status Operation failed 4430call bug13012()| 4431Table Op Msg_type Msg_text 4432test.t1 repair status OK 4433test.t2 repair status OK 4434test.t3 repair status OK 4435test.v1 repair Error 'test.v1' is not BASE TABLE 4436test.v1 repair status Operation failed 4437Table Op Msg_type Msg_text 4438test.t1 optimize status OK 4439test.t2 optimize status OK 4440test.t3 optimize status OK 4441test.v1 optimize Error 'test.v1' is not BASE TABLE 4442test.v1 optimize status Operation failed 4443Table Op Msg_type Msg_text 4444test.t1 analyze status Table is already up to date 4445test.t2 analyze status Table is already up to date 4446test.t3 analyze status Table is already up to date 4447test.v1 analyze Error 'test.v1' is not BASE TABLE 4448test.v1 analyze status Operation failed 4449call bug13012()| 4450Table Op Msg_type Msg_text 4451test.t1 repair status OK 4452test.t2 repair status OK 4453test.t3 repair status OK 4454test.v1 repair Error 'test.v1' is not BASE TABLE 4455test.v1 repair status Operation failed 4456Table Op Msg_type Msg_text 4457test.t1 optimize status OK 4458test.t2 optimize status OK 4459test.t3 optimize status OK 4460test.v1 optimize Error 'test.v1' is not BASE TABLE 4461test.v1 optimize status Operation failed 4462Table Op Msg_type Msg_text 4463test.t1 analyze status Table is already up to date 4464test.t2 analyze status Table is already up to date 4465test.t3 analyze status Table is already up to date 4466test.v1 analyze Error 'test.v1' is not BASE TABLE 4467test.v1 analyze status Operation failed 4468drop procedure bug13012| 4469drop view v1| 4470select * from t1 order by data| 4471id data 4472aa 0 4473aa 1 4474aa 2 4475aa 3 4476aa 4 4477aa 5 4478aa 6 4479aa 7 4480aa 8 4481aa 9 4482drop schema if exists mysqltest1| 4483Warnings: 4484Note 1008 Can't drop database 'mysqltest1'; database doesn't exist 4485drop schema if exists mysqltest2| 4486Warnings: 4487Note 1008 Can't drop database 'mysqltest2'; database doesn't exist 4488drop schema if exists mysqltest3| 4489Warnings: 4490Note 1008 Can't drop database 'mysqltest3'; database doesn't exist 4491create schema mysqltest1| 4492create schema mysqltest2| 4493create schema mysqltest3| 4494use mysqltest3| 4495create procedure mysqltest1.p1 (out prequestid varchar(100)) 4496begin 4497call mysqltest2.p2('call mysqltest3.p3(1, 2)'); 4498end| 4499create procedure mysqltest2.p2(in psql text) 4500begin 4501declare lsql text; 4502set @lsql= psql; 4503prepare lstatement from @lsql; 4504execute lstatement; 4505deallocate prepare lstatement; 4506end| 4507create procedure mysqltest3.p3(in p1 int) 4508begin 4509select p1; 4510end| 4511call mysqltest1.p1(@rs)| 4512ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 4513call mysqltest1.p1(@rs)| 4514ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 4515call mysqltest1.p1(@rs)| 4516ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 4517drop schema if exists mysqltest1| 4518drop schema if exists mysqltest2| 4519drop schema if exists mysqltest3| 4520use test| 4521drop table if exists t3| 4522drop procedure if exists bug15441| 4523create table t3 (id int not null primary key, county varchar(25))| 4524insert into t3 (id, county) values (1, 'York')| 4525create procedure bug15441(c varchar(25)) 4526begin 4527update t3 set id=2, county=values(c); 4528end| 4529call bug15441('county')| 4530ERROR 42S22: Unknown column 'c' in 'field list' 4531drop procedure bug15441| 4532create procedure bug15441(county varchar(25)) 4533begin 4534declare c varchar(25) default "hello"; 4535insert into t3 (id, county) values (1, county) 4536on duplicate key update county= values(county); 4537select * from t3; 4538update t3 set id=2, county=values(id); 4539select * from t3; 4540end| 4541call bug15441('Yale')| 4542id county 45431 Yale 4544id county 45452 NULL 4546drop table t3| 4547drop procedure bug15441| 4548drop procedure if exists bug14498_1| 4549drop procedure if exists bug14498_2| 4550drop procedure if exists bug14498_3| 4551drop procedure if exists bug14498_4| 4552drop procedure if exists bug14498_5| 4553create procedure bug14498_1() 4554begin 4555declare continue handler for sqlexception select 'error' as 'Handler'; 4556if v then 4557select 'yes' as 'v'; 4558else 4559select 'no' as 'v'; 4560end if; 4561select 'done' as 'End'; 4562end| 4563create procedure bug14498_2() 4564begin 4565declare continue handler for sqlexception select 'error' as 'Handler'; 4566while v do 4567select 'yes' as 'v'; 4568end while; 4569select 'done' as 'End'; 4570end| 4571create procedure bug14498_3() 4572begin 4573declare continue handler for sqlexception select 'error' as 'Handler'; 4574repeat 4575select 'maybe' as 'v'; 4576until v end repeat; 4577select 'done' as 'End'; 4578end| 4579create procedure bug14498_4() 4580begin 4581declare continue handler for sqlexception select 'error' as 'Handler'; 4582case v 4583when 1 then 4584select '1' as 'v'; 4585when 2 then 4586select '2' as 'v'; 4587else 4588select '?' as 'v'; 4589end case; 4590select 'done' as 'End'; 4591end| 4592create procedure bug14498_5() 4593begin 4594declare continue handler for sqlexception select 'error' as 'Handler'; 4595case 4596when v = 1 then 4597select '1' as 'v'; 4598when v = 2 then 4599select '2' as 'v'; 4600else 4601select '?' as 'v'; 4602end case; 4603select 'done' as 'End'; 4604end| 4605call bug14498_1()| 4606Handler 4607error 4608End 4609done 4610Warnings: 4611Error 1054 Unknown column 'v' in 'field list' 4612call bug14498_2()| 4613Handler 4614error 4615End 4616done 4617Warnings: 4618Error 1054 Unknown column 'v' in 'field list' 4619call bug14498_3()| 4620v 4621maybe 4622Handler 4623error 4624End 4625done 4626Warnings: 4627Error 1054 Unknown column 'v' in 'field list' 4628call bug14498_4()| 4629Handler 4630error 4631End 4632done 4633Warnings: 4634Error 1054 Unknown column 'v' in 'field list' 4635call bug14498_5()| 4636Handler 4637error 4638End 4639done 4640Warnings: 4641Error 1054 Unknown column 'v' in 'field list' 4642drop procedure bug14498_1| 4643drop procedure bug14498_2| 4644drop procedure bug14498_3| 4645drop procedure bug14498_4| 4646drop procedure bug14498_5| 4647drop table if exists t3| 4648drop procedure if exists bug15231_1| 4649drop procedure if exists bug15231_2| 4650drop procedure if exists bug15231_3| 4651drop procedure if exists bug15231_4| 4652drop procedure if exists bug15231_5| 4653drop procedure if exists bug15231_6| 4654create table t3 (id int not null)| 4655create procedure bug15231_1() 4656begin 4657declare xid integer; 4658declare xdone integer default 0; 4659declare continue handler for not found set xdone = 1; 4660set xid=null; 4661call bug15231_2(xid); 4662select xid, xdone; 4663end| 4664create procedure bug15231_2(inout ioid integer) 4665begin 4666select "Before NOT FOUND condition is triggered" as '1'; 4667select id into ioid from t3 where id=ioid; 4668select "After NOT FOUND condtition is triggered" as '2'; 4669if ioid is null then 4670set ioid=1; 4671end if; 4672end| 4673create procedure bug15231_3() 4674begin 4675declare exit handler for sqlwarning 4676select 'Caught it (correct)' as 'Result'; 4677call bug15231_4(); 4678end| 4679create procedure bug15231_4() 4680begin 4681declare x decimal(2,1); 4682set x = 'zap'; 4683select 'Missed it (correct)' as 'Result'; 4684show warnings; 4685end| 4686create procedure bug15231_5() 4687begin 4688declare exit handler for sqlwarning 4689select 'Caught it (wrong)' as 'Result'; 4690call bug15231_6(); 4691end| 4692create procedure bug15231_6() 4693begin 4694declare x decimal(2,1); 4695set x = 'zap'; 4696select 'Missed it (correct)' as 'Result'; 4697select id from t3; 4698end| 4699call bug15231_1()| 47001 4701Before NOT FOUND condition is triggered 47022 4703After NOT FOUND condtition is triggered 4704xid xdone 47051 1 4706Warnings: 4707Warning 1329 No data - zero rows fetched, selected, or processed 4708call bug15231_3()| 4709Result 4710Missed it (correct) 4711Level Code Message 4712Warning 1366 Incorrect decimal value: 'zap' for column 'x' at row 1 4713Result 4714Caught it (correct) 4715Warnings: 4716Warning 1366 Incorrect decimal value: 'zap' for column 'x' at row 1 4717call bug15231_5()| 4718Result 4719Missed it (correct) 4720id 4721drop table t3| 4722drop procedure bug15231_1| 4723drop procedure bug15231_2| 4724drop procedure bug15231_3| 4725drop procedure bug15231_4| 4726drop procedure bug15231_5| 4727drop procedure bug15231_6| 4728drop procedure if exists bug15011| 4729create table t3 (c1 int primary key)| 4730insert into t3 values (1)| 4731create procedure bug15011() 4732deterministic 4733begin 4734declare continue handler for 1062 4735select 'Outer' as 'Handler'; 4736begin 4737declare continue handler for 1062 4738select 'Inner' as 'Handler'; 4739insert into t3 values (1); 4740end; 4741end| 4742call bug15011()| 4743Handler 4744Inner 4745Warnings: 4746Error 1062 Duplicate entry '1' for key 'PRIMARY' 4747drop procedure bug15011| 4748drop table t3| 4749drop procedure if exists bug17476| 4750create table t3 ( d date )| 4751insert into t3 values 4752( '2005-01-01' ), ( '2005-01-02' ), ( '2005-01-03' ), 4753( '2005-01-04' ), ( '2005-02-01' ), ( '2005-02-02' )| 4754create procedure bug17476(pDateFormat varchar(10)) 4755select date_format(t3.d, pDateFormat), count(*) 4756from t3 4757group by date_format(t3.d, pDateFormat)| 4758call bug17476('%Y-%m')| 4759date_format(t3.d, pDateFormat) count(*) 47602005-01 4 47612005-02 2 4762call bug17476('%Y-%m')| 4763date_format(t3.d, pDateFormat) count(*) 47642005-01 4 47652005-02 2 4766drop table t3| 4767drop procedure bug17476| 4768drop table if exists t3| 4769drop procedure if exists bug16887| 4770create table t3 ( c varchar(1) )| 4771insert into t3 values 4772(' '),('.'),(';'),(','),('-'),('_'),('('),(')'),('/'),('\\')| 4773create procedure bug16887() 4774begin 4775declare i int default 10; 4776again: 4777while i > 0 do 4778begin 4779declare breakchar varchar(1); 4780declare done int default 0; 4781declare t3_cursor cursor for select c from t3; 4782declare continue handler for not found set done = 1; 4783set i = i - 1; 4784select i; 4785if i = 3 then 4786iterate again; 4787end if; 4788open t3_cursor; 4789loop 4790fetch t3_cursor into breakchar; 4791if done = 1 then 4792begin 4793close t3_cursor; 4794iterate again; 4795end; 4796end if; 4797end loop; 4798end; 4799end while; 4800end| 4801call bug16887()| 4802i 48039 4804i 48058 4806i 48077 4808i 48096 4810i 48115 4812i 48134 4814i 48153 4816i 48172 4818i 48191 4820i 48210 4822Warnings: 4823Error 1329 No data - zero rows fetched, selected, or processed 4824drop table t3| 4825drop procedure bug16887| 4826drop procedure if exists bug16474_1| 4827drop procedure if exists bug16474_2| 4828delete from t1| 4829insert into t1 values ('c', 2), ('b', 3), ('a', 1)| 4830create procedure bug16474_1() 4831begin 4832declare x int; 4833select id from t1 order by x, id; 4834end| 4835drop procedure if exists bug14945| 4836create table t3 (id int not null auto_increment primary key)| 4837create procedure bug14945() deterministic truncate t3| 4838insert into t3 values (null)| 4839call bug14945()| 4840insert into t3 values (null)| 4841select * from t3| 4842id 48431 4844drop table t3| 4845drop procedure bug14945| 4846create procedure bug16474_2(x int) 4847select id from t1 order by x, id| 4848call bug16474_1()| 4849id 4850a 4851b 4852c 4853call bug16474_2(1)| 4854id 4855a 4856b 4857c 4858call bug16474_2(2)| 4859id 4860a 4861b 4862c 4863drop procedure bug16474_1| 4864drop procedure bug16474_2| 4865set @x = 2| 4866select * from t1 order by @x, data| 4867id data 4868a 1 4869c 2 4870b 3 4871delete from t1| 4872drop function if exists bug15728| 4873drop table if exists t3| 4874create table t3 ( 4875id int not null auto_increment, 4876primary key (id) 4877)| 4878create function bug15728() returns int(11) 4879return last_insert_id()| 4880insert into t3 values (0)| 4881select last_insert_id()| 4882last_insert_id() 48831 4884select bug15728()| 4885bug15728() 48861 4887drop function bug15728| 4888drop table t3| 4889drop procedure if exists bug18787| 4890create procedure bug18787() 4891begin 4892declare continue handler for sqlexception begin end; 4893select no_such_function(); 4894end| 4895call bug18787()| 4896Warnings: 4897Error 1305 FUNCTION test.no_such_function does not exist 4898drop procedure bug18787| 4899create database bug18344_012345678901| 4900use bug18344_012345678901| 4901create procedure bug18344() begin end| 4902create procedure bug18344_2() begin end| 4903create database bug18344_0123456789012| 4904use bug18344_0123456789012| 4905create procedure bug18344() begin end| 4906create procedure bug18344_2() begin end| 4907use test| 4908select schema_name from information_schema.schemata where 4909schema_name like 'bug18344%'| 4910schema_name 4911bug18344_012345678901 4912bug18344_0123456789012 4913select routine_name,routine_schema from information_schema.routines where 4914routine_schema like 'bug18344%'| 4915routine_name routine_schema 4916bug18344 bug18344_012345678901 4917bug18344_2 bug18344_012345678901 4918bug18344 bug18344_0123456789012 4919bug18344_2 bug18344_0123456789012 4920drop database bug18344_012345678901| 4921drop database bug18344_0123456789012| 4922select schema_name from information_schema.schemata where 4923schema_name like 'bug18344%'| 4924schema_name 4925select routine_name,routine_schema from information_schema.routines where 4926routine_schema like 'bug18344%'| 4927routine_name routine_schema 4928drop function if exists bug12472| 4929create function bug12472() returns int return (select count(*) from t1)| 4930create table t3 as select bug12472() as i| 4931show create table t3| 4932Table Create Table 4933t3 CREATE TABLE `t3` ( 4934 `i` int(11) DEFAULT NULL 4935) ENGINE=MyISAM DEFAULT CHARSET=latin1 4936select * from t3| 4937i 49380 4939drop table t3| 4940create view v1 as select bug12472() as j| 4941create table t3 as select * from v1| 4942show create table t3| 4943Table Create Table 4944t3 CREATE TABLE `t3` ( 4945 `j` int(11) DEFAULT NULL 4946) ENGINE=MyISAM DEFAULT CHARSET=latin1 4947select * from t3| 4948j 49490 4950drop table t3| 4951drop view v1| 4952drop function bug12472| 4953DROP FUNCTION IF EXISTS bug18589_f1| 4954DROP PROCEDURE IF EXISTS bug18589_p1| 4955DROP PROCEDURE IF EXISTS bug18589_p2| 4956CREATE FUNCTION bug18589_f1(arg TEXT) RETURNS TEXT 4957BEGIN 4958RETURN CONCAT(arg, ""); 4959END| 4960CREATE PROCEDURE bug18589_p1(arg TEXT, OUT ret TEXT) 4961BEGIN 4962SET ret = CONCAT(arg, ""); 4963END| 4964CREATE PROCEDURE bug18589_p2(arg TEXT) 4965BEGIN 4966DECLARE v TEXT; 4967CALL bug18589_p1(arg, v); 4968SELECT v; 4969END| 4970SELECT bug18589_f1(REPEAT("a", 767))| 4971bug18589_f1(REPEAT("a", 767)) 4972aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 4973SET @bug18589_v1 = ""| 4974CALL bug18589_p1(REPEAT("a", 767), @bug18589_v1)| 4975SELECT @bug18589_v1| 4976@bug18589_v1 4977aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 4978CALL bug18589_p2(REPEAT("a", 767))| 4979v 4980aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 4981DROP FUNCTION bug18589_f1| 4982DROP PROCEDURE bug18589_p1| 4983DROP PROCEDURE bug18589_p2| 4984DROP FUNCTION IF EXISTS bug18037_f1| 4985DROP PROCEDURE IF EXISTS bug18037_p1| 4986DROP PROCEDURE IF EXISTS bug18037_p2| 4987CREATE FUNCTION bug18037_f1() RETURNS INT 4988BEGIN 4989RETURN @@server_id; 4990END| 4991CREATE PROCEDURE bug18037_p1() 4992BEGIN 4993DECLARE v INT DEFAULT @@server_id; 4994END| 4995CREATE PROCEDURE bug18037_p2() 4996BEGIN 4997CASE @@server_id 4998WHEN -1 THEN 4999SELECT 0; 5000ELSE 5001SELECT 1; 5002END CASE; 5003END| 5004SELECT bug18037_f1()| 5005bug18037_f1() 50061 5007CALL bug18037_p1()| 5008CALL bug18037_p2()| 50091 50101 5011DROP FUNCTION bug18037_f1| 5012DROP PROCEDURE bug18037_p1| 5013DROP PROCEDURE bug18037_p2| 5014use test| 5015create table t3 (i int)| 5016insert into t3 values (1), (2)| 5017create database mysqltest1| 5018use mysqltest1| 5019create function bug17199() returns varchar(2) deterministic return 'ok'| 5020use test| 5021select *, mysqltest1.bug17199() from t3| 5022i mysqltest1.bug17199() 50231 ok 50242 ok 5025use mysqltest1| 5026create function bug18444(i int) returns int no sql deterministic return i + 1| 5027use test| 5028select mysqltest1.bug18444(i) from t3| 5029mysqltest1.bug18444(i) 50302 50313 5032drop database mysqltest1| 5033create database mysqltest1 charset=utf8| 5034create database mysqltest2 charset=utf8| 5035create procedure mysqltest1.p1() 5036begin 5037-- alters the default collation of database test 5038alter database character set koi8r; 5039end| 5040use mysqltest1| 5041call p1()| 5042show create database mysqltest1| 5043Database Create Database 5044mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET koi8r */ 5045show create database mysqltest2| 5046Database Create Database 5047mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET utf8 */ 5048alter database mysqltest1 character set utf8| 5049use mysqltest2| 5050call mysqltest1.p1()| 5051show create database mysqltest1| 5052Database Create Database 5053mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET koi8r */ 5054show create database mysqltest2| 5055Database Create Database 5056mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET utf8 */ 5057drop database mysqltest1| 5058drop database mysqltest2| 5059use test| 5060drop table if exists t3| 5061drop procedure if exists bug15217| 5062create table t3 as select 1| 5063create procedure bug15217() 5064begin 5065declare var1 char(255); 5066declare cur1 cursor for select * from t3; 5067open cur1; 5068fetch cur1 into var1; 5069select concat('data was: /', var1, '/'); 5070close cur1; 5071end | 5072call bug15217()| 5073concat('data was: /', var1, '/') 5074data was: /1/ 5075flush tables | 5076call bug15217()| 5077concat('data was: /', var1, '/') 5078data was: /1/ 5079drop table t3| 5080drop procedure bug15217| 5081DROP PROCEDURE IF EXISTS bug21013 | 5082CREATE PROCEDURE bug21013(IN lim INT) 5083BEGIN 5084DECLARE i INT DEFAULT 0; 5085WHILE (i < lim) DO 5086SET @b = LOCATE(_latin1'b', @a, 1); 5087SET i = i + 1; 5088END WHILE; 5089END | 5090SET @a = _latin2"aaaaaaaaaa" | 5091CALL bug21013(10) | 5092DROP PROCEDURE bug21013 | 5093DROP DATABASE IF EXISTS mysqltest1| 5094DROP DATABASE IF EXISTS mysqltest2| 5095CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8| 5096CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8| 5097use mysqltest1| 5098CREATE FUNCTION bug16211_f1() RETURNS CHAR(10) 5099RETURN ""| 5100CREATE FUNCTION bug16211_f2() RETURNS CHAR(10) CHARSET koi8r 5101RETURN ""| 5102CREATE FUNCTION mysqltest2.bug16211_f3() RETURNS CHAR(10) 5103RETURN ""| 5104CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r 5105RETURN ""| 5106SHOW CREATE FUNCTION bug16211_f1| 5107Function sql_mode Create Function character_set_client collation_connection Database Collation 5108bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8 5109RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5110SHOW CREATE FUNCTION bug16211_f2| 5111Function sql_mode Create Function character_set_client collation_connection Database Collation 5112bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r 5113RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5114SHOW CREATE FUNCTION mysqltest2.bug16211_f3| 5115Function sql_mode Create Function character_set_client collation_connection Database Collation 5116bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8 5117RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5118SHOW CREATE FUNCTION mysqltest2.bug16211_f4| 5119Function sql_mode Create Function character_set_client collation_connection Database Collation 5120bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r 5121RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5122SELECT dtd_identifier 5123FROM INFORMATION_SCHEMA.ROUTINES 5124WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| 5125dtd_identifier 5126char(10) 5127SELECT dtd_identifier 5128FROM INFORMATION_SCHEMA.ROUTINES 5129WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"| 5130dtd_identifier 5131char(10) 5132SELECT dtd_identifier 5133FROM INFORMATION_SCHEMA.ROUTINES 5134WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"| 5135dtd_identifier 5136char(10) 5137SELECT dtd_identifier 5138FROM INFORMATION_SCHEMA.ROUTINES 5139WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"| 5140dtd_identifier 5141char(10) 5142SELECT CHARSET(bug16211_f1())| 5143CHARSET(bug16211_f1()) 5144utf8 5145SELECT CHARSET(bug16211_f2())| 5146CHARSET(bug16211_f2()) 5147koi8r 5148SELECT CHARSET(mysqltest2.bug16211_f3())| 5149CHARSET(mysqltest2.bug16211_f3()) 5150utf8 5151SELECT CHARSET(mysqltest2.bug16211_f4())| 5152CHARSET(mysqltest2.bug16211_f4()) 5153koi8r 5154ALTER DATABASE mysqltest1 CHARACTER SET cp1251| 5155ALTER DATABASE mysqltest2 CHARACTER SET cp1251| 5156SHOW CREATE FUNCTION bug16211_f1| 5157Function sql_mode Create Function character_set_client collation_connection Database Collation 5158bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8 5159RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5160SHOW CREATE FUNCTION bug16211_f2| 5161Function sql_mode Create Function character_set_client collation_connection Database Collation 5162bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r 5163RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5164SHOW CREATE FUNCTION mysqltest2.bug16211_f3| 5165Function sql_mode Create Function character_set_client collation_connection Database Collation 5166bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8 5167RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5168SHOW CREATE FUNCTION mysqltest2.bug16211_f4| 5169Function sql_mode Create Function character_set_client collation_connection Database Collation 5170bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r 5171RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5172SELECT dtd_identifier 5173FROM INFORMATION_SCHEMA.ROUTINES 5174WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| 5175dtd_identifier 5176char(10) 5177SELECT dtd_identifier 5178FROM INFORMATION_SCHEMA.ROUTINES 5179WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"| 5180dtd_identifier 5181char(10) 5182SELECT dtd_identifier 5183FROM INFORMATION_SCHEMA.ROUTINES 5184WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"| 5185dtd_identifier 5186char(10) 5187SELECT dtd_identifier 5188FROM INFORMATION_SCHEMA.ROUTINES 5189WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"| 5190dtd_identifier 5191char(10) 5192SELECT CHARSET(bug16211_f1())| 5193CHARSET(bug16211_f1()) 5194utf8 5195SELECT CHARSET(bug16211_f2())| 5196CHARSET(bug16211_f2()) 5197koi8r 5198SELECT CHARSET(mysqltest2.bug16211_f3())| 5199CHARSET(mysqltest2.bug16211_f3()) 5200utf8 5201SELECT CHARSET(mysqltest2.bug16211_f4())| 5202CHARSET(mysqltest2.bug16211_f4()) 5203koi8r 5204use test| 5205DROP DATABASE mysqltest1| 5206DROP DATABASE mysqltest2| 5207DROP DATABASE IF EXISTS mysqltest1| 5208CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8| 5209use mysqltest1| 5210CREATE PROCEDURE bug16676_p1( 5211IN p1 CHAR(10), 5212INOUT p2 CHAR(10), 5213OUT p3 CHAR(10)) 5214BEGIN 5215SELECT CHARSET(p1), COLLATION(p1); 5216SELECT CHARSET(p2), COLLATION(p2); 5217SELECT CHARSET(p3), COLLATION(p3); 5218END| 5219CREATE PROCEDURE bug16676_p2( 5220IN p1 CHAR(10) CHARSET koi8r, 5221INOUT p2 CHAR(10) CHARSET cp1251, 5222OUT p3 CHAR(10) CHARSET greek) 5223BEGIN 5224SELECT CHARSET(p1), COLLATION(p1); 5225SELECT CHARSET(p2), COLLATION(p2); 5226SELECT CHARSET(p3), COLLATION(p3); 5227END| 5228SET @v2 = 'b'| 5229SET @v3 = 'c'| 5230CALL bug16676_p1('a', @v2, @v3)| 5231CHARSET(p1) COLLATION(p1) 5232utf8 utf8_general_ci 5233CHARSET(p2) COLLATION(p2) 5234utf8 utf8_general_ci 5235CHARSET(p3) COLLATION(p3) 5236utf8 utf8_general_ci 5237CALL bug16676_p2('a', @v2, @v3)| 5238CHARSET(p1) COLLATION(p1) 5239koi8r koi8r_general_ci 5240CHARSET(p2) COLLATION(p2) 5241cp1251 cp1251_general_ci 5242CHARSET(p3) COLLATION(p3) 5243greek greek_general_ci 5244use test| 5245DROP DATABASE mysqltest1| 5246drop table if exists t3| 5247drop table if exists t4| 5248drop procedure if exists bug8153_subselect| 5249drop procedure if exists bug8153_subselect_a| 5250drop procedure if exists bug8153_subselect_b| 5251drop procedure if exists bug8153_proc_a| 5252drop procedure if exists bug8153_proc_b| 5253create table t3 (a int)| 5254create table t4 (a int)| 5255insert into t3 values (1), (1), (2), (3)| 5256insert into t4 values (1), (1)| 5257create procedure bug8153_subselect() 5258begin 5259declare continue handler for sqlexception 5260begin 5261select 'statement failed'; 5262end; 5263update t3 set a=a+1 where (select a from t4 where a=1) is null; 5264select 'statement after update'; 5265end| 5266call bug8153_subselect()| 5267statement failed 5268statement failed 5269statement after update 5270statement after update 5271Warnings: 5272Error 1242 Subquery returns more than 1 row 5273select * from t3| 5274a 52751 52761 52772 52783 5279call bug8153_subselect()| 5280statement failed 5281statement failed 5282statement after update 5283statement after update 5284Warnings: 5285Error 1242 Subquery returns more than 1 row 5286select * from t3| 5287a 52881 52891 52902 52913 5292drop procedure bug8153_subselect| 5293create procedure bug8153_subselect_a() 5294begin 5295declare continue handler for sqlexception 5296begin 5297select 'in continue handler'; 5298end; 5299select 'reachable code a1'; 5300call bug8153_subselect_b(); 5301select 'reachable code a2'; 5302end| 5303create procedure bug8153_subselect_b() 5304begin 5305select 'reachable code b1'; 5306update t3 set a=a+1 where (select a from t4 where a=1) is null; 5307select 'unreachable code b2'; 5308end| 5309call bug8153_subselect_a()| 5310reachable code a1 5311reachable code a1 5312reachable code b1 5313reachable code b1 5314in continue handler 5315in continue handler 5316reachable code a2 5317reachable code a2 5318Warnings: 5319Error 1242 Subquery returns more than 1 row 5320select * from t3| 5321a 53221 53231 53242 53253 5326call bug8153_subselect_a()| 5327reachable code a1 5328reachable code a1 5329reachable code b1 5330reachable code b1 5331in continue handler 5332in continue handler 5333reachable code a2 5334reachable code a2 5335Warnings: 5336Error 1242 Subquery returns more than 1 row 5337select * from t3| 5338a 53391 53401 53412 53423 5343drop procedure bug8153_subselect_a| 5344drop procedure bug8153_subselect_b| 5345create procedure bug8153_proc_a() 5346begin 5347declare continue handler for sqlexception 5348begin 5349select 'in continue handler'; 5350end; 5351select 'reachable code a1'; 5352call bug8153_proc_b(); 5353select 'reachable code a2'; 5354end| 5355create procedure bug8153_proc_b() 5356begin 5357select 'reachable code b1'; 5358select no_such_function(); 5359select 'unreachable code b2'; 5360end| 5361call bug8153_proc_a()| 5362reachable code a1 5363reachable code a1 5364reachable code b1 5365reachable code b1 5366in continue handler 5367in continue handler 5368reachable code a2 5369reachable code a2 5370Warnings: 5371Error 1305 FUNCTION test.no_such_function does not exist 5372drop procedure bug8153_proc_a| 5373drop procedure bug8153_proc_b| 5374drop table t3| 5375drop table t4| 5376drop procedure if exists bug19862| 5377CREATE TABLE t11 (a INT)| 5378CREATE TABLE t12 (a INT)| 5379CREATE FUNCTION bug19862(x INT) RETURNS INT 5380BEGIN 5381INSERT INTO t11 VALUES (x); 5382RETURN x+1; 5383END| 5384INSERT INTO t12 VALUES (1), (2)| 5385SELECT bug19862(a) FROM t12 ORDER BY 1| 5386bug19862(a) 53872 53883 5389SELECT * FROM t11| 5390a 53911 53922 5393DROP TABLE t11, t12| 5394DROP FUNCTION bug19862| 5395drop table if exists t3| 5396drop database if exists mysqltest1| 5397create table t3 (a int)| 5398insert into t3 (a) values (1), (2)| 5399create database mysqltest1| 5400use mysqltest1| 5401drop database mysqltest1| 5402select database()| 5403database() 5404NULL 5405select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2| 5406a 54071 5408use test| 5409drop table t3| 5410DROP PROCEDURE IF EXISTS bug16899_p1| 5411DROP FUNCTION IF EXISTS bug16899_f1| 5412CREATE DEFINER=1234567890abcdefGHIKL@localhost PROCEDURE bug16899_p1() 5413BEGIN 5414SET @a = 1; 5415END| 5416ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16) 5417CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY 5418FUNCTION bug16899_f1() RETURNS INT 5419BEGIN 5420RETURN 1; 5421END| 5422ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) 5423drop procedure if exists bug21416| 5424create procedure bug21416() show create procedure bug21416| 5425call bug21416()| 5426Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 5427bug21416 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21416`() 5428show create procedure bug21416 latin1 latin1_swedish_ci latin1_swedish_ci 5429drop procedure bug21416| 5430DROP PROCEDURE IF EXISTS bug21414| 5431CREATE PROCEDURE bug21414() SELECT 1| 5432FLUSH TABLES WITH READ LOCK| 5433DROP PROCEDURE bug21414| 5434ERROR HY000: Can't execute the query because you have a conflicting read lock 5435UNLOCK TABLES| 5436The following should succeed. 5437DROP PROCEDURE bug21414| 5438set names utf8| 5439drop database if exists това_е_дълго_име_за_база_данни_нали| 5440create database това_е_дълго_име_за_база_данни_нали| 5441INSERT 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')| 5442call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()| 5443ERROR HY000: Failed to load routine това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) 5444drop database това_е_дълго_име_за_база_данни_нали| 5445CREATE TABLE t3 ( 5446Member_ID varchar(15) NOT NULL, 5447PRIMARY KEY (Member_ID) 5448)| 5449CREATE TABLE t4 ( 5450ID int(10) unsigned NOT NULL auto_increment, 5451Member_ID varchar(15) NOT NULL default '', 5452Action varchar(12) NOT NULL, 5453Action_Date datetime NOT NULL, 5454Track varchar(15) default NULL, 5455User varchar(12) default NULL, 5456Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update 5457CURRENT_TIMESTAMP, 5458PRIMARY KEY (ID), 5459KEY Action (Action), 5460KEY Action_Date (Action_Date) 5461)| 5462INSERT INTO t3(Member_ID) VALUES 5463('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')| 5464INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES 5465('111111', 'Disenrolled', '2006-03-01', 'CAD' ), 5466('111111', 'Enrolled', '2006-03-01', 'CAD' ), 5467('111111', 'Disenrolled', '2006-07-03', 'CAD' ), 5468('222222', 'Enrolled', '2006-03-07', 'CAD' ), 5469('222222', 'Enrolled', '2006-03-07', 'CHF' ), 5470('222222', 'Disenrolled', '2006-08-02', 'CHF' ), 5471('333333', 'Enrolled', '2006-03-01', 'CAD' ), 5472('333333', 'Disenrolled', '2006-03-01', 'CAD' ), 5473('444444', 'Enrolled', '2006-03-01', 'CAD' ), 5474('555555', 'Disenrolled', '2006-03-01', 'CAD' ), 5475('555555', 'Enrolled', '2006-07-21', 'CAD' ), 5476('555555', 'Disenrolled', '2006-03-01', 'CHF' ), 5477('666666', 'Enrolled', '2006-02-09', 'CAD' ), 5478('666666', 'Enrolled', '2006-05-12', 'CHF' ), 5479('666666', 'Disenrolled', '2006-06-01', 'CAD' )| 5480DROP FUNCTION IF EXISTS bug21493| 5481CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45) 5482BEGIN 5483DECLARE tracks VARCHAR(45); 5484SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4 5485WHERE Member_ID=paramMember AND Action='Enrolled' AND 5486(Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4 5487WHERE Member_ID=paramMember GROUP BY Track); 5488RETURN tracks; 5489END| 5490SELECT bug21493('111111')| 5491bug21493('111111') 5492NULL 5493SELECT bug21493('222222')| 5494bug21493('222222') 5495CAD 5496SELECT bug21493(Member_ID) FROM t3| 5497bug21493(Member_ID) 5498NULL 5499CAD 5500CAD 5501CAD 5502CAD 5503CHF 5504DROP FUNCTION bug21493| 5505DROP TABLE t3,t4| 5506drop function if exists func_20028_a| 5507drop function if exists func_20028_b| 5508drop function if exists func_20028_c| 5509drop procedure if exists proc_20028_a| 5510drop procedure if exists proc_20028_b| 5511drop procedure if exists proc_20028_c| 5512drop table if exists table_20028| 5513create table table_20028 (i int)| 5514SET @save_sql_mode=@@sql_mode| 5515SET sql_mode=''| 5516create function func_20028_a() returns integer 5517begin 5518declare temp integer; 5519select i into temp from table_20028 limit 1; 5520return ifnull(temp, 0); 5521end| 5522create function func_20028_b() returns integer 5523begin 5524return func_20028_a(); 5525end| 5526create function func_20028_c() returns integer 5527begin 5528declare div_zero integer; 5529set SQL_MODE='TRADITIONAL'; 5530select 1/0 into div_zero; 5531return div_zero; 5532end| 5533create procedure proc_20028_a() 5534begin 5535declare temp integer; 5536select i into temp from table_20028 limit 1; 5537end| 5538create procedure proc_20028_b() 5539begin 5540call proc_20028_a(); 5541end| 5542create procedure proc_20028_c() 5543begin 5544declare div_zero integer; 5545set SQL_MODE='TRADITIONAL'; 5546select 1/0 into div_zero; 5547end| 5548select func_20028_a()| 5549func_20028_a() 55500 5551Warnings: 5552Warning 1329 No data - zero rows fetched, selected, or processed 5553select func_20028_b()| 5554func_20028_b() 55550 5556Warnings: 5557Warning 1329 No data - zero rows fetched, selected, or processed 5558select func_20028_c()| 5559ERROR 22012: Division by 0 5560call proc_20028_a()| 5561Warnings: 5562Warning 1329 No data - zero rows fetched, selected, or processed 5563call proc_20028_b()| 5564Warnings: 5565Warning 1329 No data - zero rows fetched, selected, or processed 5566call proc_20028_c()| 5567ERROR 22012: Division by 0 5568SET sql_mode='TRADITIONAL'| 5569drop function func_20028_a| 5570drop function func_20028_b| 5571drop function func_20028_c| 5572drop procedure proc_20028_a| 5573drop procedure proc_20028_b| 5574drop procedure proc_20028_c| 5575create function func_20028_a() returns integer 5576begin 5577declare temp integer; 5578select i into temp from table_20028 limit 1; 5579return ifnull(temp, 0); 5580end| 5581create function func_20028_b() returns integer 5582begin 5583return func_20028_a(); 5584end| 5585create function func_20028_c() returns integer 5586begin 5587declare div_zero integer; 5588set SQL_MODE=''; 5589select 1/0 into div_zero; 5590return div_zero; 5591end| 5592create procedure proc_20028_a() 5593begin 5594declare temp integer; 5595select i into temp from table_20028 limit 1; 5596end| 5597create procedure proc_20028_b() 5598begin 5599call proc_20028_a(); 5600end| 5601create procedure proc_20028_c() 5602begin 5603declare div_zero integer; 5604set SQL_MODE=''; 5605select 1/0 into div_zero; 5606end| 5607select func_20028_a()| 5608func_20028_a() 56090 5610Warnings: 5611Warning 1329 No data - zero rows fetched, selected, or processed 5612select func_20028_b()| 5613func_20028_b() 56140 5615Warnings: 5616Warning 1329 No data - zero rows fetched, selected, or processed 5617select func_20028_c()| 5618func_20028_c() 5619NULL 5620call proc_20028_a()| 5621Warnings: 5622Warning 1329 No data - zero rows fetched, selected, or processed 5623call proc_20028_b()| 5624Warnings: 5625Warning 1329 No data - zero rows fetched, selected, or processed 5626call proc_20028_c()| 5627SET @@sql_mode=@save_sql_mode| 5628drop function func_20028_a| 5629drop function func_20028_b| 5630drop function func_20028_c| 5631drop procedure proc_20028_a| 5632drop procedure proc_20028_b| 5633drop procedure proc_20028_c| 5634drop table table_20028| 5635drop procedure if exists proc_21462_a| 5636drop procedure if exists proc_21462_b| 5637create procedure proc_21462_a() 5638begin 5639select "Called A"; 5640end| 5641create procedure proc_21462_b(x int) 5642begin 5643select "Called B"; 5644end| 5645call proc_21462_a| 5646Called A 5647Called A 5648call proc_21462_a()| 5649Called A 5650Called A 5651call proc_21462_a(1)| 5652ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_a; expected 0, got 1 5653call proc_21462_b| 5654ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_b; expected 1, got 0 5655call proc_21462_b()| 5656ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_b; expected 1, got 0 5657call proc_21462_b(1)| 5658Called B 5659Called B 5660drop procedure proc_21462_a| 5661drop procedure proc_21462_b| 5662drop table if exists t3| 5663drop procedure if exists proc_bug19733| 5664create table t3 (s1 int)| 5665create procedure proc_bug19733() 5666begin 5667declare v int default 0; 5668while v < 100 do 5669create index i on t3 (s1); 5670drop index i on t3; 5671set v = v + 1; 5672end while; 5673end| 5674call proc_bug19733()| 5675call proc_bug19733()| 5676call proc_bug19733()| 5677drop procedure proc_bug19733| 5678drop table t3| 5679DROP PROCEDURE IF EXISTS p1| 5680DROP VIEW IF EXISTS v1, v2| 5681DROP TABLE IF EXISTS t3, t4| 5682CREATE TABLE t3 (t3_id INT)| 5683INSERT INTO t3 VALUES (0)| 5684INSERT INTO t3 VALUES (1)| 5685CREATE TABLE t4 (t4_id INT)| 5686INSERT INTO t4 VALUES (2)| 5687CREATE VIEW v1 AS 5688SELECT t3.t3_id, t4.t4_id 5689FROM t3 JOIN t4 ON t3.t3_id = 0| 5690CREATE VIEW v2 AS 5691SELECT t3.t3_id AS t3_id_1, v1.t3_id AS t3_id_2, v1.t4_id 5692FROM t3 LEFT JOIN v1 ON t3.t3_id = 0| 5693CREATE PROCEDURE p1() SELECT * FROM v2| 5694CALL p1()| 5695t3_id_1 t3_id_2 t4_id 56960 0 2 56971 NULL NULL 5698CALL p1()| 5699t3_id_1 t3_id_2 t4_id 57000 0 2 57011 NULL NULL 5702DROP PROCEDURE p1| 5703DROP VIEW v1, v2| 5704DROP TABLE t3, t4| 5705End of 5.0 tests 5706Begin of 5.1 tests 5707drop function if exists pi; 5708create function pi() returns varchar(50) 5709return "pie, my favorite desert."; 5710Warnings: 5711Note 1585 This function 'pi' has the same name as a native function 5712SET @save_sql_mode=@@sql_mode; 5713SET SQL_MODE='IGNORE_SPACE'; 5714select pi(), pi (); 5715pi() pi () 57163.141593 3.141593 5717select test.pi(), test.pi (); 5718test.pi() test.pi () 5719pie, my favorite desert. pie, my favorite desert. 5720SET SQL_MODE=''; 5721select pi(), pi (); 5722pi() pi () 57233.141593 3.141593 5724select test.pi(), test.pi (); 5725test.pi() test.pi () 5726pie, my favorite desert. pie, my favorite desert. 5727SET @@sql_mode=@save_sql_mode; 5728drop function pi; 5729drop function if exists test.database; 5730drop function if exists test.current_user; 5731drop function if exists test.md5; 5732create database nowhere; 5733use nowhere; 5734drop database nowhere; 5735SET @save_sql_mode=@@sql_mode; 5736SET SQL_MODE='IGNORE_SPACE'; 5737select database(), database (); 5738database() database () 5739NULL NULL 5740select current_user(), current_user (); 5741current_user() current_user () 5742root@localhost root@localhost 5743select md5("aaa"), md5 ("aaa"); 5744md5("aaa") md5 ("aaa") 574547bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 5746SET SQL_MODE=''; 5747select database(), database (); 5748database() database () 5749NULL NULL 5750select current_user(), current_user (); 5751current_user() current_user () 5752root@localhost root@localhost 5753select md5("aaa"), md5 ("aaa"); 5754md5("aaa") md5 ("aaa") 575547bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 5756use test; 5757create function `database`() returns varchar(50) 5758return "Stored function database"; 5759Warnings: 5760Note 1585 This function 'database' has the same name as a native function 5761create function `current_user`() returns varchar(50) 5762return "Stored function current_user"; 5763Warnings: 5764Note 1585 This function 'current_user' has the same name as a native function 5765create function md5(x varchar(50)) returns varchar(50) 5766return "Stored function md5"; 5767Warnings: 5768Note 1585 This function 'md5' has the same name as a native function 5769SET SQL_MODE='IGNORE_SPACE'; 5770select database(), database (); 5771database() database () 5772test test 5773select current_user(), current_user (); 5774current_user() current_user () 5775root@localhost root@localhost 5776select md5("aaa"), md5 ("aaa"); 5777md5("aaa") md5 ("aaa") 577847bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 5779select test.database(), test.database (); 5780test.database() test.database () 5781Stored function database Stored function database 5782select test.current_user(), test.current_user (); 5783test.current_user() test.current_user () 5784Stored function current_user Stored function current_user 5785select test.md5("aaa"), test.md5 ("aaa"); 5786test.md5("aaa") test.md5 ("aaa") 5787Stored function md5 Stored function md5 5788SET SQL_MODE=''; 5789select database(), database (); 5790database() database () 5791test test 5792select current_user(), current_user (); 5793current_user() current_user () 5794root@localhost root@localhost 5795select md5("aaa"), md5 ("aaa"); 5796md5("aaa") md5 ("aaa") 579747bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 5798select test.database(), test.database (); 5799test.database() test.database () 5800Stored function database Stored function database 5801select test.current_user(), test.current_user (); 5802test.current_user() test.current_user () 5803Stored function current_user Stored function current_user 5804select test.md5("aaa"), test.md5 ("aaa"); 5805test.md5("aaa") test.md5 ("aaa") 5806Stored function md5 Stored function md5 5807SET @@sql_mode=@save_sql_mode; 5808drop function test.database; 5809drop function test.current_user; 5810drop function md5; 5811use test; 5812End of 5.1 tests 5813DROP TABLE IF EXISTS bug23760| 5814DROP TABLE IF EXISTS bug23760_log| 5815DROP PROCEDURE IF EXISTS bug23760_update_log| 5816DROP PROCEDURE IF EXISTS bug23760_test_row_count| 5817DROP FUNCTION IF EXISTS bug23760_rc_test| 5818CREATE TABLE bug23760 ( 5819id INT NOT NULL AUTO_INCREMENT , 5820num INT NOT NULL , 5821PRIMARY KEY ( id ) 5822)| 5823CREATE TABLE bug23760_log ( 5824id INT NOT NULL AUTO_INCREMENT , 5825reason VARCHAR(50)NULL , 5826ammount INT NOT NULL , 5827PRIMARY KEY ( id ) 5828)| 5829CREATE PROCEDURE bug23760_update_log(r Varchar(50), a INT) 5830BEGIN 5831INSERT INTO bug23760_log (reason, ammount) VALUES(r, a); 5832END| 5833CREATE PROCEDURE bug23760_test_row_count() 5834BEGIN 5835UPDATE bug23760 SET num = num + 1; 5836CALL bug23760_update_log('Test is working', ROW_COUNT()); 5837UPDATE bug23760 SET num = num - 1; 5838END| 5839CREATE PROCEDURE bug23760_test_row_count2(level INT) 5840BEGIN 5841IF level THEN 5842UPDATE bug23760 SET num = num + 1; 5843CALL bug23760_update_log('Test2 is working', ROW_COUNT()); 5844CALL bug23760_test_row_count2(level - 1); 5845END IF; 5846END| 5847CREATE FUNCTION bug23760_rc_test(in_var INT) RETURNS INT RETURN in_var| 5848INSERT INTO bug23760 (num) VALUES (0), (1), (1), (2), (3), (5), (8)| 5849SELECT ROW_COUNT()| 5850ROW_COUNT() 58517 5852CALL bug23760_test_row_count()| 5853SELECT * FROM bug23760_log ORDER BY id| 5854id reason ammount 58551 Test is working 7 5856SET @save_max_sp_recursion= @@max_sp_recursion_depth| 5857SELECT @save_max_sp_recursion| 5858@save_max_sp_recursion 58590 5860SET max_sp_recursion_depth= 5| 5861SELECT @@max_sp_recursion_depth| 5862@@max_sp_recursion_depth 58635 5864CALL bug23760_test_row_count2(2)| 5865SELECT ROW_COUNT()| 5866ROW_COUNT() 58671 5868SELECT * FROM bug23760_log ORDER BY id| 5869id reason ammount 58701 Test is working 7 58712 Test2 is working 7 58723 Test2 is working 7 5873SELECT * FROM bug23760 ORDER by ID| 5874id num 58751 2 58762 3 58773 3 58784 4 58795 5 58806 7 58817 10 5882SET max_sp_recursion_depth= @save_max_sp_recursion| 5883SELECT bug23760_rc_test(123)| 5884bug23760_rc_test(123) 5885123 5886INSERT INTO bug23760 (num) VALUES (13), (21), (34), (55)| 5887SELECT bug23760_rc_test(ROW_COUNT())| 5888bug23760_rc_test(ROW_COUNT()) 58894 5890DROP TABLE bug23760, bug23760_log| 5891DROP PROCEDURE bug23760_update_log| 5892DROP PROCEDURE bug23760_test_row_count| 5893DROP PROCEDURE bug23760_test_row_count2| 5894DROP FUNCTION bug23760_rc_test| 5895DROP PROCEDURE IF EXISTS bug24117| 5896DROP TABLE IF EXISTS t3| 5897CREATE TABLE t3(c1 ENUM('abc'))| 5898INSERT INTO t3 VALUES('abc')| 5899CREATE PROCEDURE bug24117() 5900BEGIN 5901DECLARE t3c1 ENUM('abc'); 5902DECLARE mycursor CURSOR FOR SELECT c1 FROM t3; 5903OPEN mycursor; 5904FLUSH TABLES; 5905FETCH mycursor INTO t3c1; 5906CLOSE mycursor; 5907END| 5908CALL bug24117()| 5909DROP PROCEDURE bug24117| 5910DROP TABLE t3| 5911drop function if exists func_8407_a| 5912drop function if exists func_8407_b| 5913create function func_8407_a() returns int 5914begin 5915declare x int; 5916declare continue handler for sqlexception 5917begin 5918end; 5919select 1 from no_such_view limit 1 into x; 5920return x; 5921end| 5922create function func_8407_b() returns int 5923begin 5924declare x int default 0; 5925declare continue handler for sqlstate '42S02' 5926 begin 5927set x:= x+1000; 5928end; 5929case (select 1 from no_such_view limit 1) 5930when 1 then set x:= x+1; 5931when 2 then set x:= x+2; 5932else set x:= x+100; 5933end case; 5934set x:=x + 500; 5935return x; 5936end| 5937select func_8407_a()| 5938func_8407_a() 5939NULL 5940Warnings: 5941Error 1146 Table 'test.no_such_view' doesn't exist 5942select func_8407_b()| 5943func_8407_b() 59441500 5945Warnings: 5946Error 1146 Table 'test.no_such_view' doesn't exist 5947drop function func_8407_a| 5948drop function func_8407_b| 5949drop table if exists table_26503| 5950drop procedure if exists proc_26503_ok_1| 5951drop procedure if exists proc_26503_ok_2| 5952drop procedure if exists proc_26503_ok_3| 5953drop procedure if exists proc_26503_ok_4| 5954create table table_26503(a int unique)| 5955create procedure proc_26503_ok_1(v int) 5956begin 5957declare i int default 5; 5958declare continue handler for sqlexception 5959begin 5960select 'caught something'; 5961retry: 5962while i > 0 do 5963begin 5964set i = i - 1; 5965select 'looping', i; 5966iterate retry; 5967select 'dead code'; 5968end; 5969end while retry; 5970select 'leaving handler'; 5971end; 5972select 'do something'; 5973insert into table_26503 values (v); 5974select 'do something again'; 5975insert into table_26503 values (v); 5976end| 5977create procedure proc_26503_ok_2(v int) 5978begin 5979declare i int default 5; 5980declare continue handler for sqlexception 5981begin 5982select 'caught something'; 5983retry: 5984while i > 0 do 5985begin 5986set i = i - 1; 5987select 'looping', i; 5988leave retry; 5989select 'dead code'; 5990end; 5991end while; 5992select 'leaving handler'; 5993end; 5994select 'do something'; 5995insert into table_26503 values (v); 5996select 'do something again'; 5997insert into table_26503 values (v); 5998end| 5999create procedure proc_26503_ok_3(v int) 6000begin 6001declare i int default 5; 6002retry: 6003begin 6004declare continue handler for sqlexception 6005begin 6006select 'caught something'; 6007retry: 6008while i > 0 do 6009begin 6010set i = i - 1; 6011select 'looping', i; 6012iterate retry; 6013select 'dead code'; 6014end; 6015end while retry; 6016select 'leaving handler'; 6017end; 6018select 'do something'; 6019insert into table_26503 values (v); 6020select 'do something again'; 6021insert into table_26503 values (v); 6022end; 6023end| 6024create procedure proc_26503_ok_4(v int) 6025begin 6026declare i int default 5; 6027retry: 6028begin 6029declare continue handler for sqlexception 6030begin 6031select 'caught something'; 6032retry: 6033while i > 0 do 6034begin 6035set i = i - 1; 6036select 'looping', i; 6037leave retry; 6038select 'dead code'; 6039end; 6040end while; 6041select 'leaving handler'; 6042end; 6043select 'do something'; 6044insert into table_26503 values (v); 6045select 'do something again'; 6046insert into table_26503 values (v); 6047end; 6048end| 6049call proc_26503_ok_1(1)| 6050do something 6051do something 6052do something again 6053do something again 6054caught something 6055caught something 6056looping i 6057looping 4 6058looping i 6059looping 3 6060looping i 6061looping 2 6062looping i 6063looping 1 6064looping i 6065looping 0 6066leaving handler 6067leaving handler 6068Warnings: 6069Error 1062 Duplicate entry '1' for key 'a' 6070call proc_26503_ok_2(2)| 6071do something 6072do something 6073do something again 6074do something again 6075caught something 6076caught something 6077looping i 6078looping 4 6079leaving handler 6080leaving handler 6081Warnings: 6082Error 1062 Duplicate entry '2' for key 'a' 6083call proc_26503_ok_3(3)| 6084do something 6085do something 6086do something again 6087do something again 6088caught something 6089caught something 6090looping i 6091looping 4 6092looping i 6093looping 3 6094looping i 6095looping 2 6096looping i 6097looping 1 6098looping i 6099looping 0 6100leaving handler 6101leaving handler 6102Warnings: 6103Error 1062 Duplicate entry '3' for key 'a' 6104call proc_26503_ok_4(4)| 6105do something 6106do something 6107do something again 6108do something again 6109caught something 6110caught something 6111looping i 6112looping 4 6113leaving handler 6114leaving handler 6115Warnings: 6116Error 1062 Duplicate entry '4' for key 'a' 6117drop table table_26503| 6118drop procedure proc_26503_ok_1| 6119drop procedure proc_26503_ok_2| 6120drop procedure proc_26503_ok_3| 6121drop procedure proc_26503_ok_4| 6122DROP FUNCTION IF EXISTS bug25373| 6123CREATE FUNCTION bug25373(p1 INTEGER) RETURNS INTEGER 6124LANGUAGE SQL DETERMINISTIC 6125RETURN p1;| 6126CREATE TABLE t3 (f1 INT, f2 FLOAT)| 6127INSERT INTO t3 VALUES (1, 3.4), (1, 2), (1, 0.9), (2, 8), (2, 7)| 6128SELECT SUM(f2), bug25373(f1) FROM t3 GROUP BY bug25373(f1) WITH ROLLUP| 6129SUM(f2) bug25373(f1) 61306.300000071525574 1 613115 2 613221.300000071525574 NULL 6133DROP FUNCTION bug25373| 6134DROP TABLE t3| 6135DROP DATABASE IF EXISTS mysqltest1| 6136DROP DATABASE IF EXISTS mysqltest2| 6137CREATE DATABASE mysqltest1| 6138CREATE DATABASE mysqltest2| 6139CREATE PROCEDURE mysqltest1.p1() 6140DROP DATABASE mysqltest2| 6141use mysqltest2| 6142CALL mysqltest1.p1()| 6143Warnings: 6144Note 1049 Unknown database 'mysqltest2' 6145SELECT DATABASE()| 6146DATABASE() 6147NULL 6148DROP DATABASE mysqltest1| 6149use test| 6150drop function if exists bug20777| 6151drop table if exists examplebug20777| 6152create function bug20777(f1 bigint unsigned) returns bigint unsigned 6153begin 6154set f1 = (f1 - 10); set f1 = (f1 + 10); 6155return f1; 6156end| 6157select bug20777(9223372036854775803) as '9223372036854775803 2**63-5'; 61589223372036854775803 2**63-5 61599223372036854775803 6160select bug20777(9223372036854775804) as '9223372036854775804 2**63-4'; 61619223372036854775804 2**63-4 61629223372036854775804 6163select bug20777(9223372036854775805) as '9223372036854775805 2**63-3'; 61649223372036854775805 2**63-3 61659223372036854775805 6166select bug20777(9223372036854775806) as '9223372036854775806 2**63-2'; 61679223372036854775806 2**63-2 61689223372036854775806 6169select bug20777(9223372036854775807) as '9223372036854775807 2**63-1'; 61709223372036854775807 2**63-1 61719223372036854775807 6172select bug20777(9223372036854775808) as '9223372036854775808 2**63+0'; 61739223372036854775808 2**63+0 61749223372036854775808 6175select bug20777(9223372036854775809) as '9223372036854775809 2**63+1'; 61769223372036854775809 2**63+1 61779223372036854775809 6178select bug20777(9223372036854775810) as '9223372036854775810 2**63+2'; 61799223372036854775810 2**63+2 61809223372036854775810 6181select bug20777(-9223372036854775808) as 'lower bounds signed bigint'; 6182ERROR 22003: BIGINT UNSIGNED value is out of range in '(f1@0 - 10)' 6183select bug20777(9223372036854775807) as 'upper bounds signed bigint'; 6184upper bounds signed bigint 61859223372036854775807 6186select bug20777(0) as 'lower bounds unsigned bigint'; 6187ERROR 22003: BIGINT UNSIGNED value is out of range in '(f1@0 - 10)' 6188select bug20777(18446744073709551615) as 'upper bounds unsigned bigint'; 6189upper bounds unsigned bigint 619018446744073709551615 6191select bug20777(18446744073709551616) as 'upper bounds unsigned bigint + 1'; 6192upper bounds unsigned bigint + 1 619318446744073709551615 6194Warnings: 6195Warning 1264 Out of range value for column 'f1' at row 1 6196select bug20777(-1) as 'lower bounds unsigned bigint - 1'; 6197ERROR 22003: BIGINT UNSIGNED value is out of range in '(f1@0 - 10)' 6198create table examplebug20777 as select 61990 as 'i', 6200bug20777(9223372036854775806) as '2**63-2', 6201bug20777(9223372036854775807) as '2**63-1', 6202bug20777(9223372036854775808) as '2**63', 6203bug20777(9223372036854775809) as '2**63+1', 6204bug20777(18446744073709551614) as '2**64-2', 6205bug20777(18446744073709551615) as '2**64-1', 6206bug20777(18446744073709551616) as '2**64'; 6207Warnings: 6208Warning 1264 Out of range value for column 'f1' at row 1 6209insert into examplebug20777 values (1, 9223372036854775806, 9223372036854775807, 223372036854775808, 9223372036854775809, 18446744073709551614, 18446744073709551615, 8446744073709551616); 6210show create table examplebug20777; 6211Table Create Table 6212examplebug20777 CREATE TABLE `examplebug20777` ( 6213 `i` int(1) NOT NULL DEFAULT '0', 6214 `2**63-2` bigint(20) unsigned DEFAULT NULL, 6215 `2**63-1` bigint(20) unsigned DEFAULT NULL, 6216 `2**63` bigint(20) unsigned DEFAULT NULL, 6217 `2**63+1` bigint(20) unsigned DEFAULT NULL, 6218 `2**64-2` bigint(20) unsigned DEFAULT NULL, 6219 `2**64-1` bigint(20) unsigned DEFAULT NULL, 6220 `2**64` bigint(20) unsigned DEFAULT NULL 6221) ENGINE=MyISAM DEFAULT CHARSET=latin1 6222select * from examplebug20777 order by i; 6223i 2**63-2 2**63-1 2**63 2**63+1 2**64-2 2**64-1 2**64 62240 9223372036854775806 9223372036854775807 9223372036854775808 9223372036854775809 18446744073709551614 18446744073709551615 18446744073709551615 62251 9223372036854775806 9223372036854775807 223372036854775808 9223372036854775809 18446744073709551614 18446744073709551615 8446744073709551616 6226drop table examplebug20777; 6227select bug20777(18446744073709551613)+1; 6228bug20777(18446744073709551613)+1 622918446744073709551614 6230drop function bug20777; 6231DROP FUNCTION IF EXISTS bug5274_f1| 6232DROP FUNCTION IF EXISTS bug5274_f2| 6233CREATE FUNCTION bug5274_f1(p1 CHAR) RETURNS CHAR 6234RETURN CONCAT(p1, p1)| 6235CREATE FUNCTION bug5274_f2() RETURNS CHAR 6236BEGIN 6237DECLARE v1 INT DEFAULT 0; 6238DECLARE v2 CHAR DEFAULT 'x'; 6239WHILE v1 < 30 DO 6240SET v1 = v1 + 1; 6241SET v2 = bug5274_f1(v2); 6242END WHILE; 6243RETURN v2; 6244END| 6245SELECT bug5274_f2()| 6246bug5274_f2() 6247x 6248Warnings: 6249Warning 1265 Data truncated for column 'bug5274_f1' at row 1 6250DROP FUNCTION bug5274_f1| 6251DROP FUNCTION bug5274_f2| 6252drop procedure if exists proc_21513| 6253create procedure proc_21513()`my_label`:BEGIN END| 6254show create procedure proc_21513| 6255Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 6256proc_21513 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_21513`() 6257`my_label`:BEGIN END utf8 utf8_general_ci latin1_swedish_ci 6258drop procedure proc_21513| 6259End of 5.0 tests. 6260drop table t1,t2; 6261CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM; 6262CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb; 6263set @a=0; 6264CREATE function bug27354() RETURNS int not deterministic 6265begin 6266insert into t1 values (null); 6267set @a=@a+1; 6268return @a; 6269end| 6270update t2 set b=1 where a=bug27354(); 6271select count(t_1.a),count(t_2.a) from t1 as t_1, t2 as t_2 /* must be 0,0 */; 6272count(t_1.a) count(t_2.a) 62730 0 6274insert into t2 values (1,1),(2,2),(3,3); 6275update t2 set b=-b where a=bug27354(); 6276select * from t2 /* must return 1,-1 ... */; 6277a b 62781 -1 62792 -2 62803 -3 6281select count(*) from t1 /* must be 3 */; 6282count(*) 62833 6284drop table t1,t2; 6285drop function bug27354; 6286CREATE TABLE t1 (a INT); 6287INSERT INTO t1 VALUES (1),(2); 6288CREATE FUNCTION metered(a INT) RETURNS INT RETURN 12; 6289CREATE VIEW v1 AS SELECT test.metered(a) as metered FROM t1; 6290SHOW CREATE VIEW v1; 6291View Create View character_set_client collation_connection 6292v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`metered`(`t1`.`a`) AS `metered` from `t1` utf8 utf8_general_ci 6293DROP VIEW v1; 6294DROP FUNCTION metered; 6295DROP TABLE t1; 6296SET @p1_p2_cnt= 2; 6297CREATE TABLE t1 (c1 INT); 6298CREATE VIEW v1 AS SELECT * FROM t1; 6299PREPARE s1 FROM 'SELECT c1 FROM v1'; 6300EXECUTE s1; 6301c1 6302EXECUTE s1; 6303c1 6304CREATE PROCEDURE p1(IN loops BIGINT(19) UNSIGNED) 6305BEGIN 6306WHILE loops > 0 DO 6307SELECT c1 FROM v1; 6308SET loops = loops - 1; 6309END WHILE; 6310END| 6311CREATE PROCEDURE p2(IN loops BIGINT(19) UNSIGNED) 6312BEGIN 6313WHILE loops > 0 DO 6314SELECT c1 FROM v1; 6315CALL p1(@p1_p2_cnt); 6316SET loops = loops - 1; 6317END WHILE; 6318END| 6319CREATE FUNCTION f1(loops INT UNSIGNED) 6320RETURNS INT 6321BEGIN 6322DECLARE tmp INT; 6323WHILE loops > 0 DO 6324SELECT c1 INTO tmp FROM v1; 6325SET loops = loops - 1; 6326END WHILE; 6327RETURN loops; 6328END| 6329CALL p1(2); 6330c1 6331c1 6332CALL p2(2); 6333c1 6334c1 6335c1 6336c1 6337c1 6338c1 6339SELECT f1(2); 6340f1(2) 63410 6342Warnings: 6343Warning 1329 No data - zero rows fetched, selected, or processed 6344PREPARE s1 FROM 'SELECT f1(2)'; 6345EXECUTE s1; 6346f1(2) 63470 6348Warnings: 6349Warning 1329 No data - zero rows fetched, selected, or processed 6350EXECUTE s1; 6351f1(2) 63520 6353Warnings: 6354Warning 1329 No data - zero rows fetched, selected, or processed 6355DROP PROCEDURE p1; 6356DROP PROCEDURE p2; 6357DROP FUNCTION f1; 6358DROP VIEW v1; 6359DROP TABLE t1; 6360drop database if exists mysqltest_db1; 6361create database mysqltest_db1; 6362create procedure mysqltest_db1.sp_bug28551() begin end; 6363call mysqltest_db1.sp_bug28551(); 6364show warnings; 6365Level Code Message 6366Note 1008 Can't drop database 'mysqltest_db1'; database doesn't exist 6367drop database mysqltest_db1; 6368drop database if exists mysqltest_db1; 6369drop table if exists test.t1; 6370create database mysqltest_db1; 6371use mysqltest_db1; 6372drop database mysqltest_db1; 6373create table test.t1 (id int); 6374insert into test.t1 (id) values (1); 6375create procedure test.sp_bug29050() begin select * from t1; end// 6376show warnings; 6377Level Code Message 6378call test.sp_bug29050(); 6379id 63801 6381show warnings; 6382Level Code Message 6383use test; 6384drop procedure sp_bug29050; 6385drop table t1; 6386SET NAMES latin1; 6387CREATE PROCEDURE p1() 6388BEGIN 6389DECLARE ��� INT; 6390SELECT ���; 6391END| 6392CALL p1(); 6393��� 6394NULL 6395SET NAMES default; 6396DROP PROCEDURE p1; 6397drop procedure if exists proc_25411_a; 6398drop procedure if exists proc_25411_b; 6399drop procedure if exists proc_25411_c; 6400create procedure proc_25411_a() 6401begin 6402/* real comment */ 6403select 1; 6404/*! select 2; */ 6405select 3; 6406/*!00000 select 4; */ 6407/*!99999 select 5; */ 6408end 6409$$ 6410create procedure proc_25411_b( 6411/* real comment */ 6412/*! p1 int, */ 6413/*!00000 p2 int */ 6414/*!99999 ,p3 int */ 6415) 6416begin 6417select p1, p2; 6418end 6419$$ 6420create procedure proc_25411_c() 6421begin 6422select 1/*!,2*//*!00000,3*//*!99999,4*/; 6423select 1/*! ,2*//*!00000 ,3*//*!99999 ,4*/; 6424select 1/*!,2 *//*!00000,3 *//*!99999,4 */; 6425select 1/*! ,2 *//*!00000 ,3 *//*!99999 ,4 */; 6426select 1 /*!,2*/ /*!00000,3*/ /*!99999,4*/ ; 6427end 6428$$ 6429show create procedure proc_25411_a; 6430Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 6431proc_25411_a CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_a`() 6432begin 6433/* real comment */ 6434select 1; 6435 select 2; 6436select 3; 6437 select 4; 6438 6439end latin1 latin1_swedish_ci latin1_swedish_ci 6440call proc_25411_a(); 64411 64421 64432 64442 64453 64463 64474 64484 6449show create procedure proc_25411_b; 6450Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 6451proc_25411_b CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_b`( 6452/* real comment */ 6453 p1 int, 6454 p2 int 6455 6456) 6457begin 6458select p1, p2; 6459end latin1 latin1_swedish_ci latin1_swedish_ci 6460select name, param_list, body from mysql.proc where name like "%25411%"; 6461name param_list body 6462proc_25411_a begin 6463/* real comment */ 6464select 1; 6465 select 2; 6466select 3; 6467 select 4; 6468 6469end 6470proc_25411_b 6471/* real comment */ 6472 p1 int, 6473 p2 int 6474 6475 begin 6476select p1, p2; 6477end 6478proc_25411_c begin 6479select 1,2,3; 6480select 1 ,2 ,3; 6481select 1,2 ,3 ; 6482select 1 ,2 ,3 ; 6483select 1 ,2 ,3 ; 6484end 6485call proc_25411_b(10, 20); 6486p1 p2 648710 20 6488show create procedure proc_25411_c; 6489Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 6490proc_25411_c CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_c`() 6491begin 6492select 1,2,3; 6493select 1 ,2 ,3; 6494select 1,2 ,3 ; 6495select 1 ,2 ,3 ; 6496select 1 ,2 ,3 ; 6497end latin1 latin1_swedish_ci latin1_swedish_ci 6498call proc_25411_c(); 64991 2 3 65001 2 3 65011 2 3 65021 2 3 65031 2 3 65041 2 3 65051 2 3 65061 2 3 65071 2 3 65081 2 3 6509drop procedure proc_25411_a; 6510drop procedure proc_25411_b; 6511drop procedure proc_25411_c; 6512drop procedure if exists proc_26302; 6513create procedure proc_26302() 6514select 1 /* testing */; 6515show create procedure proc_26302; 6516Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 6517proc_26302 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_26302`() 6518select 1 /* testing */ latin1 latin1_swedish_ci latin1_swedish_ci 6519select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES 6520where ROUTINE_NAME = "proc_26302"; 6521ROUTINE_NAME ROUTINE_DEFINITION 6522proc_26302 select 1 /* testing */ 6523drop procedure proc_26302; 6524CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2; 6525CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3; 6526CREATE TABLE t1 (c1 INT, INDEX(c1)); 6527INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 6528CREATE VIEW v1 AS SELECT c1 FROM t1; 6529EXPLAIN SELECT * FROM t1 WHERE c1=1; 6530id select_type table type possible_keys key key_len ref rows Extra 65311 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index 6532EXPLAIN SELECT * FROM t1 WHERE c1=f1(); 6533id select_type table type possible_keys key key_len ref rows Extra 65341 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index 6535EXPLAIN SELECT * FROM v1 WHERE c1=1; 6536id select_type table type possible_keys key key_len ref rows Extra 65371 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index 6538EXPLAIN SELECT * FROM v1 WHERE c1=f1(); 6539id select_type table type possible_keys key key_len ref rows Extra 65401 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index 6541EXPLAIN SELECT * FROM t1 WHERE c1=f2(10); 6542id select_type table type possible_keys key key_len ref rows Extra 65431 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index 6544EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1); 6545id select_type table type possible_keys key key_len ref rows Extra 65461 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index 6547EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand()); 6548id select_type table type possible_keys key key_len ref rows Extra 65491 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index 6550DROP VIEW v1; 6551DROP FUNCTION f1; 6552DROP FUNCTION f2; 6553DROP TABLE t1; 6554create function f1() 6555returns int(11) 6556not deterministic 6557contains sql 6558sql security definer 6559comment '' 6560begin 6561declare x int(11); 6562set x=-1; 6563return x; 6564end| 6565create view v1 as select 1 as one, f1() as days; 6566show create view test.v1; 6567View Create View character_set_client collation_connection 6568v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select 1 AS `one`,`f1`() AS `days` latin1 latin1_swedish_ci 6569select column_name from information_schema.columns 6570where table_name='v1' and table_schema='test'; 6571column_name 6572one 6573days 6574drop view v1; 6575drop function f1; 6576 6577# Bug#13675. 6578 6579DROP PROCEDURE IF EXISTS p1; 6580DROP PROCEDURE IF EXISTS p2; 6581DROP TABLE IF EXISTS t1; 6582 6583CREATE PROCEDURE p1(v DATETIME) CREATE TABLE t1 SELECT v; 6584CREATE PROCEDURE p2(v INT) CREATE TABLE t1 SELECT v; 6585 6586CALL p1(NOW()); 6587SHOW CREATE TABLE t1; 6588Table Create Table 6589t1 CREATE TABLE `t1` ( 6590 `v` datetime DEFAULT NULL 6591) ENGINE=MyISAM DEFAULT CHARSET=latin1 6592 6593DROP TABLE t1; 6594 6595CALL p1('text'); 6596Warnings: 6597Warning 1264 Out of range value for column 'v' at row 1 6598SHOW CREATE TABLE t1; 6599Table Create Table 6600t1 CREATE TABLE `t1` ( 6601 `v` datetime DEFAULT NULL 6602) ENGINE=MyISAM DEFAULT CHARSET=latin1 6603 6604DROP TABLE t1; 6605 6606CALL p2(10); 6607SHOW CREATE TABLE t1; 6608Table Create Table 6609t1 CREATE TABLE `t1` ( 6610 `v` bigint(11) DEFAULT NULL 6611) ENGINE=MyISAM DEFAULT CHARSET=latin1 6612 6613DROP TABLE t1; 6614 6615CALL p2('text'); 6616Warnings: 6617Warning 1366 Incorrect integer value: 'text' for column 'v' at row 1 6618SHOW CREATE TABLE t1; 6619Table Create Table 6620t1 CREATE TABLE `t1` ( 6621 `v` bigint(11) DEFAULT NULL 6622) ENGINE=MyISAM DEFAULT CHARSET=latin1 6623 6624DROP TABLE t1; 6625 6626DROP PROCEDURE p1; 6627DROP PROCEDURE p2; 6628 6629# 6630# Bug#31035. 6631# 6632 6633# 6634# - Prepare. 6635# 6636 6637DROP TABLE IF EXISTS t1; 6638DROP FUNCTION IF EXISTS f1; 6639DROP FUNCTION IF EXISTS f2; 6640DROP FUNCTION IF EXISTS f3; 6641DROP FUNCTION IF EXISTS f4; 6642 6643# 6644# - Create required objects. 6645# 6646 6647CREATE TABLE t1(c1 INT); 6648 6649INSERT INTO t1 VALUES (1), (2), (3); 6650 6651CREATE FUNCTION f1() 6652RETURNS INT 6653NOT DETERMINISTIC 6654RETURN 1; 6655 6656CREATE FUNCTION f2(p INT) 6657RETURNS INT 6658NOT DETERMINISTIC 6659RETURN 1; 6660 6661CREATE FUNCTION f3() 6662RETURNS INT 6663DETERMINISTIC 6664RETURN 1; 6665 6666CREATE FUNCTION f4(p INT) 6667RETURNS INT 6668DETERMINISTIC 6669RETURN 1; 6670 6671# 6672# - Check. 6673# 6674 6675SELECT f1() AS a FROM t1 GROUP BY a; 6676a 66771 6678 6679SELECT f2(@a) AS a FROM t1 GROUP BY a; 6680a 66811 6682 6683SELECT f3() AS a FROM t1 GROUP BY a; 6684a 66851 6686 6687SELECT f4(0) AS a FROM t1 GROUP BY a; 6688a 66891 6690 6691SELECT f4(@a) AS a FROM t1 GROUP BY a; 6692a 66931 6694 6695# 6696# - Cleanup. 6697# 6698 6699DROP TABLE t1; 6700DROP FUNCTION f1; 6701DROP FUNCTION f2; 6702DROP FUNCTION f3; 6703DROP FUNCTION f4; 6704 6705# 6706# Bug#31191. 6707# 6708 6709# 6710# - Prepare. 6711# 6712 6713DROP TABLE IF EXISTS t1; 6714DROP TABLE IF EXISTS t2; 6715DROP FUNCTION IF EXISTS f1; 6716 6717# 6718# - Create required objects. 6719# 6720 6721CREATE TABLE t1 ( 6722id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 6723barcode INT(8) UNSIGNED ZEROFILL nOT NULL, 6724PRIMARY KEY (id), 6725UNIQUE KEY barcode (barcode) 6726); 6727 6728INSERT INTO t1 (id, barcode) VALUES (1, 12345678); 6729INSERT INTO t1 (id, barcode) VALUES (2, 12345679); 6730 6731CREATE TABLE test.t2 ( 6732id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 6733barcode BIGINT(11) UNSIGNED ZEROFILL NOT NULL, 6734PRIMARY KEY (id) 6735); 6736 6737INSERT INTO test.t2 (id, barcode) VALUES (1, 12345106708); 6738INSERT INTO test.t2 (id, barcode) VALUES (2, 12345106709); 6739 6740CREATE FUNCTION f1(p INT(8)) 6741RETURNS BIGINT(11) UNSIGNED 6742READS SQL DATA 6743RETURN FLOOR(p/1000)*1000000 + 100000 + FLOOR((p MOD 1000)/10)*100 + (p MOD 10); 6744 6745# 6746# - Check. 6747# 6748 6749SELECT DISTINCT t1.barcode, f1(t1.barcode) 6750FROM t1 6751INNER JOIN t2 6752ON f1(t1.barcode) = t2.barcode 6753WHERE t1.barcode=12345678; 6754barcode f1(t1.barcode) 675512345678 12345106708 6756 6757# 6758# - Cleanup. 6759# 6760 6761DROP TABLE t1; 6762DROP TABLE t2; 6763DROP FUNCTION f1; 6764 6765# 6766# Bug#31226. 6767# 6768 6769# 6770# - Prepare. 6771# 6772 6773DROP TABLE IF EXISTS t1; 6774DROP FUNCTION IF EXISTS f1; 6775 6776# 6777# - Create required objects. 6778# 6779 6780CREATE TABLE t1(id INT); 6781 6782INSERT INTO t1 VALUES (1), (2), (3); 6783 6784CREATE FUNCTION f1() 6785RETURNS DATETIME 6786NOT DETERMINISTIC NO SQL 6787RETURN NOW(); 6788 6789# 6790# - Check. 6791# 6792 6793SELECT f1() FROM t1 GROUP BY 1; 6794f1() 6795<timestamp> 6796 6797# 6798# - Cleanup. 6799# 6800 6801DROP TABLE t1; 6802DROP FUNCTION f1; 6803 6804DROP PROCEDURE IF EXISTS db28318_a.t1; 6805DROP PROCEDURE IF EXISTS db28318_b.t2; 6806DROP DATABASE IF EXISTS db28318_a; 6807DROP DATABASE IF EXISTS db28318_b; 6808CREATE DATABASE db28318_a; 6809CREATE DATABASE db28318_b; 6810CREATE PROCEDURE db28318_a.t1() SELECT "db28318_a.t1"; 6811CREATE PROCEDURE db28318_b.t2() CALL t1(); 6812use db28318_a; 6813CALL db28318_b.t2(); 6814ERROR 42000: PROCEDURE db28318_b.t1 does not exist 6815DROP PROCEDURE db28318_a.t1; 6816DROP PROCEDURE db28318_b.t2; 6817DROP DATABASE db28318_a; 6818DROP DATABASE db28318_b; 6819use test; 6820DROP TABLE IF EXISTS t1; 6821DROP PROCEDURE IF EXISTS bug29770; 6822CREATE TABLE t1(a int); 6823CREATE PROCEDURE bug29770() 6824BEGIN 6825DECLARE CONTINUE HANDLER FOR SQLSTATE '42S22' SET @state:= 'run'; 6826DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @exception:= 'run'; 6827SELECT x FROM t1; 6828END| 6829CALL bug29770(); 6830Warnings: 6831Error 1054 Unknown column 'x' in 'field list' 6832SELECT @state, @exception; 6833@state @exception 6834run NULL 6835DROP TABLE t1; 6836DROP PROCEDURE bug29770; 6837use test; 6838drop table if exists t_33618; 6839drop procedure if exists proc_33618; 6840create table t_33618 (`a` int, unique(`a`), `b` varchar(30)) engine=myisam; 6841insert into t_33618 (`a`,`b`) values (1,'1'),(2,'2'); 6842create procedure proc_33618(num int) 6843begin 6844declare count1 int default '0'; 6845declare vb varchar(30); 6846declare last_row int; 6847while(num>=1) do 6848set num=num-1; 6849begin 6850declare cur1 cursor for select `a` from t_33618; 6851declare continue handler for not found set last_row = 1; 6852set last_row:=0; 6853open cur1; 6854rep1: 6855repeat 6856begin 6857declare exit handler for 1062 begin end; 6858fetch cur1 into vb; 6859if (last_row = 1) then 6860leave rep1; 6861end if; 6862end; 6863until last_row=1 6864end repeat; 6865close cur1; 6866end; 6867end while; 6868end// 6869call proc_33618(20); 6870Warnings: 6871Error 1329 No data - zero rows fetched, selected, or processed 6872drop table t_33618; 6873drop procedure proc_33618; 6874# 6875# Bug#30787: Stored function ignores user defined alias. 6876# 6877use test; 6878drop function if exists func30787; 6879create table t1(f1 int); 6880insert into t1 values(1),(2); 6881create function func30787(p1 int) returns int 6882begin 6883return p1; 6884end | 6885select (select func30787(f1)) as ttt from t1; 6886ttt 68871 68882 6889drop function func30787; 6890drop table t1; 6891CREATE TABLE t1 (id INT); 6892INSERT INTO t1 VALUES (1),(2),(3),(4); 6893CREATE PROCEDURE test_sp() 6894SELECT t1.* FROM t1 RIGHT JOIN t1 t2 ON t1.id=t2.id; 6895CALL test_sp(); 6896id 68971 68982 68993 69004 6901CALL test_sp(); 6902id 69031 69042 69053 69064 6907DROP PROCEDURE test_sp; 6908DROP TABLE t1; 6909create table t1(c1 INT); 6910create function f1(p1 int) returns varchar(32) 6911return 'aaa'; 6912create view v1 as select f1(c1) as parent_control_name from t1; 6913create procedure p1() 6914begin 6915select parent_control_name as c1 from v1; 6916end // 6917call p1(); 6918c1 6919call p1(); 6920c1 6921drop procedure p1; 6922drop function f1; 6923drop view v1; 6924drop table t1; 6925drop procedure if exists `p2` $ 6926create procedure `p2`(in `a` text charset utf8) 6927begin 6928declare `pos` int default 1; 6929declare `str` text charset utf8; 6930set `str` := `a`; 6931select substr(`str`, `pos`+ 1 ) into `str`; 6932end $ 6933call `p2`('s s s s s s'); 6934drop procedure `p2`; 6935drop table if exists t1; 6936drop procedure if exists p1; 6937create procedure p1() begin select * from t1; end$ 6938call p1$ 6939ERROR 42S02: Table 'test.t1' doesn't exist 6940create table t1 (a integer)$ 6941call p1$ 6942a 6943alter table t1 add b integer; 6944call p1$ 6945a 6946drop table t1; 6947drop procedure p1; 6948# ------------------------------------------------------------------ 6949# -- End of 5.0 tests 6950# ------------------------------------------------------------------ 6951 6952# 6953# Bug#20550. 6954# 6955 6956# 6957# - Prepare. 6958# 6959 6960DROP VIEW IF EXISTS v1; 6961DROP VIEW IF EXISTS v2; 6962DROP FUNCTION IF EXISTS f1; 6963DROP FUNCTION IF EXISTS f2; 6964 6965# 6966# - Create required objects. 6967# 6968 6969CREATE FUNCTION f1() RETURNS VARCHAR(65525) RETURN 'Hello'; 6970 6971CREATE FUNCTION f2() RETURNS TINYINT RETURN 1; 6972 6973CREATE VIEW v1 AS SELECT f1(); 6974 6975CREATE VIEW v2 AS SELECT f2(); 6976 6977# 6978# - Check. 6979# 6980 6981SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v1'; 6982DATA_TYPE 6983varchar 6984 6985SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v2'; 6986DATA_TYPE 6987tinyint 6988 6989# 6990# - Cleanup. 6991# 6992 6993DROP FUNCTION f1; 6994DROP FUNCTION f2; 6995DROP VIEW v1; 6996DROP VIEW v2; 6997 6998# 6999# - Bug#24923: prepare. 7000# 7001 7002DROP FUNCTION IF EXISTS f1; 7003 7004# 7005# - Bug#24923: create required objects. 7006# 7007 7008CREATE FUNCTION f1(p INT) 7009RETURNS ENUM ('Very_long_enum_element_identifier', 7010'Another_very_long_enum_element_identifier') 7011BEGIN 7012CASE p 7013WHEN 1 THEN 7014RETURN 'Very_long_enum_element_identifier'; 7015ELSE 7016RETURN 'Another_very_long_enum_element_identifier'; 7017END CASE; 7018END| 7019 7020# 7021# - Bug#24923: check. 7022# 7023 7024SELECT f1(1); 7025f1(1) 7026Very_long_enum_element_identifier 7027 7028SELECT f1(2); 7029f1(2) 7030Another_very_long_enum_element_identifier 7031 7032SHOW CREATE FUNCTION f1; 7033Function sql_mode Create Function character_set_client collation_connection Database Collation 7034f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(p INT) RETURNS enum('Very_long_enum_element_identifier','Another_very_long_enum_element_identifier') CHARSET latin1 7035BEGIN 7036CASE p 7037WHEN 1 THEN 7038RETURN 'Very_long_enum_element_identifier'; 7039ELSE 7040RETURN 'Another_very_long_enum_element_identifier'; 7041END CASE; 7042END latin1 latin1_swedish_ci latin1_swedish_ci 7043# 7044# - Bug#24923: cleanup. 7045# 7046 7047DROP FUNCTION f1; 7048 7049drop procedure if exists p; 7050set @old_mode= @@sql_mode; 7051set @@sql_mode= cast(pow(2,32)-1 as unsigned integer); 7052select @@sql_mode into @full_mode; 7053create procedure p() begin end; 7054call p(); 7055set @@sql_mode= @old_mode; 7056select replace(@full_mode, ',,,', ',NOT_USED,') into @full_mode; 7057select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode; 7058select name from mysql.proc where name = 'p' and sql_mode = @full_mode; 7059name 7060p 7061drop procedure p; 7062CREATE DEFINER = 'root'@'localhost' PROCEDURE p1() 7063NOT DETERMINISTIC 7064CONTAINS SQL 7065SQL SECURITY DEFINER 7066COMMENT '' 7067BEGIN 7068SHOW TABLE STATUS like 't1'; 7069END;// 7070CREATE TABLE t1 (f1 INT); 7071CALL p1(); 7072CALL p1(); 7073CALL p1(); 7074CALL p1(); 7075DROP PROCEDURE p1; 7076DROP TABLE t1; 7077CREATE TABLE t1 ( f1 integer, primary key (f1)); 7078CREATE TABLE t2 LIKE t1; 7079CREATE TEMPORARY TABLE t3 LIKE t1; 7080CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ; 7081END| 7082CALL p1; 7083ERROR HY000: Can't reopen table: 'A' 7084CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 ); 7085DROP TABLE t3; 7086CALL p1; 7087f1 7088CALL p1; 7089f1 7090DROP PROCEDURE p1; 7091DROP TABLE t1, t2; 7092DROP VIEW t3; 7093# 7094# Bug #46629: Item_in_subselect::val_int(): Assertion `0' 7095# on subquery inside a SP 7096# 7097CREATE TABLE t1(a INT); 7098CREATE TABLE t2(a INT, b INT PRIMARY KEY); 7099CREATE PROCEDURE p1 () 7100BEGIN 7101SELECT a FROM t1 A WHERE A.b IN (SELECT b FROM t2 AS B); 7102END| 7103CALL p1; 7104ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' 7105CALL p1; 7106ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' 7107DROP PROCEDURE p1; 7108DROP TABLE t1, t2; 7109# 7110# Bug#47627: SET @@{global.session}.local_variable in stored routine causes crash 7111# Bug#48626: Crash or lost connection using SET for declared variables with @@ 7112# 7113DROP PROCEDURE IF EXISTS p1; 7114DROP PROCEDURE IF EXISTS p2; 7115DROP PROCEDURE IF EXISTS p3; 7116CREATE PROCEDURE p1() 7117BEGIN 7118DECLARE v INT DEFAULT 0; 7119SET @@SESSION.v= 10; 7120END// 7121ERROR HY000: Unknown system variable 'v' 7122CREATE PROCEDURE p2() 7123BEGIN 7124DECLARE v INT DEFAULT 0; 7125SET v= 10; 7126END// 7127call p2()// 7128CREATE PROCEDURE p3() 7129BEGIN 7130DECLARE v INT DEFAULT 0; 7131SELECT @@SESSION.v; 7132END// 7133ERROR HY000: Unknown system variable 'v' 7134CREATE PROCEDURE p4() 7135BEGIN 7136DECLARE v INT DEFAULT 0; 7137SET @@GLOBAL.v= 10; 7138END// 7139ERROR HY000: Unknown system variable 'v' 7140CREATE PROCEDURE p5() 7141BEGIN 7142DECLARE init_connect INT DEFAULT 0; 7143SET init_connect= 10; 7144SET @@GLOBAL.init_connect= 'SELECT 1'; 7145SET @@SESSION.IDENTITY= 1; 7146SELECT @@SESSION.IDENTITY; 7147SELECT @@GLOBAL.init_connect; 7148SELECT init_connect; 7149END// 7150CREATE PROCEDURE p6() 7151BEGIN 7152DECLARE v INT DEFAULT 0; 7153SET @@v= 0; 7154END// 7155ERROR HY000: Unknown system variable 'v' 7156SET @old_init_connect= @@GLOBAL.init_connect; 7157CALL p5(); 7158@@SESSION.IDENTITY 71591 7160@@GLOBAL.init_connect 7161SELECT 1 7162init_connect 716310 7164SET @@GLOBAL.init_connect= @old_init_connect; 7165DROP PROCEDURE p2; 7166DROP PROCEDURE p5; 7167# 7168# Bug#11840395 (formerly known as bug#60347): 7169# The string "versiondata" seems 7170# to be 'leaking' into the schema name space 7171# 7172DROP DATABASE IF EXISTS mixedCaseDbName; 7173CREATE DATABASE mixedCaseDbName; 7174CREATE PROCEDURE mixedCaseDbName.tryMyProc() begin end| 7175CREATE FUNCTION mixedCaseDbName.tryMyFunc() returns text begin return 'IT WORKS'; end 7176| 7177call mixedCaseDbName.tryMyProc(); 7178select mixedCaseDbName.tryMyFunc(); 7179mixedCaseDbName.tryMyFunc() 7180IT WORKS 7181DROP DATABASE mixedCaseDbName; 7182# 7183# Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C 7184# 7185CREATE TABLE t1 (a INT, b INT, KEY(b)); 7186CREATE TABLE t2 (c INT, d INT, KEY(c)); 7187INSERT INTO t1 VALUES (1,1),(1,1),(1,2); 7188INSERT INTO t2 VALUES (1,1),(1,2); 7189CREATE FUNCTION f1() RETURNS INT DETERMINISTIC 7190BEGIN 7191DECLARE a int; 7192-- SQL statement inside 7193SELECT 1 INTO a; 7194RETURN a; 7195END $ 7196SELECT COUNT(DISTINCT d) FROM t1, t2 WHERE a = c AND b = f1(); 7197COUNT(DISTINCT d) 71982 7199DROP FUNCTION f1; 7200DROP TABLE t1, t2; 7201# ------------------------------------------------------------------ 7202# -- End of 5.1 tests 7203# ------------------------------------------------------------------ 7204DROP FUNCTION IF EXISTS f1; 7205DROP TABLE IF EXISTS t_non_existing; 7206DROP TABLE IF EXISTS t1; 7207CREATE FUNCTION f1() RETURNS INT 7208BEGIN 7209DECLARE v INT; 7210SELECT a INTO v FROM t_non_existing; 7211RETURN 1; 7212END| 7213CREATE TABLE t1 (a INT) ENGINE = myisam; 7214INSERT INTO t1 VALUES (1); 7215SELECT * FROM t1 WHERE a = f1(); 7216ERROR 42S02: Table 'test.t_non_existing' doesn't exist 7217DROP FUNCTION f1; 7218DROP TABLE t1; 7219DROP PROCEDURE IF EXISTS p1; 7220CREATE PROCEDURE p1(a INT, b CHAR) 7221BEGIN 7222IF a > 0 THEN 7223CALL p1(a-1, 'ab'); 7224ELSE 7225SELECT 1; 7226END IF; 7227END| 7228SET @save_max_sp_recursion= @@max_sp_recursion_depth; 7229SET @@max_sp_recursion_depth= 5; 7230CALL p1(4, 'a'); 72311 72321 7233Warnings: 7234Warning 1265 Data truncated for column 'b' at row 1 7235Warning 1265 Data truncated for column 'b' at row 1 7236Warning 1265 Data truncated for column 'b' at row 1 7237Warning 1265 Data truncated for column 'b' at row 1 7238SET @@max_sp_recursion_depth= @save_max_sp_recursion; 7239DROP PROCEDURE p1; 7240DROP PROCEDURE IF EXISTS p1; 7241CREATE PROCEDURE p1(a CHAR) 7242BEGIN 7243SELECT 1; 7244SELECT CAST('10 ' as UNSIGNED INTEGER); 7245SELECT 1; 7246END| 7247CALL p1('data truncated parameter'); 72481 72491 7250CAST('10 ' as UNSIGNED INTEGER) 725110 72521 72531 7254Warnings: 7255Warning 1265 Data truncated for column 'a' at row 1 7256Warning 1292 Truncated incorrect INTEGER value: '10 ' 7257DROP PROCEDURE p1; 7258DROP PROCEDURE IF EXISTS p1; 7259DROP PROCEDURE IF EXISTS p2; 7260DROP PROCEDURE IF EXISTS p3; 7261DROP PROCEDURE IF EXISTS p4; 7262CREATE PROCEDURE p1() 7263CALL p2()| 7264CREATE PROCEDURE p2() 7265CALL p3()| 7266CREATE PROCEDURE p3() 7267CALL p4()| 7268CREATE PROCEDURE p4() 7269BEGIN 7270SELECT 1; 7271SELECT CAST('10 ' as UNSIGNED INTEGER); 7272SELECT 2; 7273END| 7274CALL p1(); 72751 72761 7277CAST('10 ' as UNSIGNED INTEGER) 727810 72792 72802 7281Warnings: 7282Warning 1292 Truncated incorrect INTEGER value: '10 ' 7283DROP PROCEDURE p1; 7284DROP PROCEDURE p2; 7285DROP PROCEDURE p3; 7286DROP PROCEDURE p4; 7287DROP FUNCTION IF EXISTS f1; 7288DROP FUNCTION IF EXISTS f2; 7289DROP FUNCTION IF EXISTS f3; 7290DROP FUNCTION IF EXISTS f4; 7291DROP TABLE IF EXISTS t1; 7292CREATE TABLE t1 (a CHAR(2)); 7293INSERT INTO t1 VALUES ('aa'); 7294CREATE FUNCTION f1() RETURNS CHAR 7295RETURN (SELECT f2())| 7296CREATE FUNCTION f2() RETURNS CHAR 7297RETURN (SELECT f3())| 7298CREATE FUNCTION f3() RETURNS CHAR 7299RETURN (SELECT f4())| 7300CREATE FUNCTION f4() RETURNS CHAR 7301BEGIN 7302RETURN (SELECT a FROM t1); 7303END| 7304SELECT f1(); 7305f1() 7306a 7307Warnings: 7308Warning 1265 Data truncated for column 'f4()' at row 1 7309DROP FUNCTION f1; 7310DROP FUNCTION f2; 7311DROP FUNCTION f3; 7312DROP FUNCTION f4; 7313DROP TABLE t1; 7314# 7315# Bug#34197: CREATE PROCEDURE fails when COMMENT truncated in non 7316# strict SQL mode 7317# 7318DROP PROCEDURE IF EXISTS p1; 7319CREATE PROCEDURE p1 () 7320COMMENT 7321'12345678901234567890123456789012345678901234567890123456789012345678901234567890' 7322BEGIN 7323END; 7324SELECT comment FROM mysql.proc WHERE name = "p1"; 7325comment 732612345678901234567890123456789012345678901234567890123456789012345678901234567890 7327SELECT routine_comment FROM information_schema.routines WHERE routine_name = "p1"; 7328routine_comment 732912345678901234567890123456789012345678901234567890123456789012345678901234567890 7330DROP PROCEDURE p1; 7331# 7332# Bug #47313 assert in check_key_in_view during CALL procedure 7333# 7334DROP TABLE IF EXISTS t1; 7335DROP VIEW IF EXISTS t1, t2_unrelated; 7336DROP PROCEDURE IF EXISTS p1; 7337CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x); 7338CREATE VIEW t1 AS SELECT 10 AS f1; 7339# t1 refers to the view 7340CALL p1(1); 7341ERROR HY000: The target table t1 of the INSERT is not insertable-into 7342CREATE TEMPORARY TABLE t1 (f1 INT); 7343# t1 still refers to the view since it was inlined 7344CALL p1(2); 7345ERROR HY000: The target table t1 of the INSERT is not insertable-into 7346DROP VIEW t1; 7347# t1 now refers to the temporary table 7348CALL p1(3); 7349# Check which values were inserted into the temp table. 7350SELECT * FROM t1; 7351f1 73523 7353DROP TEMPORARY TABLE t1; 7354DROP PROCEDURE p1; 7355# Now test what happens if the sp cache is invalidated. 7356CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x); 7357CREATE VIEW t1 AS SELECT 10 AS f1; 7358CREATE VIEW v2_unrelated AS SELECT 1 AS r1; 7359# Load the procedure into the sp cache 7360CALL p1(4); 7361ERROR HY000: The target table t1 of the INSERT is not insertable-into 7362CREATE TEMPORARY TABLE t1 (f1 int); 7363ALTER VIEW v2_unrelated AS SELECT 2 AS r1; 7364# Alter view causes the sp cache to be invalidated. 7365# Now t1 refers to the temporary table, not the view. 7366CALL p1(5); 7367# Check which values were inserted into the temp table. 7368SELECT * FROM t1; 7369f1 73705 7371DROP TEMPORARY TABLE t1; 7372DROP VIEW t1, v2_unrelated; 7373DROP PROCEDURE p1; 7374CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x); 7375CREATE TEMPORARY TABLE t1 (f1 INT); 7376# t1 refers to the temporary table 7377CALL p1(6); 7378CREATE VIEW t1 AS SELECT 10 AS f1; 7379# Create view causes the sp cache to be invalidated. 7380# t1 still refers to the temporary table since it shadows the view. 7381CALL p1(7); 7382DROP VIEW t1; 7383# Check which values were inserted into the temp table. 7384SELECT * FROM t1; 7385f1 73866 73877 7388DROP TEMPORARY TABLE t1; 7389DROP PROCEDURE p1; 7390# 7391# Bug #11918 Can't use a declared variable in LIMIT clause 7392# 7393drop table if exists t1; 7394drop procedure if exists p1; 7395create table t1 (c1 int); 7396insert into t1 (c1) values (1), (2), (3), (4), (5); 7397create procedure p1() 7398begin 7399declare a integer; 7400declare b integer; 7401select * from t1 limit a, b; 7402end| 7403# How do we handle NULL limit values? 7404call p1(); 7405c1 7406drop table t1; 7407create table t1 (a int); 7408insert into t1 (a) values (1), (2), (3), (4), (5); 7409# 7410# Do we correctly resolve identifiers in LIMIT? 7411# Since DROP and CREATE did not invalidate 7412# the SP cache, we can't test until 7413# we drop and re-create the procedure. 7414# 7415call p1(); 7416ERROR 42S22: Unknown column 'test.t1.c1' in 'field list' 7417# 7418# Drop and recreate the procedure, then repeat 7419# 7420drop procedure p1; 7421create procedure p1() 7422begin 7423declare a integer; 7424declare b integer; 7425select * from t1 limit a, b; 7426end| 7427# Stored procedure variables are resolved correctly in the LIMIT 7428call p1(); 7429a 7430drop table t1; 7431create table t1 (c1 int); 7432insert into t1 (c1) values (1), (2), (3), (4), (5); 7433drop procedure p1; 7434# Try to create a procedure that 7435# refers to non-existing variables. 7436create procedure p1(p1 integer, p2 integer) 7437select * from t1 limit a, b; 7438ERROR 42000: Undeclared variable: a 7439# 7440# Try to use data types not allowed in LIMIT 7441# 7442create procedure p1(p1 date, p2 date) select * from t1 limit p1, p2; 7443ERROR HY000: A variable of a non-integer based type in LIMIT clause 7444create procedure p1(p1 integer, p2 float) select * from t1 limit p1, p2; 7445ERROR HY000: A variable of a non-integer based type in LIMIT clause 7446create procedure p1(p1 integer, p2 char(1)) select * from t1 limit p1, p2; 7447ERROR HY000: A variable of a non-integer based type in LIMIT clause 7448create procedure p1(p1 varchar(5), p2 char(1)) select * from t1 limit p1, p2; 7449ERROR HY000: A variable of a non-integer based type in LIMIT clause 7450create procedure p1(p1 decimal, p2 decimal) select * from t1 limit p1, p2; 7451ERROR HY000: A variable of a non-integer based type in LIMIT clause 7452create procedure p1(p1 double, p2 double) select * from t1 limit p1, p2; 7453ERROR HY000: A variable of a non-integer based type in LIMIT clause 7454# 7455# Finally, test the valid case. 7456# 7457create procedure p1(p1 integer, p2 integer) 7458select * from t1 limit p1, p2; 7459call p1(NULL, NULL); 7460c1 7461call p1(0, 0); 7462c1 7463call p1(0, -1); 7464c1 74651 74662 74673 74684 74695 7470call p1(-1, 0); 7471c1 7472call p1(-1, -1); 7473c1 7474call p1(0, 1); 7475c1 74761 7477call p1(1, 0); 7478c1 7479call p1(1, 5); 7480c1 74812 74823 74834 74845 7485call p1(3, 2); 7486c1 74874 74885 7489# Try to create a function that 7490# refers to non-existing variables. 7491create function f1(p1 integer, p2 integer) 7492returns int 7493begin 7494declare a int; 7495set a = (select count(*) from t1 limit a, b); 7496return a; 7497end| 7498ERROR 42000: Undeclared variable: b 7499create function f1() 7500returns int 7501begin 7502declare a, b, c int; 7503set a = (select count(*) from t1 limit b, c); 7504return a; 7505end| 7506# How do we handle NULL limit values? 7507select f1(); 7508f1() 7509NULL 7510drop function f1; 7511# 7512# Try to use data types not allowed in LIMIT 7513# 7514create function f1(p1 date, p2 date) 7515returns int 7516begin 7517declare a int; 7518set a = (select count(*) from t1 limit p1, p2); 7519return a; 7520end| 7521ERROR HY000: A variable of a non-integer based type in LIMIT clause 7522create function f1(p1 integer, p2 float) 7523returns int 7524begin 7525declare a int; 7526set a = (select count(*) from t1 limit p1, p2); 7527return a; 7528end| 7529ERROR HY000: A variable of a non-integer based type in LIMIT clause 7530create function f1(p1 integer, p2 char(1)) 7531returns int 7532begin 7533declare a int; 7534set a = (select count(*) from t1 limit p1, p2); 7535return a; 7536end| 7537ERROR HY000: A variable of a non-integer based type in LIMIT clause 7538create function f1(p1 varchar(5), p2 char(1)) 7539returns int 7540begin 7541declare a int; 7542set a = (select count(*) from t1 limit p1, p2); 7543return a; 7544end| 7545ERROR HY000: A variable of a non-integer based type in LIMIT clause 7546create function f1(p1 decimal, p2 decimal) 7547returns int 7548begin 7549declare a int; 7550set a = (select count(*) from t1 limit p1, p2); 7551return a; 7552end| 7553ERROR HY000: A variable of a non-integer based type in LIMIT clause 7554create function f1(p1 double, p2 double) 7555returns int 7556begin 7557declare a int; 7558set a = (select count(*) from t1 limit p1, p2); 7559return a; 7560end| 7561ERROR HY000: A variable of a non-integer based type in LIMIT clause 7562# 7563# Finally, test the valid case. 7564# 7565create function f1(p1 integer, p2 integer) 7566returns int 7567begin 7568declare count int; 7569set count= (select count(*) from (select * from t1 limit p1, p2) t_1); 7570return count; 7571end| 7572select f1(0, 0); 7573f1(0, 0) 75740 7575select f1(0, -1); 7576f1(0, -1) 75775 7578select f1(-1, 0); 7579f1(-1, 0) 75800 7581select f1(-1, -1); 7582f1(-1, -1) 75830 7584select f1(0, 1); 7585f1(0, 1) 75861 7587select f1(1, 0); 7588f1(1, 0) 75890 7590select f1(1, 5); 7591f1(1, 5) 75924 7593select f1(3, 2); 7594f1(3, 2) 75952 7596# Cleanup 7597drop table t1; 7598drop procedure p1; 7599drop function f1; 7600# 7601# BUG#11766234: 59299: ASSERT (TABLE_REF->TABLE || TABLE_REF->VIEW) 7602# FAILS IN SET_FIELD_ITERATOR 7603# 7604CREATE TABLE t1 (a INT); 7605CREATE TABLE t2 (a INT); 7606CREATE VIEW v1 AS SELECT a FROM t2; 7607CREATE PROCEDURE proc() SELECT * FROM t1 NATURAL JOIN v1; 7608ALTER TABLE t2 CHANGE COLUMN a b CHAR; 7609 7610CALL proc(); 7611ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 7612CALL proc(); 7613ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 7614 7615DROP TABLE t1,t2; 7616DROP VIEW v1; 7617DROP PROCEDURE proc; 7618 7619# -- 7620# -- Bug 11765684 - 58674: SP-cache does not detect changes in 7621# -- pre-locking list caused by triggers 7622# --- 7623DROP TABLE IF EXISTS t1; 7624DROP TABLE IF EXISTS t2; 7625DROP TABLE IF EXISTS t3; 7626DROP PROCEDURE IF EXISTS p1; 7627CREATE TABLE t1(a INT); 7628CREATE TABLE t2(a INT); 7629CREATE TABLE t3(a INT); 7630CREATE PROCEDURE p1() 7631INSERT INTO t1(a) VALUES (1); 7632 7633CREATE TRIGGER t1_ai AFTER INSERT ON t1 7634FOR EACH ROW 7635INSERT INTO t2(a) VALUES (new.a); 7636 7637CALL p1(); 7638 7639CREATE TRIGGER t1_bi BEFORE INSERT ON t1 7640FOR EACH ROW 7641INSERT INTO t3(a) VALUES (new.a); 7642 7643CALL p1(); 7644 7645DROP TABLE t1, t2, t3; 7646DROP PROCEDURE p1; 7647 7648 7649# -- 7650# -- Bug#12652769 - 61470: case operator in stored routine retains old 7651# -- value of input parameter 7652# --- 7653DROP TABLE IF EXISTS t1; 7654DROP PROCEDURE IF EXISTS p1; 7655CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET utf8); 7656INSERT INTO t1 VALUES ('a'); 7657CREATE PROCEDURE p1(dt DATETIME, i INT) 7658BEGIN 7659SELECT 7660CASE 7661WHEN i = 1 THEN 2 7662ELSE dt 7663END AS x1; 7664SELECT 7665CASE _latin1'a' 7666 WHEN _utf8'a' THEN 'A' 7667 END AS x2; 7668SELECT 7669CASE _utf8'a' 7670 WHEN _latin1'a' THEN _utf8'A' 7671 END AS x3; 7672SELECT 7673CASE s1 7674WHEN _latin1'a' THEN _latin1'b' 7675 ELSE _latin1'c' 7676 END AS x4 7677FROM t1; 7678END| 7679 7680CALL p1('2011-04-03 05:14:10', 1); 7681x1 76822 7683x2 7684A 7685x3 7686A 7687x4 7688b 7689CALL p1('2011-04-03 05:14:11', 2); 7690x1 76912011-04-03 05:14:11 7692x2 7693A 7694x3 7695A 7696x4 7697b 7698CALL p1('2011-04-03 05:14:12', 2); 7699x1 77002011-04-03 05:14:12 7701x2 7702A 7703x3 7704A 7705x4 7706b 7707CALL p1('2011-04-03 05:14:13', 2); 7708x1 77092011-04-03 05:14:13 7710x2 7711A 7712x3 7713A 7714x4 7715b 7716 7717DROP TABLE t1; 7718DROP PROCEDURE p1; 7719 7720# 7721# Bug#12621017 - Crash if a sp variable is used in the 7722# limit clause of a set statement 7723# 7724DROP TABLE IF EXISTS t1; 7725DROP PROCEDURE IF EXISTS p1; 7726DROP PROCEDURE IF EXISTS p2; 7727CREATE TABLE t1 (c1 INT); 7728INSERT INTO t1 VALUES (1); 7729CREATE PROCEDURE p1() 7730BEGIN 7731DECLARE foo, cnt INT UNSIGNED DEFAULT 1; 7732SET foo = (SELECT MIN(c1) FROM t1 LIMIT cnt); 7733END| 7734CREATE PROCEDURE p2() 7735BEGIN 7736DECLARE iLimit INT; 7737DECLARE iVal INT; 7738DECLARE cur1 CURSOR FOR 7739SELECT c1 FROM t1 7740LIMIT iLimit; 7741SET iLimit=1; 7742OPEN cur1; 7743FETCH cur1 INTO iVal; 7744END| 7745CALL p1(); 7746CALL p2(); 7747DROP PROCEDURE p1; 7748DROP PROCEDURE p2; 7749DROP TABLE t1; 7750 7751# Bug#13805127: Stored program cache produces wrong result in same THD 7752 7753CREATE PROCEDURE p1(x INT UNSIGNED) 7754BEGIN 7755SELECT c1, t2.c2, count(c3) 7756FROM 7757( 7758SELECT 3 as c2 FROM dual WHERE x = 1 7759UNION 7760SELECT 2 FROM dual WHERE x = 1 OR x = 2 7761) AS t1, 7762( 7763SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual 7764UNION 7765SELECT '2012-03-01 02:00:00', 3, 2 FROM dual 7766UNION 7767SELECT '2012-03-01 01:00:00', 2, 1 FROM dual 7768) AS t2 7769WHERE t2.c2 = t1.c2 7770GROUP BY c1, c2 7771; 7772END| 7773 7774CALL p1(1); 7775c1 c2 count(c3) 77762012-03-01 01:00:00 2 1 77772012-03-01 01:00:00 3 1 77782012-03-01 02:00:00 3 1 7779CALL p1(2); 7780c1 c2 count(c3) 77812012-03-01 01:00:00 2 1 7782CALL p1(1); 7783c1 c2 count(c3) 77842012-03-01 01:00:00 2 1 77852012-03-01 01:00:00 3 1 77862012-03-01 02:00:00 3 1 7787DROP PROCEDURE p1; 7788# End of 5.5 test 7789# 7790# Bug#12663165 SP DEAD CODE REMOVAL DOESN'T UNDERSTAND CONTINUE HANDLERS 7791# 7792DROP FUNCTION IF EXISTS f1; 7793CREATE FUNCTION f1() RETURNS INT 7794BEGIN 7795DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; 7796BEGIN 7797DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN f1(); 7798BEGIN 7799DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN f1(); 7800RETURN f1(); 7801END; 7802END; 7803RETURN 1; 7804END $ 7805SELECT f1(); 7806f1() 78071 7808Warnings: 7809Error 1424 Recursive stored functions and triggers are not allowed. 7810Error 1305 FUNCTION test.f1 does not exist 7811DROP FUNCTION f1; 7812 7813# 7814# BUG 16041903: CONTINUE HANDLER NOT INVOKED 7815# IN A STORED FUNCTION AFTER A LOCK WAIT TIMEOUT 7816# 7817 7818# Save and set lock wait timeout 7819SET @lock_wait_timeout_saved= @@lock_wait_timeout; 7820SET @innodb_lock_wait_timeout_saved= @@innodb_lock_wait_timeout; 7821SET @@lock_wait_timeout= 1; 7822SET @@innodb_lock_wait_timeout= 1; 7823 7824# Create a function with exit handler: 7825CREATE FUNCTION f1() RETURNS VARCHAR(20) 7826BEGIN 7827DECLARE EXIT HANDLER FOR SQLSTATE '42S02' RETURN 'No such table'; 7828INSERT INTO no_such_table VALUES (1); 7829END// 7830 7831# Create a function calling f1(): 7832CREATE FUNCTION f2() RETURNS VARCHAR(20) 7833BEGIN 7834RETURN f1(); 7835END// 7836 7837# Create a function provoking deadlock: 7838CREATE FUNCTION f3() RETURNS VARCHAR(20) 7839BEGIN 7840UPDATE t1 SET i= 1 WHERE i= 1; 7841RETURN 'Will never get here'; 7842END// 7843 7844# Create a function calling f3, to create 7845# a deadlock indirectly: 7846CREATE FUNCTION f4() RETURNS VARCHAR(20) 7847BEGIN 7848RETURN f3(); 7849END// 7850 7851# Open another connection, create and initialize a table 7852# to be used for provoking deadlock, put a lock on the table: 7853CREATE TABLE t1 (i INT) ENGINE=InnoDB; 7854INSERT INTO t1 VALUES (1); 7855SET AUTOCOMMIT= 0; 7856UPDATE t1 SET i=1 WHERE i=1; 7857 7858# On the default connection, do an update to provoke a 7859# deadlock, then call the function with handler. This case 7860# fails without the patch (with error ER_NO_SUCH_TABLE): 7861SET AUTOCOMMIT= 0; 7862UPDATE t1 SET i=1 WHERE i=1; 7863ERROR HY000: Lock wait timeout exceeded; try restarting transaction 7864SELECT f1() AS 'f1():'; 7865f1(): 7866No such table 7867Warnings: 7868Error 1146 Table 'test.no_such_table' doesn't exist 7869 7870# Provoke another deadlock, then call the function with 7871# handler indirectly. This case fails without the patch 7872# (with error ER_NO_SUCH_TABLE): 7873UPDATE t1 SET i= 1 WHERE i= 1; 7874ERROR HY000: Lock wait timeout exceeded; try restarting transaction 7875SELECT f2() AS 'f2():'; 7876f2(): 7877No such table 7878Warnings: 7879Error 1146 Table 'test.no_such_table' doesn't exist 7880 7881# Provoke yet another deadlock, but now from within a function, 7882# then call the function with handler. This succeeds even 7883# without the patch because is_fatal_sub_stmt_error is reset 7884# in restore_sub_stmt after the failing function has been 7885# executed. The test case is included anyway for better coverage: 7886SELECT f3() AS 'f3():'; 7887ERROR HY000: Lock wait timeout exceeded; try restarting transaction 7888SELECT f1() AS 'f1():'; 7889f1(): 7890No such table 7891Warnings: 7892Error 1146 Table 'test.no_such_table' doesn't exist 7893# Provoke yet another deadlock, but now from within a function, 7894# calling another function, then call the function with handler. 7895# This succeeds even without the patch because 7896# is_fatal_sub_stmt_error is reset in restore_sub_stmt after 7897# the failing function has been executed. The test case is 7898# included anyway for better coverage: 7899SELECT f4() AS 'f4():'; 7900ERROR HY000: Lock wait timeout exceeded; try restarting transaction 7901SELECT f1() AS 'f1():'; 7902f1(): 7903No such table 7904Warnings: 7905Error 1146 Table 'test.no_such_table' doesn't exist 7906 7907# Disconnect, drop functions and table: 7908DROP FUNCTION f4; 7909DROP FUNCTION f3; 7910DROP FUNCTION f2; 7911DROP FUNCTION f1; 7912DROP TABLE t1; 7913 7914# Reset lock wait timeouts 7915SET @@lock_wait_timeout= @lock_wait_timeout_saved; 7916SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; 7917# 7918# BUG 16041903: End of test case 7919# 7920