1drop procedure if exists empty; 2drop procedure if exists code_sample; 3create procedure empty() 4begin 5end; 6show procedure code empty; 7Pos Instruction 8drop procedure empty; 9create function almost_empty() 10returns int 11return 0; 12show function code almost_empty; 13Pos Instruction 140 freturn 3 0 15drop function almost_empty; 16create procedure code_sample(x int, out err int, out nulls int) 17begin 18declare count int default 0; 19set nulls = 0; 20begin 21declare c cursor for select name from t1; 22declare exit handler for not found close c; 23open c; 24loop 25begin 26declare n varchar(20); 27declare continue handler for sqlexception set err=1; 28fetch c into n; 29if isnull(n) then 30set nulls = nulls + 1; 31else 32set count = count + 1; 33update t2 set idx = count where name=n; 34end if; 35end; 36end loop; 37end; 38select t.name, t.idx from t2 t order by idx asc; 39end// 40show procedure code code_sample; 41Pos Instruction 420 set count@3 0 431 set nulls@2 0 442 cpush c@0: select name from t1 453 hpush_jump 6 4 EXIT HANDLER FOR NOT FOUND 464 cclose c@0 475 hreturn 0 19 486 copen c@0 497 set n@4 NULL 508 hpush_jump 11 5 CONTINUE HANDLER FOR SQLEXCEPTION 519 set err@1 1 5210 hreturn 5 5311 cfetch c@0 n@4 5412 jump_if_not 15(17) isnull(n@4) 5513 set nulls@2 (nulls@2 + 1) 5614 jump 17 5715 set count@3 (count@3 + 1) 5816 stmt "update t2 set idx = count where name=n" 5917 hpop 6018 jump 7 6119 hpop 6220 cpop 1 6321 stmt "select t.name, t.idx from t2 t order ..." 64drop procedure code_sample; 65drop procedure if exists sudoku_solve; 66create procedure sudoku_solve(p_naive boolean, p_all boolean) 67deterministic 68modifies sql data 69begin 70drop temporary table if exists sudoku_work, sudoku_schedule; 71create temporary table sudoku_work 72( 73row smallint not null, 74col smallint not null, 75dig smallint not null, 76cnt smallint, 77key using btree (cnt), 78key using btree (row), 79key using btree (col), 80unique key using hash (row,col) 81); 82create temporary table sudoku_schedule 83( 84idx int not null auto_increment primary key, 85row smallint not null, 86col smallint not null 87); 88call sudoku_init(); 89if p_naive then 90update sudoku_work set cnt = 0 where dig = 0; 91else 92call sudoku_count(); 93end if; 94insert into sudoku_schedule (row,col) 95select row,col from sudoku_work where cnt is not null order by cnt desc; 96begin 97declare v_scounter bigint default 0; 98declare v_i smallint default 1; 99declare v_dig smallint; 100declare v_schedmax smallint; 101select count(*) into v_schedmax from sudoku_schedule; 102more: 103loop 104begin 105declare v_tcounter bigint default 0; 106sched: 107while v_i <= v_schedmax do 108begin 109declare v_row, v_col smallint; 110select row,col into v_row,v_col from sudoku_schedule where v_i = idx; 111select dig into v_dig from sudoku_work 112where v_row = row and v_col = col; 113case v_dig 114when 0 then 115set v_dig = 1; 116update sudoku_work set dig = 1 117where v_row = row and v_col = col; 118when 9 then 119if v_i > 0 then 120update sudoku_work set dig = 0 121where v_row = row and v_col = col; 122set v_i = v_i - 1; 123iterate sched; 124else 125select v_scounter as 'Solutions'; 126leave more; 127end if; 128else 129set v_dig = v_dig + 1; 130update sudoku_work set dig = v_dig 131where v_row = row and v_col = col; 132end case; 133set v_tcounter = v_tcounter + 1; 134if not sudoku_digit_ok(v_row, v_col, v_dig) then 135iterate sched; 136end if; 137set v_i = v_i + 1; 138end; 139end while sched; 140select dig from sudoku_work; 141select v_tcounter as 'Tests'; 142set v_scounter = v_scounter + 1; 143if p_all and v_i > 0 then 144set v_i = v_i - 1; 145else 146leave more; 147end if; 148end; 149end loop more; 150end; 151drop temporary table sudoku_work, sudoku_schedule; 152end// 153show procedure code sudoku_solve; 154Pos Instruction 1550 stmt "drop temporary table if exists sudoku..." 1561 stmt "create temporary table sudoku_work ( ..." 1572 stmt "create temporary table sudoku_schedul..." 1583 stmt "call sudoku_init()" 1594 jump_if_not 7(8) p_naive@0 1605 stmt "update sudoku_work set cnt = 0 where ..." 1616 jump 8 1627 stmt "call sudoku_count()" 1638 stmt "insert into sudoku_schedule (row,col)..." 1649 set v_scounter@2 0 16510 set v_i@3 1 16611 set v_dig@4 NULL 16712 set v_schedmax@5 NULL 16813 stmt "select count(*) into v_schedmax from ..." 16914 set v_tcounter@6 0 17015 jump_if_not 39(39) (v_i@3 <= v_schedmax@5) 17116 set v_row@7 NULL 17217 set v_col@8 NULL 17318 stmt "select row,col into v_row,v_col from ..." 17419 stmt "select dig into v_dig from sudoku_wor..." 17520 set_case_expr (34) 0 v_dig@4 17621 jump_if_not_case_when 25(34) (case_expr@0 = 0) 17722 set v_dig@4 1 17823 stmt "update sudoku_work set dig = 1 where ..." 17924 jump 34 18025 jump_if_not_case_when 32(34) (case_expr@0 = 9) 18126 jump_if_not 30(34) (v_i@3 > 0) 18227 stmt "update sudoku_work set dig = 0 where ..." 18328 set v_i@3 (v_i@3 - 1) 18429 jump 15 18530 stmt "select v_scounter as 'Solutions'" 18631 jump 45 18732 set v_dig@4 (v_dig@4 + 1) 18833 stmt "update sudoku_work set dig = v_dig wh..." 18934 set v_tcounter@6 (v_tcounter@6 + 1) 19035 jump_if_not 37(37) (not(`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4))) 19136 jump 15 19237 set v_i@3 (v_i@3 + 1) 19338 jump 15 19439 stmt "select dig from sudoku_work" 19540 stmt "select v_tcounter as 'Tests'" 19641 set v_scounter@2 (v_scounter@2 + 1) 19742 jump_if_not 45(14) (p_all@1 and (v_i@3 > 0)) 19843 set v_i@3 (v_i@3 - 1) 19944 jump 14 20045 stmt "drop temporary table sudoku_work, sud..." 201drop procedure sudoku_solve; 202DROP PROCEDURE IF EXISTS proc_19194_simple; 203DROP PROCEDURE IF EXISTS proc_19194_searched; 204DROP PROCEDURE IF EXISTS proc_19194_nested_1; 205DROP PROCEDURE IF EXISTS proc_19194_nested_2; 206DROP PROCEDURE IF EXISTS proc_19194_nested_3; 207DROP PROCEDURE IF EXISTS proc_19194_nested_4; 208CREATE PROCEDURE proc_19194_simple(i int) 209BEGIN 210DECLARE str CHAR(10); 211CASE i 212WHEN 1 THEN SET str="1"; 213WHEN 2 THEN SET str="2"; 214WHEN 3 THEN SET str="3"; 215ELSE SET str="unknown"; 216END CASE; 217SELECT str; 218END| 219CREATE PROCEDURE proc_19194_searched(i int) 220BEGIN 221DECLARE str CHAR(10); 222CASE 223WHEN i=1 THEN SET str="1"; 224WHEN i=2 THEN SET str="2"; 225WHEN i=3 THEN SET str="3"; 226ELSE SET str="unknown"; 227END CASE; 228SELECT str; 229END| 230CREATE PROCEDURE proc_19194_nested_1(i int, j int) 231BEGIN 232DECLARE str_i CHAR(10); 233DECLARE str_j CHAR(10); 234CASE i 235WHEN 10 THEN SET str_i="10"; 236WHEN 20 THEN 237BEGIN 238set str_i="20"; 239CASE 240WHEN j=1 THEN SET str_j="1"; 241WHEN j=2 THEN SET str_j="2"; 242WHEN j=3 THEN SET str_j="3"; 243ELSE SET str_j="unknown"; 244END CASE; 245select "i was 20"; 246END; 247WHEN 30 THEN SET str_i="30"; 248WHEN 40 THEN SET str_i="40"; 249ELSE SET str_i="unknown"; 250END CASE; 251SELECT str_i, str_j; 252END| 253CREATE PROCEDURE proc_19194_nested_2(i int, j int) 254BEGIN 255DECLARE str_i CHAR(10); 256DECLARE str_j CHAR(10); 257CASE 258WHEN i=10 THEN SET str_i="10"; 259WHEN i=20 THEN 260BEGIN 261set str_i="20"; 262CASE j 263WHEN 1 THEN SET str_j="1"; 264WHEN 2 THEN SET str_j="2"; 265WHEN 3 THEN SET str_j="3"; 266ELSE SET str_j="unknown"; 267END CASE; 268select "i was 20"; 269END; 270WHEN i=30 THEN SET str_i="30"; 271WHEN i=40 THEN SET str_i="40"; 272ELSE SET str_i="unknown"; 273END CASE; 274SELECT str_i, str_j; 275END| 276CREATE PROCEDURE proc_19194_nested_3(i int, j int) 277BEGIN 278DECLARE str_i CHAR(10); 279DECLARE str_j CHAR(10); 280CASE i 281WHEN 10 THEN SET str_i="10"; 282WHEN 20 THEN 283BEGIN 284set str_i="20"; 285CASE j 286WHEN 1 THEN SET str_j="1"; 287WHEN 2 THEN SET str_j="2"; 288WHEN 3 THEN SET str_j="3"; 289ELSE SET str_j="unknown"; 290END CASE; 291select "i was 20"; 292END; 293WHEN 30 THEN SET str_i="30"; 294WHEN 40 THEN SET str_i="40"; 295ELSE SET str_i="unknown"; 296END CASE; 297SELECT str_i, str_j; 298END| 299CREATE PROCEDURE proc_19194_nested_4(i int, j int) 300BEGIN 301DECLARE str_i CHAR(10); 302DECLARE str_j CHAR(10); 303CASE 304WHEN i=10 THEN SET str_i="10"; 305WHEN i=20 THEN 306BEGIN 307set str_i="20"; 308CASE 309WHEN j=1 THEN SET str_j="1"; 310WHEN j=2 THEN SET str_j="2"; 311WHEN j=3 THEN SET str_j="3"; 312ELSE SET str_j="unknown"; 313END CASE; 314select "i was 20"; 315END; 316WHEN i=30 THEN SET str_i="30"; 317WHEN i=40 THEN SET str_i="40"; 318ELSE SET str_i="unknown"; 319END CASE; 320SELECT str_i, str_j; 321END| 322SHOW PROCEDURE CODE proc_19194_simple; 323Pos Instruction 3240 set str@1 NULL 3251 set_case_expr (12) 0 i@0 3262 jump_if_not_case_when 5(12) (case_expr@0 = 1) 3273 set str@1 '1' 3284 jump 12 3295 jump_if_not_case_when 8(12) (case_expr@0 = 2) 3306 set str@1 '2' 3317 jump 12 3328 jump_if_not_case_when 11(12) (case_expr@0 = 3) 3339 set str@1 '3' 33410 jump 12 33511 set str@1 'unknown' 33612 stmt "SELECT str" 337SHOW PROCEDURE CODE proc_19194_searched; 338Pos Instruction 3390 set str@1 NULL 3401 jump_if_not 4(11) (i@0 = 1) 3412 set str@1 '1' 3423 jump 11 3434 jump_if_not 7(11) (i@0 = 2) 3445 set str@1 '2' 3456 jump 11 3467 jump_if_not 10(11) (i@0 = 3) 3478 set str@1 '3' 3489 jump 11 34910 set str@1 'unknown' 35011 stmt "SELECT str" 351SHOW PROCEDURE CODE proc_19194_nested_1; 352Pos Instruction 3530 set str_i@2 NULL 3541 set str_j@3 NULL 3552 set_case_expr (27) 0 i@0 3563 jump_if_not_case_when 6(27) (case_expr@0 = 10) 3574 set str_i@2 '10' 3585 jump 27 3596 jump_if_not_case_when 20(27) (case_expr@0 = 20) 3607 set str_i@2 '20' 3618 jump_if_not 11(18) (j@1 = 1) 3629 set str_j@3 '1' 36310 jump 18 36411 jump_if_not 14(18) (j@1 = 2) 36512 set str_j@3 '2' 36613 jump 18 36714 jump_if_not 17(18) (j@1 = 3) 36815 set str_j@3 '3' 36916 jump 18 37017 set str_j@3 'unknown' 37118 stmt "select "i was 20"" 37219 jump 27 37320 jump_if_not_case_when 23(27) (case_expr@0 = 30) 37421 set str_i@2 '30' 37522 jump 27 37623 jump_if_not_case_when 26(27) (case_expr@0 = 40) 37724 set str_i@2 '40' 37825 jump 27 37926 set str_i@2 'unknown' 38027 stmt "SELECT str_i, str_j" 381SHOW PROCEDURE CODE proc_19194_nested_2; 382Pos Instruction 3830 set str_i@2 NULL 3841 set str_j@3 NULL 3852 jump_if_not 5(27) (i@0 = 10) 3863 set str_i@2 '10' 3874 jump 27 3885 jump_if_not 20(27) (i@0 = 20) 3896 set str_i@2 '20' 3907 set_case_expr (18) 0 j@1 3918 jump_if_not_case_when 11(18) (case_expr@0 = 1) 3929 set str_j@3 '1' 39310 jump 18 39411 jump_if_not_case_when 14(18) (case_expr@0 = 2) 39512 set str_j@3 '2' 39613 jump 18 39714 jump_if_not_case_when 17(18) (case_expr@0 = 3) 39815 set str_j@3 '3' 39916 jump 18 40017 set str_j@3 'unknown' 40118 stmt "select "i was 20"" 40219 jump 27 40320 jump_if_not 23(27) (i@0 = 30) 40421 set str_i@2 '30' 40522 jump 27 40623 jump_if_not 26(27) (i@0 = 40) 40724 set str_i@2 '40' 40825 jump 27 40926 set str_i@2 'unknown' 41027 stmt "SELECT str_i, str_j" 411SHOW PROCEDURE CODE proc_19194_nested_3; 412Pos Instruction 4130 set str_i@2 NULL 4141 set str_j@3 NULL 4152 set_case_expr (28) 0 i@0 4163 jump_if_not_case_when 6(28) (case_expr@0 = 10) 4174 set str_i@2 '10' 4185 jump 28 4196 jump_if_not_case_when 21(28) (case_expr@0 = 20) 4207 set str_i@2 '20' 4218 set_case_expr (19) 1 j@1 4229 jump_if_not_case_when 12(19) (case_expr@1 = 1) 42310 set str_j@3 '1' 42411 jump 19 42512 jump_if_not_case_when 15(19) (case_expr@1 = 2) 42613 set str_j@3 '2' 42714 jump 19 42815 jump_if_not_case_when 18(19) (case_expr@1 = 3) 42916 set str_j@3 '3' 43017 jump 19 43118 set str_j@3 'unknown' 43219 stmt "select "i was 20"" 43320 jump 28 43421 jump_if_not_case_when 24(28) (case_expr@0 = 30) 43522 set str_i@2 '30' 43623 jump 28 43724 jump_if_not_case_when 27(28) (case_expr@0 = 40) 43825 set str_i@2 '40' 43926 jump 28 44027 set str_i@2 'unknown' 44128 stmt "SELECT str_i, str_j" 442SHOW PROCEDURE CODE proc_19194_nested_4; 443Pos Instruction 4440 set str_i@2 NULL 4451 set str_j@3 NULL 4462 jump_if_not 5(26) (i@0 = 10) 4473 set str_i@2 '10' 4484 jump 26 4495 jump_if_not 19(26) (i@0 = 20) 4506 set str_i@2 '20' 4517 jump_if_not 10(17) (j@1 = 1) 4528 set str_j@3 '1' 4539 jump 17 45410 jump_if_not 13(17) (j@1 = 2) 45511 set str_j@3 '2' 45612 jump 17 45713 jump_if_not 16(17) (j@1 = 3) 45814 set str_j@3 '3' 45915 jump 17 46016 set str_j@3 'unknown' 46117 stmt "select "i was 20"" 46218 jump 26 46319 jump_if_not 22(26) (i@0 = 30) 46420 set str_i@2 '30' 46521 jump 26 46622 jump_if_not 25(26) (i@0 = 40) 46723 set str_i@2 '40' 46824 jump 26 46925 set str_i@2 'unknown' 47026 stmt "SELECT str_i, str_j" 471CALL proc_19194_nested_1(10, 1); 472str_i str_j 47310 NULL 474CALL proc_19194_nested_1(25, 1); 475str_i str_j 476unknown NULL 477CALL proc_19194_nested_1(20, 1); 478i was 20 479i was 20 480str_i str_j 48120 1 482CALL proc_19194_nested_1(20, 2); 483i was 20 484i was 20 485str_i str_j 48620 2 487CALL proc_19194_nested_1(20, 3); 488i was 20 489i was 20 490str_i str_j 49120 3 492CALL proc_19194_nested_1(20, 4); 493i was 20 494i was 20 495str_i str_j 49620 unknown 497CALL proc_19194_nested_1(30, 1); 498str_i str_j 49930 NULL 500CALL proc_19194_nested_1(40, 1); 501str_i str_j 50240 NULL 503CALL proc_19194_nested_1(0, 0); 504str_i str_j 505unknown NULL 506CALL proc_19194_nested_2(10, 1); 507str_i str_j 50810 NULL 509CALL proc_19194_nested_2(25, 1); 510str_i str_j 511unknown NULL 512CALL proc_19194_nested_2(20, 1); 513i was 20 514i was 20 515str_i str_j 51620 1 517CALL proc_19194_nested_2(20, 2); 518i was 20 519i was 20 520str_i str_j 52120 2 522CALL proc_19194_nested_2(20, 3); 523i was 20 524i was 20 525str_i str_j 52620 3 527CALL proc_19194_nested_2(20, 4); 528i was 20 529i was 20 530str_i str_j 53120 unknown 532CALL proc_19194_nested_2(30, 1); 533str_i str_j 53430 NULL 535CALL proc_19194_nested_2(40, 1); 536str_i str_j 53740 NULL 538CALL proc_19194_nested_2(0, 0); 539str_i str_j 540unknown NULL 541CALL proc_19194_nested_3(10, 1); 542str_i str_j 54310 NULL 544CALL proc_19194_nested_3(25, 1); 545str_i str_j 546unknown NULL 547CALL proc_19194_nested_3(20, 1); 548i was 20 549i was 20 550str_i str_j 55120 1 552CALL proc_19194_nested_3(20, 2); 553i was 20 554i was 20 555str_i str_j 55620 2 557CALL proc_19194_nested_3(20, 3); 558i was 20 559i was 20 560str_i str_j 56120 3 562CALL proc_19194_nested_3(20, 4); 563i was 20 564i was 20 565str_i str_j 56620 unknown 567CALL proc_19194_nested_3(30, 1); 568str_i str_j 56930 NULL 570CALL proc_19194_nested_3(40, 1); 571str_i str_j 57240 NULL 573CALL proc_19194_nested_3(0, 0); 574str_i str_j 575unknown NULL 576CALL proc_19194_nested_4(10, 1); 577str_i str_j 57810 NULL 579CALL proc_19194_nested_4(25, 1); 580str_i str_j 581unknown NULL 582CALL proc_19194_nested_4(20, 1); 583i was 20 584i was 20 585str_i str_j 58620 1 587CALL proc_19194_nested_4(20, 2); 588i was 20 589i was 20 590str_i str_j 59120 2 592CALL proc_19194_nested_4(20, 3); 593i was 20 594i was 20 595str_i str_j 59620 3 597CALL proc_19194_nested_4(20, 4); 598i was 20 599i was 20 600str_i str_j 60120 unknown 602CALL proc_19194_nested_4(30, 1); 603str_i str_j 60430 NULL 605CALL proc_19194_nested_4(40, 1); 606str_i str_j 60740 NULL 608CALL proc_19194_nested_4(0, 0); 609str_i str_j 610unknown NULL 611DROP PROCEDURE proc_19194_simple; 612DROP PROCEDURE proc_19194_searched; 613DROP PROCEDURE proc_19194_nested_1; 614DROP PROCEDURE proc_19194_nested_2; 615DROP PROCEDURE proc_19194_nested_3; 616DROP PROCEDURE proc_19194_nested_4; 617DROP PROCEDURE IF EXISTS p1; 618CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1); 619SHOW PROCEDURE CODE p1; 620Pos Instruction 6210 stmt "CREATE INDEX idx ON t1 (c1)" 622DROP PROCEDURE p1; 623drop table if exists t1; 624drop procedure if exists proc_26977_broken; 625drop procedure if exists proc_26977_works; 626create table t1(a int unique); 627create procedure proc_26977_broken(v int) 628begin 629declare i int default 5; 630declare continue handler for sqlexception 631begin 632select 'caught something'; 633retry: 634while i > 0 do 635begin 636set i = i - 1; 637select 'looping', i; 638end; 639end while retry; 640end; 641select 'do something'; 642insert into t1 values (v); 643select 'do something again'; 644insert into t1 values (v); 645end// 646create procedure proc_26977_works(v int) 647begin 648declare i int default 5; 649declare continue handler for sqlexception 650begin 651select 'caught something'; 652retry: 653while i > 0 do 654begin 655set i = i - 1; 656select 'looping', i; 657end; 658end while retry; 659select 'optimizer: keep hreturn'; 660end; 661select 'do something'; 662insert into t1 values (v); 663select 'do something again'; 664insert into t1 values (v); 665end// 666show procedure code proc_26977_broken; 667Pos Instruction 6680 set i@1 5 6691 hpush_jump 8 2 CONTINUE HANDLER FOR SQLEXCEPTION 6702 stmt "select 'caught something'" 6713 jump_if_not 7(7) (i@1 > 0) 6724 set i@1 (i@1 - 1) 6735 stmt "select 'looping', i" 6746 jump 3 6757 hreturn 2 6768 stmt "select 'do something'" 6779 stmt "insert into t1 values (v)" 67810 stmt "select 'do something again'" 67911 stmt "insert into t1 values (v)" 68012 hpop 681show procedure code proc_26977_works; 682Pos Instruction 6830 set i@1 5 6841 hpush_jump 9 2 CONTINUE HANDLER FOR SQLEXCEPTION 6852 stmt "select 'caught something'" 6863 jump_if_not 7(7) (i@1 > 0) 6874 set i@1 (i@1 - 1) 6885 stmt "select 'looping', i" 6896 jump 3 6907 stmt "select 'optimizer: keep hreturn'" 6918 hreturn 2 6929 stmt "select 'do something'" 69310 stmt "insert into t1 values (v)" 69411 stmt "select 'do something again'" 69512 stmt "insert into t1 values (v)" 69613 hpop 697call proc_26977_broken(1); 698do something 699do something 700do something again 701do something again 702caught something 703caught something 704looping i 705looping 4 706looping i 707looping 3 708looping i 709looping 2 710looping i 711looping 1 712looping i 713looping 0 714call proc_26977_works(2); 715do something 716do something 717do something again 718do something again 719caught something 720caught something 721looping i 722looping 4 723looping i 724looping 3 725looping i 726looping 2 727looping i 728looping 1 729looping i 730looping 0 731optimizer: keep hreturn 732optimizer: keep hreturn 733drop table t1; 734drop procedure proc_26977_broken; 735drop procedure proc_26977_works; 736drop procedure if exists proc_33618_h; 737drop procedure if exists proc_33618_c; 738create procedure proc_33618_h(num int) 739begin 740declare count1 int default '0'; 741declare vb varchar(30); 742declare last_row int; 743while(num>=1) do 744set num=num-1; 745begin 746declare cur1 cursor for select `a` from t_33618; 747declare continue handler for not found set last_row = 1; 748set last_row:=0; 749open cur1; 750rep1: 751repeat 752begin 753declare exit handler for 1062 begin end; 754fetch cur1 into vb; 755if (last_row = 1) then 756## should generate a hpop instruction here 757leave rep1; 758end if; 759end; 760until last_row=1 761end repeat; 762close cur1; 763end; 764end while; 765end// 766create procedure proc_33618_c(num int) 767begin 768declare count1 int default '0'; 769declare vb varchar(30); 770declare last_row int; 771while(num>=1) do 772set num=num-1; 773begin 774declare cur1 cursor for select `a` from t_33618; 775declare continue handler for not found set last_row = 1; 776set last_row:=0; 777open cur1; 778rep1: 779repeat 780begin 781declare cur2 cursor for select `b` from t_33618; 782fetch cur1 into vb; 783if (last_row = 1) then 784## should generate a cpop instruction here 785leave rep1; 786end if; 787end; 788until last_row=1 789end repeat; 790close cur1; 791end; 792end while; 793end// 794show procedure code proc_33618_h; 795Pos Instruction 7960 set count1@1 '0' 7971 set vb@2 NULL 7982 set last_row@3 NULL 7993 jump_if_not 24(24) (num@0 >= 1) 8004 set num@0 (num@0 - 1) 8015 cpush cur1@0: select `a` from t_33618 8026 hpush_jump 9 4 CONTINUE HANDLER FOR NOT FOUND 8037 set last_row@3 1 8048 hreturn 4 8059 set last_row@3 0 80610 copen cur1@0 80711 hpush_jump 13 4 EXIT HANDLER FOR 1062 80812 hreturn 0 17 80913 cfetch cur1@0 vb@2 81014 jump_if_not 17(17) (last_row@3 = 1) 81115 hpop 81216 jump 19 81317 hpop 81418 jump_if_not 11(19) (last_row@3 = 1) 81519 cclose cur1@0 81620 hpop 81721 cpop 1 81822 jump 3 819show procedure code proc_33618_c; 820Pos Instruction 8210 set count1@1 '0' 8221 set vb@2 NULL 8232 set last_row@3 NULL 8243 jump_if_not 23(23) (num@0 >= 1) 8254 set num@0 (num@0 - 1) 8265 cpush cur1@0: select `a` from t_33618 8276 hpush_jump 9 4 CONTINUE HANDLER FOR NOT FOUND 8287 set last_row@3 1 8298 hreturn 4 8309 set last_row@3 0 83110 copen cur1@0 83211 cpush cur2@1: select `b` from t_33618 83312 cfetch cur1@0 vb@2 83413 jump_if_not 16(16) (last_row@3 = 1) 83514 cpop 1 83615 jump 18 83716 cpop 1 83817 jump_if_not 11(18) (last_row@3 = 1) 83918 cclose cur1@0 84019 hpop 84120 cpop 1 84221 jump 3 843drop procedure proc_33618_h; 844drop procedure proc_33618_c; 845drop procedure if exists p_20906_a; 846drop procedure if exists p_20906_b; 847create procedure p_20906_a() SET @a=@a+1, @b=@b+1; 848show procedure code p_20906_a; 849Pos Instruction 8500 stmt "SET @a=@a+1" 8511 stmt "SET @b=@b+1" 852set @a=1; 853set @b=1; 854call p_20906_a(); 855select @a, @b; 856@a @b 8572 2 858create procedure p_20906_b() SET @a=@a+1, @b=@b+1, @c=@c+1; 859show procedure code p_20906_b; 860Pos Instruction 8610 stmt "SET @a=@a+1" 8621 stmt "SET @b=@b+1" 8632 stmt "SET @c=@c+1" 864set @a=1; 865set @b=1; 866set @c=1; 867call p_20906_b(); 868select @a, @b, @c; 869@a @b @c 8702 2 2 871drop procedure p_20906_a; 872drop procedure p_20906_b; 873End of 5.0 tests. 874CREATE PROCEDURE p1() 875BEGIN 876DECLARE dummy int default 0; 877CASE 12 878WHEN 12 879THEN SET dummy = 0; 880END CASE; 881END// 882SHOW PROCEDURE CODE p1; 883Pos Instruction 8840 set dummy@0 0 8851 set_case_expr (6) 0 12 8862 jump_if_not_case_when 5(6) (case_expr@0 = 12) 8873 set dummy@0 0 8884 jump 6 8895 error 1339 890DROP PROCEDURE p1; 891# 892# Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP 893# 894 895# - Case 4: check that "No Data trumps Warning". 896 897CREATE TABLE t1(a INT); 898INSERT INTO t1 VALUES (1), (2), (3); 899CREATE PROCEDURE p1() 900BEGIN 901DECLARE c CURSOR FOR SELECT a FROM t1; 902OPEN c; 903BEGIN 904DECLARE v INT; 905DECLARE CONTINUE HANDLER FOR SQLWARNING 906BEGIN 907GET DIAGNOSTICS @n = NUMBER; 908GET DIAGNOSTICS CONDITION @n @err_no = MYSQL_ERRNO, @err_txt = MESSAGE_TEXT; 909SELECT "Warning found!"; 910SELECT @err_no, @err_txt; 911END; 912DECLARE EXIT HANDLER FOR NOT FOUND 913BEGIN 914GET DIAGNOSTICS @n = NUMBER; 915GET DIAGNOSTICS CONDITION @n @err_no = MYSQL_ERRNO, @err_txt = MESSAGE_TEXT; 916SELECT "End of Result Set found!"; 917SELECT @err_no, @err_txt; 918END; 919WHILE TRUE DO 920FETCH c INTO v; 921END WHILE; 922END; 923CLOSE c; 924SELECT a INTO @foo FROM t1 LIMIT 1; # Clear warning stack 925END| 926SET SESSION debug="+d,bug23032_emit_warning"; 927CALL p1(); 928Warning found! 929Warning found! 930@err_no @err_txt 9311105 Unknown error 932Warning found! 933Warning found! 934@err_no @err_txt 9351105 Unknown error 936Warning found! 937Warning found! 938@err_no @err_txt 9391105 Unknown error 940End of Result Set found! 941End of Result Set found! 942@err_no @err_txt 9431329 No data - zero rows fetched, selected, or processed 944SET SESSION debug="-d,bug23032_emit_warning"; 945DROP PROCEDURE p1; 946DROP TABLE t1; 947# 948# Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE 949# 950SET @@SQL_MODE = ''; 951Warnings: 952Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 953CREATE FUNCTION testf_bug11763507() RETURNS INT 954BEGIN 955RETURN 0; 956END 957$ 958CREATE PROCEDURE testp_bug11763507() 959BEGIN 960SELECT "PROCEDURE testp_bug11763507"; 961END 962$ 963SHOW FUNCTION CODE testf_bug11763507; 964Pos Instruction 9650 freturn 3 0 966SHOW FUNCTION CODE TESTF_bug11763507; 967Pos Instruction 9680 freturn 3 0 969SHOW PROCEDURE CODE testp_bug11763507; 970Pos Instruction 9710 stmt "SELECT "PROCEDURE testp_bug11763507"" 972SHOW PROCEDURE CODE TESTP_bug11763507; 973Pos Instruction 9740 stmt "SELECT "PROCEDURE testp_bug11763507"" 975DROP PROCEDURE testp_bug11763507; 976DROP FUNCTION testf_bug11763507; 977#END OF BUG#11763507 test. 978 979# WL#4179: Stored programs: validation of stored program statements. 980# 981# Check that query string is stored only for the expressions, which 982# reference tables or stored functions. 983# 984# Test cases in this file require SHOW ... CODE, which is available only 985# in the debug mode. 986 987CREATE TABLE t1(a INT); 988INSERT INTO t1 VALUES (10); 989CREATE TEMPORARY TABLE t2(a INT); 990INSERT INTO t2 VALUES (20); 991CREATE VIEW t3 AS SELECT 30; 992CREATE FUNCTION f() RETURNS INT 993RETURN 1| 994CREATE PROCEDURE p1() 995BEGIN 996# DEFAULT-expression 997DECLARE x1 INT DEFAULT (SELECT 1 + 2); 998DECLARE x2 INT DEFAULT (SELECT * FROM (SELECT 1 + 2) t1); 999DECLARE x3 INT DEFAULT (SELECT * FROM t1); 1000DECLARE x4 INT DEFAULT (SELECT * FROM t2); 1001DECLARE x5 INT DEFAULT (SELECT * FROM t3); 1002DECLARE x6 INT DEFAULT (SELECT f()); 1003# CURSOR-query. 1004DECLARE c1 CURSOR FOR SELECT (1 + 2) FROM dual; 1005DECLARE c2 CURSOR FOR SELECT * FROM (SELECT 1 + 2) t1; 1006DECLARE c3 CURSOR FOR SELECT * FROM t1; 1007DECLARE c4 CURSOR FOR SELECT * FROM t2; 1008DECLARE c5 CURSOR FOR SELECT * FROM t3; 1009DECLARE c6 CURSOR FOR SELECT f(); 1010# IF-expression. 1011IF (SELECT 1 + 2) THEN 1012SET @dummy = 1; 1013END IF; 1014IF (SELECT * FROM (SELECT 1 + 2) t1) THEN 1015SET @dummy = 1; 1016END IF; 1017IF (SELECT * FROM t1) THEN 1018SET @dummy = 1; 1019END IF; 1020IF (SELECT * FROM t2) THEN 1021SET @dummy = 1; 1022END IF; 1023IF (SELECT * FROM t3) THEN 1024SET @dummy = 1; 1025END IF; 1026IF (SELECT f()) THEN 1027SET @dummy = 1; 1028END IF; 1029# SET-expression. 1030SET x1 = (SELECT 1 + 2); 1031SET x1 = (SELECT * FROM (SELECT 1 + 2) t1); 1032SET x1 = (SELECT * FROM t1); 1033SET x1 = (SELECT * FROM t2); 1034SET x1 = (SELECT * FROM t3); 1035SET x1 = (SELECT f()); 1036# CASE-expressions. 1037CASE 1038WHEN (SELECT 1 + 2) = 1 THEN SET @dummy = 1; 1039WHEN (SELECT * FROM (SELECT 1 + 2) t1) = 2 THEN SET @dummy = 1; 1040WHEN (SELECT * FROM t1) = 3 THEN SET @dummy = 1; 1041WHEN (SELECT * FROM t2) = 3 THEN SET @dummy = 1; 1042WHEN (SELECT * FROM t3) = 3 THEN SET @dummy = 1; 1043WHEN (SELECT f()) = 3 THEN SET @dummy = 1; 1044END CASE; 1045CASE (SELECT 1 + 2) 1046WHEN 1 THEN SET @dummy = 1; 1047ELSE SET @dummy = 1; 1048END CASE; 1049CASE (SELECT * FROM (SELECT 1 + 2) t1) 1050WHEN 1 THEN SET @dummy = 1; 1051ELSE SET @dummy = 1; 1052END CASE; 1053CASE (SELECT * FROM t1) 1054WHEN 1 THEN SET @dummy = 1; 1055ELSE SET @dummy = 1; 1056END CASE; 1057CASE (SELECT * FROM t2) 1058WHEN 1 THEN SET @dummy = 1; 1059ELSE SET @dummy = 1; 1060END CASE; 1061CASE (SELECT * FROM t3) 1062WHEN 1 THEN SET @dummy = 1; 1063ELSE SET @dummy = 1; 1064END CASE; 1065CASE (SELECT f()) 1066WHEN 1 THEN SET @dummy = 1; 1067ELSE SET @dummy = 1; 1068END CASE; 1069# WHILE-expression. 1070WHILE (SELECT 1 - 1) DO 1071SET @dummy = 1; 1072END WHILE; 1073WHILE (SELECT * FROM (SELECT 1 - 1) t1) DO 1074SET @dummy = 1; 1075END WHILE; 1076WHILE (SELECT * FROM t1) - 10 DO 1077SET @dummy = 1; 1078END WHILE; 1079WHILE (SELECT * FROM t2) - 10 DO 1080SET @dummy = 1; 1081END WHILE; 1082WHILE (SELECT * FROM t3) - 10 DO 1083SET @dummy = 1; 1084END WHILE; 1085WHILE (SELECT f()) - 1 DO 1086SET @dummy = 1; 1087END WHILE; 1088# REPEAT-expression. 1089REPEAT 1090SET @dummy = 1; 1091UNTIL (SELECT 1 - 1) END REPEAT; 1092REPEAT 1093SET @dummy = 1; 1094UNTIL (SELECT * FROM (SELECT 1 - 1) t1) END REPEAT; 1095REPEAT 1096SET @dummy = 1; 1097UNTIL (SELECT * FROM t1) - 10 END REPEAT; 1098REPEAT 1099SET @dummy = 1; 1100UNTIL (SELECT * FROM t2) - 10 END REPEAT; 1101REPEAT 1102SET @dummy = 1; 1103UNTIL (SELECT * FROM t3) - 10 END REPEAT; 1104REPEAT 1105SET @dummy = 1; 1106UNTIL (SELECT f()) - 1 END REPEAT; 1107END| 1108CREATE FUNCTION f1() RETURNS INT 1109RETURN (SELECT 1 + 2)| 1110CREATE FUNCTION f2() RETURNS INT 1111RETURN (SELECT * FROM (SELECT 1 + 2) t1)| 1112CREATE FUNCTION f3() RETURNS INT 1113RETURN (SELECT * FROM t1)| 1114CREATE FUNCTION f4() RETURNS INT 1115RETURN (SELECT * FROM t2)| 1116CREATE FUNCTION f5() RETURNS INT 1117RETURN (SELECT * FROM t3)| 1118CREATE FUNCTION f6() RETURNS INT 1119RETURN f()| 1120 1121SHOW PROCEDURE CODE p1; 1122Pos Instruction 11230 set x1@0 (select (1 + 2)) 11241 set x2@1 (select `*` from (select (1 + 2) AS `1 + 2`) `t1`) 11252 set x3@2 (select `*` from `test`.`t1`) 11263 set x4@3 (select `*` from `test`.`t2`) 11274 set x5@4 (select `*` from `test`.`t3`) 11285 set x6@5 (select `f`()) 11296 cpush c1@0: 11307 cpush c2@1: SELECT * FROM (SELECT 1 + 2) t1 11318 cpush c3@2: SELECT * FROM t1 11329 cpush c4@3: SELECT * FROM t2 113310 cpush c5@4: SELECT * FROM t3 113411 cpush c6@5: SELECT f() 113512 jump_if_not 14(14) (select (1 + 2)) 113613 stmt "SET @dummy = 1" 113714 jump_if_not 16(16) (select `*` from (select (1 + 2) AS `1 + 2`) `t1`) 113815 stmt "SET @dummy = 1" 113916 jump_if_not 18(18) (select `*` from `test`.`t1`) 114017 stmt "SET @dummy = 1" 114118 jump_if_not 20(20) (select `*` from `test`.`t2`) 114219 stmt "SET @dummy = 1" 114320 jump_if_not 22(22) (select `*` from `test`.`t3`) 114421 stmt "SET @dummy = 1" 114522 jump_if_not 24(24) (select `f`()) 114623 stmt "SET @dummy = 1" 114724 set x1@0 (select (1 + 2)) 114825 set x1@0 (select `*` from (select (1 + 2) AS `1 + 2`) `t1`) 114926 set x1@0 (select `*` from `test`.`t1`) 115027 set x1@0 (select `*` from `test`.`t2`) 115128 set x1@0 (select `*` from `test`.`t3`) 115229 set x1@0 (select `f`()) 115330 jump_if_not 33(49) ((select (1 + 2)) = 1) 115431 stmt "SET @dummy = 1" 115532 jump 49 115633 jump_if_not 36(49) ((select `*` from (select (1 + 2) AS `1 + 2`) `t1`) = 2) 115734 stmt "SET @dummy = 1" 115835 jump 49 115936 jump_if_not 39(49) ((select `*` from `test`.`t1`) = 3) 116037 stmt "SET @dummy = 1" 116138 jump 49 116239 jump_if_not 42(49) ((select `*` from `test`.`t2`) = 3) 116340 stmt "SET @dummy = 1" 116441 jump 49 116542 jump_if_not 45(49) ((select `*` from `test`.`t3`) = 3) 116643 stmt "SET @dummy = 1" 116744 jump 49 116845 jump_if_not 48(49) ((select `f`()) = 3) 116946 stmt "SET @dummy = 1" 117047 jump 49 117148 error 1339 117249 set_case_expr (54) 0 (select (1 + 2)) 117350 jump_if_not_case_when 53(54) (case_expr@0 = 1) 117451 stmt "SET @dummy = 1" 117552 jump 54 117653 stmt "SET @dummy = 1" 117754 set_case_expr (59) 1 (select `*` from (select (1 + 2) AS `1 + 2`) `t1`) 117855 jump_if_not_case_when 58(59) (case_expr@1 = 1) 117956 stmt "SET @dummy = 1" 118057 jump 59 118158 stmt "SET @dummy = 1" 118259 set_case_expr (64) 2 (select `*` from `test`.`t1`) 118360 jump_if_not_case_when 63(64) (case_expr@2 = 1) 118461 stmt "SET @dummy = 1" 118562 jump 64 118663 stmt "SET @dummy = 1" 118764 set_case_expr (69) 3 (select `*` from `test`.`t2`) 118865 jump_if_not_case_when 68(69) (case_expr@3 = 1) 118966 stmt "SET @dummy = 1" 119067 jump 69 119168 stmt "SET @dummy = 1" 119269 set_case_expr (74) 4 (select `*` from `test`.`t3`) 119370 jump_if_not_case_when 73(74) (case_expr@4 = 1) 119471 stmt "SET @dummy = 1" 119572 jump 74 119673 stmt "SET @dummy = 1" 119774 set_case_expr (79) 5 (select `f`()) 119875 jump_if_not_case_when 78(79) (case_expr@5 = 1) 119976 stmt "SET @dummy = 1" 120077 jump 79 120178 stmt "SET @dummy = 1" 120279 jump_if_not 82(82) (select (1 - 1)) 120380 stmt "SET @dummy = 1" 120481 jump 79 120582 jump_if_not 85(85) (select `*` from (select (1 - 1) AS `1 - 1`) `t1`) 120683 stmt "SET @dummy = 1" 120784 jump 82 120885 jump_if_not 88(88) ((select `*` from `test`.`t1`) - 10) 120986 stmt "SET @dummy = 1" 121087 jump 85 121188 jump_if_not 91(91) ((select `*` from `test`.`t2`) - 10) 121289 stmt "SET @dummy = 1" 121390 jump 88 121491 jump_if_not 94(94) ((select `*` from `test`.`t3`) - 10) 121592 stmt "SET @dummy = 1" 121693 jump 91 121794 jump_if_not 97(97) ((select `f`()) - 1) 121895 stmt "SET @dummy = 1" 121996 jump 94 122097 stmt "SET @dummy = 1" 122198 jump_if_not 97(99) (select (1 - 1)) 122299 stmt "SET @dummy = 1" 1223100 jump_if_not 99(101) (select `*` from (select (1 - 1) AS `1 - 1`) `t1`) 1224101 stmt "SET @dummy = 1" 1225102 jump_if_not 101(103) ((select `*` from `test`.`t1`) - 10) 1226103 stmt "SET @dummy = 1" 1227104 jump_if_not 103(105) ((select `*` from `test`.`t2`) - 10) 1228105 stmt "SET @dummy = 1" 1229106 jump_if_not 105(107) ((select `*` from `test`.`t3`) - 10) 1230107 stmt "SET @dummy = 1" 1231108 jump_if_not 107(109) ((select `f`()) - 1) 1232109 cpop 6 1233 1234SHOW FUNCTION CODE f1; 1235Pos Instruction 12360 freturn 3 (select (1 + 2)) 1237SHOW FUNCTION CODE f2; 1238Pos Instruction 12390 freturn 3 (select `*` from (select (1 + 2) AS `1 + 2`) `t1`) 1240SHOW FUNCTION CODE f3; 1241Pos Instruction 12420 freturn 3 (select `*` from `test`.`t1`) 1243SHOW FUNCTION CODE f4; 1244Pos Instruction 12450 freturn 3 (select `*` from `test`.`t2`) 1246SHOW FUNCTION CODE f5; 1247Pos Instruction 12480 freturn 3 (select `*` from `test`.`t3`) 1249SHOW FUNCTION CODE f6; 1250Pos Instruction 12510 freturn 3 `f`() 1252 1253DROP FUNCTION f; 1254DROP PROCEDURE p1; 1255DROP FUNCTION f1; 1256DROP FUNCTION f2; 1257DROP FUNCTION f3; 1258DROP FUNCTION f4; 1259DROP FUNCTION f5; 1260DROP FUNCTION f6; 1261DROP TABLE t1; 1262DROP TEMPORARY TABLE t2; 1263DROP VIEW t3; 1264 1265# SHOW ... CODE 1266# 1267CREATE PROCEDURE p11_many_handlers () 1268BEGIN 1269DECLARE CONTINUE HANDLER FOR 1050 SELECT "1050 for 401a, please"; 1270DECLARE EXIT HANDLER FOR NOT FOUND SELECT "a place not found"; 1271DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT "a warn place"; 1272DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT "an exceptional place"; 1273DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' SELECT "state of the nation"; 1274BEGIN 1275DECLARE EXIT HANDLER FOR NOT FOUND, 1,2 SELECT "multi multi"; 1276END; 1277END| 1278SHOW PROCEDURE CODE p11_many_handlers; 1279Pos Instruction 12800 hpush_jump 3 0 CONTINUE HANDLER FOR 1050 12811 stmt "SELECT "1050 for 401a, please"" 12822 hreturn 0 12833 hpush_jump 6 0 EXIT HANDLER FOR NOT FOUND 12844 stmt "SELECT "a place not found"" 12855 hreturn 0 19 12866 hpush_jump 9 0 CONTINUE HANDLER FOR SQLWARNING 12877 stmt "SELECT "a warn place"" 12888 hreturn 0 12899 hpush_jump 12 0 EXIT HANDLER FOR SQLEXCEPTION 129010 stmt "SELECT "an exceptional place"" 129111 hreturn 0 19 129212 hpush_jump 15 0 CONTINUE HANDLER FOR SQLSTATE '01000' 129313 stmt "SELECT "state of the nation"" 129414 hreturn 0 129515 hpush_jump 18 0 EXIT HANDLER FOR NOT FOUND, 1, 2 129616 stmt "SELECT "multi multi"" 129717 hreturn 0 18 129818 hpop 129919 hpop 1300DROP PROCEDURE p11_many_handlers; 1301