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()| 740select * from t1| 741id data 742foo 40 743bar 15 744zap 663 745drop procedure cur1| 746create table t3 ( s char(16), i int )| 747drop procedure if exists cur2| 748create procedure cur2() 749begin 750declare done int default 0; 751declare c1 cursor for select id,data from test.t1 order by id,data; 752declare c2 cursor for select i from test.t2 order by i; 753declare continue handler for sqlstate '02000' set done = 1; 754open c1; 755open c2; 756repeat 757begin 758declare a char(16); 759declare b,c int; 760fetch from c1 into a, b; 761fetch next from c2 into c; 762if not done then 763if b < c then 764insert into test.t3 values (a, b); 765else 766insert into test.t3 values (a, c); 767end if; 768end if; 769end; 770until done end repeat; 771close c1; 772close c2; 773end| 774call cur2()| 775select * from t3 order by i,s| 776s i 777bar 3 778foo 40 779zap 663 780delete from t1| 781delete from t2| 782drop table t3| 783drop procedure cur2| 784drop procedure if exists chistics| 785create procedure chistics() 786language sql 787modifies sql data 788not deterministic 789sql security definer 790comment 'Characteristics procedure test' 791 insert into t1 values ("chistics", 1)| 792show create procedure chistics| 793Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 794chistics ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`() 795 MODIFIES SQL DATA 796 COMMENT 'Characteristics procedure test' 797insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci 798call chistics()| 799select * from t1| 800id data 801chistics 1 802delete from t1| 803alter procedure chistics sql security invoker| 804show create procedure chistics| 805Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 806chistics ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `chistics`() 807 MODIFIES SQL DATA 808 SQL SECURITY INVOKER 809 COMMENT 'Characteristics procedure test' 810insert into t1 values ("chistics", 1) latin1 latin1_swedish_ci latin1_swedish_ci 811drop procedure chistics| 812drop function if exists chistics| 813create function chistics() returns int 814language sql 815deterministic 816sql security invoker 817comment 'Characteristics procedure test' 818 return 42| 819show create function chistics| 820Function sql_mode Create Function character_set_client collation_connection Database Collation 821chistics ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11) 822 DETERMINISTIC 823 SQL SECURITY INVOKER 824 COMMENT 'Characteristics procedure test' 825return 42 latin1 latin1_swedish_ci latin1_swedish_ci 826select chistics()| 827chistics() 82842 829alter function chistics 830no sql 831comment 'Characteristics function test'| 832show create function chistics| 833Function sql_mode Create Function character_set_client collation_connection Database Collation 834chistics ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `chistics`() RETURNS int(11) 835 NO SQL 836 DETERMINISTIC 837 SQL SECURITY INVOKER 838 COMMENT 'Characteristics function test' 839return 42 latin1 latin1_swedish_ci latin1_swedish_ci 840drop function chistics| 841insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)| 842set @@sql_mode = 'ANSI'| 843Warnings: 844Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 845drop procedure if exists modes$ 846create procedure modes(out c1 int, out c2 int) 847begin 848declare done int default 0; 849declare x int; 850declare c cursor for select data from t1; 851declare continue handler for sqlstate '02000' set done = 1; 852select 1 || 2 into c1; 853set c2 = 0; 854open c; 855repeat 856fetch c into x; 857if not done then 858set c2 = c2 + 1; 859end if; 860until done end repeat; 861close c; 862end$ 863set @@sql_mode = ''| 864set sql_select_limit = 1| 865call modes(@c1, @c2)| 866set sql_select_limit = default| 867select @c1, @c2| 868@c1 @c2 86912 3 870delete from t1| 871drop procedure modes| 872create database sp_db1| 873drop database sp_db1| 874create database sp_db2| 875use sp_db2| 876create table t3 ( s char(4), t int )| 877insert into t3 values ("abcd", 42), ("dcba", 666)| 878use test| 879drop database sp_db2| 880create database sp_db3| 881use sp_db3| 882drop procedure if exists dummy| 883create procedure dummy(out x int) 884set x = 42| 885use test| 886drop database sp_db3| 887select type,db,name from mysql.proc where db = 'sp_db3'| 888type db name 889drop procedure if exists rc| 890create procedure rc() 891begin 892delete from t1; 893insert into t1 values ("a", 1), ("b", 2), ("c", 3); 894end| 895call rc()| 896select row_count()| 897row_count() 8983 899update t1 set data=42 where id = "b"; 900select row_count()| 901row_count() 9021 903delete from t1| 904select row_count()| 905row_count() 9063 907delete from t1| 908select row_count()| 909row_count() 9100 911select * from t1| 912id data 913select row_count()| 914row_count() 915-1 916drop procedure rc| 917drop function if exists f0| 918drop function if exists f1| 919drop function if exists f2| 920drop function if exists f3| 921drop function if exists f4| 922drop function if exists f5| 923drop function if exists f6| 924drop function if exists f7| 925drop function if exists f8| 926drop function if exists f9| 927drop function if exists f10| 928drop function if exists f11| 929drop function if exists f12_1| 930drop function if exists f12_2| 931drop view if exists v0| 932drop view if exists v1| 933drop view if exists v2| 934delete from t1| 935delete from t2| 936insert into t1 values ("a", 1), ("b", 2) | 937insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) | 938create function f1() returns int 939return (select sum(data) from t1)| 940select f1()| 941f1() 9423 943select id, f1() from t1 order by id| 944id f1() 945a 3 946b 3 947create function f2() returns int 948return (select data from t1 where data <= (select sum(data) from t1) order by data limit 1)| 949select f2()| 950f2() 9511 952select id, f2() from t1 order by id| 953id f2() 954a 1 955b 1 956create function f3() returns int 957begin 958declare n int; 959declare m int; 960set n:= (select min(data) from t1); 961set m:= (select max(data) from t1); 962return n < m; 963end| 964select f3()| 965f3() 9661 967select id, f3() from t1 order by id| 968id f3() 969a 1 970b 1 971select f1(), f3()| 972f1() f3() 9733 1 974select id, f1(), f3() from t1 order by id| 975id f1() f3() 976a 3 1 977b 3 1 978create function f4() returns double 979return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| 980select f4()| 981f4() 9822 983select s, f4() from t2 order by s| 984s f4() 985a 2 986b 2 987c 2 988create function f5(i int) returns int 989begin 990if i <= 0 then 991return 0; 992elseif i = 1 then 993return (select count(*) from t1 where data = i); 994else 995return (select count(*) + f5( i - 1) from t1 where data = i); 996end if; 997end| 998select f5(1)| 999f5(1) 10001 1001select f5(2)| 1002ERROR HY000: Recursive stored functions and triggers are not allowed. 1003select f5(3)| 1004ERROR HY000: Recursive stored functions and triggers are not allowed. 1005create function f6() returns int 1006begin 1007declare n int; 1008set n:= f1(); 1009return (select count(*) from t1 where data <= f7() and data <= n); 1010end| 1011create function f7() returns int 1012return (select sum(data) from t1 where data <= f1())| 1013select f6()| 1014f6() 10152 1016select id, f6() from t1 order by id| 1017id f6() 1018a 2 1019b 2 1020create view v1 (a) as select f1()| 1021select * from v1| 1022a 10233 1024select id, a from t1, v1 order by id| 1025id a 1026a 3 1027b 3 1028select * from v1, v1 as v| 1029a a 10303 3 1031create view v2 (a) as select a*10 from v1| 1032select * from v2| 1033a 103430 1035select id, a from t1, v2 order by id| 1036id a 1037a 30 1038b 30 1039select * from v1, v2| 1040a a 10413 30 1042create function f8 () returns int 1043return (select count(*) from v2)| 1044select *, f8() from v1| 1045a f8() 10463 1 1047drop function f1| 1048select * from v1| 1049ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 1050create function f1() returns int 1051return (select sum(data) from t1) + (select sum(data) from v1)| 1052select f1()| 1053ERROR HY000: Recursive stored functions and triggers are not allowed. 1054select * from v1| 1055ERROR HY000: Recursive stored functions and triggers are not allowed. 1056select * from v2| 1057ERROR HY000: Recursive stored functions and triggers are not allowed. 1058drop function f1| 1059create function f1() returns int 1060return (select sum(data) from t1)| 1061create function f0() returns int 1062return (select * from (select 100) as r)| 1063select f0()| 1064f0() 1065100 1066select *, f0() from (select 1) as t| 10671 f0() 10681 100 1069create view v0 as select f0()| 1070select * from v0| 1071f0() 1072100 1073select *, f0() from v0| 1074f0() f0() 1075100 100 1076lock tables t1 read, t1 as t11 read| 1077select f3()| 1078f3() 10791 1080select id, f3() from t1 as t11 order by id| 1081id f3() 1082a 1 1083b 1 1084select f0()| 1085f0() 1086100 1087select * from v0| 1088ERROR HY000: Table 'v0' was not locked with LOCK TABLES 1089select *, f0() from v0, (select 123) as d1| 1090ERROR HY000: Table 'v0' was not locked with LOCK TABLES 1091select id, f3() from t1| 1092ERROR HY000: Table 't1' was not locked with LOCK TABLES 1093select f4()| 1094ERROR HY000: Table 't2' was not locked with LOCK TABLES 1095unlock tables| 1096lock tables v2 read, mysql.proc read| 1097select * from v2| 1098a 109930 1100select * from v1| 1101a 11023 1103select * from v1, t1| 1104ERROR HY000: Table 't1' was not locked with LOCK TABLES 1105select f4()| 1106ERROR HY000: Table 't2' was not locked with LOCK TABLES 1107unlock tables| 1108create function f9() returns int 1109begin 1110declare a, b int; 1111drop temporary table if exists t3; 1112create temporary table t3 (id int); 1113insert into t3 values (1), (2), (3); 1114set a:= (select count(*) from t3); 1115set b:= (select count(*) from t3 t3_alias); 1116return a + b; 1117end| 1118select f9()| 1119f9() 11206 1121select f9() from t1 limit 1| 1122f9() 11236 1124create function f10() returns int 1125begin 1126drop temporary table if exists t3; 1127create temporary table t3 (id int); 1128insert into t3 select id from t4; 1129return (select count(*) from t3); 1130end| 1131select f10()| 1132ERROR 42S02: Table 'test.t4' doesn't exist 1133create table t4 as select 1 as id| 1134select f10()| 1135f10() 11361 1137create function f11() returns int 1138begin 1139drop temporary table if exists t3; 1140create temporary table t3 (id int); 1141insert into t3 values (1), (2), (3); 1142return (select count(*) from t3 as a, t3 as b); 1143end| 1144select f11()| 1145ERROR HY000: Can't reopen table: 'a' 1146select f11() from t1| 1147ERROR HY000: Can't reopen table: 'a' 1148create function f12_1() returns int 1149begin 1150drop temporary table if exists t3; 1151create temporary table t3 (id int); 1152insert into t3 values (1), (2), (3); 1153return f12_2(); 1154end| 1155create function f12_2() returns int 1156return (select count(*) from t3)| 1157drop temporary table t3| 1158select f12_1()| 1159f12_1() 11603 1161select f12_1() from t1 limit 1| 1162f12_1() 11633 1164drop function f0| 1165drop function f1| 1166drop function f2| 1167drop function f3| 1168drop function f4| 1169drop function f5| 1170drop function f6| 1171drop function f7| 1172drop function f8| 1173drop function f9| 1174drop function f10| 1175drop function f11| 1176drop function f12_1| 1177drop function f12_2| 1178drop view v0| 1179drop view v1| 1180drop view v2| 1181truncate table t1 | 1182truncate table t2 | 1183drop table t4| 1184drop table if exists t3| 1185create table t3 (n int unsigned not null primary key, f bigint unsigned)| 1186drop procedure if exists ifac| 1187create procedure ifac(n int unsigned) 1188begin 1189declare i int unsigned default 1; 1190if n > 20 then 1191set n = 20; # bigint overflow otherwise 1192end if; 1193while i <= n do 1194begin 1195insert into test.t3 values (i, fac(i)); 1196set i = i + 1; 1197end; 1198end while; 1199end| 1200call ifac(20)| 1201select * from t3| 1202n f 12031 1 12042 2 12053 6 12064 24 12075 120 12086 720 12097 5040 12108 40320 12119 362880 121210 3628800 121311 39916800 121412 479001600 121513 6227020800 121614 87178291200 121715 1307674368000 121816 20922789888000 121917 355687428096000 122018 6402373705728000 122119 121645100408832000 122220 2432902008176640000 1223drop table t3| 1224show function status like '%fac'| 1225Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1226test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1227drop procedure ifac| 1228drop function fac| 1229show function status like '%fac'| 1230Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1231drop table if exists t3| 1232create table t3 ( 1233i int unsigned not null primary key, 1234p bigint unsigned not null 1235)| 1236insert into t3 values 1237( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), 1238( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), 1239(10, 37), (11, 41), (12, 43), (13, 47), (14, 53), 1240(15, 59), (16, 61), (17, 67), (18, 71), (19, 73), 1241(20, 79), (21, 83), (22, 89), (23, 97), (24, 101), 1242(25, 103), (26, 107), (27, 109), (28, 113), (29, 127), 1243(30, 131), (31, 137), (32, 139), (33, 149), (34, 151), 1244(35, 157), (36, 163), (37, 167), (38, 173), (39, 179), 1245(40, 181), (41, 191), (42, 193), (43, 197), (44, 199)| 1246drop procedure if exists opp| 1247create procedure opp(n bigint unsigned, out pp bool) 1248begin 1249declare r double; 1250declare b, s bigint unsigned default 0; 1251set r = sqrt(n); 1252again: 1253loop 1254if s = 45 then 1255set b = b+200, s = 0; 1256else 1257begin 1258declare p bigint unsigned; 1259select t.p into p from test.t3 t where t.i = s; 1260if b+p > r then 1261set pp = 1; 1262leave again; 1263end if; 1264if mod(n, b+p) = 0 then 1265set pp = 0; 1266leave again; 1267end if; 1268set s = s+1; 1269end; 1270end if; 1271end loop; 1272end| 1273drop procedure if exists ip| 1274create procedure ip(m int unsigned) 1275begin 1276declare p bigint unsigned; 1277declare i int unsigned; 1278set i=45, p=201; 1279while i < m do 1280begin 1281declare pp bool default 0; 1282call opp(p, pp); 1283if pp then 1284insert into test.t3 values (i, p); 1285set i = i+1; 1286end if; 1287set p = p+2; 1288end; 1289end while; 1290end| 1291show create procedure opp| 1292Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1293opp CREATE DEFINER=`root`@`localhost` PROCEDURE `opp`(n bigint unsigned, out pp bool) 1294begin 1295declare r double; 1296declare b, s bigint unsigned default 0; 1297set r = sqrt(n); 1298again: 1299loop 1300if s = 45 then 1301set b = b+200, s = 0; 1302else 1303begin 1304declare p bigint unsigned; 1305select t.p into p from test.t3 t where t.i = s; 1306if b+p > r then 1307set pp = 1; 1308leave again; 1309end if; 1310if mod(n, b+p) = 0 then 1311set pp = 0; 1312leave again; 1313end if; 1314set s = s+1; 1315end; 1316end if; 1317end loop; 1318end latin1 latin1_swedish_ci latin1_swedish_ci 1319show procedure status where name like '%p%' and db='test'| 1320Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1321test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1322test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 1323call ip(200)| 1324select * from t3 where i=45 or i=100 or i=199| 1325i p 132645 211 1327100 557 1328199 1229 1329drop table t3| 1330drop procedure opp| 1331drop procedure ip| 1332show procedure status where name like '%p%' and db='test'| 1333Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1334drop procedure if exists bar| 1335create procedure bar(x char(16), y int) 1336comment "111111111111" sql security invoker 1337insert into test.t1 values (x, y)| 1338show procedure status like 'bar'| 1339Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1340test 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 1341alter procedure bar comment "2222222222" sql security definer| 1342alter procedure bar comment "3333333333"| 1343alter procedure bar| 1344show create procedure bar| 1345Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1346bar CREATE DEFINER=`root`@`localhost` PROCEDURE `bar`(x char(16), y int) 1347 COMMENT '3333333333' 1348insert into test.t1 values (x, y) latin1 latin1_swedish_ci latin1_swedish_ci 1349show procedure status like 'bar'| 1350Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1351test 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 1352drop procedure bar| 1353drop procedure if exists p1| 1354create procedure p1 () 1355select (select s1 from t3) from t3| 1356create table t3 (s1 int)| 1357call p1()| 1358(select s1 from t3) 1359insert into t3 values (1)| 1360call p1()| 1361(select s1 from t3) 13621 1363drop procedure p1| 1364drop table t3| 1365drop function if exists foo| 1366create function `foo` () returns int 1367return 5| 1368select `foo` ()| 1369`foo` () 13705 1371drop function `foo`| 1372drop function if exists t1max| 1373create function t1max() returns int 1374begin 1375declare x int; 1376select max(data) into x from t1; 1377return x; 1378end| 1379insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)| 1380select t1max()| 1381t1max() 13825 1383drop function t1max| 1384create table t3 ( 1385v char(16) not null primary key, 1386c int unsigned not null 1387)| 1388create function getcount(s char(16)) returns int 1389begin 1390declare x int; 1391select count(*) into x from t3 where v = s; 1392if x = 0 then 1393insert into t3 values (s, 1); 1394else 1395update t3 set c = c+1 where v = s; 1396end if; 1397return x; 1398end| 1399select * from t1 where data = getcount("bar")| 1400id data 1401zap 1 1402select * from t3| 1403v c 1404bar 4 1405select getcount("zip")| 1406getcount("zip") 14070 1408select getcount("zip")| 1409getcount("zip") 14101 1411select * from t3| 1412v c 1413bar 4 1414zip 2 1415select getcount(id) from t1 where data = 3| 1416getcount(id) 14170 1418select getcount(id) from t1 where data = 5| 1419getcount(id) 14201 1421select * from t3| 1422v c 1423bar 4 1424zip 3 1425foo 1 1426drop table t3| 1427drop function getcount| 1428drop table if exists t3| 1429drop procedure if exists h_ee| 1430drop procedure if exists h_es| 1431drop procedure if exists h_en| 1432drop procedure if exists h_ew| 1433drop procedure if exists h_ex| 1434drop procedure if exists h_se| 1435drop procedure if exists h_ss| 1436drop procedure if exists h_sn| 1437drop procedure if exists h_sw| 1438drop procedure if exists h_sx| 1439drop procedure if exists h_ne| 1440drop procedure if exists h_ns| 1441drop procedure if exists h_nn| 1442drop procedure if exists h_we| 1443drop procedure if exists h_ws| 1444drop procedure if exists h_ww| 1445drop procedure if exists h_xe| 1446drop procedure if exists h_xs| 1447drop procedure if exists h_xx| 1448create table t3 (a smallint primary key)| 1449insert into t3 (a) values (1)| 1450create procedure h_ee() 1451deterministic 1452begin 1453declare continue handler for 1062 -- ER_DUP_ENTRY 1454select 'Outer (bad)' as 'h_ee'; 1455begin 1456declare continue handler for 1062 -- ER_DUP_ENTRY 1457select 'Inner (good)' as 'h_ee'; 1458insert into t3 values (1); 1459end; 1460end| 1461create procedure h_es() 1462deterministic 1463begin 1464declare continue handler for 1062 -- ER_DUP_ENTRY 1465select 'Outer (bad)' as 'h_es'; 1466begin 1467-- integrity constraint violation 1468declare continue handler for sqlstate '23000' 1469 select 'Inner (good)' as 'h_es'; 1470insert into t3 values (1); 1471end; 1472end| 1473create procedure h_en() 1474deterministic 1475begin 1476declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA 1477select 'Outer (bad)' as 'h_en'; 1478begin 1479declare x int; 1480declare continue handler for sqlstate '02000' -- no data 1481select 'Inner (good)' as 'h_en'; 1482select a into x from t3 where a = 42; 1483end; 1484end| 1485create procedure h_ew() 1486deterministic 1487begin 1488declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE 1489select 'Outer (bad)' as 'h_ew'; 1490begin 1491declare continue handler for sqlwarning 1492select 'Inner (good)' as 'h_ew'; 1493insert into t3 values (123456789012); 1494end; 1495delete from t3; 1496insert into t3 values (1); 1497end| 1498create procedure h_ex() 1499deterministic 1500begin 1501declare continue handler for 1062 -- ER_DUP_ENTRY 1502select 'Outer (bad)' as 'h_ex'; 1503begin 1504declare continue handler for sqlexception 1505select 'Inner (good)' as 'h_ex'; 1506insert into t3 values (1); 1507end; 1508end| 1509create procedure h_se() 1510deterministic 1511begin 1512-- integrity constraint violation 1513declare continue handler for sqlstate '23000' 1514select 'Outer (bad)' as 'h_se'; 1515begin 1516declare continue handler for 1062 -- ER_DUP_ENTRY 1517select 'Inner (good)' as 'h_se'; 1518insert into t3 values (1); 1519end; 1520end| 1521create procedure h_ss() 1522deterministic 1523begin 1524-- integrity constraint violation 1525declare continue handler for sqlstate '23000' 1526select 'Outer (bad)' as 'h_ss'; 1527begin 1528-- integrity constraint violation 1529declare continue handler for sqlstate '23000' 1530select 'Inner (good)' as 'h_ss'; 1531insert into t3 values (1); 1532end; 1533end| 1534create procedure h_sn() 1535deterministic 1536begin 1537-- Note: '02000' is more specific than NOT FOUND ; 1538-- there might be other not found states 1539declare continue handler for sqlstate '02000' -- no data 1540select 'Outer (bad)' as 'h_sn'; 1541begin 1542declare x int; 1543declare continue handler for not found 1544select 'Inner (good)' as 'h_sn'; 1545select a into x from t3 where a = 42; 1546end; 1547end| 1548create procedure h_sw() 1549deterministic 1550begin 1551-- data exception - numeric value out of range 1552declare continue handler for sqlstate '22003' 1553 select 'Outer (bad)' as 'h_sw'; 1554begin 1555declare continue handler for sqlwarning 1556select 'Inner (good)' as 'h_sw'; 1557insert into t3 values (123456789012); 1558end; 1559delete from t3; 1560insert into t3 values (1); 1561end| 1562create procedure h_sx() 1563deterministic 1564begin 1565-- integrity constraint violation 1566declare continue handler for sqlstate '23000' 1567select 'Outer (bad)' as 'h_sx'; 1568begin 1569declare continue handler for sqlexception 1570select 'Inner (good)' as 'h_sx'; 1571insert into t3 values (1); 1572end; 1573end| 1574create procedure h_ne() 1575deterministic 1576begin 1577declare continue handler for not found 1578select 'Outer (bad)' as 'h_ne'; 1579begin 1580declare x int; 1581declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA 1582select 'Inner (good)' as 'h_ne'; 1583select a into x from t3 where a = 42; 1584end; 1585end| 1586create procedure h_ns() 1587deterministic 1588begin 1589declare continue handler for not found 1590select 'Outer (bad)' as 'h_ns'; 1591begin 1592declare x int; 1593declare continue handler for sqlstate '02000' -- no data 1594select 'Inner (good)' as 'h_ns'; 1595select a into x from t3 where a = 42; 1596end; 1597end| 1598create procedure h_nn() 1599deterministic 1600begin 1601declare continue handler for not found 1602select 'Outer (bad)' as 'h_nn'; 1603begin 1604declare x int; 1605declare continue handler for not found 1606select 'Inner (good)' as 'h_nn'; 1607select a into x from t3 where a = 42; 1608end; 1609end| 1610create procedure h_we() 1611deterministic 1612begin 1613declare continue handler for sqlwarning 1614select 'Outer (bad)' as 'h_we'; 1615begin 1616declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE 1617select 'Inner (good)' as 'h_we'; 1618insert into t3 values (123456789012); 1619end; 1620delete from t3; 1621insert into t3 values (1); 1622end| 1623create procedure h_ws() 1624deterministic 1625begin 1626declare continue handler for sqlwarning 1627select 'Outer (bad)' as 'h_ws'; 1628begin 1629-- data exception - numeric value out of range 1630declare continue handler for sqlstate '22003' 1631 select 'Inner (good)' as 'h_ws'; 1632insert into t3 values (123456789012); 1633end; 1634delete from t3; 1635insert into t3 values (1); 1636end| 1637create procedure h_ww() 1638deterministic 1639begin 1640declare continue handler for sqlwarning 1641select 'Outer (bad)' as 'h_ww'; 1642begin 1643declare continue handler for sqlwarning 1644select 'Inner (good)' as 'h_ww'; 1645insert into t3 values (123456789012); 1646end; 1647delete from t3; 1648insert into t3 values (1); 1649end| 1650create procedure h_xe() 1651deterministic 1652begin 1653declare continue handler for sqlexception 1654select 'Outer (bad)' as 'h_xe'; 1655begin 1656declare continue handler for 1062 -- ER_DUP_ENTRY 1657select 'Inner (good)' as 'h_xe'; 1658insert into t3 values (1); 1659end; 1660end| 1661create procedure h_xs() 1662deterministic 1663begin 1664declare continue handler for sqlexception 1665select 'Outer (bad)' as 'h_xs'; 1666begin 1667-- integrity constraint violation 1668declare continue handler for sqlstate '23000' 1669 select 'Inner (good)' as 'h_xs'; 1670insert into t3 values (1); 1671end; 1672end| 1673create procedure h_xx() 1674deterministic 1675begin 1676declare continue handler for sqlexception 1677select 'Outer (bad)' as 'h_xx'; 1678begin 1679declare continue handler for sqlexception 1680select 'Inner (good)' as 'h_xx'; 1681insert into t3 values (1); 1682end; 1683end| 1684call h_ee()| 1685h_ee 1686Inner (good) 1687call h_es()| 1688h_es 1689Inner (good) 1690call h_en()| 1691h_en 1692Inner (good) 1693call h_ew()| 1694h_ew 1695Inner (good) 1696call h_ex()| 1697h_ex 1698Inner (good) 1699call h_se()| 1700h_se 1701Inner (good) 1702call h_ss()| 1703h_ss 1704Inner (good) 1705call h_sn()| 1706h_sn 1707Inner (good) 1708call h_sw()| 1709h_sw 1710Inner (good) 1711call h_sx()| 1712h_sx 1713Inner (good) 1714call h_ne()| 1715h_ne 1716Inner (good) 1717call h_ns()| 1718h_ns 1719Inner (good) 1720call h_nn()| 1721h_nn 1722Inner (good) 1723call h_we()| 1724h_we 1725Inner (good) 1726call h_ws()| 1727h_ws 1728Inner (good) 1729call h_ww()| 1730h_ww 1731Inner (good) 1732call h_xe()| 1733h_xe 1734Inner (good) 1735call h_xs()| 1736h_xs 1737Inner (good) 1738call h_xx()| 1739h_xx 1740Inner (good) 1741drop table t3| 1742drop procedure h_ee| 1743drop procedure h_es| 1744drop procedure h_en| 1745drop procedure h_ew| 1746drop procedure h_ex| 1747drop procedure h_se| 1748drop procedure h_ss| 1749drop procedure h_sn| 1750drop procedure h_sw| 1751drop procedure h_sx| 1752drop procedure h_ne| 1753drop procedure h_ns| 1754drop procedure h_nn| 1755drop procedure h_we| 1756drop procedure h_ws| 1757drop procedure h_ww| 1758drop procedure h_xe| 1759drop procedure h_xs| 1760drop procedure h_xx| 1761drop procedure if exists bug822| 1762create procedure bug822(a_id char(16), a_data int) 1763begin 1764declare n int; 1765select count(*) into n from t1 where id = a_id and data = a_data; 1766if n = 0 then 1767insert into t1 (id, data) values (a_id, a_data); 1768end if; 1769end| 1770delete from t1| 1771call bug822('foo', 42)| 1772call bug822('foo', 42)| 1773call bug822('bar', 666)| 1774select * from t1 order by data| 1775id data 1776foo 42 1777bar 666 1778delete from t1| 1779drop procedure bug822| 1780drop procedure if exists bug1495| 1781create procedure bug1495() 1782begin 1783declare x int; 1784select data into x from t1 order by id limit 1; 1785if x > 10 then 1786insert into t1 values ("less", x-10); 1787else 1788insert into t1 values ("more", x+10); 1789end if; 1790end| 1791insert into t1 values ('foo', 12)| 1792call bug1495()| 1793delete from t1 where id='foo'| 1794insert into t1 values ('bar', 7)| 1795call bug1495()| 1796delete from t1 where id='bar'| 1797select * from t1 order by data| 1798id data 1799less 2 1800more 17 1801delete from t1| 1802drop procedure bug1495| 1803drop procedure if exists bug1547| 1804create procedure bug1547(s char(16)) 1805begin 1806declare x int; 1807select data into x from t1 where s = id limit 1; 1808if x > 10 then 1809insert into t1 values ("less", x-10); 1810else 1811insert into t1 values ("more", x+10); 1812end if; 1813end| 1814insert into t1 values ("foo", 12), ("bar", 7)| 1815call bug1547("foo")| 1816call bug1547("bar")| 1817select * from t1 order by id| 1818id data 1819bar 7 1820foo 12 1821less 2 1822more 17 1823delete from t1| 1824drop procedure bug1547| 1825drop table if exists t70| 1826create table t70 (s1 int,s2 int)| 1827insert into t70 values (1,2)| 1828drop procedure if exists bug1656| 1829create procedure bug1656(out p1 int, out p2 int) 1830select * into p1, p1 from t70| 1831call bug1656(@1, @2)| 1832select @1, @2| 1833@1 @2 18342 NULL 1835drop table t70| 1836drop procedure bug1656| 1837create table t3(a int)| 1838drop procedure if exists bug1862| 1839create procedure bug1862() 1840begin 1841insert into t3 values(2); 1842flush tables; 1843end| 1844call bug1862()| 1845call bug1862()| 1846select * from t3| 1847a 18482 18492 1850drop table t3| 1851drop procedure bug1862| 1852drop procedure if exists bug1874| 1853create procedure bug1874() 1854begin 1855declare x int; 1856declare y double; 1857select max(data) into x from t1; 1858insert into t2 values ("max", x, 0); 1859select min(data) into x from t1; 1860insert into t2 values ("min", x, 0); 1861select sum(data) into x from t1; 1862insert into t2 values ("sum", x, 0); 1863select avg(data) into y from t1; 1864insert into t2 values ("avg", 0, y); 1865end| 1866insert into t1 (data) values (3), (1), (5), (9), (4)| 1867call bug1874()| 1868select * from t2 order by i| 1869s i d 1870avg 0 4.4 1871min 1 0 1872max 9 0 1873sum 22 0 1874delete from t1| 1875delete from t2| 1876drop procedure bug1874| 1877drop procedure if exists bug2260| 1878create procedure bug2260() 1879begin 1880declare v1 int; 1881declare c1 cursor for select data from t1; 1882declare continue handler for not found set @x2 = 1; 1883open c1; 1884fetch c1 into v1; 1885set @x2 = 2; 1886close c1; 1887end| 1888call bug2260()| 1889select @x2| 1890@x2 18912 1892drop procedure bug2260| 1893drop procedure if exists bug2267_1| 1894create procedure bug2267_1() 1895begin 1896show procedure status where db='test'; 1897end| 1898drop procedure if exists bug2267_2| 1899create procedure bug2267_2() 1900begin 1901show function status where db='test'; 1902end| 1903drop procedure if exists bug2267_3| 1904create procedure bug2267_3() 1905begin 1906show create procedure bug2267_1; 1907end| 1908drop procedure if exists bug2267_4| 1909drop function if exists bug2267_4| 1910create procedure bug2267_4() 1911begin 1912show create function bug2267_4; 1913end| 1914create function bug2267_4() returns int return 100| 1915call bug2267_1()| 1916Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1917test 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 1918test 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 1919test 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 1920test 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 1921call bug2267_2()| 1922Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 1923test 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 1924call bug2267_3()| 1925Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 1926bug2267_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2267_1`() 1927begin 1928show procedure status where db='test'; 1929end latin1 latin1_swedish_ci latin1_swedish_ci 1930call bug2267_4()| 1931Function sql_mode Create Function character_set_client collation_connection Database Collation 1932bug2267_4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2267_4`() RETURNS int(11) 1933return 100 latin1 latin1_swedish_ci latin1_swedish_ci 1934drop procedure bug2267_1| 1935drop procedure bug2267_2| 1936drop procedure bug2267_3| 1937drop procedure bug2267_4| 1938drop function bug2267_4| 1939drop procedure if exists bug2227| 1940create procedure bug2227(x int) 1941begin 1942declare y float default 2.6; 1943declare z char(16) default "zzz"; 1944select 1.3, x, y, 42, z; 1945end| 1946call bug2227(9)| 19471.3 x y 42 z 19481.3 9 2.6 42 zzz 1949drop procedure bug2227| 1950drop procedure if exists bug2614| 1951create procedure bug2614() 1952begin 1953drop table if exists t3; 1954create table t3 (id int default '0' not null); 1955insert into t3 select 12; 1956insert into t3 select * from t3; 1957end| 1958call bug2614()| 1959call bug2614()| 1960drop table t3| 1961drop procedure bug2614| 1962drop function if exists bug2674| 1963create function bug2674() returns int 1964return @@sort_buffer_size| 1965set @osbs = @@sort_buffer_size| 1966set @@sort_buffer_size = 262000| 1967select bug2674()| 1968bug2674() 1969262000 1970drop function bug2674| 1971set @@sort_buffer_size = @osbs| 1972drop procedure if exists bug3259_1 | 1973create procedure bug3259_1 () begin end| 1974drop procedure if exists BUG3259_2 | 1975create procedure BUG3259_2 () begin end| 1976drop procedure if exists Bug3259_3 | 1977create procedure Bug3259_3 () begin end| 1978call BUG3259_1()| 1979call BUG3259_1()| 1980call bug3259_2()| 1981call Bug3259_2()| 1982call bug3259_3()| 1983call bUG3259_3()| 1984drop procedure bUg3259_1| 1985drop procedure BuG3259_2| 1986drop procedure BUG3259_3| 1987drop function if exists bug2772| 1988create function bug2772() returns char(10) character set latin2 1989return 'a'| 1990select bug2772()| 1991bug2772() 1992a 1993drop function bug2772| 1994create table t3 (s1 smallint)| 1995insert into t3 values (123456789012)| 1996Warnings: 1997Warning 1264 Out of range value for column 's1' at row 1 1998drop procedure if exists bug2780| 1999create procedure bug2780() 2000begin 2001declare exit handler for sqlwarning set @x = 1; 2002set @x = 0; 2003insert into t3 values (123456789012); 2004insert into t3 values (0); 2005end| 2006call bug2780()| 2007select @x| 2008@x 20091 2010select * from t3| 2011s1 201232767 201332767 2014drop procedure bug2780| 2015drop table t3| 2016create table t3 (content varchar(10) )| 2017insert into t3 values ("test1")| 2018insert into t3 values ("test2")| 2019create table t4 (f1 int, rc int, t3 int)| 2020drop procedure if exists bug1863| 2021create procedure bug1863(in1 int) 2022begin 2023declare ind int default 0; 2024declare t1 int; 2025declare t2 int; 2026declare t3 int; 2027declare rc int default 0; 2028declare continue handler for 1065 set rc = 1; 2029drop temporary table if exists temp_t1; 2030create temporary table temp_t1 ( 2031f1 int auto_increment, f2 varchar(20), primary key (f1) 2032); 2033insert into temp_t1 (f2) select content from t3; 2034select f2 into t3 from temp_t1 where f1 = 10; 2035if (rc) then 2036insert into t4 values (1, rc, t3); 2037end if; 2038insert into t4 values (2, rc, t3); 2039end| 2040call bug1863(10)| 2041call bug1863(10)| 2042select * from t4| 2043f1 rc t3 20442 0 NULL 20452 0 NULL 2046drop procedure bug1863| 2047drop temporary table temp_t1; 2048drop table t3, t4| 2049create table t3 ( 2050OrderID int not null, 2051MarketID int, 2052primary key (OrderID) 2053)| 2054create table t4 ( 2055MarketID int not null, 2056Market varchar(60), 2057Status char(1), 2058primary key (MarketID) 2059)| 2060insert t3 (OrderID,MarketID) values (1,1)| 2061insert t3 (OrderID,MarketID) values (2,2)| 2062insert t4 (MarketID,Market,Status) values (1,"MarketID One","A")| 2063insert t4 (MarketID,Market,Status) values (2,"MarketID Two","A")| 2064drop procedure if exists bug2656_1| 2065create procedure bug2656_1() 2066begin 2067select 2068m.Market 2069from t4 m JOIN t3 o 2070ON o.MarketID != 1 and o.MarketID = m.MarketID; 2071end | 2072drop procedure if exists bug2656_2| 2073create procedure bug2656_2() 2074begin 2075select 2076m.Market 2077from 2078t4 m, t3 o 2079where 2080m.MarketID != 1 and m.MarketID = o.MarketID; 2081end | 2082call bug2656_1()| 2083Market 2084MarketID Two 2085call bug2656_1()| 2086Market 2087MarketID Two 2088call bug2656_2()| 2089Market 2090MarketID Two 2091call bug2656_2()| 2092Market 2093MarketID Two 2094drop procedure bug2656_1| 2095drop procedure bug2656_2| 2096drop table t3, t4| 2097drop procedure if exists bug3426| 2098create procedure bug3426(in_time int unsigned, out x int) 2099begin 2100if in_time is null then 2101set @stamped_time=10; 2102set x=1; 2103else 2104set @stamped_time=in_time; 2105set x=2; 2106end if; 2107end| 2108set time_zone='+03:00'; 2109call bug3426(1000, @i)| 2110select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| 2111@i time 21122 01-01-1970 03:16:40 2113call bug3426(NULL, @i)| 2114select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| 2115@i time 21161 01-01-1970 03:00:10 2117alter procedure bug3426 sql security invoker| 2118call bug3426(NULL, @i)| 2119select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| 2120@i time 21211 01-01-1970 03:00:10 2122call bug3426(1000, @i)| 2123select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| 2124@i time 21252 01-01-1970 03:16:40 2126drop procedure bug3426| 2127create table t3 ( 2128id int unsigned auto_increment not null primary key, 2129title VARCHAR(200), 2130body text, 2131fulltext (title,body) 2132)| 2133insert into t3 (title,body) values 2134('MySQL Tutorial','DBMS stands for DataBase ...'), 2135('How To Use MySQL Well','After you went through a ...'), 2136('Optimizing MySQL','In this tutorial we will show ...'), 2137('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 2138('MySQL vs. YourSQL','In the following database comparison ...'), 2139('MySQL Security','When configured properly, MySQL ...')| 2140drop procedure if exists bug3734 | 2141create procedure bug3734 (param1 varchar(100)) 2142select * from t3 where match (title,body) against (param1)| 2143call bug3734('database')| 2144id title body 21455 MySQL vs. YourSQL In the following database comparison ... 21461 MySQL Tutorial DBMS stands for DataBase ... 2147call bug3734('Security')| 2148id title body 21496 MySQL Security When configured properly, MySQL ... 2150drop procedure bug3734| 2151drop table t3| 2152drop procedure if exists bug3863| 2153create procedure bug3863() 2154begin 2155set @a = 0; 2156while @a < 5 do 2157set @a = @a + 1; 2158end while; 2159end| 2160call bug3863()| 2161select @a| 2162@a 21635 2164call bug3863()| 2165select @a| 2166@a 21675 2168drop procedure bug3863| 2169create table t3 ( 2170id int(10) unsigned not null default 0, 2171rid int(10) unsigned not null default 0, 2172msg text not null, 2173primary key (id), 2174unique key rid (rid, id) 2175)| 2176drop procedure if exists bug2460_1| 2177create procedure bug2460_1(in v int) 2178begin 2179( select n0.id from t3 as n0 where n0.id = v ) 2180union 2181( select n0.id from t3 as n0, t3 as n1 2182where n0.id = n1.rid and n1.id = v ) 2183union 2184( select n0.id from t3 as n0, t3 as n1, t3 as n2 2185where n0.id = n1.rid and n1.id = n2.rid and n2.id = v ); 2186end| 2187call bug2460_1(2)| 2188id 2189call bug2460_1(2)| 2190id 2191insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')| 2192call bug2460_1(2)| 2193id 21942 21951 2196call bug2460_1(2)| 2197id 21982 21991 2200drop procedure if exists bug2460_2| 2201create procedure bug2460_2() 2202begin 2203drop table if exists t3; 2204create temporary table t3 (s1 int); 2205insert into t3 select 1 union select 1; 2206end| 2207call bug2460_2()| 2208call bug2460_2()| 2209select * from t3| 2210s1 22111 2212drop procedure bug2460_1| 2213drop procedure bug2460_2| 2214drop table t3| 2215set @@sql_mode = ''| 2216drop procedure if exists bug2564_1| 2217create procedure bug2564_1() 2218comment 'Joe''s procedure' 2219 insert into `t1` values ("foo", 1)| 2220set @@sql_mode = 'ANSI_QUOTES'| 2221drop procedure if exists bug2564_2| 2222create procedure bug2564_2() 2223insert into "t1" values ('foo', 1)| 2224set @@sql_mode = ''$ 2225drop function if exists bug2564_3$ 2226create function bug2564_3(x int, y int) returns int 2227return x || y$ 2228set @@sql_mode = 'ANSI'$ 2229drop function if exists bug2564_4$ 2230create function bug2564_4(x int, y int) returns int 2231return x || y$ 2232set @@sql_mode = ''| 2233show create procedure bug2564_1| 2234Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 2235bug2564_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug2564_1`() 2236 COMMENT 'Joe''s procedure' 2237insert into `t1` values ("foo", 1) latin1 latin1_swedish_ci latin1_swedish_ci 2238show create procedure bug2564_2| 2239Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 2240bug2564_2 ANSI_QUOTES CREATE DEFINER="root"@"localhost" PROCEDURE "bug2564_2"() 2241insert into "t1" values ('foo', 1) latin1 latin1_swedish_ci latin1_swedish_ci 2242show create function bug2564_3| 2243Function sql_mode Create Function character_set_client collation_connection Database Collation 2244bug2564_3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug2564_3`(x int, y int) RETURNS int(11) 2245return x || y latin1 latin1_swedish_ci latin1_swedish_ci 2246show create function bug2564_4| 2247Function sql_mode Create Function character_set_client collation_connection Database Collation 2248bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI CREATE DEFINER="root"@"localhost" FUNCTION "bug2564_4"(x int, y int) RETURNS int(11) 2249return x || y latin1 latin1_swedish_ci latin1_swedish_ci 2250drop procedure bug2564_1| 2251drop procedure bug2564_2| 2252drop function bug2564_3| 2253drop function bug2564_4| 2254drop function if exists bug3132| 2255create function bug3132(s char(20)) returns char(50) 2256return concat('Hello, ', s, '!')| 2257select bug3132('Bob') union all select bug3132('Judy')| 2258bug3132('Bob') 2259Hello, Bob! 2260Hello, Judy! 2261drop function bug3132| 2262drop procedure if exists bug3843| 2263create procedure bug3843() 2264analyze table t1| 2265call bug3843()| 2266Table Op Msg_type Msg_text 2267test.t1 analyze status OK 2268call bug3843()| 2269Table Op Msg_type Msg_text 2270test.t1 analyze status Table is already up to date 2271select 1+2| 22721+2 22733 2274drop procedure bug3843| 2275create table t3 ( s1 char(10) )| 2276insert into t3 values ('a'), ('b')| 2277drop procedure if exists bug3368| 2278create procedure bug3368(v char(10)) 2279begin 2280select group_concat(v) from t3; 2281end| 2282call bug3368('x')| 2283group_concat(v) 2284x,x 2285call bug3368('yz')| 2286group_concat(v) 2287yz,yz 2288drop procedure bug3368| 2289drop table t3| 2290create table t3 (f1 int, f2 int)| 2291insert into t3 values (1,1)| 2292drop procedure if exists bug4579_1| 2293create procedure bug4579_1 () 2294begin 2295declare sf1 int; 2296select f1 into sf1 from t3 where f1=1 and f2=1; 2297update t3 set f2 = f2 + 1 where f1=1 and f2=1; 2298call bug4579_2(); 2299end| 2300drop procedure if exists bug4579_2| 2301create procedure bug4579_2 () 2302begin 2303end| 2304call bug4579_1()| 2305call bug4579_1()| 2306call bug4579_1()| 2307drop procedure bug4579_1| 2308drop procedure bug4579_2| 2309drop table t3| 2310drop procedure if exists bug2773| 2311create function bug2773() returns int return null| 2312create table t3 as select bug2773()| 2313show create table t3| 2314Table Create Table 2315t3 CREATE TABLE `t3` ( 2316 `bug2773()` int(11) DEFAULT NULL 2317) ENGINE=MyISAM DEFAULT CHARSET=latin1 2318drop table t3| 2319drop function bug2773| 2320drop procedure if exists bug3788| 2321create function bug3788() returns date return cast("2005-03-04" as date)| 2322select bug3788()| 2323bug3788() 23242005-03-04 2325drop function bug3788| 2326create function bug3788() returns binary(1) return 5| 2327select bug3788()| 2328bug3788() 23295 2330drop function bug3788| 2331create table t3 (f1 int, f2 int, f3 int)| 2332insert into t3 values (1,1,1)| 2333drop procedure if exists bug4726| 2334create procedure bug4726() 2335begin 2336declare tmp_o_id INT; 2337declare tmp_d_id INT default 1; 2338while tmp_d_id <= 2 do 2339begin 2340select f1 into tmp_o_id from t3 where f2=1 and f3=1; 2341set tmp_d_id = tmp_d_id + 1; 2342end; 2343end while; 2344end| 2345call bug4726()| 2346call bug4726()| 2347call bug4726()| 2348drop procedure bug4726| 2349drop table t3| 2350drop procedure if exists bug4902| 2351create procedure bug4902() 2352begin 2353show charset like 'foo'; 2354show collation like 'foo'; 2355show create table t1; 2356show create database test; 2357show databases like 'foo'; 2358show errors; 2359show columns from t1; 2360show keys from t1; 2361show open tables like 'foo'; 2362# Removed because result will differ in embedded mode. 2363#show privileges; 2364show status like 'foo'; 2365show tables like 'foo'; 2366show variables like 'foo'; 2367show warnings; 2368end| 2369call bug4902()| 2370Charset Description Default collation Maxlen 2371Collation Charset Id Default Compiled Sortlen 2372Table Create Table 2373t1 CREATE TABLE `t1` ( 2374 `id` char(16) NOT NULL DEFAULT '', 2375 `data` int(11) NOT NULL 2376) ENGINE=MyISAM DEFAULT CHARSET=latin1 2377Database Create Database 2378test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ 2379Database (foo) 2380Level Code Message 2381Field Type Null Key Default Extra 2382id char(16) NO 2383data int(11) NO NULL 2384Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2385Database Table In_use Name_locked 2386Variable_name Value 2387Tables_in_test (foo) 2388Variable_name Value 2389Level Code Message 2390call bug4902()| 2391Charset Description Default collation Maxlen 2392Collation Charset Id Default Compiled Sortlen 2393Table Create Table 2394t1 CREATE TABLE `t1` ( 2395 `id` char(16) NOT NULL DEFAULT '', 2396 `data` int(11) NOT NULL 2397) ENGINE=MyISAM DEFAULT CHARSET=latin1 2398Database Create Database 2399test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ 2400Database (foo) 2401Level Code Message 2402Field Type Null Key Default Extra 2403id char(16) NO 2404data int(11) NO NULL 2405Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 2406Database Table In_use Name_locked 2407Variable_name Value 2408Tables_in_test (foo) 2409Variable_name Value 2410Level Code Message 2411drop procedure bug4902| 2412drop procedure if exists bug4904| 2413create procedure bug4904() 2414begin 2415declare continue handler for sqlstate 'HY000' begin end; 2416create table t2 as select * from t3; 2417end| 2418call bug4904()| 2419ERROR 42S02: Table 'test.t3' doesn't exist 2420drop procedure bug4904| 2421create table t3 (s1 char character set latin1, s2 char character set latin2)| 2422drop procedure if exists bug4904| 2423create procedure bug4904 () 2424begin 2425declare continue handler for sqlstate 'HY000' begin end; 2426select s1 from t3 union select s2 from t3; 2427end| 2428call bug4904()| 2429drop procedure bug4904| 2430drop table t3| 2431drop procedure if exists bug336| 2432create procedure bug336(out y int) 2433begin 2434declare x int; 2435set x = (select sum(t.data) from test.t1 t); 2436set y = x; 2437end| 2438insert into t1 values ("a", 2), ("b", 3)| 2439call bug336(@y)| 2440select @y| 2441@y 24425 2443delete from t1| 2444drop procedure bug336| 2445drop procedure if exists bug3157| 2446create procedure bug3157() 2447begin 2448if exists(select * from t1) then 2449set @n= @n + 1; 2450end if; 2451if (select count(*) from t1) then 2452set @n= @n + 1; 2453end if; 2454end| 2455set @n = 0| 2456insert into t1 values ("a", 1)| 2457call bug3157()| 2458select @n| 2459@n 24602 2461delete from t1| 2462drop procedure bug3157| 2463drop procedure if exists bug5251| 2464create procedure bug5251() 2465begin 2466end| 2467select created into @c1 from mysql.proc 2468where db='test' and name='bug5251'| 2469alter procedure bug5251 comment 'foobar'| 2470select count(*) from mysql.proc 2471where db='test' and name='bug5251' and created = @c1| 2472count(*) 24731 2474drop procedure bug5251| 2475drop procedure if exists bug5251| 2476create procedure bug5251() 2477checksum table t1| 2478call bug5251()| 2479Table Checksum 2480test.t1 0 2481call bug5251()| 2482Table Checksum 2483test.t1 0 2484drop procedure bug5251| 2485drop procedure if exists bug5287| 2486create procedure bug5287(param1 int) 2487label1: 2488begin 2489declare c cursor for select 5; 2490loop 2491if param1 >= 0 then 2492leave label1; 2493end if; 2494end loop; 2495end| 2496call bug5287(1)| 2497drop procedure bug5287| 2498drop procedure if exists bug5307| 2499create procedure bug5307() 2500begin 2501end; set @x = 3| 2502call bug5307()| 2503select @x| 2504@x 25053 2506drop procedure bug5307| 2507drop procedure if exists bug5258| 2508create procedure bug5258() 2509begin 2510end| 2511drop procedure if exists bug5258_aux| 2512create procedure bug5258_aux() 2513begin 2514declare c, m char(19); 2515select created,modified into c,m from mysql.proc where name = 'bug5258'; 2516if c = m then 2517select 'Ok'; 2518else 2519select c, m; 2520end if; 2521end| 2522call bug5258_aux()| 2523Ok 2524Ok 2525drop procedure bug5258| 2526drop procedure bug5258_aux| 2527drop function if exists bug4487| 2528create function bug4487() returns char 2529begin 2530declare v char; 2531return v; 2532end| 2533select bug4487()| 2534bug4487() 2535NULL 2536drop function bug4487| 2537drop procedure if exists bug4941| 2538drop procedure if exists bug4941| 2539create procedure bug4941(out x int) 2540begin 2541declare c cursor for select i from t2 limit 1; 2542open c; 2543fetch c into x; 2544close c; 2545end| 2546insert into t2 values (null, null, null)| 2547set @x = 42| 2548call bug4941(@x)| 2549select @x| 2550@x 2551NULL 2552delete from t1| 2553drop procedure bug4941| 2554drop procedure if exists bug4905| 2555create table t3 (s1 int,primary key (s1))| 2556drop procedure if exists bug4905| 2557create procedure bug4905() 2558begin 2559declare v int; 2560declare continue handler for sqlstate '23000' set v = 5; 2561insert into t3 values (1); 2562end| 2563call bug4905()| 2564select row_count()| 2565row_count() 25661 2567call bug4905()| 2568select row_count()| 2569row_count() 25700 2571call bug4905()| 2572select row_count()| 2573row_count() 25740 2575select * from t3| 2576s1 25771 2578drop procedure bug4905| 2579drop table t3| 2580drop procedure if exists bug6029| 2581drop procedure if exists bug6029| 2582create procedure bug6029() 2583begin 2584declare exit handler for 1136 select '1136'; 2585declare exit handler for sqlstate '23000' select 'sqlstate 23000'; 2586declare continue handler for sqlexception select 'sqlexception'; 2587insert into t3 values (1); 2588insert into t3 values (1,2); 2589end| 2590create table t3 (s1 int, primary key (s1))| 2591insert into t3 values (1)| 2592call bug6029()| 2593sqlstate 23000 2594sqlstate 23000 2595delete from t3| 2596call bug6029()| 25971136 25981136 2599drop procedure bug6029| 2600drop table t3| 2601drop procedure if exists bug8540| 2602create procedure bug8540() 2603begin 2604declare x int default 1; 2605select x as y, x+0 as z; 2606end| 2607call bug8540()| 2608y z 26091 1 2610drop procedure bug8540| 2611create table t3 (s1 int)| 2612drop procedure if exists bug6642| 2613create procedure bug6642() 2614select abs(count(s1)) from t3| 2615call bug6642()| 2616abs(count(s1)) 26170 2618call bug6642()| 2619abs(count(s1)) 26200 2621drop procedure bug6642| 2622insert into t3 values (0),(1)| 2623drop procedure if exists bug7013| 2624create procedure bug7013() 2625select s1,count(s1) from t3 group by s1 with rollup| 2626call bug7013()| 2627s1 count(s1) 26280 1 26291 1 2630NULL 2 2631call bug7013()| 2632s1 count(s1) 26330 1 26341 1 2635NULL 2 2636drop procedure bug7013| 2637drop table if exists t4| 2638create table t4 ( 2639a mediumint(8) unsigned not null auto_increment, 2640b smallint(5) unsigned not null, 2641c char(32) not null, 2642primary key (a) 2643) engine=myisam default charset=latin1| 2644insert into t4 values (1, 2, 'oneword')| 2645insert into t4 values (2, 2, 'anotherword')| 2646drop procedure if exists bug7743| 2647create procedure bug7743 ( searchstring char(28) ) 2648begin 2649declare var mediumint(8) unsigned; 2650select a into var from t4 where b = 2 and c = binary searchstring limit 1; 2651show warnings; 2652select var; 2653end| 2654call bug7743("oneword")| 2655Level Code Message 2656var 26571 2658call bug7743("OneWord")| 2659Level Code Message 2660Warning 1329 No data - zero rows fetched, selected, or processed 2661var 2662NULL 2663call bug7743("anotherword")| 2664Level Code Message 2665var 26662 2667call bug7743("AnotherWord")| 2668Level Code Message 2669Warning 1329 No data - zero rows fetched, selected, or processed 2670var 2671NULL 2672drop procedure bug7743| 2673drop table t4| 2674delete from t3| 2675insert into t3 values(1)| 2676drop procedure if exists bug7992_1| 2677Warnings: 2678Note 1305 PROCEDURE test.bug7992_1 does not exist 2679drop procedure if exists bug7992_2| 2680Warnings: 2681Note 1305 PROCEDURE test.bug7992_2 does not exist 2682create procedure bug7992_1() 2683begin 2684declare i int; 2685select max(s1)+1 into i from t3; 2686end| 2687create procedure bug7992_2() 2688insert into t3 (s1) select max(t4.s1)+1 from t3 as t4| 2689call bug7992_1()| 2690call bug7992_1()| 2691call bug7992_2()| 2692call bug7992_2()| 2693drop procedure bug7992_1| 2694drop procedure bug7992_2| 2695drop table t3| 2696create table t3 ( userid bigint(20) not null default 0 )| 2697drop procedure if exists bug8116| 2698create procedure bug8116(in _userid int) 2699select * from t3 where userid = _userid| 2700call bug8116(42)| 2701userid 2702call bug8116(42)| 2703userid 2704drop procedure bug8116| 2705drop table t3| 2706drop procedure if exists bug6857| 2707create procedure bug6857() 2708begin 2709declare t0, t1 int; 2710declare plus bool default 0; 2711set t0 = unix_timestamp(); 2712select sleep(1.1); 2713set t1 = unix_timestamp(); 2714if t1 > t0 then 2715set plus = 1; 2716end if; 2717select plus; 2718end| 2719call bug6857()| 2720sleep(1.1) 27210 2722plus 27231 2724drop procedure bug6857| 2725drop procedure if exists bug8757| 2726create procedure bug8757() 2727begin 2728declare x int; 2729declare c1 cursor for select data from t1 limit 1; 2730begin 2731declare y int; 2732declare c2 cursor for select i from t2 limit 1; 2733open c2; 2734fetch c2 into y; 2735close c2; 2736select 2,y; 2737end; 2738open c1; 2739fetch c1 into x; 2740close c1; 2741select 1,x; 2742end| 2743delete from t1| 2744delete from t2| 2745insert into t1 values ("x", 1)| 2746insert into t2 values ("y", 2, 0.0)| 2747call bug8757()| 27482 y 27492 2 27501 x 27511 1 2752delete from t1| 2753delete from t2| 2754drop procedure bug8757| 2755drop procedure if exists bug8762| 2756drop procedure if exists bug8762; create procedure bug8762() begin end| 2757drop procedure if exists bug8762; create procedure bug8762() begin end| 2758drop procedure bug8762| 2759drop function if exists bug5240| 2760create function bug5240 () returns int 2761begin 2762declare x int; 2763declare c cursor for select data from t1 limit 1; 2764open c; 2765fetch c into x; 2766close c; 2767return x; 2768end| 2769delete from t1| 2770insert into t1 values ("answer", 42)| 2771select id, bug5240() from t1| 2772id bug5240() 2773answer 42 2774drop function bug5240| 2775drop procedure if exists p1| 2776create table t3(id int)| 2777insert into t3 values(1)| 2778create procedure bug7992() 2779begin 2780declare i int; 2781select max(id)+1 into i from t3; 2782end| 2783call bug7992()| 2784call bug7992()| 2785drop procedure bug7992| 2786drop table t3| 2787create table t3 ( 2788lpitnumber int(11) default null, 2789lrecordtype int(11) default null 2790)| 2791create table t4 ( 2792lbsiid int(11) not null default '0', 2793ltradingmodeid int(11) not null default '0', 2794ltradingareaid int(11) not null default '0', 2795csellingprice decimal(19,4) default null, 2796primary key (lbsiid,ltradingmodeid,ltradingareaid) 2797)| 2798create table t5 ( 2799lbsiid int(11) not null default '0', 2800ltradingareaid int(11) not null default '0', 2801primary key (lbsiid,ltradingareaid) 2802)| 2803drop procedure if exists bug8849| 2804create procedure bug8849() 2805begin 2806insert into t5 2807( 2808t5.lbsiid, 2809t5.ltradingareaid 2810) 2811select distinct t3.lpitnumber, t4.ltradingareaid 2812from 2813t4 join t3 on 2814t3.lpitnumber = t4.lbsiid 2815and t3.lrecordtype = 1 2816left join t4 as price01 on 2817price01.lbsiid = t4.lbsiid and 2818price01.ltradingmodeid = 1 and 2819t4.ltradingareaid = price01.ltradingareaid; 2820end| 2821call bug8849()| 2822call bug8849()| 2823call bug8849()| 2824drop procedure bug8849| 2825drop tables t3,t4,t5| 2826drop procedure if exists bug8937| 2827create procedure bug8937() 2828begin 2829declare s,x,y,z int; 2830declare a float; 2831select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1; 2832select s,x,y,z; 2833select avg(data) into a from t1; 2834select a; 2835end| 2836delete from t1| 2837insert into t1 (data) values (1), (2), (3), (4), (6)| 2838call bug8937()| 2839s x y z 284016 3 1 6 2841a 28423.2 2843drop procedure bug8937| 2844delete from t1| 2845drop procedure if exists bug6900| 2846drop procedure if exists bug9074| 2847drop procedure if exists bug6900_9074| 2848create table t3 (w char unique, x char)| 2849insert into t3 values ('a', 'b')| 2850create procedure bug6900() 2851begin 2852declare exit handler for sqlexception select '1'; 2853begin 2854declare exit handler for sqlexception select '2'; 2855insert into t3 values ('x', 'y', 'z'); 2856end; 2857end| 2858create procedure bug9074() 2859begin 2860declare x1, x2, x3, x4, x5, x6 int default 0; 2861begin 2862declare continue handler for sqlstate '23000' set x5 = 1; 2863insert into t3 values ('a', 'b'); 2864set x6 = 1; 2865end; 2866begin1_label: 2867begin 2868declare continue handler for sqlstate '23000' set x1 = 1; 2869insert into t3 values ('a', 'b'); 2870set x2 = 1; 2871begin2_label: 2872begin 2873declare exit handler for sqlstate '23000' set x3 = 1; 2874set x4= 1; 2875insert into t3 values ('a','b'); 2876set x4= 0; 2877end begin2_label; 2878end begin1_label; 2879select x1, x2, x3, x4, x5, x6; 2880end| 2881create procedure bug6900_9074(z int) 2882begin 2883declare exit handler for sqlstate '23000' select '23000'; 2884begin 2885declare exit handler for sqlexception select 'sqlexception'; 2886if z = 1 then 2887insert into t3 values ('a', 'b'); 2888else 2889insert into t3 values ('x', 'y', 'z'); 2890end if; 2891end; 2892end| 2893call bug6900()| 28942 28952 2896call bug9074()| 2897x1 x2 x3 x4 x5 x6 28981 1 1 1 1 1 2899call bug6900_9074(0)| 2900sqlexception 2901sqlexception 2902call bug6900_9074(1)| 2903sqlexception 2904sqlexception 2905drop procedure bug6900| 2906drop procedure bug9074| 2907drop procedure bug6900_9074| 2908drop table t3| 2909drop procedure if exists avg| 2910create procedure avg () 2911begin 2912end| 2913call avg ()| 2914drop procedure avg| 2915drop procedure if exists bug6129| 2916set @old_mode= @@sql_mode; 2917set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO"; 2918create procedure bug6129() 2919select @@sql_mode| 2920call bug6129()| 2921@@sql_mode 2922ERROR_FOR_DIVISION_BY_ZERO 2923set @@sql_mode= "STRICT_ALL_TABLES,NO_ZERO_DATE"| 2924Warnings: 2925Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. 2926call bug6129()| 2927@@sql_mode 2928ERROR_FOR_DIVISION_BY_ZERO 2929set @@sql_mode= "NO_ZERO_IN_DATE"| 2930Warnings: 2931Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. 2932call bug6129()| 2933@@sql_mode 2934ERROR_FOR_DIVISION_BY_ZERO 2935set @@sql_mode=@old_mode; 2936drop procedure bug6129| 2937drop procedure if exists bug9856| 2938create procedure bug9856() 2939begin 2940declare v int; 2941declare c cursor for select data from t1; 2942declare exit handler for sqlexception, not found select '16'; 2943open c; 2944fetch c into v; 2945select v; 2946end| 2947delete from t1| 2948call bug9856()| 294916 295016 2951call bug9856()| 295216 295316 2954drop procedure bug9856| 2955drop procedure if exists bug9674_1| 2956drop procedure if exists bug9674_2| 2957create procedure bug9674_1(out arg int) 2958begin 2959declare temp_in1 int default 0; 2960declare temp_fl1 int default 0; 2961set temp_in1 = 100; 2962set temp_fl1 = temp_in1/10; 2963set arg = temp_fl1; 2964end| 2965create procedure bug9674_2() 2966begin 2967declare v int default 100; 2968select v/10; 2969end| 2970call bug9674_1(@sptmp)| 2971call bug9674_1(@sptmp)| 2972select @sptmp| 2973@sptmp 297410 2975call bug9674_2()| 2976v/10 297710.0000 2978call bug9674_2()| 2979v/10 298010.0000 2981drop procedure bug9674_1| 2982drop procedure bug9674_2| 2983drop procedure if exists bug9598_1| 2984drop procedure if exists bug9598_2| 2985create procedure bug9598_1(in var_1 char(16), 2986out var_2 integer, out var_3 integer) 2987begin 2988set var_2 = 50; 2989set var_3 = 60; 2990end| 2991create procedure bug9598_2(in v1 char(16), 2992in v2 integer, 2993in v3 integer, 2994in v4 integer, 2995in v5 integer) 2996begin 2997select v1,v2,v3,v4,v5; 2998call bug9598_1(v1,@tmp1,@tmp2); 2999select v1,v2,v3,v4,v5; 3000end| 3001call bug9598_2('Test',2,3,4,5)| 3002v1 v2 v3 v4 v5 3003Test 2 3 4 5 3004v1 v2 v3 v4 v5 3005Test 2 3 4 5 3006select @tmp1, @tmp2| 3007@tmp1 @tmp2 300850 60 3009drop procedure bug9598_1| 3010drop procedure bug9598_2| 3011drop procedure if exists bug9902| 3012create function bug9902() returns int(11) 3013begin 3014set @x = @x + 1; 3015return @x; 3016end| 3017set @qcs1 = @@query_cache_size| 3018Warnings: 3019Warning 1287 '@@query_cache_size' is deprecated and will be removed in a future release. 3020set global query_cache_size = 102400| 3021Warnings: 3022Warning 1287 '@@query_cache_size' is deprecated and will be removed in a future release. 3023set @x = 1| 3024insert into t1 values ("qc", 42)| 3025select bug9902() from t1| 3026bug9902() 30272 3028select bug9902() from t1| 3029bug9902() 30303 3031select @x| 3032@x 30333 3034set global query_cache_size = @qcs1| 3035Warnings: 3036Warning 1287 '@@query_cache_size' is deprecated and will be removed in a future release. 3037delete from t1| 3038drop function bug9902| 3039drop function if exists bug9102| 3040create function bug9102() returns blob return 'a'| 3041select bug9102()| 3042bug9102() 3043a 3044drop function bug9102| 3045drop function if exists bug7648| 3046create function bug7648() returns bit(8) return 'a'| 3047select bug7648()| 3048bug7648() 3049a 3050drop function bug7648| 3051drop function if exists bug9775| 3052create function bug9775(v1 char(1)) returns enum('a','b') return v1| 3053select bug9775('a'),bug9775('b'),bug9775('c')| 3054bug9775('a') bug9775('b') bug9775('c') 3055a b 3056Warnings: 3057Warning 1265 Data truncated for column 'bug9775('c')' at row 1 3058drop function bug9775| 3059create function bug9775(v1 int) returns enum('a','b') return v1| 3060select bug9775(1),bug9775(2),bug9775(3)| 3061bug9775(1) bug9775(2) bug9775(3) 3062a b 3063Warnings: 3064Warning 1265 Data truncated for column 'bug9775(3)' at row 1 3065drop function bug9775| 3066create function bug9775(v1 char(1)) returns set('a','b') return v1| 3067select bug9775('a'),bug9775('b'),bug9775('a,b'),bug9775('c')| 3068bug9775('a') bug9775('b') bug9775('a,b') bug9775('c') 3069a b a 3070Warnings: 3071Warning 1265 Data truncated for column 'v1' at row 1 3072Warning 1265 Data truncated for column 'bug9775('c')' at row 1 3073drop function bug9775| 3074create function bug9775(v1 int) returns set('a','b') return v1| 3075select bug9775(1),bug9775(2),bug9775(3),bug9775(4)| 3076bug9775(1) bug9775(2) bug9775(3) bug9775(4) 3077a b a,b 3078Warnings: 3079Warning 1265 Data truncated for column 'bug9775(4)' at row 1 3080drop function bug9775| 3081drop function if exists bug8861| 3082create function bug8861(v1 int) returns year return v1| 3083select bug8861(05)| 3084bug8861(05) 30852005 3086set @x = bug8861(05)| 3087select @x| 3088@x 30892005 3090drop function bug8861| 3091drop procedure if exists bug9004_1| 3092drop procedure if exists bug9004_2| 3093create procedure bug9004_1(x char(16)) 3094begin 3095insert into t1 values (x, 42); 3096insert into t1 values (x, 17); 3097end| 3098create procedure bug9004_2(x char(16)) 3099call bug9004_1(x)| 3100call bug9004_1('12345678901234567')| 3101Warnings: 3102Warning 1265 Data truncated for column 'x' at row 1 3103call bug9004_2('12345678901234567890')| 3104Warnings: 3105Warning 1265 Data truncated for column 'x' at row 1 3106delete from t1| 3107drop procedure bug9004_1| 3108drop procedure bug9004_2| 3109drop procedure if exists bug7293| 3110insert into t1 values ('secret', 0)| 3111create procedure bug7293(p1 varchar(100)) 3112begin 3113if exists (select id from t1 where soundex(p1)=soundex(id)) then 3114select 'yes'; 3115end if; 3116end;| 3117call bug7293('secret')| 3118yes 3119yes 3120call bug7293 ('secrete')| 3121yes 3122yes 3123drop procedure bug7293| 3124delete from t1| 3125drop procedure if exists bug9841| 3126drop view if exists v1| 3127create view v1 as select * from t1, t2 where id = s| 3128create procedure bug9841 () 3129update v1 set data = 10| 3130call bug9841()| 3131drop view v1| 3132drop procedure bug9841| 3133drop procedure if exists bug5963| 3134create procedure bug5963_1 () begin declare v int; set v = (select s1 from t3); select v; end;| 3135create table t3 (s1 int)| 3136insert into t3 values (5)| 3137call bug5963_1()| 3138v 31395 3140call bug5963_1()| 3141v 31425 3143drop procedure bug5963_1| 3144drop table t3| 3145create procedure bug5963_2 (cfk_value int) 3146begin 3147if cfk_value in (select cpk from t3) then 3148set @x = 5; 3149end if; 3150end; 3151| 3152create table t3 (cpk int)| 3153insert into t3 values (1)| 3154call bug5963_2(1)| 3155call bug5963_2(1)| 3156drop procedure bug5963_2| 3157drop table t3| 3158drop function if exists bug9559| 3159create function bug9559() 3160returns int 3161begin 3162set @y = -6/2; 3163return @y; 3164end| 3165select bug9559()| 3166bug9559() 3167-3 3168drop function bug9559| 3169drop procedure if exists bug10961| 3170create procedure bug10961() 3171begin 3172declare v char; 3173declare x int; 3174declare c cursor for select * from dual; 3175declare continue handler for sqlexception select x; 3176set x = 1; 3177open c; 3178set x = 2; 3179fetch c into v; 3180set x = 3; 3181close c; 3182end| 3183call bug10961()| 3184x 31851 3186x 31872 3188x 31893 3190call bug10961()| 3191x 31921 3193x 31942 3195x 31963 3197drop procedure bug10961| 3198DROP PROCEDURE IF EXISTS bug6866| 3199DROP VIEW IF EXISTS tv| 3200Warnings: 3201Note 1051 Unknown table 'test.tv' 3202DROP TABLE IF EXISTS tt1,tt2,tt3| 3203Warnings: 3204Note 1051 Unknown table 'test.tt1' 3205Note 1051 Unknown table 'test.tt2' 3206Note 1051 Unknown table 'test.tt3' 3207CREATE TABLE tt1 (a1 int, a2 int, a3 int, data varchar(10))| 3208CREATE TABLE tt2 (a2 int, data2 varchar(10))| 3209CREATE TABLE tt3 (a3 int, data3 varchar(10))| 3210INSERT INTO tt1 VALUES (1, 1, 4, 'xx')| 3211INSERT INTO tt2 VALUES (1, 'a')| 3212INSERT INTO tt2 VALUES (2, 'b')| 3213INSERT INTO tt2 VALUES (3, 'c')| 3214INSERT INTO tt3 VALUES (4, 'd')| 3215INSERT INTO tt3 VALUES (5, 'e')| 3216INSERT INTO tt3 VALUES (6, 'f')| 3217CREATE VIEW tv AS 3218SELECT tt1.*, tt2.data2, tt3.data3 3219FROM tt1 INNER JOIN tt2 ON tt1.a2 = tt2.a2 3220LEFT JOIN tt3 ON tt1.a3 = tt3.a3 3221ORDER BY tt1.a1, tt2.a2, tt3.a3| 3222CREATE PROCEDURE bug6866 (_a1 int) 3223BEGIN 3224SELECT * FROM tv WHERE a1 = _a1; 3225END| 3226CALL bug6866(1)| 3227a1 a2 a3 data data2 data3 32281 1 4 xx a d 3229CALL bug6866(1)| 3230a1 a2 a3 data data2 data3 32311 1 4 xx a d 3232CALL bug6866(1)| 3233a1 a2 a3 data data2 data3 32341 1 4 xx a d 3235DROP PROCEDURE bug6866; 3236DROP VIEW tv| 3237DROP TABLE tt1, tt2, tt3| 3238DROP PROCEDURE IF EXISTS bug10136| 3239create table t3 ( name char(5) not null primary key, val float not null)| 3240insert into t3 values ('aaaaa', 1), ('bbbbb', 2), ('ccccc', 3)| 3241create procedure bug10136() 3242begin 3243declare done int default 3; 3244repeat 3245select * from t3; 3246set done = done - 1; 3247until done <= 0 end repeat; 3248end| 3249call bug10136()| 3250name val 3251aaaaa 1 3252bbbbb 2 3253ccccc 3 3254name val 3255aaaaa 1 3256bbbbb 2 3257ccccc 3 3258name val 3259aaaaa 1 3260bbbbb 2 3261ccccc 3 3262call bug10136()| 3263name val 3264aaaaa 1 3265bbbbb 2 3266ccccc 3 3267name val 3268aaaaa 1 3269bbbbb 2 3270ccccc 3 3271name val 3272aaaaa 1 3273bbbbb 2 3274ccccc 3 3275call bug10136()| 3276name val 3277aaaaa 1 3278bbbbb 2 3279ccccc 3 3280name val 3281aaaaa 1 3282bbbbb 2 3283ccccc 3 3284name val 3285aaaaa 1 3286bbbbb 2 3287ccccc 3 3288drop procedure bug10136| 3289drop table t3| 3290drop procedure if exists bug11529| 3291create procedure bug11529() 3292begin 3293declare c cursor for select id, data from t1 where data in (10,13); 3294open c; 3295begin 3296declare vid char(16); 3297declare vdata int; 3298declare exit handler for not found begin end; 3299while true do 3300fetch c into vid, vdata; 3301end while; 3302end; 3303close c; 3304end| 3305insert into t1 values 3306('Name1', 10), 3307('Name2', 11), 3308('Name3', 12), 3309('Name4', 13), 3310('Name5', 14)| 3311call bug11529()| 3312call bug11529()| 3313delete from t1| 3314drop procedure bug11529| 3315set character set utf8| 3316drop procedure if exists bug6063| 3317drop procedure if exists bug7088_1| 3318drop procedure if exists bug7088_2| 3319create procedure bug6063() 3320begin 3321lâbel: begin end; 3322label: begin end; 3323label1: begin end; 3324end| 3325create procedure bug7088_1() 3326label1: begin end label1| 3327create procedure bug7088_2() 3328läbel1: begin end| 3329call bug6063()| 3330call bug7088_1()| 3331call bug7088_2()| 3332set character set default| 3333show create procedure bug6063| 3334Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 3335bug6063 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug6063`() 3336begin 3337l�bel: begin end; 3338label: begin end; 3339label1: begin end; 3340end utf8 latin1_swedish_ci latin1_swedish_ci 3341show create procedure bug7088_1| 3342Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 3343bug7088_1 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug7088_1`() 3344label1: begin end label1 utf8 latin1_swedish_ci latin1_swedish_ci 3345show create procedure bug7088_2| 3346Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 3347bug7088_2 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug7088_2`() 3348l�bel1: begin end utf8 latin1_swedish_ci latin1_swedish_ci 3349drop procedure bug6063| 3350drop procedure bug7088_1| 3351drop procedure bug7088_2| 3352drop procedure if exists bug9565_sub| 3353drop procedure if exists bug9565| 3354create procedure bug9565_sub() 3355begin 3356select * from t1; 3357end| 3358create procedure bug9565() 3359begin 3360insert into t1 values ("one", 1); 3361call bug9565_sub(); 3362end| 3363call bug9565()| 3364id data 3365one 1 3366delete from t1| 3367drop procedure bug9565_sub| 3368drop procedure bug9565| 3369drop procedure if exists bug9538| 3370create procedure bug9538() 3371set @@sort_buffer_size = 1000000| 3372set @x = @@sort_buffer_size| 3373set @@sort_buffer_size = 2000000| 3374select @@sort_buffer_size| 3375@@sort_buffer_size 33762000000 3377call bug9538()| 3378select @@sort_buffer_size| 3379@@sort_buffer_size 33801000000 3381set @@sort_buffer_size = @x| 3382drop procedure bug9538| 3383drop procedure if exists bug8692| 3384create table t3 (c1 varchar(5), c2 char(5), c3 enum('one','two'), c4 text, c5 blob, c6 char(5), c7 varchar(5))| 3385insert into t3 values ('', '', '', '', '', '', NULL)| 3386Warnings: 3387Warning 1265 Data truncated for column 'c3' at row 1 3388create procedure bug8692() 3389begin 3390declare v1 VARCHAR(10); 3391declare v2 VARCHAR(10); 3392declare v3 VARCHAR(10); 3393declare v4 VARCHAR(10); 3394declare v5 VARCHAR(10); 3395declare v6 VARCHAR(10); 3396declare v7 VARCHAR(10); 3397declare c8692 cursor for select c1,c2,c3,c4,c5,c6,c7 from t3; 3398open c8692; 3399fetch c8692 into v1,v2,v3,v4,v5,v6,v7; 3400select v1, v2, v3, v4, v5, v6, v7; 3401end| 3402call bug8692()| 3403v1 v2 v3 v4 v5 v6 v7 3404 NULL 3405drop procedure bug8692| 3406drop table t3| 3407drop function if exists bug10055| 3408create function bug10055(v char(255)) returns char(255) return lower(v)| 3409select t.column_name, bug10055(t.column_name) 3410from information_schema.columns as t 3411where t.table_schema = 'test' and t.table_name = 't1'| 3412column_name bug10055(t.column_name) 3413id id 3414data data 3415drop function bug10055| 3416drop procedure if exists bug12297| 3417create procedure bug12297(lim int) 3418begin 3419set @x = 0; 3420repeat 3421insert into t1(id,data) 3422values('aa', @x); 3423set @x = @x + 1; 3424until @x >= lim 3425end repeat; 3426end| 3427call bug12297(10)| 3428drop procedure bug12297| 3429drop function if exists f_bug11247| 3430drop procedure if exists p_bug11247| 3431create function f_bug11247(param int) 3432returns int 3433return param + 1| 3434create procedure p_bug11247(lim int) 3435begin 3436declare v int default 0; 3437while v < lim do 3438set v= f_bug11247(v); 3439end while; 3440end| 3441call p_bug11247(10)| 3442drop function f_bug11247| 3443drop procedure p_bug11247| 3444drop procedure if exists bug12168| 3445drop table if exists t3, t4| 3446create table t3 (a int)| 3447insert into t3 values (1),(2),(3),(4)| 3448create table t4 (a int)| 3449create procedure bug12168(arg1 char(1)) 3450begin 3451declare b, c integer; 3452if arg1 = 'a' then 3453begin 3454declare c1 cursor for select a from t3 where a % 2; 3455declare continue handler for not found set b = 1; 3456set b = 0; 3457open c1; 3458c1_repeat: repeat 3459fetch c1 into c; 3460if (b = 1) then 3461leave c1_repeat; 3462end if; 3463insert into t4 values (c); 3464until b = 1 3465end repeat; 3466end; 3467end if; 3468if arg1 = 'b' then 3469begin 3470declare c2 cursor for select a from t3 where not a % 2; 3471declare continue handler for not found set b = 1; 3472set b = 0; 3473open c2; 3474c2_repeat: repeat 3475fetch c2 into c; 3476if (b = 1) then 3477leave c2_repeat; 3478end if; 3479insert into t4 values (c); 3480until b = 1 3481end repeat; 3482end; 3483end if; 3484end| 3485call bug12168('a')| 3486select * from t4| 3487a 34881 34893 3490truncate t4| 3491call bug12168('b')| 3492select * from t4| 3493a 34942 34954 3496truncate t4| 3497call bug12168('a')| 3498select * from t4| 3499a 35001 35013 3502truncate t4| 3503call bug12168('b')| 3504select * from t4| 3505a 35062 35074 3508truncate t4| 3509drop table t3, t4| 3510drop procedure if exists bug12168| 3511drop table if exists t3| 3512drop procedure if exists bug11333| 3513create table t3 (c1 char(128))| 3514insert into t3 values 3515('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')| 3516create procedure bug11333(i int) 3517begin 3518declare tmp varchar(128); 3519set @x = 0; 3520repeat 3521select c1 into tmp from t3 3522where c1 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'; 3523set @x = @x + 1; 3524until @x >= i 3525end repeat; 3526end| 3527call bug11333(10)| 3528drop procedure bug11333| 3529drop table t3| 3530drop function if exists bug9048| 3531create function bug9048(f1 char binary) returns char 3532begin 3533set f1= concat( 'hello', f1 ); 3534return f1; 3535end| 3536drop function bug9048| 3537create function bug9048(f1 char binary) returns char binary 3538begin 3539set f1= concat( 'hello', f1 ); 3540return f1; 3541end| 3542ERROR 42000: This version of MySQL doesn't yet support 'return value collation' 3543drop procedure if exists bug12849_1| 3544create procedure bug12849_1(inout x char) select x into x| 3545set @var='a'| 3546call bug12849_1(@var)| 3547select @var| 3548@var 3549a 3550drop procedure bug12849_1| 3551drop procedure if exists bug12849_2| 3552create procedure bug12849_2(inout foo varchar(15)) 3553begin 3554select concat(foo, foo) INTO foo; 3555end| 3556set @var='abcd'| 3557call bug12849_2(@var)| 3558select @var| 3559@var 3560abcdabcd 3561drop procedure bug12849_2| 3562drop procedure if exists bug131333| 3563drop function if exists bug131333| 3564create procedure bug131333() 3565begin 3566begin 3567declare a int; 3568select a; 3569set a = 1; 3570select a; 3571end; 3572begin 3573declare b int; 3574select b; 3575end; 3576end| 3577create function bug131333() 3578returns int 3579begin 3580begin 3581declare a int; 3582set a = 1; 3583end; 3584begin 3585declare b int; 3586return b; 3587end; 3588end| 3589call bug131333()| 3590a 3591NULL 3592a 35931 3594b 3595NULL 3596select bug131333()| 3597bug131333() 3598NULL 3599drop procedure bug131333| 3600drop function bug131333| 3601drop function if exists bug12379| 3602drop procedure if exists bug12379_1| 3603drop procedure if exists bug12379_2| 3604drop procedure if exists bug12379_3| 3605drop table if exists t3| 3606create table t3 (c1 char(1) primary key not null)| 3607create function bug12379() 3608returns integer 3609begin 3610insert into t3 values('X'); 3611insert into t3 values('X'); 3612return 0; 3613end| 3614create procedure bug12379_1() 3615begin 3616declare exit handler for sqlexception select 42; 3617select bug12379(); 3618END| 3619create procedure bug12379_2() 3620begin 3621declare exit handler for sqlexception begin end; 3622select bug12379(); 3623end| 3624create procedure bug12379_3() 3625begin 3626select bug12379(); 3627end| 3628select bug12379()| 3629ERROR 23000: Duplicate entry 'X' for key 'PRIMARY' 3630select 1| 36311 36321 3633call bug12379_1()| 3634bug12379() 363542 363642 3637select 2| 36382 36392 3640call bug12379_2()| 3641bug12379() 3642select 3| 36433 36443 3645call bug12379_3()| 3646ERROR 23000: Duplicate entry 'X' for key 'PRIMARY' 3647select 4| 36484 36494 3650drop function bug12379| 3651drop procedure bug12379_1| 3652drop procedure bug12379_2| 3653drop procedure bug12379_3| 3654drop table t3| 3655drop procedure if exists bug13124| 3656create procedure bug13124() 3657begin 3658declare y integer; 3659set @x=y; 3660end| 3661call bug13124()| 3662drop procedure bug13124| 3663drop procedure if exists bug12979_1| 3664create procedure bug12979_1(inout d decimal(5)) set d = d / 2| 3665set @bug12979_user_var = NULL| 3666call bug12979_1(@bug12979_user_var)| 3667drop procedure bug12979_1| 3668drop procedure if exists bug12979_2| 3669create procedure bug12979_2() 3670begin 3671declare internal_var decimal(5); 3672set internal_var= internal_var / 2; 3673select internal_var; 3674end| 3675call bug12979_2()| 3676internal_var 3677NULL 3678drop procedure bug12979_2| 3679drop table if exists t3| 3680drop procedure if exists bug6127| 3681create table t3 (s1 int unique)| 3682set @sm=@@sql_mode| 3683set sql_mode='traditional'| 3684Warnings: 3685Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 3686create procedure bug6127() 3687begin 3688declare continue handler for sqlstate '23000' 3689 begin 3690declare continue handler for sqlstate '22003' 3691 insert into t3 values (0); 3692insert into t3 values (1000000000000000); 3693end; 3694insert into t3 values (1); 3695insert into t3 values (1); 3696end| 3697call bug6127()| 3698select * from t3| 3699s1 37000 37011 3702call bug6127()| 3703ERROR 23000: Duplicate entry '0' for key 's1' 3704select * from t3| 3705s1 37060 37071 3708set sql_mode=@sm| 3709Warnings: 3710Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 3711drop table t3| 3712drop procedure bug6127| 3713drop procedure if exists bug12589_1| 3714drop procedure if exists bug12589_2| 3715drop procedure if exists bug12589_3| 3716create procedure bug12589_1() 3717begin 3718declare spv1 decimal(3,3); 3719set spv1= 123.456; 3720set spv1 = 'test'; 3721create temporary table tm1 as select spv1; 3722show create table tm1; 3723drop temporary table tm1; 3724end| 3725create procedure bug12589_2() 3726begin 3727declare spv1 decimal(6,3); 3728set spv1= 123.456; 3729create temporary table tm1 as select spv1; 3730show create table tm1; 3731drop temporary table tm1; 3732end| 3733create procedure bug12589_3() 3734begin 3735declare spv1 decimal(6,3); 3736set spv1= -123.456; 3737create temporary table tm1 as select spv1; 3738show create table tm1; 3739drop temporary table tm1; 3740end| 3741call bug12589_1()| 3742Table Create Table 3743tm1 CREATE TEMPORARY TABLE `tm1` ( 3744 `spv1` decimal(3,3) DEFAULT NULL 3745) ENGINE=MyISAM DEFAULT CHARSET=latin1 3746call bug12589_2()| 3747Table Create Table 3748tm1 CREATE TEMPORARY TABLE `tm1` ( 3749 `spv1` decimal(6,3) DEFAULT NULL 3750) ENGINE=MyISAM DEFAULT CHARSET=latin1 3751call bug12589_3()| 3752Table Create Table 3753tm1 CREATE TEMPORARY TABLE `tm1` ( 3754 `spv1` decimal(6,3) DEFAULT NULL 3755) ENGINE=MyISAM DEFAULT CHARSET=latin1 3756drop procedure bug12589_1| 3757drop procedure bug12589_2| 3758drop procedure bug12589_3| 3759drop table if exists t3| 3760drop procedure if exists bug7049_1| 3761drop procedure if exists bug7049_2| 3762drop procedure if exists bug7049_3| 3763drop procedure if exists bug7049_4| 3764drop function if exists bug7049_1| 3765drop function if exists bug7049_2| 3766create table t3 ( x int unique )| 3767create procedure bug7049_1() 3768begin 3769insert into t3 values (42); 3770insert into t3 values (42); 3771end| 3772create procedure bug7049_2() 3773begin 3774declare exit handler for sqlexception 3775select 'Caught it' as 'Result'; 3776call bug7049_1(); 3777select 'Missed it' as 'Result'; 3778end| 3779create procedure bug7049_3() 3780call bug7049_1()| 3781create procedure bug7049_4() 3782begin 3783declare exit handler for sqlexception 3784select 'Caught it' as 'Result'; 3785call bug7049_3(); 3786select 'Missed it' as 'Result'; 3787end| 3788create function bug7049_1() 3789returns int 3790begin 3791insert into t3 values (42); 3792insert into t3 values (42); 3793return 42; 3794end| 3795create function bug7049_2() 3796returns int 3797begin 3798declare x int default 0; 3799declare continue handler for sqlexception 3800set x = 1; 3801set x = bug7049_1(); 3802return x; 3803end| 3804call bug7049_2()| 3805Result 3806Caught it 3807select * from t3| 3808x 380942 3810delete from t3| 3811call bug7049_4()| 3812Result 3813Caught it 3814select * from t3| 3815x 381642 3817select bug7049_2()| 3818bug7049_2() 38191 3820drop table t3| 3821drop procedure bug7049_1| 3822drop procedure bug7049_2| 3823drop procedure bug7049_3| 3824drop procedure bug7049_4| 3825drop function bug7049_1| 3826drop function bug7049_2| 3827drop function if exists bug13941| 3828drop procedure if exists bug13941| 3829create function bug13941(p_input_str text) 3830returns text 3831begin 3832declare p_output_str text; 3833set p_output_str = p_input_str; 3834set p_output_str = replace(p_output_str, 'xyzzy', 'plugh'); 3835set p_output_str = replace(p_output_str, 'test', 'prova'); 3836set p_output_str = replace(p_output_str, 'this', 'questo'); 3837set p_output_str = replace(p_output_str, ' a ', 'una '); 3838set p_output_str = replace(p_output_str, 'is', ''); 3839return p_output_str; 3840end| 3841create procedure bug13941(out sout varchar(128)) 3842begin 3843set sout = 'Local'; 3844set sout = ifnull(sout, 'DEF'); 3845end| 3846select bug13941('this is a test')| 3847bug13941('this is a test') 3848questo una prova 3849call bug13941(@a)| 3850select @a| 3851@a 3852Local 3853drop function bug13941| 3854drop procedure bug13941| 3855DROP PROCEDURE IF EXISTS bug13095; 3856DROP TABLE IF EXISTS bug13095_t1; 3857DROP VIEW IF EXISTS bug13095_v1; 3858CREATE PROCEDURE bug13095(tbl_name varchar(32)) 3859BEGIN 3860SET @str = 3861CONCAT("CREATE TABLE ", tbl_name, "(stuff char(15))"); 3862SELECT @str; 3863PREPARE stmt FROM @str; 3864EXECUTE stmt; 3865SET @str = 3866CONCAT("INSERT INTO ", tbl_name, " VALUES('row1'),('row2'),('row3')" ); 3867SELECT @str; 3868PREPARE stmt FROM @str; 3869EXECUTE stmt; 3870SET @str = 3871CONCAT("CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM ", tbl_name); 3872SELECT @str; 3873PREPARE stmt FROM @str; 3874EXECUTE stmt; 3875SELECT * FROM bug13095_v1; 3876SET @str = 3877"DROP VIEW bug13095_v1"; 3878SELECT @str; 3879PREPARE stmt FROM @str; 3880EXECUTE stmt; 3881END| 3882CALL bug13095('bug13095_t1'); 3883@str 3884CREATE TABLE bug13095_t1(stuff char(15)) 3885@str 3886INSERT INTO bug13095_t1 VALUES('row1'),('row2'),('row3') 3887@str 3888CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM bug13095_t1 3889c1 3890row1 3891row2 3892row3 3893@str 3894DROP VIEW bug13095_v1 3895DROP PROCEDURE IF EXISTS bug13095; 3896DROP VIEW IF EXISTS bug13095_v1; 3897DROP TABLE IF EXISTS bug13095_t1; 3898drop function if exists bug14723| 3899drop procedure if exists bug14723| 3900/*!50003 create function bug14723() 3901returns bigint(20) 3902main_loop: begin 3903return 42; 3904end */;; 3905show create function bug14723;; 3906Function sql_mode Create Function character_set_client collation_connection Database Collation 3907bug14723 CREATE DEFINER=`root`@`localhost` FUNCTION `bug14723`() RETURNS bigint(20) 3908main_loop: begin 3909return 42; 3910end latin1 latin1_swedish_ci latin1_swedish_ci 3911select bug14723();; 3912bug14723() 391342 3914/*!50003 create procedure bug14723() 3915main_loop: begin 3916select 42; 3917end */;; 3918show create procedure bug14723;; 3919Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 3920bug14723 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug14723`() 3921main_loop: begin 3922select 42; 3923end latin1 latin1_swedish_ci latin1_swedish_ci 3924call bug14723();; 392542 392642 3927drop function bug14723| 3928drop procedure bug14723| 3929create procedure bug14845() 3930begin 3931declare a char(255); 3932declare done int default 0; 3933declare c cursor for select count(*) from t1 where 1 = 0; 3934declare continue handler for sqlstate '02000' set done = 1; 3935open c; 3936repeat 3937fetch c into a; 3938if not done then 3939select a; 3940end if; 3941until done end repeat; 3942close c; 3943end| 3944call bug14845()| 3945a 39460 3947drop procedure bug14845| 3948drop procedure if exists bug13549_1| 3949drop procedure if exists bug13549_2| 3950CREATE PROCEDURE `bug13549_2`() 3951begin 3952call bug13549_1(); 3953end| 3954CREATE PROCEDURE `bug13549_1`() 3955begin 3956declare done int default 0; 3957set done= not done; 3958end| 3959CALL bug13549_2()| 3960drop procedure bug13549_2| 3961drop procedure bug13549_1| 3962drop function if exists bug10100f| 3963drop procedure if exists bug10100p| 3964drop procedure if exists bug10100t| 3965drop procedure if exists bug10100pt| 3966drop procedure if exists bug10100pv| 3967drop procedure if exists bug10100pd| 3968drop procedure if exists bug10100pc| 3969create function bug10100f(prm int) returns int 3970begin 3971if prm > 1 then 3972return prm * bug10100f(prm - 1); 3973end if; 3974return 1; 3975end| 3976create procedure bug10100p(prm int, inout res int) 3977begin 3978set res = res * prm; 3979if prm > 1 then 3980call bug10100p(prm - 1, res); 3981end if; 3982end| 3983create procedure bug10100t(prm int) 3984begin 3985declare res int; 3986set res = 1; 3987call bug10100p(prm, res); 3988select res; 3989end| 3990create table t3 (a int)| 3991insert into t3 values (0)| 3992create view v1 as select a from t3| 3993create procedure bug10100pt(level int, lim int) 3994begin 3995if level < lim then 3996update t3 set a=level; 3997FLUSH TABLES; 3998call bug10100pt(level+1, lim); 3999else 4000select * from t3; 4001end if; 4002end| 4003create procedure bug10100pv(level int, lim int) 4004begin 4005if level < lim then 4006update v1 set a=level; 4007FLUSH TABLES; 4008call bug10100pv(level+1, lim); 4009else 4010select * from v1; 4011end if; 4012end| 4013prepare stmt2 from "select * from t3;"| 4014create procedure bug10100pd(level int, lim int) 4015begin 4016if level < lim then 4017select level; 4018prepare stmt1 from "update t3 set a=a+2"; 4019execute stmt1; 4020FLUSH TABLES; 4021execute stmt1; 4022FLUSH TABLES; 4023execute stmt1; 4024FLUSH TABLES; 4025deallocate prepare stmt1; 4026execute stmt2; 4027select * from t3; 4028call bug10100pd(level+1, lim); 4029else 4030execute stmt2; 4031end if; 4032end| 4033create procedure bug10100pc(level int, lim int) 4034begin 4035declare lv int; 4036declare c cursor for select a from t3; 4037open c; 4038if level < lim then 4039select level; 4040fetch c into lv; 4041select lv; 4042update t3 set a=level+lv; 4043FLUSH TABLES; 4044call bug10100pc(level+1, lim); 4045else 4046select * from t3; 4047end if; 4048close c; 4049end| 4050set @@max_sp_recursion_depth=4| 4051select @@max_sp_recursion_depth| 4052@@max_sp_recursion_depth 40534 4054select bug10100f(3)| 4055ERROR HY000: Recursive stored functions and triggers are not allowed. 4056select bug10100f(6)| 4057ERROR HY000: Recursive stored functions and triggers are not allowed. 4058call bug10100t(5)| 4059res 4060120 4061call bug10100pt(1,5)| 4062a 40634 4064call bug10100pv(1,5)| 4065a 40664 4067update t3 set a=1| 4068call bug10100pd(1,5)| 4069level 40701 4071a 40727 4073a 40747 4075level 40762 4077a 407813 4079a 408013 4081level 40823 4083a 408419 4085a 408619 4087level 40884 4089a 409025 4091a 409225 4093a 409425 4095select * from t3| 4096a 409725 4098update t3 set a=1| 4099call bug10100pc(1,5)| 4100level 41011 4102lv 41031 4104level 41052 4106lv 41072 4108level 41093 4110lv 41114 4112level 41134 4114lv 41157 4116a 411711 4118select * from t3| 4119a 412011 4121set @@max_sp_recursion_depth=0| 4122select @@max_sp_recursion_depth| 4123@@max_sp_recursion_depth 41240 4125select bug10100f(5)| 4126ERROR HY000: Recursive stored functions and triggers are not allowed. 4127call bug10100t(5)| 4128ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug10100p 4129deallocate prepare stmt2| 4130drop function bug10100f| 4131drop procedure bug10100p| 4132drop procedure bug10100t| 4133drop procedure bug10100pt| 4134drop procedure bug10100pv| 4135drop procedure bug10100pd| 4136drop procedure bug10100pc| 4137drop view v1| 4138drop procedure if exists bug13729| 4139drop table if exists t3| 4140create table t3 (s1 int, primary key (s1))| 4141insert into t3 values (1),(2)| 4142create procedure bug13729() 4143begin 4144declare continue handler for sqlexception select 55; 4145update t3 set s1 = 1; 4146end| 4147call bug13729()| 414855 414955 4150select * from t3| 4151s1 41521 41532 4154drop procedure bug13729| 4155drop table t3| 4156drop procedure if exists bug14643_1| 4157drop procedure if exists bug14643_2| 4158create procedure bug14643_1() 4159begin 4160declare continue handler for sqlexception select 'boo' as 'Handler'; 4161begin 4162declare v int default undefined_var; 4163if v = 1 then 4164select 1; 4165else 4166select v, isnull(v); 4167end if; 4168end; 4169end| 4170create procedure bug14643_2() 4171begin 4172declare continue handler for sqlexception select 'boo' as 'Handler'; 4173case undefined_var 4174when 1 then 4175select 1; 4176else 4177select 2; 4178end case; 4179select undefined_var; 4180end| 4181call bug14643_1()| 4182Handler 4183boo 4184v isnull(v) 4185NULL 1 4186call bug14643_2()| 4187Handler 4188boo 4189Handler 4190boo 4191drop procedure bug14643_1| 4192drop procedure bug14643_2| 4193drop procedure if exists bug14304| 4194drop table if exists t3, t4| 4195create table t3(a int primary key auto_increment)| 4196create table t4(a int primary key auto_increment)| 4197create procedure bug14304() 4198begin 4199insert into t3 set a=null; 4200insert into t4 set a=null; 4201insert into t4 set a=null; 4202insert into t4 set a=null; 4203insert into t4 set a=null; 4204insert into t4 set a=null; 4205insert into t4 select null as a; 4206insert into t3 set a=null; 4207insert into t3 set a=null; 4208select * from t3; 4209end| 4210call bug14304()| 4211a 42121 42132 42143 4215drop procedure bug14304| 4216drop table t3, t4| 4217drop procedure if exists bug14376| 4218create procedure bug14376() 4219begin 4220declare x int default x; 4221end| 4222call bug14376()| 4223ERROR 42S22: Unknown column 'x' in 'field list' 4224drop procedure bug14376| 4225create procedure bug14376() 4226begin 4227declare x int default 42; 4228begin 4229declare x int default x; 4230select x; 4231end; 4232end| 4233call bug14376()| 4234x 423542 4236drop procedure bug14376| 4237create procedure bug14376(x int) 4238begin 4239declare x int default x; 4240select x; 4241end| 4242call bug14376(4711)| 4243x 42444711 4245drop procedure bug14376| 4246drop procedure if exists bug5967| 4247drop table if exists t3| 4248create table t3 (a varchar(255))| 4249insert into t3 (a) values ("a - table column")| 4250create procedure bug5967(a varchar(255)) 4251begin 4252declare i varchar(255); 4253declare c cursor for select a from t3; 4254select a; 4255select a from t3 into i; 4256select i as 'Parameter takes precedence over table column'; open c; 4257fetch c into i; 4258close c; 4259select i as 'Parameter takes precedence over table column in cursors'; 4260begin 4261declare a varchar(255) default 'a - local variable'; 4262declare c1 cursor for select a from t3; 4263select a as 'A local variable takes precedence over parameter'; 4264open c1; 4265fetch c1 into i; 4266close c1; 4267select i as 'A local variable takes precedence over parameter in cursors'; 4268begin 4269declare a varchar(255) default 'a - local variable in a nested compound statement'; 4270declare c2 cursor for select a from t3; 4271select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement'; 4272select a from t3 into i; 4273select i as 'A local variable in a nested compound statement takes precedence over table column'; 4274open c2; 4275fetch c2 into i; 4276close c2; 4277select i as 'A local variable in a nested compound statement takes precedence over table column in cursors'; 4278end; 4279end; 4280end| 4281call bug5967("a - stored procedure parameter")| 4282a 4283a - stored procedure parameter 4284Parameter takes precedence over table column 4285a - stored procedure parameter 4286Parameter takes precedence over table column in cursors 4287a - stored procedure parameter 4288A local variable takes precedence over parameter 4289a - local variable 4290A local variable takes precedence over parameter in cursors 4291a - local variable 4292A local variable in a nested compound statement takes precedence over a local variable in the outer statement 4293a - local variable in a nested compound statement 4294A local variable in a nested compound statement takes precedence over table column 4295a - local variable in a nested compound statement 4296A local variable in a nested compound statement takes precedence over table column in cursors 4297a - local variable in a nested compound statement 4298drop procedure bug5967| 4299drop procedure if exists bug13012| 4300create procedure bug13012() 4301BEGIN 4302REPAIR TABLE t1; 4303END| 4304call bug13012()| 4305Table Op Msg_type Msg_text 4306test.t1 repair status OK 4307drop procedure bug13012| 4308create view v1 as select * from t1| 4309create procedure bug13012() 4310BEGIN 4311REPAIR TABLE t1,t2,t3,v1; 4312OPTIMIZE TABLE t1,t2,t3,v1; 4313ANALYZE TABLE t1,t2,t3,v1; 4314END| 4315call bug13012()| 4316Table Op Msg_type Msg_text 4317test.t1 repair status OK 4318test.t2 repair status OK 4319test.t3 repair status OK 4320test.v1 repair Error 'test.v1' is not BASE TABLE 4321test.v1 repair status Operation failed 4322Table Op Msg_type Msg_text 4323test.t1 optimize status OK 4324test.t2 optimize status OK 4325test.t3 optimize status OK 4326test.v1 optimize Error 'test.v1' is not BASE TABLE 4327test.v1 optimize status Operation failed 4328Table Op Msg_type Msg_text 4329test.t1 analyze status Table is already up to date 4330test.t2 analyze status Table is already up to date 4331test.t3 analyze status Table is already up to date 4332test.v1 analyze Error 'test.v1' is not BASE TABLE 4333test.v1 analyze status Operation failed 4334call bug13012()| 4335Table Op Msg_type Msg_text 4336test.t1 repair status OK 4337test.t2 repair status OK 4338test.t3 repair status OK 4339test.v1 repair Error 'test.v1' is not BASE TABLE 4340test.v1 repair status Operation failed 4341Table Op Msg_type Msg_text 4342test.t1 optimize status OK 4343test.t2 optimize status OK 4344test.t3 optimize status OK 4345test.v1 optimize Error 'test.v1' is not BASE TABLE 4346test.v1 optimize status Operation failed 4347Table Op Msg_type Msg_text 4348test.t1 analyze status Table is already up to date 4349test.t2 analyze status Table is already up to date 4350test.t3 analyze status Table is already up to date 4351test.v1 analyze Error 'test.v1' is not BASE TABLE 4352test.v1 analyze status Operation failed 4353call bug13012()| 4354Table Op Msg_type Msg_text 4355test.t1 repair status OK 4356test.t2 repair status OK 4357test.t3 repair status OK 4358test.v1 repair Error 'test.v1' is not BASE TABLE 4359test.v1 repair status Operation failed 4360Table Op Msg_type Msg_text 4361test.t1 optimize status OK 4362test.t2 optimize status OK 4363test.t3 optimize status OK 4364test.v1 optimize Error 'test.v1' is not BASE TABLE 4365test.v1 optimize status Operation failed 4366Table Op Msg_type Msg_text 4367test.t1 analyze status Table is already up to date 4368test.t2 analyze status Table is already up to date 4369test.t3 analyze status Table is already up to date 4370test.v1 analyze Error 'test.v1' is not BASE TABLE 4371test.v1 analyze status Operation failed 4372drop procedure bug13012| 4373drop view v1| 4374select * from t1 order by data| 4375id data 4376aa 0 4377aa 1 4378aa 2 4379aa 3 4380aa 4 4381aa 5 4382aa 6 4383aa 7 4384aa 8 4385aa 9 4386drop schema if exists mysqltest1| 4387Warnings: 4388Note 1008 Can't drop database 'mysqltest1'; database doesn't exist 4389drop schema if exists mysqltest2| 4390Warnings: 4391Note 1008 Can't drop database 'mysqltest2'; database doesn't exist 4392drop schema if exists mysqltest3| 4393Warnings: 4394Note 1008 Can't drop database 'mysqltest3'; database doesn't exist 4395create schema mysqltest1| 4396create schema mysqltest2| 4397create schema mysqltest3| 4398use mysqltest3| 4399create procedure mysqltest1.p1 (out prequestid varchar(100)) 4400begin 4401call mysqltest2.p2('call mysqltest3.p3(1, 2)'); 4402end| 4403create procedure mysqltest2.p2(in psql text) 4404begin 4405declare lsql text; 4406set @lsql= psql; 4407prepare lstatement from @lsql; 4408execute lstatement; 4409deallocate prepare lstatement; 4410end| 4411create procedure mysqltest3.p3(in p1 int) 4412begin 4413select p1; 4414end| 4415call mysqltest1.p1(@rs)| 4416ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 4417call mysqltest1.p1(@rs)| 4418ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 4419call mysqltest1.p1(@rs)| 4420ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 4421drop schema if exists mysqltest1| 4422drop schema if exists mysqltest2| 4423drop schema if exists mysqltest3| 4424use test| 4425drop table if exists t3| 4426drop procedure if exists bug15441| 4427create table t3 (id int not null primary key, county varchar(25))| 4428insert into t3 (id, county) values (1, 'York')| 4429create procedure bug15441(c varchar(25)) 4430begin 4431update t3 set id=2, county=values(c); 4432end| 4433call bug15441('county')| 4434ERROR 42S22: Unknown column 'c' in 'field list' 4435drop procedure bug15441| 4436create procedure bug15441(county varchar(25)) 4437begin 4438declare c varchar(25) default "hello"; 4439insert into t3 (id, county) values (1, county) 4440on duplicate key update county= values(county); 4441select * from t3; 4442update t3 set id=2, county=values(id); 4443select * from t3; 4444end| 4445call bug15441('Yale')| 4446id county 44471 Yale 4448id county 44492 NULL 4450drop table t3| 4451drop procedure bug15441| 4452drop procedure if exists bug14498_1| 4453drop procedure if exists bug14498_2| 4454drop procedure if exists bug14498_3| 4455drop procedure if exists bug14498_4| 4456drop procedure if exists bug14498_5| 4457create procedure bug14498_1() 4458begin 4459declare continue handler for sqlexception select 'error' as 'Handler'; 4460if v then 4461select 'yes' as 'v'; 4462else 4463select 'no' as 'v'; 4464end if; 4465select 'done' as 'End'; 4466end| 4467create procedure bug14498_2() 4468begin 4469declare continue handler for sqlexception select 'error' as 'Handler'; 4470while v do 4471select 'yes' as 'v'; 4472end while; 4473select 'done' as 'End'; 4474end| 4475create procedure bug14498_3() 4476begin 4477declare continue handler for sqlexception select 'error' as 'Handler'; 4478repeat 4479select 'maybe' as 'v'; 4480until v end repeat; 4481select 'done' as 'End'; 4482end| 4483create procedure bug14498_4() 4484begin 4485declare continue handler for sqlexception select 'error' as 'Handler'; 4486case v 4487when 1 then 4488select '1' as 'v'; 4489when 2 then 4490select '2' as 'v'; 4491else 4492select '?' as 'v'; 4493end case; 4494select 'done' as 'End'; 4495end| 4496create procedure bug14498_5() 4497begin 4498declare continue handler for sqlexception select 'error' as 'Handler'; 4499case 4500when v = 1 then 4501select '1' as 'v'; 4502when v = 2 then 4503select '2' as 'v'; 4504else 4505select '?' as 'v'; 4506end case; 4507select 'done' as 'End'; 4508end| 4509call bug14498_1()| 4510Handler 4511error 4512End 4513done 4514call bug14498_2()| 4515Handler 4516error 4517End 4518done 4519call bug14498_3()| 4520v 4521maybe 4522Handler 4523error 4524End 4525done 4526call bug14498_4()| 4527Handler 4528error 4529End 4530done 4531call bug14498_5()| 4532Handler 4533error 4534End 4535done 4536drop procedure bug14498_1| 4537drop procedure bug14498_2| 4538drop procedure bug14498_3| 4539drop procedure bug14498_4| 4540drop procedure bug14498_5| 4541drop table if exists t3| 4542drop procedure if exists bug15231_1| 4543drop procedure if exists bug15231_2| 4544drop procedure if exists bug15231_3| 4545drop procedure if exists bug15231_4| 4546drop procedure if exists bug15231_5| 4547drop procedure if exists bug15231_6| 4548create table t3 (id int not null)| 4549create procedure bug15231_1() 4550begin 4551declare xid integer; 4552declare xdone integer default 0; 4553declare continue handler for not found set xdone = 1; 4554set xid=null; 4555call bug15231_2(xid); 4556select xid, xdone; 4557end| 4558create procedure bug15231_2(inout ioid integer) 4559begin 4560select "Before NOT FOUND condition is triggered" as '1'; 4561select id into ioid from t3 where id=ioid; 4562select "After NOT FOUND condtition is triggered" as '2'; 4563if ioid is null then 4564set ioid=1; 4565end if; 4566end| 4567create procedure bug15231_3() 4568begin 4569declare exit handler for sqlwarning 4570select 'Caught it (correct)' as 'Result'; 4571call bug15231_4(); 4572end| 4573create procedure bug15231_4() 4574begin 4575declare x decimal(2,1); 4576set x = 'zap'; 4577select 'Missed it (correct)' as 'Result'; 4578show warnings; 4579end| 4580create procedure bug15231_5() 4581begin 4582declare exit handler for sqlwarning 4583select 'Caught it (wrong)' as 'Result'; 4584call bug15231_6(); 4585end| 4586create procedure bug15231_6() 4587begin 4588declare x decimal(2,1); 4589set x = 'zap'; 4590select 'Missed it (correct)' as 'Result'; 4591select id from t3; 4592end| 4593call bug15231_1()| 45941 4595Before NOT FOUND condition is triggered 45962 4597After NOT FOUND condtition is triggered 4598xid xdone 45991 0 4600call bug15231_3()| 4601Result 4602Missed it (correct) 4603Level Code Message 4604call bug15231_5()| 4605Result 4606Missed it (correct) 4607id 4608drop table t3| 4609drop procedure bug15231_1| 4610drop procedure bug15231_2| 4611drop procedure bug15231_3| 4612drop procedure bug15231_4| 4613drop procedure bug15231_5| 4614drop procedure bug15231_6| 4615drop procedure if exists bug15011| 4616create table t3 (c1 int primary key)| 4617insert into t3 values (1)| 4618create procedure bug15011() 4619deterministic 4620begin 4621declare continue handler for 1062 4622select 'Outer' as 'Handler'; 4623begin 4624declare continue handler for 1062 4625select 'Inner' as 'Handler'; 4626insert into t3 values (1); 4627end; 4628end| 4629call bug15011()| 4630Handler 4631Inner 4632drop procedure bug15011| 4633drop table t3| 4634drop procedure if exists bug17476| 4635create table t3 ( d date )| 4636insert into t3 values 4637( '2005-01-01' ), ( '2005-01-02' ), ( '2005-01-03' ), 4638( '2005-01-04' ), ( '2005-02-01' ), ( '2005-02-02' )| 4639create procedure bug17476(pDateFormat varchar(10)) 4640select date_format(t3.d, pDateFormat), count(*) 4641from t3 4642group by date_format(t3.d, pDateFormat)| 4643call bug17476('%Y-%m')| 4644date_format(t3.d, pDateFormat) count(*) 46452005-01 4 46462005-02 2 4647call bug17476('%Y-%m')| 4648date_format(t3.d, pDateFormat) count(*) 46492005-01 4 46502005-02 2 4651drop table t3| 4652drop procedure bug17476| 4653drop table if exists t3| 4654drop procedure if exists bug16887| 4655create table t3 ( c varchar(1) )| 4656insert into t3 values 4657(' '),('.'),(';'),(','),('-'),('_'),('('),(')'),('/'),('\\')| 4658create procedure bug16887() 4659begin 4660declare i int default 10; 4661again: 4662while i > 0 do 4663begin 4664declare breakchar varchar(1); 4665declare done int default 0; 4666declare t3_cursor cursor for select c from t3; 4667declare continue handler for not found set done = 1; 4668set i = i - 1; 4669select i; 4670if i = 3 then 4671iterate again; 4672end if; 4673open t3_cursor; 4674loop 4675fetch t3_cursor into breakchar; 4676if done = 1 then 4677begin 4678close t3_cursor; 4679iterate again; 4680end; 4681end if; 4682end loop; 4683end; 4684end while; 4685end| 4686call bug16887()| 4687i 46889 4689i 46908 4691i 46927 4693i 46946 4695i 46965 4697i 46984 4699i 47003 4701i 47022 4703i 47041 4705i 47060 4707drop table t3| 4708drop procedure bug16887| 4709drop procedure if exists bug16474_1| 4710drop procedure if exists bug16474_2| 4711delete from t1| 4712insert into t1 values ('c', 2), ('b', 3), ('a', 1)| 4713create procedure bug16474_1() 4714begin 4715declare x int; 4716select id from t1 order by x, id; 4717end| 4718drop procedure if exists bug14945| 4719create table t3 (id int not null auto_increment primary key)| 4720create procedure bug14945() deterministic truncate t3| 4721insert into t3 values (null)| 4722call bug14945()| 4723insert into t3 values (null)| 4724select * from t3| 4725id 47261 4727drop table t3| 4728drop procedure bug14945| 4729create procedure bug16474_2(x int) 4730select id from t1 order by x, id| 4731call bug16474_1()| 4732id 4733a 4734b 4735c 4736call bug16474_2(1)| 4737id 4738a 4739b 4740c 4741call bug16474_2(2)| 4742id 4743a 4744b 4745c 4746drop procedure bug16474_1| 4747drop procedure bug16474_2| 4748set @x = 2| 4749select * from t1 order by @x, data| 4750id data 4751a 1 4752c 2 4753b 3 4754delete from t1| 4755drop function if exists bug15728| 4756drop table if exists t3| 4757create table t3 ( 4758id int not null auto_increment, 4759primary key (id) 4760)| 4761create function bug15728() returns int(11) 4762return last_insert_id()| 4763insert into t3 values (0)| 4764select last_insert_id()| 4765last_insert_id() 47661 4767select bug15728()| 4768bug15728() 47691 4770drop function bug15728| 4771drop table t3| 4772drop procedure if exists bug18787| 4773create procedure bug18787() 4774begin 4775declare continue handler for sqlexception begin end; 4776select no_such_function(); 4777end| 4778call bug18787()| 4779drop procedure bug18787| 4780create database bug18344_012345678901| 4781use bug18344_012345678901| 4782create procedure bug18344() begin end| 4783create procedure bug18344_2() begin end| 4784create database bug18344_0123456789012| 4785use bug18344_0123456789012| 4786create procedure bug18344() begin end| 4787create procedure bug18344_2() begin end| 4788use test| 4789select schema_name from information_schema.schemata where 4790schema_name like 'bug18344%'| 4791schema_name 4792bug18344_012345678901 4793bug18344_0123456789012 4794select routine_name,routine_schema from information_schema.routines where 4795routine_schema like 'bug18344%'| 4796routine_name routine_schema 4797bug18344 bug18344_012345678901 4798bug18344_2 bug18344_012345678901 4799bug18344 bug18344_0123456789012 4800bug18344_2 bug18344_0123456789012 4801drop database bug18344_012345678901| 4802drop database bug18344_0123456789012| 4803select schema_name from information_schema.schemata where 4804schema_name like 'bug18344%'| 4805schema_name 4806select routine_name,routine_schema from information_schema.routines where 4807routine_schema like 'bug18344%'| 4808routine_name routine_schema 4809drop function if exists bug12472| 4810create function bug12472() returns int return (select count(*) from t1)| 4811create table t3 as select bug12472() as i| 4812show create table t3| 4813Table Create Table 4814t3 CREATE TABLE `t3` ( 4815 `i` int(11) DEFAULT NULL 4816) ENGINE=MyISAM DEFAULT CHARSET=latin1 4817select * from t3| 4818i 48190 4820drop table t3| 4821create view v1 as select bug12472() as j| 4822create table t3 as select * from v1| 4823show create table t3| 4824Table Create Table 4825t3 CREATE TABLE `t3` ( 4826 `j` int(11) DEFAULT NULL 4827) ENGINE=MyISAM DEFAULT CHARSET=latin1 4828select * from t3| 4829j 48300 4831drop table t3| 4832drop view v1| 4833drop function bug12472| 4834DROP FUNCTION IF EXISTS bug18589_f1| 4835DROP PROCEDURE IF EXISTS bug18589_p1| 4836DROP PROCEDURE IF EXISTS bug18589_p2| 4837CREATE FUNCTION bug18589_f1(arg TEXT) RETURNS TEXT 4838BEGIN 4839RETURN CONCAT(arg, ""); 4840END| 4841CREATE PROCEDURE bug18589_p1(arg TEXT, OUT ret TEXT) 4842BEGIN 4843SET ret = CONCAT(arg, ""); 4844END| 4845CREATE PROCEDURE bug18589_p2(arg TEXT) 4846BEGIN 4847DECLARE v TEXT; 4848CALL bug18589_p1(arg, v); 4849SELECT v; 4850END| 4851SELECT bug18589_f1(REPEAT("a", 767))| 4852bug18589_f1(REPEAT("a", 767)) 4853aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 4854SET @bug18589_v1 = ""| 4855CALL bug18589_p1(REPEAT("a", 767), @bug18589_v1)| 4856SELECT @bug18589_v1| 4857@bug18589_v1 4858aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 4859CALL bug18589_p2(REPEAT("a", 767))| 4860v 4861aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 4862DROP FUNCTION bug18589_f1| 4863DROP PROCEDURE bug18589_p1| 4864DROP PROCEDURE bug18589_p2| 4865DROP FUNCTION IF EXISTS bug18037_f1| 4866DROP PROCEDURE IF EXISTS bug18037_p1| 4867DROP PROCEDURE IF EXISTS bug18037_p2| 4868CREATE FUNCTION bug18037_f1() RETURNS INT 4869BEGIN 4870RETURN @@server_id; 4871END| 4872CREATE PROCEDURE bug18037_p1() 4873BEGIN 4874DECLARE v INT DEFAULT @@server_id; 4875END| 4876CREATE PROCEDURE bug18037_p2() 4877BEGIN 4878CASE @@server_id 4879WHEN -1 THEN 4880SELECT 0; 4881ELSE 4882SELECT 1; 4883END CASE; 4884END| 4885SELECT bug18037_f1()| 4886bug18037_f1() 48871 4888CALL bug18037_p1()| 4889CALL bug18037_p2()| 48901 48911 4892DROP FUNCTION bug18037_f1| 4893DROP PROCEDURE bug18037_p1| 4894DROP PROCEDURE bug18037_p2| 4895use test| 4896create table t3 (i int)| 4897insert into t3 values (1), (2)| 4898create database mysqltest1| 4899use mysqltest1| 4900create function bug17199() returns varchar(2) deterministic return 'ok'| 4901use test| 4902select *, mysqltest1.bug17199() from t3| 4903i mysqltest1.bug17199() 49041 ok 49052 ok 4906use mysqltest1| 4907create function bug18444(i int) returns int no sql deterministic return i + 1| 4908use test| 4909select mysqltest1.bug18444(i) from t3| 4910mysqltest1.bug18444(i) 49112 49123 4913drop database mysqltest1| 4914create database mysqltest1 charset=utf8| 4915create database mysqltest2 charset=utf8| 4916create procedure mysqltest1.p1() 4917begin 4918-- alters the default collation of database test 4919alter database character set koi8r; 4920end| 4921use mysqltest1| 4922call p1()| 4923show create database mysqltest1| 4924Database Create Database 4925mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET koi8r */ 4926show create database mysqltest2| 4927Database Create Database 4928mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET utf8 */ 4929alter database mysqltest1 character set utf8| 4930use mysqltest2| 4931call mysqltest1.p1()| 4932show create database mysqltest1| 4933Database Create Database 4934mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET koi8r */ 4935show create database mysqltest2| 4936Database Create Database 4937mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET utf8 */ 4938drop database mysqltest1| 4939drop database mysqltest2| 4940use test| 4941drop table if exists t3| 4942drop procedure if exists bug15217| 4943create table t3 as select 1| 4944create procedure bug15217() 4945begin 4946declare var1 char(255); 4947declare cur1 cursor for select * from t3; 4948open cur1; 4949fetch cur1 into var1; 4950select concat('data was: /', var1, '/'); 4951close cur1; 4952end | 4953call bug15217()| 4954concat('data was: /', var1, '/') 4955data was: /1/ 4956flush tables | 4957call bug15217()| 4958concat('data was: /', var1, '/') 4959data was: /1/ 4960drop table t3| 4961drop procedure bug15217| 4962DROP PROCEDURE IF EXISTS bug21013 | 4963CREATE PROCEDURE bug21013(IN lim INT) 4964BEGIN 4965DECLARE i INT DEFAULT 0; 4966WHILE (i < lim) DO 4967SET @b = LOCATE(_latin1'b', @a, 1); 4968SET i = i + 1; 4969END WHILE; 4970END | 4971SET @a = _latin2"aaaaaaaaaa" | 4972CALL bug21013(10) | 4973DROP PROCEDURE bug21013 | 4974DROP DATABASE IF EXISTS mysqltest1| 4975DROP DATABASE IF EXISTS mysqltest2| 4976CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8| 4977CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8| 4978use mysqltest1| 4979CREATE FUNCTION bug16211_f1() RETURNS CHAR(10) 4980RETURN ""| 4981CREATE FUNCTION bug16211_f2() RETURNS CHAR(10) CHARSET koi8r 4982RETURN ""| 4983CREATE FUNCTION mysqltest2.bug16211_f3() RETURNS CHAR(10) 4984RETURN ""| 4985CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r 4986RETURN ""| 4987SHOW CREATE FUNCTION bug16211_f1| 4988Function sql_mode Create Function character_set_client collation_connection Database Collation 4989bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8 4990RETURN "" latin1 latin1_swedish_ci utf8_general_ci 4991SHOW CREATE FUNCTION bug16211_f2| 4992Function sql_mode Create Function character_set_client collation_connection Database Collation 4993bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r 4994RETURN "" latin1 latin1_swedish_ci utf8_general_ci 4995SHOW CREATE FUNCTION mysqltest2.bug16211_f3| 4996Function sql_mode Create Function character_set_client collation_connection Database Collation 4997bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8 4998RETURN "" latin1 latin1_swedish_ci utf8_general_ci 4999SHOW CREATE FUNCTION mysqltest2.bug16211_f4| 5000Function sql_mode Create Function character_set_client collation_connection Database Collation 5001bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r 5002RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5003SELECT dtd_identifier 5004FROM INFORMATION_SCHEMA.ROUTINES 5005WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| 5006dtd_identifier 5007char(10) 5008SELECT dtd_identifier 5009FROM INFORMATION_SCHEMA.ROUTINES 5010WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"| 5011dtd_identifier 5012char(10) 5013SELECT dtd_identifier 5014FROM INFORMATION_SCHEMA.ROUTINES 5015WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"| 5016dtd_identifier 5017char(10) 5018SELECT dtd_identifier 5019FROM INFORMATION_SCHEMA.ROUTINES 5020WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"| 5021dtd_identifier 5022char(10) 5023SELECT CHARSET(bug16211_f1())| 5024CHARSET(bug16211_f1()) 5025utf8 5026SELECT CHARSET(bug16211_f2())| 5027CHARSET(bug16211_f2()) 5028koi8r 5029SELECT CHARSET(mysqltest2.bug16211_f3())| 5030CHARSET(mysqltest2.bug16211_f3()) 5031utf8 5032SELECT CHARSET(mysqltest2.bug16211_f4())| 5033CHARSET(mysqltest2.bug16211_f4()) 5034koi8r 5035ALTER DATABASE mysqltest1 CHARACTER SET cp1251| 5036ALTER DATABASE mysqltest2 CHARACTER SET cp1251| 5037SHOW CREATE FUNCTION bug16211_f1| 5038Function sql_mode Create Function character_set_client collation_connection Database Collation 5039bug16211_f1 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10) CHARSET utf8 5040RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5041SHOW CREATE FUNCTION bug16211_f2| 5042Function sql_mode Create Function character_set_client collation_connection Database Collation 5043bug16211_f2 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10) CHARSET koi8r 5044RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5045SHOW CREATE FUNCTION mysqltest2.bug16211_f3| 5046Function sql_mode Create Function character_set_client collation_connection Database Collation 5047bug16211_f3 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10) CHARSET utf8 5048RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5049SHOW CREATE FUNCTION mysqltest2.bug16211_f4| 5050Function sql_mode Create Function character_set_client collation_connection Database Collation 5051bug16211_f4 CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10) CHARSET koi8r 5052RETURN "" latin1 latin1_swedish_ci utf8_general_ci 5053SELECT dtd_identifier 5054FROM INFORMATION_SCHEMA.ROUTINES 5055WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"| 5056dtd_identifier 5057char(10) 5058SELECT dtd_identifier 5059FROM INFORMATION_SCHEMA.ROUTINES 5060WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"| 5061dtd_identifier 5062char(10) 5063SELECT dtd_identifier 5064FROM INFORMATION_SCHEMA.ROUTINES 5065WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"| 5066dtd_identifier 5067char(10) 5068SELECT dtd_identifier 5069FROM INFORMATION_SCHEMA.ROUTINES 5070WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"| 5071dtd_identifier 5072char(10) 5073SELECT CHARSET(bug16211_f1())| 5074CHARSET(bug16211_f1()) 5075utf8 5076SELECT CHARSET(bug16211_f2())| 5077CHARSET(bug16211_f2()) 5078koi8r 5079SELECT CHARSET(mysqltest2.bug16211_f3())| 5080CHARSET(mysqltest2.bug16211_f3()) 5081utf8 5082SELECT CHARSET(mysqltest2.bug16211_f4())| 5083CHARSET(mysqltest2.bug16211_f4()) 5084koi8r 5085use test| 5086DROP DATABASE mysqltest1| 5087DROP DATABASE mysqltest2| 5088DROP DATABASE IF EXISTS mysqltest1| 5089CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8| 5090use mysqltest1| 5091CREATE PROCEDURE bug16676_p1( 5092IN p1 CHAR(10), 5093INOUT p2 CHAR(10), 5094OUT p3 CHAR(10)) 5095BEGIN 5096SELECT CHARSET(p1), COLLATION(p1); 5097SELECT CHARSET(p2), COLLATION(p2); 5098SELECT CHARSET(p3), COLLATION(p3); 5099END| 5100CREATE PROCEDURE bug16676_p2( 5101IN p1 CHAR(10) CHARSET koi8r, 5102INOUT p2 CHAR(10) CHARSET cp1251, 5103OUT p3 CHAR(10) CHARSET greek) 5104BEGIN 5105SELECT CHARSET(p1), COLLATION(p1); 5106SELECT CHARSET(p2), COLLATION(p2); 5107SELECT CHARSET(p3), COLLATION(p3); 5108END| 5109SET @v2 = 'b'| 5110SET @v3 = 'c'| 5111CALL bug16676_p1('a', @v2, @v3)| 5112CHARSET(p1) COLLATION(p1) 5113utf8 utf8_general_ci 5114CHARSET(p2) COLLATION(p2) 5115utf8 utf8_general_ci 5116CHARSET(p3) COLLATION(p3) 5117utf8 utf8_general_ci 5118CALL bug16676_p2('a', @v2, @v3)| 5119CHARSET(p1) COLLATION(p1) 5120koi8r koi8r_general_ci 5121CHARSET(p2) COLLATION(p2) 5122cp1251 cp1251_general_ci 5123CHARSET(p3) COLLATION(p3) 5124greek greek_general_ci 5125use test| 5126DROP DATABASE mysqltest1| 5127drop table if exists t3| 5128drop table if exists t4| 5129drop procedure if exists bug8153_subselect| 5130drop procedure if exists bug8153_subselect_a| 5131drop procedure if exists bug8153_subselect_b| 5132drop procedure if exists bug8153_proc_a| 5133drop procedure if exists bug8153_proc_b| 5134create table t3 (a int)| 5135create table t4 (a int)| 5136insert into t3 values (1), (1), (2), (3)| 5137insert into t4 values (1), (1)| 5138create procedure bug8153_subselect() 5139begin 5140declare continue handler for sqlexception 5141begin 5142select 'statement failed'; 5143end; 5144update t3 set a=a+1 where (select a from t4 where a=1) is null; 5145select 'statement after update'; 5146end| 5147call bug8153_subselect()| 5148statement failed 5149statement failed 5150statement after update 5151statement after update 5152select * from t3| 5153a 51541 51551 51562 51573 5158call bug8153_subselect()| 5159statement failed 5160statement failed 5161statement after update 5162statement after update 5163select * from t3| 5164a 51651 51661 51672 51683 5169drop procedure bug8153_subselect| 5170create procedure bug8153_subselect_a() 5171begin 5172declare continue handler for sqlexception 5173begin 5174select 'in continue handler'; 5175end; 5176select 'reachable code a1'; 5177call bug8153_subselect_b(); 5178select 'reachable code a2'; 5179end| 5180create procedure bug8153_subselect_b() 5181begin 5182select 'reachable code b1'; 5183update t3 set a=a+1 where (select a from t4 where a=1) is null; 5184select 'unreachable code b2'; 5185end| 5186call bug8153_subselect_a()| 5187reachable code a1 5188reachable code a1 5189reachable code b1 5190reachable code b1 5191in continue handler 5192in continue handler 5193reachable code a2 5194reachable code a2 5195select * from t3| 5196a 51971 51981 51992 52003 5201call bug8153_subselect_a()| 5202reachable code a1 5203reachable code a1 5204reachable code b1 5205reachable code b1 5206in continue handler 5207in continue handler 5208reachable code a2 5209reachable code a2 5210select * from t3| 5211a 52121 52131 52142 52153 5216drop procedure bug8153_subselect_a| 5217drop procedure bug8153_subselect_b| 5218create procedure bug8153_proc_a() 5219begin 5220declare continue handler for sqlexception 5221begin 5222select 'in continue handler'; 5223end; 5224select 'reachable code a1'; 5225call bug8153_proc_b(); 5226select 'reachable code a2'; 5227end| 5228create procedure bug8153_proc_b() 5229begin 5230select 'reachable code b1'; 5231select no_such_function(); 5232select 'unreachable code b2'; 5233end| 5234call bug8153_proc_a()| 5235reachable code a1 5236reachable code a1 5237reachable code b1 5238reachable code b1 5239in continue handler 5240in continue handler 5241reachable code a2 5242reachable code a2 5243drop procedure bug8153_proc_a| 5244drop procedure bug8153_proc_b| 5245drop table t3| 5246drop table t4| 5247drop procedure if exists bug19862| 5248CREATE TABLE t11 (a INT)| 5249CREATE TABLE t12 (a INT)| 5250CREATE FUNCTION bug19862(x INT) RETURNS INT 5251BEGIN 5252INSERT INTO t11 VALUES (x); 5253RETURN x+1; 5254END| 5255INSERT INTO t12 VALUES (1), (2)| 5256SELECT bug19862(a) FROM t12 ORDER BY 1| 5257bug19862(a) 52582 52593 5260SELECT * FROM t11| 5261a 52621 52632 5264DROP TABLE t11, t12| 5265DROP FUNCTION bug19862| 5266drop table if exists t3| 5267drop database if exists mysqltest1| 5268create table t3 (a int)| 5269insert into t3 (a) values (1), (2)| 5270create database mysqltest1| 5271use mysqltest1| 5272drop database mysqltest1| 5273select database()| 5274database() 5275NULL 5276select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2| 5277a 52781 5279use test| 5280drop table t3| 5281DROP PROCEDURE IF EXISTS bug16899_p1| 5282DROP FUNCTION IF EXISTS bug16899_f1| 5283CREATE DEFINER=1234567890abcdefGHIKLsdafdsjakfhkshfkshsndvkjsddngjhasdkjghskahfdksjhcnsndkhjkghskjfjsdhfkhskfdhksjdhfkjshfksh@localhost PROCEDURE bug16899_p1() 5284BEGIN 5285SET @a = 1; 5286END| 5287ERROR HY000: String '1234567890abcdefGHIKLsdafdsjakfhkshfkshsndvkjsddngjhasdkjghskahfdksjhc' is too long for user name (should be no longer than 32) 5288CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY 5289FUNCTION bug16899_f1() RETURNS INT 5290BEGIN 5291RETURN 1; 5292END| 5293ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) 5294drop procedure if exists bug21416| 5295create procedure bug21416() show create procedure bug21416| 5296call bug21416()| 5297Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 5298bug21416 CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21416`() 5299show create procedure bug21416 latin1 latin1_swedish_ci latin1_swedish_ci 5300drop procedure bug21416| 5301DROP PROCEDURE IF EXISTS bug21414| 5302CREATE PROCEDURE bug21414() SELECT 1| 5303FLUSH TABLES WITH READ LOCK| 5304DROP PROCEDURE bug21414| 5305ERROR HY000: Can't execute the query because you have a conflicting read lock 5306UNLOCK TABLES| 5307The following should succeed. 5308DROP PROCEDURE bug21414| 5309set names utf8| 5310drop database if exists това_е_дълго_име_за_база_данни_нали| 5311create database това_е_дълго_име_за_база_данни_нали| 5312INSERT 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')| 5313call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()| 5314ERROR HY000: Failed to load routine това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) 5315drop database това_е_дълго_име_за_база_данни_нали| 5316CREATE TABLE t3 ( 5317Member_ID varchar(15) NOT NULL, 5318PRIMARY KEY (Member_ID) 5319)| 5320CREATE TABLE t4 ( 5321ID int(10) unsigned NOT NULL auto_increment, 5322Member_ID varchar(15) NOT NULL default '', 5323Action varchar(12) NOT NULL, 5324Action_Date datetime NOT NULL, 5325Track varchar(15) default NULL, 5326User varchar(12) default NULL, 5327Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update 5328CURRENT_TIMESTAMP, 5329PRIMARY KEY (ID), 5330KEY Action (Action), 5331KEY Action_Date (Action_Date) 5332)| 5333INSERT INTO t3(Member_ID) VALUES 5334('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')| 5335INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES 5336('111111', 'Disenrolled', '2006-03-01', 'CAD' ), 5337('111111', 'Enrolled', '2006-03-01', 'CAD' ), 5338('111111', 'Disenrolled', '2006-07-03', 'CAD' ), 5339('222222', 'Enrolled', '2006-03-07', 'CAD' ), 5340('222222', 'Enrolled', '2006-03-07', 'CHF' ), 5341('222222', 'Disenrolled', '2006-08-02', 'CHF' ), 5342('333333', 'Enrolled', '2006-03-01', 'CAD' ), 5343('333333', 'Disenrolled', '2006-03-01', 'CAD' ), 5344('444444', 'Enrolled', '2006-03-01', 'CAD' ), 5345('555555', 'Disenrolled', '2006-03-01', 'CAD' ), 5346('555555', 'Enrolled', '2006-07-21', 'CAD' ), 5347('555555', 'Disenrolled', '2006-03-01', 'CHF' ), 5348('666666', 'Enrolled', '2006-02-09', 'CAD' ), 5349('666666', 'Enrolled', '2006-05-12', 'CHF' ), 5350('666666', 'Disenrolled', '2006-06-01', 'CAD' )| 5351DROP FUNCTION IF EXISTS bug21493| 5352CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45) 5353BEGIN 5354DECLARE tracks VARCHAR(45); 5355SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4 5356WHERE Member_ID=paramMember AND Action='Enrolled' AND 5357(Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4 5358WHERE Member_ID=paramMember GROUP BY Track); 5359RETURN tracks; 5360END| 5361SELECT bug21493('111111')| 5362bug21493('111111') 5363NULL 5364SELECT bug21493('222222')| 5365bug21493('222222') 5366CAD 5367SELECT bug21493(Member_ID) FROM t3| 5368bug21493(Member_ID) 5369NULL 5370CAD 5371CAD 5372CAD 5373CAD 5374CHF 5375DROP FUNCTION bug21493| 5376DROP TABLE t3,t4| 5377drop function if exists func_20028_a| 5378drop function if exists func_20028_b| 5379drop function if exists func_20028_c| 5380drop procedure if exists proc_20028_a| 5381drop procedure if exists proc_20028_b| 5382drop procedure if exists proc_20028_c| 5383drop table if exists table_20028| 5384create table table_20028 (i int)| 5385SET @save_sql_mode=@@sql_mode| 5386SET sql_mode=''| 5387create function func_20028_a() returns integer 5388begin 5389declare temp integer; 5390select i into temp from table_20028 limit 1; 5391return ifnull(temp, 0); 5392end| 5393create function func_20028_b() returns integer 5394begin 5395return func_20028_a(); 5396end| 5397create function func_20028_c() returns integer 5398begin 5399declare div_zero integer; 5400set SQL_MODE='TRADITIONAL'; 5401select 1/0 into div_zero; 5402return div_zero; 5403end| 5404create procedure proc_20028_a() 5405begin 5406declare temp integer; 5407select i into temp from table_20028 limit 1; 5408end| 5409create procedure proc_20028_b() 5410begin 5411call proc_20028_a(); 5412end| 5413create procedure proc_20028_c() 5414begin 5415declare div_zero integer; 5416set SQL_MODE='TRADITIONAL'; 5417select 1/0 into div_zero; 5418end| 5419select func_20028_a()| 5420func_20028_a() 54210 5422select func_20028_b()| 5423func_20028_b() 54240 5425select func_20028_c()| 5426ERROR 22012: Division by 0 5427call proc_20028_a()| 5428Warnings: 5429Warning 1329 No data - zero rows fetched, selected, or processed 5430call proc_20028_b()| 5431Warnings: 5432Warning 1329 No data - zero rows fetched, selected, or processed 5433call proc_20028_c()| 5434ERROR 22012: Division by 0 5435SET sql_mode='TRADITIONAL'| 5436Warnings: 5437Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 5438drop function func_20028_a| 5439drop function func_20028_b| 5440drop function func_20028_c| 5441drop procedure proc_20028_a| 5442drop procedure proc_20028_b| 5443drop procedure proc_20028_c| 5444create function func_20028_a() returns integer 5445begin 5446declare temp integer; 5447select i into temp from table_20028 limit 1; 5448return ifnull(temp, 0); 5449end| 5450create function func_20028_b() returns integer 5451begin 5452return func_20028_a(); 5453end| 5454create function func_20028_c() returns integer 5455begin 5456declare div_zero integer; 5457set SQL_MODE=''; 5458select 1/0 into div_zero; 5459return div_zero; 5460end| 5461create procedure proc_20028_a() 5462begin 5463declare temp integer; 5464select i into temp from table_20028 limit 1; 5465end| 5466create procedure proc_20028_b() 5467begin 5468call proc_20028_a(); 5469end| 5470create procedure proc_20028_c() 5471begin 5472declare div_zero integer; 5473set SQL_MODE=''; 5474select 1/0 into div_zero; 5475end| 5476select func_20028_a()| 5477func_20028_a() 54780 5479select func_20028_b()| 5480func_20028_b() 54810 5482select func_20028_c()| 5483func_20028_c() 5484NULL 5485call proc_20028_a()| 5486Warnings: 5487Warning 1329 No data - zero rows fetched, selected, or processed 5488call proc_20028_b()| 5489Warnings: 5490Warning 1329 No data - zero rows fetched, selected, or processed 5491call proc_20028_c()| 5492SET @@sql_mode=@save_sql_mode| 5493Warnings: 5494Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 5495drop function func_20028_a| 5496drop function func_20028_b| 5497drop function func_20028_c| 5498drop procedure proc_20028_a| 5499drop procedure proc_20028_b| 5500drop procedure proc_20028_c| 5501drop table table_20028| 5502drop procedure if exists proc_21462_a| 5503drop procedure if exists proc_21462_b| 5504create procedure proc_21462_a() 5505begin 5506select "Called A"; 5507end| 5508create procedure proc_21462_b(x int) 5509begin 5510select "Called B"; 5511end| 5512call proc_21462_a| 5513Called A 5514Called A 5515call proc_21462_a()| 5516Called A 5517Called A 5518call proc_21462_a(1)| 5519ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_a; expected 0, got 1 5520call proc_21462_b| 5521ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_b; expected 1, got 0 5522call proc_21462_b()| 5523ERROR 42000: Incorrect number of arguments for PROCEDURE test.proc_21462_b; expected 1, got 0 5524call proc_21462_b(1)| 5525Called B 5526Called B 5527drop procedure proc_21462_a| 5528drop procedure proc_21462_b| 5529drop table if exists t3| 5530drop procedure if exists proc_bug19733| 5531create table t3 (s1 int)| 5532create procedure proc_bug19733() 5533begin 5534declare v int default 0; 5535while v < 100 do 5536create index i on t3 (s1); 5537drop index i on t3; 5538set v = v + 1; 5539end while; 5540end| 5541call proc_bug19733()| 5542call proc_bug19733()| 5543call proc_bug19733()| 5544drop procedure proc_bug19733| 5545drop table t3| 5546DROP PROCEDURE IF EXISTS p1| 5547DROP VIEW IF EXISTS v1, v2| 5548DROP TABLE IF EXISTS t3, t4| 5549CREATE TABLE t3 (t3_id INT)| 5550INSERT INTO t3 VALUES (0)| 5551INSERT INTO t3 VALUES (1)| 5552CREATE TABLE t4 (t4_id INT)| 5553INSERT INTO t4 VALUES (2)| 5554CREATE VIEW v1 AS 5555SELECT t3.t3_id, t4.t4_id 5556FROM t3 JOIN t4 ON t3.t3_id = 0| 5557CREATE VIEW v2 AS 5558SELECT t3.t3_id AS t3_id_1, v1.t3_id AS t3_id_2, v1.t4_id 5559FROM t3 LEFT JOIN v1 ON t3.t3_id = 0| 5560CREATE PROCEDURE p1() SELECT * FROM v2| 5561CALL p1()| 5562t3_id_1 t3_id_2 t4_id 55630 0 2 55641 NULL NULL 5565CALL p1()| 5566t3_id_1 t3_id_2 t4_id 55670 0 2 55681 NULL NULL 5569DROP PROCEDURE p1| 5570DROP VIEW v1, v2| 5571DROP TABLE t3, t4| 5572End of 5.0 tests 5573Begin of 5.1 tests 5574drop function if exists pi; 5575create function pi() returns varchar(50) 5576return "pie, my favorite desert."; 5577Warnings: 5578Note 1585 This function 'pi' has the same name as a native function 5579SET @save_sql_mode=@@sql_mode; 5580SET SQL_MODE='IGNORE_SPACE'; 5581select pi(), pi (); 5582pi() pi () 55833.141593 3.141593 5584select test.pi(), test.pi (); 5585test.pi() test.pi () 5586pie, my favorite desert. pie, my favorite desert. 5587SET SQL_MODE=''; 5588select pi(), pi (); 5589pi() pi () 55903.141593 3.141593 5591select test.pi(), test.pi (); 5592test.pi() test.pi () 5593pie, my favorite desert. pie, my favorite desert. 5594SET @@sql_mode=@save_sql_mode; 5595drop function pi; 5596drop function if exists test.database; 5597drop function if exists test.current_user; 5598drop function if exists test.md5; 5599create database nowhere; 5600use nowhere; 5601drop database nowhere; 5602SET @save_sql_mode=@@sql_mode; 5603SET SQL_MODE='IGNORE_SPACE'; 5604select database(), database (); 5605database() database () 5606NULL NULL 5607select current_user(), current_user (); 5608current_user() current_user () 5609root@localhost root@localhost 5610select md5("aaa"), md5 ("aaa"); 5611md5("aaa") md5 ("aaa") 561247bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 5613SET SQL_MODE=''; 5614select database(), database (); 5615database() database () 5616NULL NULL 5617select current_user(), current_user (); 5618current_user() current_user () 5619root@localhost root@localhost 5620select md5("aaa"), md5 ("aaa"); 5621md5("aaa") md5 ("aaa") 562247bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 5623use test; 5624create function `database`() returns varchar(50) 5625return "Stored function database"; 5626Warnings: 5627Note 1585 This function 'database' has the same name as a native function 5628create function `current_user`() returns varchar(50) 5629return "Stored function current_user"; 5630Warnings: 5631Note 1585 This function 'current_user' has the same name as a native function 5632create function md5(x varchar(50)) returns varchar(50) 5633return "Stored function md5"; 5634Warnings: 5635Note 1585 This function 'md5' has the same name as a native function 5636SET SQL_MODE='IGNORE_SPACE'; 5637select database(), database (); 5638database() database () 5639test test 5640select current_user(), current_user (); 5641current_user() current_user () 5642root@localhost root@localhost 5643select md5("aaa"), md5 ("aaa"); 5644md5("aaa") md5 ("aaa") 564547bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 5646select test.database(), test.database (); 5647test.database() test.database () 5648Stored function database Stored function database 5649select test.current_user(), test.current_user (); 5650test.current_user() test.current_user () 5651Stored function current_user Stored function current_user 5652select test.md5("aaa"), test.md5 ("aaa"); 5653test.md5("aaa") test.md5 ("aaa") 5654Stored function md5 Stored function md5 5655SET SQL_MODE=''; 5656select database(), database (); 5657database() database () 5658test test 5659select current_user(), current_user (); 5660current_user() current_user () 5661root@localhost root@localhost 5662select md5("aaa"), md5 ("aaa"); 5663md5("aaa") md5 ("aaa") 566447bce5c74f589f4867dbd57e9ca9f808 47bce5c74f589f4867dbd57e9ca9f808 5665select test.database(), test.database (); 5666test.database() test.database () 5667Stored function database Stored function database 5668select test.current_user(), test.current_user (); 5669test.current_user() test.current_user () 5670Stored function current_user Stored function current_user 5671select test.md5("aaa"), test.md5 ("aaa"); 5672test.md5("aaa") test.md5 ("aaa") 5673Stored function md5 Stored function md5 5674SET @@sql_mode=@save_sql_mode; 5675drop function test.database; 5676drop function test.current_user; 5677drop function md5; 5678use test; 5679End of 5.1 tests 5680DROP TABLE IF EXISTS bug23760| 5681DROP TABLE IF EXISTS bug23760_log| 5682DROP PROCEDURE IF EXISTS bug23760_update_log| 5683DROP PROCEDURE IF EXISTS bug23760_test_row_count| 5684DROP FUNCTION IF EXISTS bug23760_rc_test| 5685CREATE TABLE bug23760 ( 5686id INT NOT NULL AUTO_INCREMENT , 5687num INT NOT NULL , 5688PRIMARY KEY ( id ) 5689)| 5690CREATE TABLE bug23760_log ( 5691id INT NOT NULL AUTO_INCREMENT , 5692reason VARCHAR(50)NULL , 5693ammount INT NOT NULL , 5694PRIMARY KEY ( id ) 5695)| 5696CREATE PROCEDURE bug23760_update_log(r Varchar(50), a INT) 5697BEGIN 5698INSERT INTO bug23760_log (reason, ammount) VALUES(r, a); 5699END| 5700CREATE PROCEDURE bug23760_test_row_count() 5701BEGIN 5702UPDATE bug23760 SET num = num + 1; 5703CALL bug23760_update_log('Test is working', ROW_COUNT()); 5704UPDATE bug23760 SET num = num - 1; 5705END| 5706CREATE PROCEDURE bug23760_test_row_count2(level INT) 5707BEGIN 5708IF level THEN 5709UPDATE bug23760 SET num = num + 1; 5710CALL bug23760_update_log('Test2 is working', ROW_COUNT()); 5711CALL bug23760_test_row_count2(level - 1); 5712END IF; 5713END| 5714CREATE FUNCTION bug23760_rc_test(in_var INT) RETURNS INT RETURN in_var| 5715INSERT INTO bug23760 (num) VALUES (0), (1), (1), (2), (3), (5), (8)| 5716SELECT ROW_COUNT()| 5717ROW_COUNT() 57187 5719CALL bug23760_test_row_count()| 5720SELECT * FROM bug23760_log ORDER BY id| 5721id reason ammount 57221 Test is working 7 5723SET @save_max_sp_recursion= @@max_sp_recursion_depth| 5724SELECT @save_max_sp_recursion| 5725@save_max_sp_recursion 57260 5727SET max_sp_recursion_depth= 5| 5728SELECT @@max_sp_recursion_depth| 5729@@max_sp_recursion_depth 57305 5731CALL bug23760_test_row_count2(2)| 5732SELECT ROW_COUNT()| 5733ROW_COUNT() 57341 5735SELECT * FROM bug23760_log ORDER BY id| 5736id reason ammount 57371 Test is working 7 57382 Test2 is working 7 57393 Test2 is working 7 5740SELECT * FROM bug23760 ORDER by ID| 5741id num 57421 2 57432 3 57443 3 57454 4 57465 5 57476 7 57487 10 5749SET max_sp_recursion_depth= @save_max_sp_recursion| 5750SELECT bug23760_rc_test(123)| 5751bug23760_rc_test(123) 5752123 5753INSERT INTO bug23760 (num) VALUES (13), (21), (34), (55)| 5754SELECT bug23760_rc_test(ROW_COUNT())| 5755bug23760_rc_test(ROW_COUNT()) 57564 5757DROP TABLE bug23760, bug23760_log| 5758DROP PROCEDURE bug23760_update_log| 5759DROP PROCEDURE bug23760_test_row_count| 5760DROP PROCEDURE bug23760_test_row_count2| 5761DROP FUNCTION bug23760_rc_test| 5762DROP PROCEDURE IF EXISTS bug24117| 5763DROP TABLE IF EXISTS t3| 5764CREATE TABLE t3(c1 ENUM('abc'))| 5765INSERT INTO t3 VALUES('abc')| 5766CREATE PROCEDURE bug24117() 5767BEGIN 5768DECLARE t3c1 ENUM('abc'); 5769DECLARE mycursor CURSOR FOR SELECT c1 FROM t3; 5770OPEN mycursor; 5771FLUSH TABLES; 5772FETCH mycursor INTO t3c1; 5773CLOSE mycursor; 5774END| 5775CALL bug24117()| 5776DROP PROCEDURE bug24117| 5777DROP TABLE t3| 5778drop function if exists func_8407_a| 5779drop function if exists func_8407_b| 5780create function func_8407_a() returns int 5781begin 5782declare x int; 5783declare continue handler for sqlexception 5784begin 5785end; 5786select 1 from no_such_view limit 1 into x; 5787return x; 5788end| 5789create function func_8407_b() returns int 5790begin 5791declare x int default 0; 5792declare continue handler for sqlstate '42S02' 5793 begin 5794set x:= x+1000; 5795end; 5796case (select 1 from no_such_view limit 1) 5797when 1 then set x:= x+1; 5798when 2 then set x:= x+2; 5799else set x:= x+100; 5800end case; 5801set x:=x + 500; 5802return x; 5803end| 5804select func_8407_a()| 5805func_8407_a() 5806NULL 5807select func_8407_b()| 5808func_8407_b() 58091500 5810drop function func_8407_a| 5811drop function func_8407_b| 5812drop table if exists table_26503| 5813drop procedure if exists proc_26503_ok_1| 5814drop procedure if exists proc_26503_ok_2| 5815drop procedure if exists proc_26503_ok_3| 5816drop procedure if exists proc_26503_ok_4| 5817create table table_26503(a int unique)| 5818create procedure proc_26503_ok_1(v int) 5819begin 5820declare i int default 5; 5821declare continue handler for sqlexception 5822begin 5823select 'caught something'; 5824retry: 5825while i > 0 do 5826begin 5827set i = i - 1; 5828select 'looping', i; 5829iterate retry; 5830select 'dead code'; 5831end; 5832end while retry; 5833select 'leaving handler'; 5834end; 5835select 'do something'; 5836insert into table_26503 values (v); 5837select 'do something again'; 5838insert into table_26503 values (v); 5839end| 5840create procedure proc_26503_ok_2(v int) 5841begin 5842declare i int default 5; 5843declare continue handler for sqlexception 5844begin 5845select 'caught something'; 5846retry: 5847while i > 0 do 5848begin 5849set i = i - 1; 5850select 'looping', i; 5851leave retry; 5852select 'dead code'; 5853end; 5854end while; 5855select 'leaving handler'; 5856end; 5857select 'do something'; 5858insert into table_26503 values (v); 5859select 'do something again'; 5860insert into table_26503 values (v); 5861end| 5862create procedure proc_26503_ok_3(v int) 5863begin 5864declare i int default 5; 5865retry: 5866begin 5867declare continue handler for sqlexception 5868begin 5869select 'caught something'; 5870retry: 5871while i > 0 do 5872begin 5873set i = i - 1; 5874select 'looping', i; 5875iterate retry; 5876select 'dead code'; 5877end; 5878end while retry; 5879select 'leaving handler'; 5880end; 5881select 'do something'; 5882insert into table_26503 values (v); 5883select 'do something again'; 5884insert into table_26503 values (v); 5885end; 5886end| 5887create procedure proc_26503_ok_4(v int) 5888begin 5889declare i int default 5; 5890retry: 5891begin 5892declare continue handler for sqlexception 5893begin 5894select 'caught something'; 5895retry: 5896while i > 0 do 5897begin 5898set i = i - 1; 5899select 'looping', i; 5900leave retry; 5901select 'dead code'; 5902end; 5903end while; 5904select 'leaving handler'; 5905end; 5906select 'do something'; 5907insert into table_26503 values (v); 5908select 'do something again'; 5909insert into table_26503 values (v); 5910end; 5911end| 5912call proc_26503_ok_1(1)| 5913do something 5914do something 5915do something again 5916do something again 5917caught something 5918caught something 5919looping i 5920looping 4 5921looping i 5922looping 3 5923looping i 5924looping 2 5925looping i 5926looping 1 5927looping i 5928looping 0 5929leaving handler 5930leaving handler 5931call proc_26503_ok_2(2)| 5932do something 5933do something 5934do something again 5935do something again 5936caught something 5937caught something 5938looping i 5939looping 4 5940leaving handler 5941leaving handler 5942call proc_26503_ok_3(3)| 5943do something 5944do something 5945do something again 5946do something again 5947caught something 5948caught something 5949looping i 5950looping 4 5951looping i 5952looping 3 5953looping i 5954looping 2 5955looping i 5956looping 1 5957looping i 5958looping 0 5959leaving handler 5960leaving handler 5961call proc_26503_ok_4(4)| 5962do something 5963do something 5964do something again 5965do something again 5966caught something 5967caught something 5968looping i 5969looping 4 5970leaving handler 5971leaving handler 5972drop table table_26503| 5973drop procedure proc_26503_ok_1| 5974drop procedure proc_26503_ok_2| 5975drop procedure proc_26503_ok_3| 5976drop procedure proc_26503_ok_4| 5977DROP FUNCTION IF EXISTS bug25373| 5978CREATE FUNCTION bug25373(p1 INTEGER) RETURNS INTEGER 5979LANGUAGE SQL DETERMINISTIC 5980RETURN p1;| 5981CREATE TABLE t3 (f1 INT, f2 FLOAT)| 5982INSERT INTO t3 VALUES (1, 3.4), (1, 2), (1, 0.9), (2, 8), (2, 7)| 5983SELECT SUM(f2), bug25373(f1) FROM t3 GROUP BY bug25373(f1) WITH ROLLUP| 5984SUM(f2) bug25373(f1) 59856.300000071525574 1 598615 2 598721.300000071525574 NULL 5988DROP FUNCTION bug25373| 5989DROP TABLE t3| 5990DROP DATABASE IF EXISTS mysqltest1| 5991DROP DATABASE IF EXISTS mysqltest2| 5992CREATE DATABASE mysqltest1| 5993CREATE DATABASE mysqltest2| 5994CREATE PROCEDURE mysqltest1.p1() 5995DROP DATABASE mysqltest2| 5996use mysqltest2| 5997CALL mysqltest1.p1()| 5998Warnings: 5999Note 1049 Unknown database 'mysqltest2' 6000SELECT DATABASE()| 6001DATABASE() 6002NULL 6003DROP DATABASE mysqltest1| 6004use test| 6005drop function if exists bug20777| 6006drop table if exists examplebug20777| 6007create function bug20777(f1 bigint unsigned) returns bigint unsigned 6008begin 6009set f1 = (f1 - 10); set f1 = (f1 + 10); 6010return f1; 6011end| 6012select bug20777(9223372036854775803) as '9223372036854775803 2**63-5'; 60139223372036854775803 2**63-5 60149223372036854775803 6015select bug20777(9223372036854775804) as '9223372036854775804 2**63-4'; 60169223372036854775804 2**63-4 60179223372036854775804 6018select bug20777(9223372036854775805) as '9223372036854775805 2**63-3'; 60199223372036854775805 2**63-3 60209223372036854775805 6021select bug20777(9223372036854775806) as '9223372036854775806 2**63-2'; 60229223372036854775806 2**63-2 60239223372036854775806 6024select bug20777(9223372036854775807) as '9223372036854775807 2**63-1'; 60259223372036854775807 2**63-1 60269223372036854775807 6027select bug20777(9223372036854775808) as '9223372036854775808 2**63+0'; 60289223372036854775808 2**63+0 60299223372036854775808 6030select bug20777(9223372036854775809) as '9223372036854775809 2**63+1'; 60319223372036854775809 2**63+1 60329223372036854775809 6033select bug20777(9223372036854775810) as '9223372036854775810 2**63+2'; 60349223372036854775810 2**63+2 60359223372036854775810 6036select bug20777(-9223372036854775808) as 'lower bounds signed bigint'; 6037ERROR 22003: BIGINT UNSIGNED value is out of range in '(f1@0 - 10)' 6038select bug20777(9223372036854775807) as 'upper bounds signed bigint'; 6039upper bounds signed bigint 60409223372036854775807 6041select bug20777(0) as 'lower bounds unsigned bigint'; 6042ERROR 22003: BIGINT UNSIGNED value is out of range in '(f1@0 - 10)' 6043select bug20777(18446744073709551615) as 'upper bounds unsigned bigint'; 6044upper bounds unsigned bigint 604518446744073709551615 6046select bug20777(18446744073709551616) as 'upper bounds unsigned bigint + 1'; 6047upper bounds unsigned bigint + 1 604818446744073709551615 6049Warnings: 6050Warning 1264 Out of range value for column 'f1' at row 1 6051select bug20777(-1) as 'lower bounds unsigned bigint - 1'; 6052ERROR 22003: BIGINT UNSIGNED value is out of range in '(f1@0 - 10)' 6053create table examplebug20777 as select 60540 as 'i', 6055bug20777(9223372036854775806) as '2**63-2', 6056bug20777(9223372036854775807) as '2**63-1', 6057bug20777(9223372036854775808) as '2**63', 6058bug20777(9223372036854775809) as '2**63+1', 6059bug20777(18446744073709551614) as '2**64-2', 6060bug20777(18446744073709551615) as '2**64-1', 6061bug20777(18446744073709551616) as '2**64'; 6062Warnings: 6063Warning 1264 Out of range value for column 'f1' at row 1 6064insert into examplebug20777 values (1, 9223372036854775806, 9223372036854775807, 223372036854775808, 9223372036854775809, 18446744073709551614, 18446744073709551615, 8446744073709551616); 6065show create table examplebug20777; 6066Table Create Table 6067examplebug20777 CREATE TABLE `examplebug20777` ( 6068 `i` int(1) NOT NULL DEFAULT '0', 6069 `2**63-2` bigint(20) unsigned DEFAULT NULL, 6070 `2**63-1` bigint(20) unsigned DEFAULT NULL, 6071 `2**63` bigint(20) unsigned DEFAULT NULL, 6072 `2**63+1` bigint(20) unsigned DEFAULT NULL, 6073 `2**64-2` bigint(20) unsigned DEFAULT NULL, 6074 `2**64-1` bigint(20) unsigned DEFAULT NULL, 6075 `2**64` bigint(20) unsigned DEFAULT NULL 6076) ENGINE=MyISAM DEFAULT CHARSET=latin1 6077select * from examplebug20777 order by i; 6078i 2**63-2 2**63-1 2**63 2**63+1 2**64-2 2**64-1 2**64 60790 9223372036854775806 9223372036854775807 9223372036854775808 9223372036854775809 18446744073709551614 18446744073709551615 18446744073709551615 60801 9223372036854775806 9223372036854775807 223372036854775808 9223372036854775809 18446744073709551614 18446744073709551615 8446744073709551616 6081drop table examplebug20777; 6082select bug20777(18446744073709551613)+1; 6083bug20777(18446744073709551613)+1 608418446744073709551614 6085drop function bug20777; 6086DROP FUNCTION IF EXISTS bug5274_f1| 6087DROP FUNCTION IF EXISTS bug5274_f2| 6088CREATE FUNCTION bug5274_f1(p1 CHAR) RETURNS CHAR 6089RETURN CONCAT(p1, p1)| 6090CREATE FUNCTION bug5274_f2() RETURNS CHAR 6091BEGIN 6092DECLARE v1 INT DEFAULT 0; 6093DECLARE v2 CHAR DEFAULT 'x'; 6094WHILE v1 < 30 DO 6095SET v1 = v1 + 1; 6096SET v2 = bug5274_f1(v2); 6097END WHILE; 6098RETURN v2; 6099END| 6100SELECT bug5274_f2()| 6101bug5274_f2() 6102x 6103DROP FUNCTION bug5274_f1| 6104DROP FUNCTION bug5274_f2| 6105drop procedure if exists proc_21513| 6106create procedure proc_21513()`my_label`:BEGIN END| 6107show create procedure proc_21513| 6108Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 6109proc_21513 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_21513`() 6110`my_label`:BEGIN END utf8 utf8_general_ci latin1_swedish_ci 6111drop procedure proc_21513| 6112End of 5.0 tests. 6113drop table t1,t2; 6114CREATE TABLE t1 (a int auto_increment primary key) engine=MyISAM; 6115CREATE TABLE t2 (a int auto_increment primary key, b int) engine=innodb; 6116set @a=0; 6117CREATE function bug27354() RETURNS int not deterministic 6118begin 6119insert into t1 values (null); 6120set @a=@a+1; 6121return @a; 6122end| 6123update t2 set b=1 where a=bug27354(); 6124select count(t_1.a),count(t_2.a) from t1 as t_1, t2 as t_2 /* must be 0,0 */; 6125count(t_1.a) count(t_2.a) 61260 0 6127insert into t2 values (1,1),(2,2),(3,3); 6128update t2 set b=-b where a=bug27354(); 6129select * from t2 /* must return 1,-1 ... */; 6130a b 61311 -1 61322 -2 61333 -3 6134select count(*) from t1 /* must be 3 */; 6135count(*) 61363 6137drop table t1,t2; 6138drop function bug27354; 6139CREATE TABLE t1 (a INT); 6140INSERT INTO t1 VALUES (1),(2); 6141CREATE FUNCTION metered(a INT) RETURNS INT RETURN 12; 6142CREATE VIEW v1 AS SELECT test.metered(a) as metered FROM t1; 6143SHOW CREATE VIEW v1; 6144View Create View character_set_client collation_connection 6145v1 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 6146DROP VIEW v1; 6147DROP FUNCTION metered; 6148DROP TABLE t1; 6149SET @p1_p2_cnt= 2; 6150CREATE TABLE t1 (c1 INT); 6151CREATE VIEW v1 AS SELECT * FROM t1; 6152PREPARE s1 FROM 'SELECT c1 FROM v1'; 6153EXECUTE s1; 6154c1 6155EXECUTE s1; 6156c1 6157CREATE PROCEDURE p1(IN loops BIGINT(19) UNSIGNED) 6158BEGIN 6159WHILE loops > 0 DO 6160SELECT c1 FROM v1; 6161SET loops = loops - 1; 6162END WHILE; 6163END| 6164CREATE PROCEDURE p2(IN loops BIGINT(19) UNSIGNED) 6165BEGIN 6166WHILE loops > 0 DO 6167SELECT c1 FROM v1; 6168CALL p1(@p1_p2_cnt); 6169SET loops = loops - 1; 6170END WHILE; 6171END| 6172CREATE FUNCTION f1(loops INT UNSIGNED) 6173RETURNS INT 6174BEGIN 6175DECLARE tmp INT; 6176WHILE loops > 0 DO 6177SELECT c1 INTO tmp FROM v1; 6178SET loops = loops - 1; 6179END WHILE; 6180RETURN loops; 6181END| 6182CALL p1(2); 6183c1 6184c1 6185CALL p2(2); 6186c1 6187c1 6188c1 6189c1 6190c1 6191c1 6192SELECT f1(2); 6193f1(2) 61940 6195PREPARE s1 FROM 'SELECT f1(2)'; 6196EXECUTE s1; 6197f1(2) 61980 6199EXECUTE s1; 6200f1(2) 62010 6202DROP PROCEDURE p1; 6203DROP PROCEDURE p2; 6204DROP FUNCTION f1; 6205DROP VIEW v1; 6206DROP TABLE t1; 6207drop database if exists mysqltest_db1; 6208create database mysqltest_db1; 6209create procedure mysqltest_db1.sp_bug28551() begin end; 6210call mysqltest_db1.sp_bug28551(); 6211show warnings; 6212Level Code Message 6213drop database mysqltest_db1; 6214drop database if exists mysqltest_db1; 6215drop table if exists test.t1; 6216create database mysqltest_db1; 6217use mysqltest_db1; 6218drop database mysqltest_db1; 6219create table test.t1 (id int); 6220insert into test.t1 (id) values (1); 6221create procedure test.sp_bug29050() begin select * from t1; end// 6222show warnings; 6223Level Code Message 6224call test.sp_bug29050(); 6225id 62261 6227show warnings; 6228Level Code Message 6229use test; 6230drop procedure sp_bug29050; 6231drop table t1; 6232SET NAMES latin1; 6233CREATE PROCEDURE p1() 6234BEGIN 6235DECLARE ��� INT; 6236SELECT ���; 6237END| 6238CALL p1(); 6239��� 6240NULL 6241SET NAMES default; 6242DROP PROCEDURE p1; 6243drop procedure if exists proc_25411_a; 6244drop procedure if exists proc_25411_b; 6245drop procedure if exists proc_25411_c; 6246create procedure proc_25411_a() 6247begin 6248/* real comment */ 6249select 1; 6250/*! select 2; */ 6251select 3; 6252/*!00000 select 4; */ 6253/*!99999 select 5; */ 6254end 6255$$ 6256create procedure proc_25411_b( 6257/* real comment */ 6258/*! p1 int, */ 6259/*!00000 p2 int */ 6260/*!99999 ,p3 int */ 6261) 6262begin 6263select p1, p2; 6264end 6265$$ 6266create procedure proc_25411_c() 6267begin 6268select 1/*!,2*//*!00000,3*//*!99999,4*/; 6269select 1/*! ,2*//*!00000 ,3*//*!99999 ,4*/; 6270select 1/*!,2 *//*!00000,3 *//*!99999,4 */; 6271select 1/*! ,2 *//*!00000 ,3 *//*!99999 ,4 */; 6272select 1 /*!,2*/ /*!00000,3*/ /*!99999,4*/ ; 6273end 6274$$ 6275show create procedure proc_25411_a; 6276Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 6277proc_25411_a CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_a`() 6278begin 6279/* real comment */ 6280select 1; 6281 select 2; 6282select 3; 6283 select 4; 6284 6285end latin1 latin1_swedish_ci latin1_swedish_ci 6286call proc_25411_a(); 62871 62881 62892 62902 62913 62923 62934 62944 6295show create procedure proc_25411_b; 6296Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 6297proc_25411_b CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_b`( 6298/* real comment */ 6299 p1 int, 6300 p2 int 6301 6302) 6303begin 6304select p1, p2; 6305end latin1 latin1_swedish_ci latin1_swedish_ci 6306select name, param_list, body from mysql.proc where name like "%25411%"; 6307name param_list body 6308proc_25411_a begin 6309/* real comment */ 6310select 1; 6311 select 2; 6312select 3; 6313 select 4; 6314 6315end 6316proc_25411_b 6317/* real comment */ 6318 p1 int, 6319 p2 int 6320 6321 begin 6322select p1, p2; 6323end 6324proc_25411_c begin 6325select 1,2 ,3 ; 6326select 1 ,2 ,3 ; 6327select 1,2 ,3 ; 6328select 1 ,2 ,3 ; 6329select 1 ,2 ,3 ; 6330end 6331call proc_25411_b(10, 20); 6332p1 p2 633310 20 6334show create procedure proc_25411_c; 6335Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 6336proc_25411_c CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_c`() 6337begin 6338select 1,2 ,3 ; 6339select 1 ,2 ,3 ; 6340select 1,2 ,3 ; 6341select 1 ,2 ,3 ; 6342select 1 ,2 ,3 ; 6343end latin1 latin1_swedish_ci latin1_swedish_ci 6344call proc_25411_c(); 63451 2 3 63461 2 3 63471 2 3 63481 2 3 63491 2 3 63501 2 3 63511 2 3 63521 2 3 63531 2 3 63541 2 3 6355drop procedure proc_25411_a; 6356drop procedure proc_25411_b; 6357drop procedure proc_25411_c; 6358drop procedure if exists proc_26302; 6359create procedure proc_26302() 6360select 1 /* testing */; 6361show create procedure proc_26302; 6362Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 6363proc_26302 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_26302`() 6364select 1 /* testing */ latin1 latin1_swedish_ci latin1_swedish_ci 6365select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES 6366where ROUTINE_NAME = "proc_26302"; 6367ROUTINE_NAME ROUTINE_DEFINITION 6368proc_26302 select 1 /* testing */ 6369drop procedure proc_26302; 6370CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2; 6371CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3; 6372CREATE TABLE t1 (c1 INT, INDEX(c1)); 6373INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 6374CREATE VIEW v1 AS SELECT c1 FROM t1; 6375EXPLAIN SELECT * FROM t1 WHERE c1=1; 6376id select_type table partitions type possible_keys key key_len ref rows filtered Extra 63771 SIMPLE t1 NULL ref c1 c1 5 const 1 100.00 Using index 6378Warnings: 6379Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (`test`.`t1`.`c1` = 1) 6380EXPLAIN SELECT * FROM t1 WHERE c1=f1(); 6381id select_type table partitions type possible_keys key key_len ref rows filtered Extra 63821 SIMPLE t1 NULL ref c1 c1 5 const 1 100.00 Using index 6383Warnings: 6384Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (`test`.`t1`.`c1` = `f1`()) 6385EXPLAIN SELECT * FROM v1 WHERE c1=1; 6386id select_type table partitions type possible_keys key key_len ref rows filtered Extra 63871 SIMPLE t1 NULL ref c1 c1 5 const 1 100.00 Using index 6388Warnings: 6389Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (`test`.`t1`.`c1` = 1) 6390EXPLAIN SELECT * FROM v1 WHERE c1=f1(); 6391id select_type table partitions type possible_keys key key_len ref rows filtered Extra 63921 SIMPLE t1 NULL ref c1 c1 5 const 1 100.00 Using index 6393Warnings: 6394Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (`test`.`t1`.`c1` = `f1`()) 6395EXPLAIN SELECT * FROM t1 WHERE c1=f2(10); 6396id select_type table partitions type possible_keys key key_len ref rows filtered Extra 63971 SIMPLE t1 NULL ref c1 c1 5 const 1 100.00 Using index 6398Warnings: 6399Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (`test`.`t1`.`c1` = `f2`(10)) 6400EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1); 6401id select_type table partitions type possible_keys key key_len ref rows filtered Extra 64021 SIMPLE t1 NULL index NULL c1 5 NULL 5 20.00 Using where; Using index 6403Warnings: 6404Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (`test`.`t1`.`c1` = `f2`(`test`.`t1`.`c1`)) 6405EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand()); 6406id select_type table partitions type possible_keys key key_len ref rows filtered Extra 64071 SIMPLE t1 NULL index NULL c1 5 NULL 5 20.00 Using where; Using index 6408Warnings: 6409Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where (`test`.`t1`.`c1` = `f2`(rand())) 6410DROP VIEW v1; 6411DROP FUNCTION f1; 6412DROP FUNCTION f2; 6413DROP TABLE t1; 6414create function f1() 6415returns int(11) 6416not deterministic 6417contains sql 6418sql security definer 6419comment '' 6420begin 6421declare x int(11); 6422set x=-1; 6423return x; 6424end| 6425create view v1 as select 1 as one, f1() as days; 6426show create view test.v1; 6427View Create View character_set_client collation_connection 6428v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`v1` AS select 1 AS `one`,`f1`() AS `days` latin1 latin1_swedish_ci 6429select column_name from information_schema.columns 6430where table_name='v1' and table_schema='test'; 6431column_name 6432one 6433days 6434drop view v1; 6435drop function f1; 6436 6437# Bug#13675. 6438 6439DROP PROCEDURE IF EXISTS p1; 6440DROP PROCEDURE IF EXISTS p2; 6441DROP TABLE IF EXISTS t1; 6442 6443CREATE PROCEDURE p1(v DATETIME) CREATE TABLE t1 SELECT v; 6444CREATE PROCEDURE p2(v INT) CREATE TABLE t1 SELECT v; 6445 6446CALL p1(NOW()); 6447SHOW CREATE TABLE t1; 6448Table Create Table 6449t1 CREATE TABLE `t1` ( 6450 `v` datetime DEFAULT NULL 6451) ENGINE=MyISAM DEFAULT CHARSET=latin1 6452 6453DROP TABLE t1; 6454 6455CALL p1('text'); 6456Warnings: 6457Warning 1265 Data truncated for column 'v' at row 1 6458SHOW CREATE TABLE t1; 6459Table Create Table 6460t1 CREATE TABLE `t1` ( 6461 `v` datetime DEFAULT NULL 6462) ENGINE=MyISAM DEFAULT CHARSET=latin1 6463 6464DROP TABLE t1; 6465 6466CALL p2(10); 6467SHOW CREATE TABLE t1; 6468Table Create Table 6469t1 CREATE TABLE `t1` ( 6470 `v` bigint(11) DEFAULT NULL 6471) ENGINE=MyISAM DEFAULT CHARSET=latin1 6472 6473DROP TABLE t1; 6474 6475CALL p2('text'); 6476Warnings: 6477Warning 1366 Incorrect integer value: 'text' for column 'v' at row 1 6478SHOW CREATE TABLE t1; 6479Table Create Table 6480t1 CREATE TABLE `t1` ( 6481 `v` bigint(11) DEFAULT NULL 6482) ENGINE=MyISAM DEFAULT CHARSET=latin1 6483 6484DROP TABLE t1; 6485 6486DROP PROCEDURE p1; 6487DROP PROCEDURE p2; 6488 6489# 6490# Bug#31035. 6491# 6492 6493# 6494# - Prepare. 6495# 6496 6497DROP TABLE IF EXISTS t1; 6498DROP FUNCTION IF EXISTS f1; 6499DROP FUNCTION IF EXISTS f2; 6500DROP FUNCTION IF EXISTS f3; 6501DROP FUNCTION IF EXISTS f4; 6502 6503# 6504# - Create required objects. 6505# 6506 6507CREATE TABLE t1(c1 INT); 6508 6509INSERT INTO t1 VALUES (1), (2), (3); 6510 6511CREATE FUNCTION f1() 6512RETURNS INT 6513NOT DETERMINISTIC 6514RETURN 1; 6515 6516CREATE FUNCTION f2(p INT) 6517RETURNS INT 6518NOT DETERMINISTIC 6519RETURN 1; 6520 6521CREATE FUNCTION f3() 6522RETURNS INT 6523DETERMINISTIC 6524RETURN 1; 6525 6526CREATE FUNCTION f4(p INT) 6527RETURNS INT 6528DETERMINISTIC 6529RETURN 1; 6530 6531# 6532# - Check. 6533# 6534 6535SELECT f1() AS a FROM t1 GROUP BY a; 6536a 65371 6538 6539SELECT f2(@a) AS a FROM t1 GROUP BY a; 6540a 65411 6542 6543SELECT f3() AS a FROM t1 GROUP BY a; 6544a 65451 6546 6547SELECT f4(0) AS a FROM t1 GROUP BY a; 6548a 65491 6550 6551SELECT f4(@a) AS a FROM t1 GROUP BY a; 6552a 65531 6554 6555# 6556# - Cleanup. 6557# 6558 6559DROP TABLE t1; 6560DROP FUNCTION f1; 6561DROP FUNCTION f2; 6562DROP FUNCTION f3; 6563DROP FUNCTION f4; 6564 6565# 6566# Bug#31191. 6567# 6568 6569# 6570# - Prepare. 6571# 6572 6573DROP TABLE IF EXISTS t1; 6574DROP TABLE IF EXISTS t2; 6575DROP FUNCTION IF EXISTS f1; 6576 6577# 6578# - Create required objects. 6579# 6580 6581CREATE TABLE t1 ( 6582id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 6583barcode INT(8) UNSIGNED ZEROFILL nOT NULL, 6584PRIMARY KEY (id), 6585UNIQUE KEY barcode (barcode) 6586); 6587 6588INSERT INTO t1 (id, barcode) VALUES (1, 12345678); 6589INSERT INTO t1 (id, barcode) VALUES (2, 12345679); 6590 6591CREATE TABLE test.t2 ( 6592id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 6593barcode BIGINT(11) UNSIGNED ZEROFILL NOT NULL, 6594PRIMARY KEY (id) 6595); 6596 6597INSERT INTO test.t2 (id, barcode) VALUES (1, 12345106708); 6598INSERT INTO test.t2 (id, barcode) VALUES (2, 12345106709); 6599 6600CREATE FUNCTION f1(p INT(8)) 6601RETURNS BIGINT(11) UNSIGNED 6602READS SQL DATA 6603RETURN FLOOR(p/1000)*1000000 + 100000 + FLOOR((p MOD 1000)/10)*100 + (p MOD 10); 6604 6605# 6606# - Check. 6607# 6608 6609SELECT DISTINCT t1.barcode, f1(t1.barcode) 6610FROM t1 6611INNER JOIN t2 6612ON f1(t1.barcode) = t2.barcode 6613WHERE t1.barcode=12345678; 6614barcode f1(t1.barcode) 661512345678 12345106708 6616 6617# 6618# - Cleanup. 6619# 6620 6621DROP TABLE t1; 6622DROP TABLE t2; 6623DROP FUNCTION f1; 6624 6625# 6626# Bug#31226. 6627# 6628 6629# 6630# - Prepare. 6631# 6632 6633DROP TABLE IF EXISTS t1; 6634DROP FUNCTION IF EXISTS f1; 6635 6636# 6637# - Create required objects. 6638# 6639 6640CREATE TABLE t1(id INT); 6641 6642INSERT INTO t1 VALUES (1), (2), (3); 6643 6644CREATE FUNCTION f1() 6645RETURNS DATETIME 6646NOT DETERMINISTIC NO SQL 6647RETURN NOW(); 6648 6649# 6650# - Check. 6651# 6652 6653SELECT f1() FROM t1 GROUP BY 1; 6654f1() 6655<timestamp> 6656 6657# 6658# - Cleanup. 6659# 6660 6661DROP TABLE t1; 6662DROP FUNCTION f1; 6663 6664DROP PROCEDURE IF EXISTS db28318_a.t1; 6665DROP PROCEDURE IF EXISTS db28318_b.t2; 6666DROP DATABASE IF EXISTS db28318_a; 6667DROP DATABASE IF EXISTS db28318_b; 6668CREATE DATABASE db28318_a; 6669CREATE DATABASE db28318_b; 6670CREATE PROCEDURE db28318_a.t1() SELECT "db28318_a.t1"; 6671CREATE PROCEDURE db28318_b.t2() CALL t1(); 6672use db28318_a; 6673CALL db28318_b.t2(); 6674ERROR 42000: PROCEDURE db28318_b.t1 does not exist 6675DROP PROCEDURE db28318_a.t1; 6676DROP PROCEDURE db28318_b.t2; 6677DROP DATABASE db28318_a; 6678DROP DATABASE db28318_b; 6679use test; 6680DROP TABLE IF EXISTS t1; 6681DROP PROCEDURE IF EXISTS bug29770; 6682CREATE TABLE t1(a int); 6683CREATE PROCEDURE bug29770() 6684BEGIN 6685DECLARE CONTINUE HANDLER FOR SQLSTATE '42S22' SET @state:= 'run'; 6686DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @exception:= 'run'; 6687SELECT x FROM t1; 6688END| 6689CALL bug29770(); 6690SELECT @state, @exception; 6691@state @exception 6692run NULL 6693DROP TABLE t1; 6694DROP PROCEDURE bug29770; 6695use test; 6696drop table if exists t_33618; 6697drop procedure if exists proc_33618; 6698create table t_33618 (`a` int, unique(`a`), `b` varchar(30)) engine=myisam; 6699insert into t_33618 (`a`,`b`) values (1,'1'),(2,'2'); 6700create procedure proc_33618(num int) 6701begin 6702declare count1 int default '0'; 6703declare vb varchar(30); 6704declare last_row int; 6705while(num>=1) do 6706set num=num-1; 6707begin 6708declare cur1 cursor for select `a` from t_33618; 6709declare continue handler for not found set last_row = 1; 6710set last_row:=0; 6711open cur1; 6712rep1: 6713repeat 6714begin 6715declare exit handler for 1062 begin end; 6716fetch cur1 into vb; 6717if (last_row = 1) then 6718leave rep1; 6719end if; 6720end; 6721until last_row=1 6722end repeat; 6723close cur1; 6724end; 6725end while; 6726end// 6727call proc_33618(20); 6728drop table t_33618; 6729drop procedure proc_33618; 6730# 6731# Bug#30787: Stored function ignores user defined alias. 6732# 6733use test; 6734drop function if exists func30787; 6735create table t1(f1 int); 6736insert into t1 values(1),(2); 6737create function func30787(p1 int) returns int 6738begin 6739return p1; 6740end | 6741select (select func30787(f1)) as ttt from t1; 6742ttt 67431 67442 6745drop function func30787; 6746drop table t1; 6747CREATE TABLE t1 (id INT); 6748INSERT INTO t1 VALUES (1),(2),(3),(4); 6749CREATE PROCEDURE test_sp() 6750SELECT t1.* FROM t1 RIGHT JOIN t1 t2 ON t1.id=t2.id; 6751CALL test_sp(); 6752id 67531 67542 67553 67564 6757CALL test_sp(); 6758id 67591 67602 67613 67624 6763DROP PROCEDURE test_sp; 6764DROP TABLE t1; 6765create table t1(c1 INT); 6766create function f1(p1 int) returns varchar(32) 6767return 'aaa'; 6768create view v1 as select f1(c1) as parent_control_name from t1; 6769create procedure p1() 6770begin 6771select parent_control_name as c1 from v1; 6772end // 6773call p1(); 6774c1 6775call p1(); 6776c1 6777drop procedure p1; 6778drop function f1; 6779drop view v1; 6780drop table t1; 6781drop procedure if exists `p2` $ 6782create procedure `p2`(in `a` text charset utf8) 6783begin 6784declare `pos` int default 1; 6785declare `str` text charset utf8; 6786set `str` := `a`; 6787select substr(`str`, `pos`+ 1 ) into `str`; 6788end $ 6789call `p2`('s s s s s s'); 6790drop procedure `p2`; 6791drop table if exists t1; 6792drop procedure if exists p1; 6793create procedure p1() begin select * from t1; end$ 6794call p1$ 6795ERROR 42S02: Table 'test.t1' doesn't exist 6796create table t1 (a integer)$ 6797call p1$ 6798a 6799alter table t1 add b integer; 6800call p1$ 6801a b 6802drop table t1; 6803drop procedure p1; 6804# ------------------------------------------------------------------ 6805# -- End of 5.0 tests 6806# ------------------------------------------------------------------ 6807 6808# 6809# Bug#20550. 6810# 6811 6812# 6813# - Prepare. 6814# 6815 6816DROP VIEW IF EXISTS v1; 6817DROP VIEW IF EXISTS v2; 6818DROP FUNCTION IF EXISTS f1; 6819DROP FUNCTION IF EXISTS f2; 6820 6821# 6822# - Create required objects. 6823# 6824 6825CREATE FUNCTION f1() RETURNS VARCHAR(65525) RETURN 'Hello'; 6826 6827CREATE FUNCTION f2() RETURNS TINYINT RETURN 1; 6828 6829CREATE VIEW v1 AS SELECT f1(); 6830 6831CREATE VIEW v2 AS SELECT f2(); 6832 6833# 6834# - Check. 6835# 6836 6837SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v1'; 6838DATA_TYPE 6839varchar 6840 6841SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v2'; 6842DATA_TYPE 6843tinyint 6844 6845# 6846# - Cleanup. 6847# 6848 6849DROP FUNCTION f1; 6850DROP FUNCTION f2; 6851DROP VIEW v1; 6852DROP VIEW v2; 6853 6854# 6855# - Bug#24923: prepare. 6856# 6857 6858DROP FUNCTION IF EXISTS f1; 6859 6860# 6861# - Bug#24923: create required objects. 6862# 6863 6864CREATE FUNCTION f1(p INT) 6865RETURNS ENUM ('Very_long_enum_element_identifier', 6866'Another_very_long_enum_element_identifier') 6867BEGIN 6868CASE p 6869WHEN 1 THEN 6870RETURN 'Very_long_enum_element_identifier'; 6871ELSE 6872RETURN 'Another_very_long_enum_element_identifier'; 6873END CASE; 6874END| 6875 6876# 6877# - Bug#24923: check. 6878# 6879 6880SELECT f1(1); 6881f1(1) 6882Very_long_enum_element_identifier 6883 6884SELECT f1(2); 6885f1(2) 6886Another_very_long_enum_element_identifier 6887 6888SHOW CREATE FUNCTION f1; 6889Function sql_mode Create Function character_set_client collation_connection Database Collation 6890f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`(p INT) RETURNS enum('Very_long_enum_element_identifier','Another_very_long_enum_element_identifier') CHARSET latin1 6891BEGIN 6892CASE p 6893WHEN 1 THEN 6894RETURN 'Very_long_enum_element_identifier'; 6895ELSE 6896RETURN 'Another_very_long_enum_element_identifier'; 6897END CASE; 6898END latin1 latin1_swedish_ci latin1_swedish_ci 6899# 6900# - Bug#24923: cleanup. 6901# 6902 6903DROP FUNCTION f1; 6904 6905drop procedure if exists p; 6906set @old_mode= @@sql_mode; 6907set @@sql_mode= cast(pow(2,32)-1 as unsigned integer); 6908Warnings: 6909Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 6910Warning 3090 Changing sql mode 'POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40' is deprecated. It will be removed in a future release. 6911select @@sql_mode into @full_mode; 6912create procedure p() begin end; 6913call p(); 6914set @@sql_mode= @old_mode; 6915Warnings: 6916Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 6917select replace(@full_mode, ',,,', ',NOT_USED,') into @full_mode; 6918select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode; 6919select name from mysql.proc where name = 'p' and sql_mode = @full_mode; 6920name 6921p 6922drop procedure p; 6923CREATE DEFINER = 'root'@'localhost' PROCEDURE p1() 6924NOT DETERMINISTIC 6925CONTAINS SQL 6926SQL SECURITY DEFINER 6927COMMENT '' 6928BEGIN 6929SHOW TABLE STATUS like 't1'; 6930END;// 6931CREATE TABLE t1 (f1 INT); 6932CALL p1(); 6933CALL p1(); 6934CALL p1(); 6935CALL p1(); 6936DROP PROCEDURE p1; 6937DROP TABLE t1; 6938CREATE TABLE t1 ( f1 integer, primary key (f1)); 6939CREATE TABLE t2 LIKE t1; 6940CREATE TEMPORARY TABLE t3 LIKE t1; 6941CREATE PROCEDURE p1 () BEGIN SELECT f1 FROM t3 AS A WHERE A.f1 IN ( SELECT f1 FROM t3 ) ; 6942END| 6943CALL p1; 6944ERROR HY000: Can't reopen table: 'A' 6945CREATE VIEW t3 AS SELECT f1 FROM t2 A WHERE A.f1 IN ( SELECT f1 FROM t2 ); 6946DROP TABLE t3; 6947CALL p1; 6948f1 6949CALL p1; 6950f1 6951DROP PROCEDURE p1; 6952DROP TABLE t1, t2; 6953DROP VIEW t3; 6954# 6955# Bug #46629: Item_in_subselect::val_int(): Assertion `0' 6956# on subquery inside a SP 6957# 6958CREATE TABLE t1(a INT); 6959CREATE TABLE t2(a INT, b INT PRIMARY KEY); 6960CREATE PROCEDURE p1 () 6961BEGIN 6962SELECT a FROM t1 A WHERE A.b IN (SELECT b FROM t2 AS B); 6963END| 6964CALL p1; 6965ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' 6966CALL p1; 6967ERROR 42S22: Unknown column 'A.b' in 'IN/ALL/ANY subquery' 6968DROP PROCEDURE p1; 6969DROP TABLE t1, t2; 6970# 6971# Bug#47627: SET @@{global.session}.local_variable in stored routine causes crash 6972# Bug#48626: Crash or lost connection using SET for declared variables with @@ 6973# 6974DROP PROCEDURE IF EXISTS p1; 6975DROP PROCEDURE IF EXISTS p2; 6976DROP PROCEDURE IF EXISTS p3; 6977CREATE PROCEDURE p1() 6978BEGIN 6979DECLARE v INT DEFAULT 0; 6980SET @@SESSION.v= 10; 6981END// 6982ERROR HY000: Unknown system variable 'v' 6983CREATE PROCEDURE p2() 6984BEGIN 6985DECLARE v INT DEFAULT 0; 6986SET v= 10; 6987END// 6988call p2()// 6989CREATE PROCEDURE p3() 6990BEGIN 6991DECLARE v INT DEFAULT 0; 6992SELECT @@SESSION.v; 6993END// 6994ERROR HY000: Unknown system variable 'v' 6995CREATE PROCEDURE p4() 6996BEGIN 6997DECLARE v INT DEFAULT 0; 6998SET @@GLOBAL.v= 10; 6999END// 7000ERROR HY000: Unknown system variable 'v' 7001CREATE PROCEDURE p5() 7002BEGIN 7003DECLARE init_connect INT DEFAULT 0; 7004SET init_connect= 10; 7005SET @@GLOBAL.init_connect= 'SELECT 1'; 7006SET @@SESSION.IDENTITY= 1; 7007SELECT @@SESSION.IDENTITY; 7008SELECT @@GLOBAL.init_connect; 7009SELECT init_connect; 7010END// 7011CREATE PROCEDURE p6() 7012BEGIN 7013DECLARE v INT DEFAULT 0; 7014SET @@v= 0; 7015END// 7016ERROR HY000: Unknown system variable 'v' 7017SET @old_init_connect= @@GLOBAL.init_connect; 7018CALL p5(); 7019@@SESSION.IDENTITY 70201 7021@@GLOBAL.init_connect 7022SELECT 1 7023init_connect 702410 7025SET @@GLOBAL.init_connect= @old_init_connect; 7026DROP PROCEDURE p2; 7027DROP PROCEDURE p5; 7028# 7029# Bug#11840395 (formerly known as bug#60347): 7030# The string "versiondata" seems 7031# to be 'leaking' into the schema name space 7032# 7033DROP DATABASE IF EXISTS mixedCaseDbName; 7034CREATE DATABASE mixedCaseDbName; 7035CREATE PROCEDURE mixedCaseDbName.tryMyProc() begin end| 7036CREATE FUNCTION mixedCaseDbName.tryMyFunc() returns text begin return 'IT WORKS'; end 7037| 7038call mixedCaseDbName.tryMyProc(); 7039select mixedCaseDbName.tryMyFunc(); 7040mixedCaseDbName.tryMyFunc() 7041IT WORKS 7042DROP DATABASE mixedCaseDbName; 7043# 7044# Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C 7045# 7046CREATE TABLE t1 (a INT, b INT, KEY(b)); 7047CREATE TABLE t2 (c INT, d INT, KEY(c)); 7048INSERT INTO t1 VALUES (1,1),(1,1),(1,2); 7049INSERT INTO t2 VALUES (1,1),(1,2); 7050CREATE FUNCTION f1() RETURNS INT DETERMINISTIC 7051BEGIN 7052DECLARE a int; 7053-- SQL statement inside 7054SELECT 1 INTO a; 7055RETURN a; 7056END $ 7057SELECT COUNT(DISTINCT d) FROM t1, t2 WHERE a = c AND b = f1(); 7058COUNT(DISTINCT d) 70592 7060DROP FUNCTION f1; 7061DROP TABLE t1, t2; 7062# ------------------------------------------------------------------ 7063# -- End of 5.1 tests 7064# ------------------------------------------------------------------ 7065DROP FUNCTION IF EXISTS f1; 7066DROP TABLE IF EXISTS t_non_existing; 7067DROP TABLE IF EXISTS t1; 7068CREATE FUNCTION f1() RETURNS INT 7069BEGIN 7070DECLARE v INT; 7071SELECT a INTO v FROM t_non_existing; 7072RETURN 1; 7073END| 7074CREATE TABLE t1 (a INT) ENGINE = myisam; 7075INSERT INTO t1 VALUES (1); 7076SELECT * FROM t1 WHERE a = f1(); 7077ERROR 42S02: Table 'test.t_non_existing' doesn't exist 7078DROP FUNCTION f1; 7079DROP TABLE t1; 7080DROP PROCEDURE IF EXISTS p1; 7081CREATE PROCEDURE p1(a INT, b CHAR) 7082BEGIN 7083IF a > 0 THEN 7084CALL p1(a-1, 'ab'); 7085ELSE 7086SELECT 1; 7087END IF; 7088END| 7089SET @save_max_sp_recursion= @@max_sp_recursion_depth; 7090SET @@max_sp_recursion_depth= 5; 7091CALL p1(4, 'a'); 70921 70931 7094Warnings: 7095Warning 1265 Data truncated for column 'b' at row 1 7096Warning 1265 Data truncated for column 'b' at row 1 7097Warning 1265 Data truncated for column 'b' at row 1 7098Warning 1265 Data truncated for column 'b' at row 1 7099SET @@max_sp_recursion_depth= @save_max_sp_recursion; 7100DROP PROCEDURE p1; 7101DROP PROCEDURE IF EXISTS p1; 7102CREATE PROCEDURE p1(a CHAR) 7103BEGIN 7104SELECT 1; 7105SELECT CAST('10 ' as UNSIGNED INTEGER); 7106END| 7107CALL p1('data truncated parameter'); 71081 71091 7110CAST('10 ' as UNSIGNED INTEGER) 711110 7112Warnings: 7113Warning 1265 Data truncated for column 'a' at row 1 7114Warning 1292 Truncated incorrect INTEGER value: '10 ' 7115DROP PROCEDURE p1; 7116DROP PROCEDURE IF EXISTS p1; 7117DROP PROCEDURE IF EXISTS p2; 7118DROP PROCEDURE IF EXISTS p3; 7119DROP PROCEDURE IF EXISTS p4; 7120CREATE PROCEDURE p1() 7121CALL p2()| 7122CREATE PROCEDURE p2() 7123CALL p3()| 7124CREATE PROCEDURE p3() 7125CALL p4()| 7126CREATE PROCEDURE p4() 7127BEGIN 7128SELECT 1; 7129SELECT CAST('10 ' as UNSIGNED INTEGER); 7130END| 7131CALL p1(); 71321 71331 7134CAST('10 ' as UNSIGNED INTEGER) 713510 7136Warnings: 7137Warning 1292 Truncated incorrect INTEGER value: '10 ' 7138DROP PROCEDURE p1; 7139DROP PROCEDURE p2; 7140DROP PROCEDURE p3; 7141DROP PROCEDURE p4; 7142DROP FUNCTION IF EXISTS f1; 7143DROP FUNCTION IF EXISTS f2; 7144DROP FUNCTION IF EXISTS f3; 7145DROP FUNCTION IF EXISTS f4; 7146DROP TABLE IF EXISTS t1; 7147CREATE TABLE t1 (a CHAR(2)); 7148INSERT INTO t1 VALUES ('aa'); 7149CREATE FUNCTION f1() RETURNS CHAR 7150RETURN (SELECT f2())| 7151CREATE FUNCTION f2() RETURNS CHAR 7152RETURN (SELECT f3())| 7153CREATE FUNCTION f3() RETURNS CHAR 7154RETURN (SELECT f4())| 7155CREATE FUNCTION f4() RETURNS CHAR 7156BEGIN 7157RETURN (SELECT a FROM t1); 7158END| 7159SELECT f1(); 7160f1() 7161a 7162Warnings: 7163Warning 1265 Data truncated for column 'f4()' at row 1 7164DROP FUNCTION f1; 7165DROP FUNCTION f2; 7166DROP FUNCTION f3; 7167DROP FUNCTION f4; 7168DROP TABLE t1; 7169# 7170# Bug#34197: CREATE PROCEDURE fails when COMMENT truncated in non 7171# strict SQL mode 7172# 7173DROP PROCEDURE IF EXISTS p1; 7174CREATE PROCEDURE p1 () 7175COMMENT 7176'12345678901234567890123456789012345678901234567890123456789012345678901234567890' 7177BEGIN 7178END; 7179SELECT comment FROM mysql.proc WHERE name = "p1"; 7180comment 718112345678901234567890123456789012345678901234567890123456789012345678901234567890 7182SELECT routine_comment FROM information_schema.routines WHERE routine_name = "p1"; 7183routine_comment 718412345678901234567890123456789012345678901234567890123456789012345678901234567890 7185DROP PROCEDURE p1; 7186# 7187# Bug #47313 assert in check_key_in_view during CALL procedure 7188# 7189DROP TABLE IF EXISTS t1; 7190DROP VIEW IF EXISTS t1, t2_unrelated; 7191DROP PROCEDURE IF EXISTS p1; 7192CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x); 7193CREATE VIEW t1 AS SELECT 10 AS f1; 7194# t1 refers to the view 7195CALL p1(1); 7196ERROR HY000: The target table t1 of the INSERT is not insertable-into 7197CREATE TEMPORARY TABLE t1 (f1 INT); 7198# t1 still refers to the view since it was inlined 7199CALL p1(2); 7200ERROR HY000: The target table t1 of the INSERT is not insertable-into 7201DROP VIEW t1; 7202# t1 now refers to the temporary table 7203CALL p1(3); 7204# Check which values were inserted into the temp table. 7205SELECT * FROM t1; 7206f1 72073 7208DROP TEMPORARY TABLE t1; 7209DROP PROCEDURE p1; 7210# Now test what happens if the sp cache is invalidated. 7211CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x); 7212CREATE VIEW t1 AS SELECT 10 AS f1; 7213CREATE VIEW v2_unrelated AS SELECT 1 AS r1; 7214# Load the procedure into the sp cache 7215CALL p1(4); 7216ERROR HY000: The target table t1 of the INSERT is not insertable-into 7217CREATE TEMPORARY TABLE t1 (f1 int); 7218ALTER VIEW v2_unrelated AS SELECT 2 AS r1; 7219# Alter view causes the sp cache to be invalidated. 7220# Now t1 refers to the temporary table, not the view. 7221CALL p1(5); 7222# Check which values were inserted into the temp table. 7223SELECT * FROM t1; 7224f1 72255 7226DROP TEMPORARY TABLE t1; 7227DROP VIEW t1, v2_unrelated; 7228DROP PROCEDURE p1; 7229CREATE PROCEDURE p1(IN x INT) INSERT INTO t1 VALUES (x); 7230CREATE TEMPORARY TABLE t1 (f1 INT); 7231# t1 refers to the temporary table 7232CALL p1(6); 7233CREATE VIEW t1 AS SELECT 10 AS f1; 7234# Create view causes the sp cache to be invalidated. 7235# t1 still refers to the temporary table since it shadows the view. 7236CALL p1(7); 7237DROP VIEW t1; 7238# Check which values were inserted into the temp table. 7239SELECT * FROM t1; 7240f1 72416 72427 7243DROP TEMPORARY TABLE t1; 7244DROP PROCEDURE p1; 7245# 7246# Bug #11918 Can't use a declared variable in LIMIT clause 7247# 7248drop table if exists t1; 7249drop procedure if exists p1; 7250create table t1 (c1 int); 7251insert into t1 (c1) values (1), (2), (3), (4), (5); 7252create procedure p1() 7253begin 7254declare a integer; 7255declare b integer; 7256select * from t1 limit a, b; 7257end| 7258# How do we handle NULL limit values? 7259call p1(); 7260c1 7261drop table t1; 7262create table t1 (a int); 7263insert into t1 (a) values (1), (2), (3), (4), (5); 7264# 7265# Do we correctly resolve identifiers in LIMIT? 7266# 7267call p1(); 7268a 7269drop table t1; 7270create table t1 (c1 int); 7271insert into t1 (c1) values (1), (2), (3), (4), (5); 7272drop procedure p1; 7273# Try to create a procedure that 7274# refers to non-existing variables. 7275create procedure p1(p1 integer, p2 integer) 7276select * from t1 limit a, b; 7277ERROR 42000: Undeclared variable: a 7278# 7279# Try to use data types not allowed in LIMIT 7280# 7281create procedure p1(p1 date, p2 date) select * from t1 limit p1, p2; 7282ERROR HY000: A variable of a non-integer based type in LIMIT clause 7283create procedure p1(p1 integer, p2 float) select * from t1 limit p1, p2; 7284ERROR HY000: A variable of a non-integer based type in LIMIT clause 7285create procedure p1(p1 integer, p2 char(1)) select * from t1 limit p1, p2; 7286ERROR HY000: A variable of a non-integer based type in LIMIT clause 7287create procedure p1(p1 varchar(5), p2 char(1)) select * from t1 limit p1, p2; 7288ERROR HY000: A variable of a non-integer based type in LIMIT clause 7289create procedure p1(p1 decimal, p2 decimal) select * from t1 limit p1, p2; 7290ERROR HY000: A variable of a non-integer based type in LIMIT clause 7291create procedure p1(p1 double, p2 double) select * from t1 limit p1, p2; 7292ERROR HY000: A variable of a non-integer based type in LIMIT clause 7293# 7294# Finally, test the valid case. 7295# 7296create procedure p1(p1 integer, p2 integer) 7297select * from t1 limit p1, p2; 7298call p1(NULL, NULL); 7299c1 7300call p1(0, 0); 7301c1 7302call p1(0, -1); 7303c1 73041 73052 73063 73074 73085 7309call p1(-1, 0); 7310c1 7311call p1(-1, -1); 7312c1 7313call p1(0, 1); 7314c1 73151 7316call p1(1, 0); 7317c1 7318call p1(1, 5); 7319c1 73202 73213 73224 73235 7324call p1(3, 2); 7325c1 73264 73275 7328# Try to create a function that 7329# refers to non-existing variables. 7330create function f1(p1 integer, p2 integer) 7331returns int 7332begin 7333declare a int; 7334set a = (select count(*) from t1 limit a, b); 7335return a; 7336end| 7337ERROR 42000: Undeclared variable: b 7338create function f1() 7339returns int 7340begin 7341declare a, b, c int; 7342set a = (select count(*) from t1 limit b, c); 7343return a; 7344end| 7345# How do we handle NULL limit values? 7346select f1(); 7347f1() 7348NULL 7349drop function f1; 7350# 7351# Try to use data types not allowed in LIMIT 7352# 7353create function f1(p1 date, p2 date) 7354returns int 7355begin 7356declare a int; 7357set a = (select count(*) from t1 limit p1, p2); 7358return a; 7359end| 7360ERROR HY000: A variable of a non-integer based type in LIMIT clause 7361create function f1(p1 integer, p2 float) 7362returns int 7363begin 7364declare a int; 7365set a = (select count(*) from t1 limit p1, p2); 7366return a; 7367end| 7368ERROR HY000: A variable of a non-integer based type in LIMIT clause 7369create function f1(p1 integer, p2 char(1)) 7370returns int 7371begin 7372declare a int; 7373set a = (select count(*) from t1 limit p1, p2); 7374return a; 7375end| 7376ERROR HY000: A variable of a non-integer based type in LIMIT clause 7377create function f1(p1 varchar(5), p2 char(1)) 7378returns int 7379begin 7380declare a int; 7381set a = (select count(*) from t1 limit p1, p2); 7382return a; 7383end| 7384ERROR HY000: A variable of a non-integer based type in LIMIT clause 7385create function f1(p1 decimal, p2 decimal) 7386returns int 7387begin 7388declare a int; 7389set a = (select count(*) from t1 limit p1, p2); 7390return a; 7391end| 7392ERROR HY000: A variable of a non-integer based type in LIMIT clause 7393create function f1(p1 double, p2 double) 7394returns int 7395begin 7396declare a int; 7397set a = (select count(*) from t1 limit p1, p2); 7398return a; 7399end| 7400ERROR HY000: A variable of a non-integer based type in LIMIT clause 7401# 7402# Finally, test the valid case. 7403# 7404create function f1(p1 integer, p2 integer) 7405returns int 7406begin 7407declare count int; 7408set count= (select count(*) from (select * from t1 limit p1, p2) t_1); 7409return count; 7410end| 7411select f1(0, 0); 7412f1(0, 0) 74130 7414select f1(0, -1); 7415f1(0, -1) 74165 7417select f1(-1, 0); 7418f1(-1, 0) 74190 7420select f1(-1, -1); 7421f1(-1, -1) 74220 7423select f1(0, 1); 7424f1(0, 1) 74251 7426select f1(1, 0); 7427f1(1, 0) 74280 7429select f1(1, 5); 7430f1(1, 5) 74314 7432select f1(3, 2); 7433f1(3, 2) 74342 7435# Cleanup 7436drop table t1; 7437drop procedure p1; 7438drop function f1; 7439# 7440# BUG#11766234: 59299: ASSERT (TABLE_REF->TABLE || TABLE_REF->VIEW) 7441# FAILS IN SET_FIELD_ITERATOR 7442# 7443CREATE TABLE t1 (a INT); 7444CREATE TABLE t2 (a INT); 7445CREATE VIEW v1 AS SELECT a FROM t2; 7446CREATE PROCEDURE proc() SELECT * FROM t1 NATURAL JOIN v1; 7447ALTER TABLE t2 CHANGE COLUMN a b CHAR; 7448 7449CALL proc(); 7450ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 7451CALL proc(); 7452ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 7453 7454DROP TABLE t1,t2; 7455DROP VIEW v1; 7456DROP PROCEDURE proc; 7457 7458# -- 7459# -- Bug 11765684 - 58674: SP-cache does not detect changes in 7460# -- pre-locking list caused by triggers 7461# --- 7462DROP TABLE IF EXISTS t1; 7463DROP TABLE IF EXISTS t2; 7464DROP TABLE IF EXISTS t3; 7465DROP PROCEDURE IF EXISTS p1; 7466CREATE TABLE t1(a INT); 7467CREATE TABLE t2(a INT); 7468CREATE TABLE t3(a INT); 7469CREATE PROCEDURE p1() 7470INSERT INTO t1(a) VALUES (1); 7471 7472CREATE TRIGGER t1_ai AFTER INSERT ON t1 7473FOR EACH ROW 7474INSERT INTO t2(a) VALUES (new.a); 7475 7476CALL p1(); 7477 7478CREATE TRIGGER t1_bi BEFORE INSERT ON t1 7479FOR EACH ROW 7480INSERT INTO t3(a) VALUES (new.a); 7481 7482CALL p1(); 7483 7484DROP TABLE t1, t2, t3; 7485DROP PROCEDURE p1; 7486 7487 7488# -- 7489# -- Bug#12652769 - 61470: case operator in stored routine retains old 7490# -- value of input parameter 7491# --- 7492DROP TABLE IF EXISTS t1; 7493DROP PROCEDURE IF EXISTS p1; 7494CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET utf8); 7495INSERT INTO t1 VALUES ('a'); 7496CREATE PROCEDURE p1(dt DATETIME, i INT) 7497BEGIN 7498SELECT 7499CASE 7500WHEN i = 1 THEN 2 7501ELSE dt 7502END AS x1; 7503SELECT 7504CASE _latin1'a' 7505 WHEN _utf8'a' THEN 'A' 7506 END AS x2; 7507SELECT 7508CASE _utf8'a' 7509 WHEN _latin1'a' THEN _utf8'A' 7510 END AS x3; 7511SELECT 7512CASE s1 7513WHEN _latin1'a' THEN _latin1'b' 7514 ELSE _latin1'c' 7515 END AS x4 7516FROM t1; 7517END| 7518 7519CALL p1('2011-04-03 05:14:10', 1); 7520x1 75212 7522x2 7523A 7524x3 7525A 7526x4 7527b 7528CALL p1('2011-04-03 05:14:11', 2); 7529x1 75302011-04-03 05:14:11 7531x2 7532A 7533x3 7534A 7535x4 7536b 7537CALL p1('2011-04-03 05:14:12', 2); 7538x1 75392011-04-03 05:14:12 7540x2 7541A 7542x3 7543A 7544x4 7545b 7546CALL p1('2011-04-03 05:14:13', 2); 7547x1 75482011-04-03 05:14:13 7549x2 7550A 7551x3 7552A 7553x4 7554b 7555 7556DROP TABLE t1; 7557DROP PROCEDURE p1; 7558 7559# 7560# Bug#12621017 - Crash if a sp variable is used in the 7561# limit clause of a set statement 7562# 7563DROP TABLE IF EXISTS t1; 7564DROP PROCEDURE IF EXISTS p1; 7565DROP PROCEDURE IF EXISTS p2; 7566CREATE TABLE t1 (c1 INT); 7567INSERT INTO t1 VALUES (1); 7568CREATE PROCEDURE p1() 7569BEGIN 7570DECLARE foo, cnt INT UNSIGNED DEFAULT 1; 7571SET foo = (SELECT MIN(c1) FROM t1 LIMIT cnt); 7572END| 7573CREATE PROCEDURE p2() 7574BEGIN 7575DECLARE iLimit INT; 7576DECLARE iVal INT; 7577DECLARE cur1 CURSOR FOR 7578SELECT c1 FROM t1 7579LIMIT iLimit; 7580SET iLimit=1; 7581OPEN cur1; 7582FETCH cur1 INTO iVal; 7583END| 7584CALL p1(); 7585CALL p2(); 7586DROP PROCEDURE p1; 7587DROP PROCEDURE p2; 7588DROP TABLE t1; 7589 7590# Bug#13805127: Stored program cache produces wrong result in same THD 7591 7592CREATE PROCEDURE p1(x INT UNSIGNED) 7593BEGIN 7594SELECT c1, t2.c2, count(c3) 7595FROM 7596( 7597SELECT 3 as c2 FROM dual WHERE x = 1 7598UNION 7599SELECT 2 FROM dual WHERE x = 1 OR x = 2 7600) AS t1, 7601( 7602SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual 7603UNION 7604SELECT '2012-03-01 02:00:00', 3, 2 FROM dual 7605UNION 7606SELECT '2012-03-01 01:00:00', 2, 1 FROM dual 7607) AS t2 7608WHERE t2.c2 = t1.c2 7609GROUP BY c1, c2 7610; 7611END| 7612 7613CALL p1(1); 7614c1 c2 count(c3) 76152012-03-01 01:00:00 2 1 76162012-03-01 01:00:00 3 1 76172012-03-01 02:00:00 3 1 7618CALL p1(2); 7619c1 c2 count(c3) 76202012-03-01 01:00:00 2 1 7621CALL p1(1); 7622c1 c2 count(c3) 76232012-03-01 01:00:00 2 1 76242012-03-01 01:00:00 3 1 76252012-03-01 02:00:00 3 1 7626DROP PROCEDURE p1; 7627# End of 5.5 test 7628# 7629# Bug#12663165 SP DEAD CODE REMOVAL DOESN'T UNDERSTAND CONTINUE HANDLERS 7630# 7631DROP FUNCTION IF EXISTS f1; 7632CREATE FUNCTION f1() RETURNS INT 7633BEGIN 7634DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; 7635BEGIN 7636DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN f1(); 7637BEGIN 7638DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN f1(); 7639RETURN f1(); 7640END; 7641END; 7642RETURN 1; 7643END $ 7644SELECT f1(); 7645f1() 76461 7647DROP FUNCTION f1; 7648# 7649# Bug#12577230 7650# RERUN OF STORED FUNCTION CAUSES SEGFAULT IN MAKE_JOIN_SELECT 7651# 7652CREATE TABLE t1 (a INT) ENGINE=myisam; 7653INSERT INTO t1 VALUES (1); 7654CREATE VIEW v1 AS SELECT a FROM t1; 7655CREATE PROCEDURE p1() 7656SELECT 1 FROM v1 JOIN t1 ON v1.a 7657WHERE (SELECT 1 FROM t1 WHERE v1.a) 7658; 7659CALL p1(); 76601 76611 7662CALL p1(); 76631 76641 7665DROP PROCEDURE p1; 7666prepare s from 'select 1 from `v1` join `t1` on `v1`.`a` 7667where (select 1 from `t1` where `v1`.`a`)'; 7668execute s; 76691 76701 7671execute s; 76721 76731 7674prepare s from 'select 1 from `v1` join `t1` on `v1`.`a`'; 7675execute s; 76761 76771 7678execute s; 76791 76801 7681prepare s from 'select 1 from `v1` join `t1` on `v1`.`a` join t1 as t2 7682on v1.a'; 7683execute s; 76841 76851 7686execute s; 76871 76881 7689create view v2 as select 0 as a from t1; 7690prepare s from 'select 1 from `v2` join `t1` on `v2`.`a` join v1 on `v1`.`a`'; 7691execute s; 76921 7693execute s; 76941 7695prepare s from 'select 1 from `v2` join `t1` on `v2`.`a`, v1 where `v1`.`a`'; 7696execute s; 76971 7698execute s; 76991 7700DROP TABLE t1; 7701DROP VIEW v1,v2; 7702# 7703# WL#2111: Add non-reserved ROW_COUNT keyword. 7704# 7705DROP PROCEDURE IF EXISTS p1; 7706CREATE PROCEDURE p1() 7707BEGIN 7708DECLARE row_count INT DEFAULT 1; 7709SELECT row_count; 7710SELECT row_count(); 7711ROW_COUNT: WHILE row_count > 0 DO 7712SET row_count = row_count - 1; 7713END WHILE ROW_COUNT; 7714SELECT ROW_COUNT; 7715END| 7716CALL p1(); 7717row_count 77181 7719row_count() 7720-1 7721ROW_COUNT 77220 7723DROP PROCEDURE p1; 7724# 7725# BUG #11748187 - 35410: STORED FUNCTION: CONFUSING 'ORDER CLAUSE' IN ERROR MESSAGE 7726# 7727DROP FUNCTION if exists f1; 7728CREATE FUNCTION f1 (p_value INT) RETURNS INT DETERMINISTIC RETURN x; 7729SELECT f1(1); 7730ERROR 42S22: Unknown column 'x' in 'field list' 7731DROP FUNCTION f1; 7732# 7733# BUG #12872824 (formerly known as 62125): testing stored function 7734# result for null incorrectly yields 1292 warning. 7735DROP FUNCTION IF EXISTS f1; 7736DROP FUNCTION IF EXISTS f2; 7737DROP FUNCTION IF EXISTS f3; 7738DROP FUNCTION IF EXISTS f4; 7739CREATE FUNCTION f1() RETURNS VARCHAR(1) 7740BEGIN RETURN 'X'; END;/ 7741CREATE FUNCTION f2() RETURNS CHAR(1) 7742BEGIN RETURN 'X'; END;/ 7743CREATE FUNCTION f3() RETURNS VARCHAR(1) 7744BEGIN RETURN NULL; END;/ 7745CREATE FUNCTION f4() RETURNS CHAR(1) 7746BEGIN RETURN NULL; END;/ 7747SELECT f1() IS NULL; 7748f1() IS NULL 77490 7750SELECT f2() IS NULL; 7751f2() IS NULL 77520 7753SELECT f3() IS NULL; 7754f3() IS NULL 77551 7756SELECT f4() IS NULL; 7757f4() IS NULL 77581 7759DROP FUNCTION f1; 7760DROP FUNCTION f2; 7761DROP FUNCTION f3; 7762DROP FUNCTION f4; 7763# 7764# 7765# WL#6230: Remove 'SET = DEFAULT' 7766# 7767CREATE TABLE t1(a INT); 7768CREATE PROCEDURE p(p INT) 7769SET p = DEFAULT| 7770ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 2 7771CREATE PROCEDURE p() 7772BEGIN 7773DECLARE v INT; 7774SET v = DEFAULT; 7775END| 7776ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT; 7777END' at line 4 7778CREATE PROCEDURE p() 7779BEGIN 7780DECLARE v INT DEFAULT 1; 7781SET v = DEFAULT; 7782END| 7783ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT; 7784END' at line 4 7785CREATE PROCEDURE p() 7786BEGIN 7787DECLARE v INT DEFAULT (SELECT * FROM t1); 7788SET v = DEFAULT; 7789END| 7790ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT; 7791END' at line 4 7792CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW 7793BEGIN 7794SET NEW.a = DEFAULT; 7795END| 7796ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT; 7797END' at line 3 7798 7799# Check that setting system variables to DEFAULT still works in SP. 7800 7801CREATE PROCEDURE p1() 7802SET @@default_storage_engine = DEFAULT; 7803SET @default_storage_engine_saved = @@default_storage_engine; 7804SELECT @@default_storage_engine; 7805@@default_storage_engine 7806MyISAM 7807SET @@default_storage_engine = InnoDB; 7808SELECT @@default_storage_engine; 7809@@default_storage_engine 7810InnoDB 7811CALL p1(); 7812SELECT @@default_storage_engine; 7813@@default_storage_engine 7814MyISAM 7815SET @@default_storage_engine = @default_storage_engine_saved; 7816DROP PROCEDURE p1; 7817DROP TABLE t1; 7818# End of 5.6 tests 7819# 7820# BUG 18484649 - STRICT MODE + CURSOR FETCH INTO WRONG TYPE, ASSERTION 7821# IN PROTOCOL::END_STATEMENT 7822# 7823SET @org_mode= @@sql_mode; 7824SET sql_mode= 'STRICT_TRANS_TABLES'; 7825Warnings: 7826Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. 7827CREATE TABLE t1(a INT) ENGINE= InnoDB; 7828INSERT INTO t1 VALUES (123456); 7829CREATE PROCEDURE p1() 7830BEGIN 7831DECLARE `v` TINYINT; 7832DECLARE `c` CURSOR FOR SELECT a FROM t1; 7833BEGIN 7834OPEN c; 7835FETCH c INTO v; 7836CLOSE c; 7837END; 7838END $ 7839# With the fix, Call to p1 will result in error as the routine was 7840# created in STRICT MODE. 7841CALL p1(); 7842ERROR 22003: Out of range value for column 'v' at row 1 7843# Clean-up 7844SET sql_mode= @org_mode; 7845DROP PROCEDURE p1; 7846DROP TABLE t1; 7847# 7848# Bug#20583321: CRASH ON PS_THREAD_INFO / SYS USAGE 7849# 7850SET sql_mode = 'only_full_group_by'; 7851CREATE TABLE t1 ( a INT, b INT ); 7852CREATE TABLE t2 ( a INT ); 7853CREATE PROCEDURE p1 () 7854BEGIN 7855DECLARE output INT; 7856SET output = ( SELECT b FROM t1 LEFT JOIN t2 USING ( a ) GROUP BY t1.a ); 7857END $$ 7858CALL p1(); 7859ERROR 42000: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t1.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 7860CALL p1(); 7861ERROR 42000: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t1.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 7862DROP PROCEDURE p1; 7863PREPARE s FROM 7864'SET @x = ( SELECT b FROM t1 LEFT JOIN t2 USING ( a ) GROUP BY t1.a )'; 7865ERROR 42000: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t1.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 7866DROP TABLE t1, t2; 7867SET sql_mode = DEFAULT; 7868# 7869# ASSERTION FAILED: FALSE IN CREATE_TMP_FIELD WITH UNKNOWN OUTER FIELD 7870# 7871CREATE TABLE t1 ( a INT ); 7872SET @v = 1; 7873PREPARE stmt FROM 'SELECT 1 FROM ( SELECT ? FROM t1 GROUP BY a ) al;'; 7874EXECUTE stmt USING @v; 78751 7876PREPARE stmt FROM 'CREATE TABLE t2 AS SELECT ? FROM t1'; 7877EXECUTE stmt USING @v; 7878DROP TABLE t1, t2; 7879# 7880# Bug #18599181: CALLING A ROUTINE WITHOUT SPECIFYING DATABASE PREFIX 7881# MAY RETURN ERROR 1370 7882# 7883CREATE DATABASE db1; 7884CREATE FUNCTION f1() RETURNS INT RETURN 1; 7885CREATE FUNCTION db1.f2() RETURNS INT RETURN test.f1(); 7886CREATE USER myuser@'localhost'; 7887GRANT ALL ON db1.* TO myuser@'localhost'; 7888SELECT f2(); 7889f2() 78901 7891SELECT f2(); 7892f2() 78931 7894DROP DATABASE db1; 7895DROP USER myuser@localhost; 7896DROP FUNCTION f1; 7897# 7898# Bug #22222013 ASSERTION `! IS_SET() || M_CAN_OVERWRITE_STATUS' FAILED 7899# IN ::SET_ERROR_STATUS 7900# 7901CREATE DATABASE bug22222013; 7902USE bug22222013; 7903SET NAMES swe7; 7904CREATE FUNCTION f1() RETURNS INT RETURN (1); 7905CREATE FUNCTION f21(p1 CHAR) RETURNS CHAR RETURN (p1); 7906SELECT sql_data_access FROM information_schema.routines WHERE 7907specific_name like 'p%' and routine_schema=0; 7908ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??@?? FUNCTION ?bug22222013?.?f1?() RETURNS int(11) 7909RETURN NULL' at line 1 7910DROP DATABASE bug22222013; 7911SET NAMES default; 7912# 7913# WL#9262: All system tables should support 32 character length user names 7914# 7915#This should just work with username + hostname of length 93 as DEFINER 7916CREATE USER user_name_robert_golebiowski1234@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char; 7917#For procedure 7918CREATE DEFINER=user_name_robert_golebiowski1234@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char PROCEDURE test.proc_test() SELECT CURRENT_USER(); 7919#and for function 7920CREATE DEFINER=user_name_robert_golebiowski1234@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char FUNCTION test.f_test() RETURNS INT RETURN 1; 7921SHOW PROCEDURE STATUS LIKE 'proc_test'; 7922Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 7923test proc_test PROCEDURE user_name_robert_golebiowski1234@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 7924DROP PROCEDURE test.proc_test; 7925SHOW FUNCTION STATUS LIKE 'f_test'; 7926Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 7927test f_test FUNCTION user_name_robert_golebiowski1234@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 7928DROP FUNCTION test.f_test; 7929# Changing mysql.proc.definer column to char(77) - i.e. old layout (5.7.12) 7930SET @orig_sql_mode= @@sql_mode; 7931SET sql_mode=''; 7932Warnings: 7933Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 7934ALTER TABLE mysql.proc 7935MODIFY definer char(77) collate utf8_bin DEFAULT '' NOT NULL; 7936SET sql_mode= @orig_sql_mode; 7937Warnings: 7938Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 7939#Now we make sure that username + hostname with length of 77 or less work with 7940#this old db schema 7941#This should just pass as the DEFINER length is 77 chars - so the same as mysql.proc.definer 7942CREATE USER robert_golebiows@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char; 7943#For procedure 7944CREATE DEFINER=robert_golebiows@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char PROCEDURE test.proc_test_1() SELECT CURRENT_USER(); 7945DROP PROCEDURE test.proc_test_1; 7946#For function 7947CREATE DEFINER=robert_golebiows@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char FUNCTION test.f_test_1() RETURNS INT RETURN 1; 7948DROP FUNCTION test.f_test_1; 7949#Now definer is 93 char long so we should get error as mysql.proc.definer in old schema was only 77 char long 7950#For procedure 7951CREATE DEFINER=user_name_robert_golebiowski1234@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char PROCEDURE test.proc_test() SELECT CURRENT_USER(); 7952ERROR HY000: It seems that your db schema is old. The definer column is 77 characters long and should be 93 characters long. Please run mysql_upgrade. 7953#For function 7954CREATE DEFINER=user_name_robert_golebiowski1234@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char FUNCTION test.f_test_1() RETURNS INT RETURN 1; 7955ERROR HY000: It seems that your db schema is old. The definer column is 77 characters long and should be 93 characters long. Please run mysql_upgrade. 7956# Let us check with user name + hostname of length 78 7957#For procedure 7958CREATE DEFINER=robert_golebiowsk@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char PROCEDURE test.proc_test() SELECT CURRENT_USER(); 7959ERROR HY000: It seems that your db schema is old. The definer column is 77 characters long and should be 93 characters long. Please run mysql_upgrade. 7960#For function 7961CREATE DEFINER=robert_golebiowsk@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char FUNCTION test.f_test_1() RETURNS INT RETURN 1; 7962ERROR HY000: It seems that your db schema is old. The definer column is 77 characters long and should be 93 characters long. Please run mysql_upgrade. 7963# DEFINER with length longer than 93 characters should fail with old plain error staying that data is too long for username 7964# as upgrading to 93 characters (with mysql_upgrade) will not help 7965# Let us try with 94 characters long username 7966CREATE DEFINER=user_name_robert_golebiowski12345@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char PROCEDURE test.proc_test() SELECT CURRENT_USER(); 7967ERROR HY000: String 'user_name_robert_golebiowski12345' is too long for user name (should be no longer than 32) 7968CREATE DEFINER=user_name_robert_golebiowski12345@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char FUNCTION test.f_test_1() RETURNS INT RETURN 1; 7969ERROR HY000: String 'user_name_robert_golebiowski12345' is too long for user name (should be no longer than 32) 7970CREATE DEFINER=user_name_robert_golebiowski1234@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char1 PROCEDURE test.proc_test() SELECT CURRENT_USER(); 7971ERROR HY000: String 'oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char1' is too long for host name (should be no longer than 60) 7972CREATE DEFINER=user_name_robert_golebiowski1234@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char1 FUNCTION test.f_test_1() RETURNS INT RETURN 1; 7973ERROR HY000: String 'oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char1' is too long for host name (should be no longer than 60) 7974#Cleanup 7975DROP USER user_name_robert_golebiowski1234@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char; 7976DROP USER robert_golebiows@oh_my_gosh_this_is_a_long_hostname_look_at_it_it_has_60_char; 7977Restore mysql.proc 7978SET @orig_sql_mode= @@sql_mode; 7979SET sql_mode=''; 7980Warnings: 7981Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 7982ALTER TABLE mysql.proc 7983MODIFY definer char(93) collate utf8_bin DEFAULT '' NOT NULL; 7984SET sql_mode= @orig_sql_mode; 7985Warnings: 7986Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 7987