diff options
Diffstat (limited to 'mysql-test/main/sp-code.result')
| -rw-r--r-- | mysql-test/main/sp-code.result | 1303 |
1 files changed, 1303 insertions, 0 deletions
diff --git a/mysql-test/main/sp-code.result b/mysql-test/main/sp-code.result new file mode 100644 index 00000000000..3a4dc9db6f8 --- /dev/null +++ b/mysql-test/main/sp-code.result @@ -0,0 +1,1303 @@ +drop procedure if exists empty; +drop procedure if exists code_sample; +create procedure empty() +begin +end; +show procedure code empty; +Pos Instruction +drop procedure empty; +create function almost_empty() +returns int +return 0; +show function code almost_empty; +Pos Instruction +0 freturn int 0 +drop function almost_empty; +create procedure code_sample(x int, out err int, out nulls int) +begin +declare count int default 0; +set nulls = 0; +begin +declare c cursor for select name from t1; +declare exit handler for not found close c; +open c; +loop +begin +declare n varchar(20); +declare continue handler for sqlexception set err=1; +fetch c into n; +if isnull(n) then +set nulls = nulls + 1; +else +set count = count + 1; +update t2 set idx = count where name=n; +end if; +end; +end loop; +end; +select t.name, t.idx from t2 t order by idx asc; +end// +show procedure code code_sample; +Pos Instruction +0 set count@3 0 +1 set nulls@2 0 +2 cpush c@0 +3 hpush_jump 6 4 EXIT +4 cclose c@0 +5 hreturn 0 19 +6 copen c@0 +7 set n@4 NULL +8 hpush_jump 11 5 CONTINUE +9 set err@1 1 +10 hreturn 5 +11 cfetch c@0 n@4 +12 jump_if_not 15(17) n@4 is null +13 set nulls@2 nulls@2 + 1 +14 jump 17 +15 set count@3 count@3 + 1 +16 stmt 4 "update t2 set idx = count where name=n" +17 hpop 1 +18 jump 7 +19 hpop 1 +20 cpop 1 +21 stmt 0 "select t.name, t.idx from t2 t order ..." +drop procedure code_sample; +drop procedure if exists sudoku_solve; +create procedure sudoku_solve(p_naive boolean, p_all boolean) +deterministic +modifies sql data +begin +drop temporary table if exists sudoku_work, sudoku_schedule; +create temporary table sudoku_work +( +row smallint not null, +col smallint not null, +dig smallint not null, +cnt smallint, +key using btree (cnt), +key using btree (row), +key using btree (col), +unique key using hash (row,col) +); +create temporary table sudoku_schedule +( +idx int not null auto_increment primary key, +row smallint not null, +col smallint not null +); +call sudoku_init(); +if p_naive then +update sudoku_work set cnt = 0 where dig = 0; +else +call sudoku_count(); +end if; +insert into sudoku_schedule (row,col) +select row,col from sudoku_work where cnt is not null order by cnt desc; +begin +declare v_scounter bigint default 0; +declare v_i smallint default 1; +declare v_dig smallint; +declare v_schedmax smallint; +select count(*) into v_schedmax from sudoku_schedule; +more: +loop +begin +declare v_tcounter bigint default 0; +sched: +while v_i <= v_schedmax do +begin +declare v_row, v_col smallint; +select row,col into v_row,v_col from sudoku_schedule where v_i = idx; +select dig into v_dig from sudoku_work +where v_row = row and v_col = col; +case v_dig +when 0 then +set v_dig = 1; +update sudoku_work set dig = 1 +where v_row = row and v_col = col; +when 9 then +if v_i > 0 then +update sudoku_work set dig = 0 +where v_row = row and v_col = col; +set v_i = v_i - 1; +iterate sched; +else +select v_scounter as 'Solutions'; +leave more; +end if; +else +set v_dig = v_dig + 1; +update sudoku_work set dig = v_dig +where v_row = row and v_col = col; +end case; +set v_tcounter = v_tcounter + 1; +if not sudoku_digit_ok(v_row, v_col, v_dig) then +iterate sched; +end if; +set v_i = v_i + 1; +end; +end while sched; +select dig from sudoku_work; +select v_tcounter as 'Tests'; +set v_scounter = v_scounter + 1; +if p_all and v_i > 0 then +set v_i = v_i - 1; +else +leave more; +end if; +end; +end loop more; +end; +drop temporary table sudoku_work, sudoku_schedule; +end// +show procedure code sudoku_solve; +Pos Instruction +0 stmt 9 "drop temporary table if exists sudoku..." +1 stmt 1 "create temporary table sudoku_work ( ..." +2 stmt 1 "create temporary table sudoku_schedul..." +3 stmt 88 "call sudoku_init()" +4 jump_if_not 7(8) p_naive@0 +5 stmt 4 "update sudoku_work set cnt = 0 where ..." +6 jump 8 +7 stmt 88 "call sudoku_count()" +8 stmt 6 "insert into sudoku_schedule (row,col)..." +9 set v_scounter@2 0 +10 set v_i@3 1 +11 set v_dig@4 NULL +12 set v_schedmax@5 NULL +13 stmt 0 "select count(*) into v_schedmax from ..." +14 set v_tcounter@6 0 +15 jump_if_not 39(39) v_i@3 <= v_schedmax@5 +16 set v_row@7 NULL +17 set v_col@8 NULL +18 stmt 0 "select row,col into v_row,v_col from ..." +19 stmt 0 "select dig into v_dig from sudoku_wor..." +20 set_case_expr (34) 0 v_dig@4 +21 jump_if_not 25(34) case_expr@0 = 0 +22 set v_dig@4 1 +23 stmt 4 "update sudoku_work set dig = 1 where ..." +24 jump 34 +25 jump_if_not 32(34) case_expr@0 = 9 +26 jump_if_not 30(34) v_i@3 > 0 +27 stmt 4 "update sudoku_work set dig = 0 where ..." +28 set v_i@3 v_i@3 - 1 +29 jump 15 +30 stmt 0 "select v_scounter as 'Solutions'" +31 jump 45 +32 set v_dig@4 v_dig@4 + 1 +33 stmt 4 "update sudoku_work set dig = v_dig wh..." +34 set v_tcounter@6 v_tcounter@6 + 1 +35 jump_if_not 37(37) !`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4) +36 jump 15 +37 set v_i@3 v_i@3 + 1 +38 jump 15 +39 stmt 0 "select dig from sudoku_work" +40 stmt 0 "select v_tcounter as 'Tests'" +41 set v_scounter@2 v_scounter@2 + 1 +42 jump_if_not 45(14) p_all@1 and v_i@3 > 0 +43 set v_i@3 v_i@3 - 1 +44 jump 14 +45 stmt 9 "drop temporary table sudoku_work, sud..." +drop procedure sudoku_solve; +DROP PROCEDURE IF EXISTS proc_19194_simple; +DROP PROCEDURE IF EXISTS proc_19194_searched; +DROP PROCEDURE IF EXISTS proc_19194_nested_1; +DROP PROCEDURE IF EXISTS proc_19194_nested_2; +DROP PROCEDURE IF EXISTS proc_19194_nested_3; +DROP PROCEDURE IF EXISTS proc_19194_nested_4; +CREATE PROCEDURE proc_19194_simple(i int) +BEGIN +DECLARE str CHAR(10); +CASE i +WHEN 1 THEN SET str="1"; +WHEN 2 THEN SET str="2"; +WHEN 3 THEN SET str="3"; +ELSE SET str="unknown"; +END CASE; +SELECT str; +END| +CREATE PROCEDURE proc_19194_searched(i int) +BEGIN +DECLARE str CHAR(10); +CASE +WHEN i=1 THEN SET str="1"; +WHEN i=2 THEN SET str="2"; +WHEN i=3 THEN SET str="3"; +ELSE SET str="unknown"; +END CASE; +SELECT str; +END| +CREATE PROCEDURE proc_19194_nested_1(i int, j int) +BEGIN +DECLARE str_i CHAR(10); +DECLARE str_j CHAR(10); +CASE i +WHEN 10 THEN SET str_i="10"; +WHEN 20 THEN +BEGIN +set str_i="20"; +CASE +WHEN j=1 THEN SET str_j="1"; +WHEN j=2 THEN SET str_j="2"; +WHEN j=3 THEN SET str_j="3"; +ELSE SET str_j="unknown"; +END CASE; +select "i was 20"; +END; +WHEN 30 THEN SET str_i="30"; +WHEN 40 THEN SET str_i="40"; +ELSE SET str_i="unknown"; +END CASE; +SELECT str_i, str_j; +END| +CREATE PROCEDURE proc_19194_nested_2(i int, j int) +BEGIN +DECLARE str_i CHAR(10); +DECLARE str_j CHAR(10); +CASE +WHEN i=10 THEN SET str_i="10"; +WHEN i=20 THEN +BEGIN +set str_i="20"; +CASE j +WHEN 1 THEN SET str_j="1"; +WHEN 2 THEN SET str_j="2"; +WHEN 3 THEN SET str_j="3"; +ELSE SET str_j="unknown"; +END CASE; +select "i was 20"; +END; +WHEN i=30 THEN SET str_i="30"; +WHEN i=40 THEN SET str_i="40"; +ELSE SET str_i="unknown"; +END CASE; +SELECT str_i, str_j; +END| +CREATE PROCEDURE proc_19194_nested_3(i int, j int) +BEGIN +DECLARE str_i CHAR(10); +DECLARE str_j CHAR(10); +CASE i +WHEN 10 THEN SET str_i="10"; +WHEN 20 THEN +BEGIN +set str_i="20"; +CASE j +WHEN 1 THEN SET str_j="1"; +WHEN 2 THEN SET str_j="2"; +WHEN 3 THEN SET str_j="3"; +ELSE SET str_j="unknown"; +END CASE; +select "i was 20"; +END; +WHEN 30 THEN SET str_i="30"; +WHEN 40 THEN SET str_i="40"; +ELSE SET str_i="unknown"; +END CASE; +SELECT str_i, str_j; +END| +CREATE PROCEDURE proc_19194_nested_4(i int, j int) +BEGIN +DECLARE str_i CHAR(10); +DECLARE str_j CHAR(10); +CASE +WHEN i=10 THEN SET str_i="10"; +WHEN i=20 THEN +BEGIN +set str_i="20"; +CASE +WHEN j=1 THEN SET str_j="1"; +WHEN j=2 THEN SET str_j="2"; +WHEN j=3 THEN SET str_j="3"; +ELSE SET str_j="unknown"; +END CASE; +select "i was 20"; +END; +WHEN i=30 THEN SET str_i="30"; +WHEN i=40 THEN SET str_i="40"; +ELSE SET str_i="unknown"; +END CASE; +SELECT str_i, str_j; +END| +SHOW PROCEDURE CODE proc_19194_simple; +Pos Instruction +0 set str@1 NULL +1 set_case_expr (12) 0 i@0 +2 jump_if_not 5(12) case_expr@0 = 1 +3 set str@1 '1' +4 jump 12 +5 jump_if_not 8(12) case_expr@0 = 2 +6 set str@1 '2' +7 jump 12 +8 jump_if_not 11(12) case_expr@0 = 3 +9 set str@1 '3' +10 jump 12 +11 set str@1 'unknown' +12 stmt 0 "SELECT str" +SHOW PROCEDURE CODE proc_19194_searched; +Pos Instruction +0 set str@1 NULL +1 jump_if_not 4(11) i@0 = 1 +2 set str@1 '1' +3 jump 11 +4 jump_if_not 7(11) i@0 = 2 +5 set str@1 '2' +6 jump 11 +7 jump_if_not 10(11) i@0 = 3 +8 set str@1 '3' +9 jump 11 +10 set str@1 'unknown' +11 stmt 0 "SELECT str" +SHOW PROCEDURE CODE proc_19194_nested_1; +Pos Instruction +0 set str_i@2 NULL +1 set str_j@3 NULL +2 set_case_expr (27) 0 i@0 +3 jump_if_not 6(27) case_expr@0 = 10 +4 set str_i@2 '10' +5 jump 27 +6 jump_if_not 20(27) case_expr@0 = 20 +7 set str_i@2 '20' +8 jump_if_not 11(18) j@1 = 1 +9 set str_j@3 '1' +10 jump 18 +11 jump_if_not 14(18) j@1 = 2 +12 set str_j@3 '2' +13 jump 18 +14 jump_if_not 17(18) j@1 = 3 +15 set str_j@3 '3' +16 jump 18 +17 set str_j@3 'unknown' +18 stmt 0 "select "i was 20"" +19 jump 27 +20 jump_if_not 23(27) case_expr@0 = 30 +21 set str_i@2 '30' +22 jump 27 +23 jump_if_not 26(27) case_expr@0 = 40 +24 set str_i@2 '40' +25 jump 27 +26 set str_i@2 'unknown' +27 stmt 0 "SELECT str_i, str_j" +SHOW PROCEDURE CODE proc_19194_nested_2; +Pos Instruction +0 set str_i@2 NULL +1 set str_j@3 NULL +2 jump_if_not 5(27) i@0 = 10 +3 set str_i@2 '10' +4 jump 27 +5 jump_if_not 20(27) i@0 = 20 +6 set str_i@2 '20' +7 set_case_expr (18) 0 j@1 +8 jump_if_not 11(18) case_expr@0 = 1 +9 set str_j@3 '1' +10 jump 18 +11 jump_if_not 14(18) case_expr@0 = 2 +12 set str_j@3 '2' +13 jump 18 +14 jump_if_not 17(18) case_expr@0 = 3 +15 set str_j@3 '3' +16 jump 18 +17 set str_j@3 'unknown' +18 stmt 0 "select "i was 20"" +19 jump 27 +20 jump_if_not 23(27) i@0 = 30 +21 set str_i@2 '30' +22 jump 27 +23 jump_if_not 26(27) i@0 = 40 +24 set str_i@2 '40' +25 jump 27 +26 set str_i@2 'unknown' +27 stmt 0 "SELECT str_i, str_j" +SHOW PROCEDURE CODE proc_19194_nested_3; +Pos Instruction +0 set str_i@2 NULL +1 set str_j@3 NULL +2 set_case_expr (28) 0 i@0 +3 jump_if_not 6(28) case_expr@0 = 10 +4 set str_i@2 '10' +5 jump 28 +6 jump_if_not 21(28) case_expr@0 = 20 +7 set str_i@2 '20' +8 set_case_expr (19) 1 j@1 +9 jump_if_not 12(19) case_expr@1 = 1 +10 set str_j@3 '1' +11 jump 19 +12 jump_if_not 15(19) case_expr@1 = 2 +13 set str_j@3 '2' +14 jump 19 +15 jump_if_not 18(19) case_expr@1 = 3 +16 set str_j@3 '3' +17 jump 19 +18 set str_j@3 'unknown' +19 stmt 0 "select "i was 20"" +20 jump 28 +21 jump_if_not 24(28) case_expr@0 = 30 +22 set str_i@2 '30' +23 jump 28 +24 jump_if_not 27(28) case_expr@0 = 40 +25 set str_i@2 '40' +26 jump 28 +27 set str_i@2 'unknown' +28 stmt 0 "SELECT str_i, str_j" +SHOW PROCEDURE CODE proc_19194_nested_4; +Pos Instruction +0 set str_i@2 NULL +1 set str_j@3 NULL +2 jump_if_not 5(26) i@0 = 10 +3 set str_i@2 '10' +4 jump 26 +5 jump_if_not 19(26) i@0 = 20 +6 set str_i@2 '20' +7 jump_if_not 10(17) j@1 = 1 +8 set str_j@3 '1' +9 jump 17 +10 jump_if_not 13(17) j@1 = 2 +11 set str_j@3 '2' +12 jump 17 +13 jump_if_not 16(17) j@1 = 3 +14 set str_j@3 '3' +15 jump 17 +16 set str_j@3 'unknown' +17 stmt 0 "select "i was 20"" +18 jump 26 +19 jump_if_not 22(26) i@0 = 30 +20 set str_i@2 '30' +21 jump 26 +22 jump_if_not 25(26) i@0 = 40 +23 set str_i@2 '40' +24 jump 26 +25 set str_i@2 'unknown' +26 stmt 0 "SELECT str_i, str_j" +CALL proc_19194_nested_1(10, 1); +str_i str_j +10 NULL +CALL proc_19194_nested_1(25, 1); +str_i str_j +unknown NULL +CALL proc_19194_nested_1(20, 1); +i was 20 +i was 20 +str_i str_j +20 1 +CALL proc_19194_nested_1(20, 2); +i was 20 +i was 20 +str_i str_j +20 2 +CALL proc_19194_nested_1(20, 3); +i was 20 +i was 20 +str_i str_j +20 3 +CALL proc_19194_nested_1(20, 4); +i was 20 +i was 20 +str_i str_j +20 unknown +CALL proc_19194_nested_1(30, 1); +str_i str_j +30 NULL +CALL proc_19194_nested_1(40, 1); +str_i str_j +40 NULL +CALL proc_19194_nested_1(0, 0); +str_i str_j +unknown NULL +CALL proc_19194_nested_2(10, 1); +str_i str_j +10 NULL +CALL proc_19194_nested_2(25, 1); +str_i str_j +unknown NULL +CALL proc_19194_nested_2(20, 1); +i was 20 +i was 20 +str_i str_j +20 1 +CALL proc_19194_nested_2(20, 2); +i was 20 +i was 20 +str_i str_j +20 2 +CALL proc_19194_nested_2(20, 3); +i was 20 +i was 20 +str_i str_j +20 3 +CALL proc_19194_nested_2(20, 4); +i was 20 +i was 20 +str_i str_j +20 unknown +CALL proc_19194_nested_2(30, 1); +str_i str_j +30 NULL +CALL proc_19194_nested_2(40, 1); +str_i str_j +40 NULL +CALL proc_19194_nested_2(0, 0); +str_i str_j +unknown NULL +CALL proc_19194_nested_3(10, 1); +str_i str_j +10 NULL +CALL proc_19194_nested_3(25, 1); +str_i str_j +unknown NULL +CALL proc_19194_nested_3(20, 1); +i was 20 +i was 20 +str_i str_j +20 1 +CALL proc_19194_nested_3(20, 2); +i was 20 +i was 20 +str_i str_j +20 2 +CALL proc_19194_nested_3(20, 3); +i was 20 +i was 20 +str_i str_j +20 3 +CALL proc_19194_nested_3(20, 4); +i was 20 +i was 20 +str_i str_j +20 unknown +CALL proc_19194_nested_3(30, 1); +str_i str_j +30 NULL +CALL proc_19194_nested_3(40, 1); +str_i str_j +40 NULL +CALL proc_19194_nested_3(0, 0); +str_i str_j +unknown NULL +CALL proc_19194_nested_4(10, 1); +str_i str_j +10 NULL +CALL proc_19194_nested_4(25, 1); +str_i str_j +unknown NULL +CALL proc_19194_nested_4(20, 1); +i was 20 +i was 20 +str_i str_j +20 1 +CALL proc_19194_nested_4(20, 2); +i was 20 +i was 20 +str_i str_j +20 2 +CALL proc_19194_nested_4(20, 3); +i was 20 +i was 20 +str_i str_j +20 3 +CALL proc_19194_nested_4(20, 4); +i was 20 +i was 20 +str_i str_j +20 unknown +CALL proc_19194_nested_4(30, 1); +str_i str_j +30 NULL +CALL proc_19194_nested_4(40, 1); +str_i str_j +40 NULL +CALL proc_19194_nested_4(0, 0); +str_i str_j +unknown NULL +DROP PROCEDURE proc_19194_simple; +DROP PROCEDURE proc_19194_searched; +DROP PROCEDURE proc_19194_nested_1; +DROP PROCEDURE proc_19194_nested_2; +DROP PROCEDURE proc_19194_nested_3; +DROP PROCEDURE proc_19194_nested_4; +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1); +SHOW PROCEDURE CODE p1; +Pos Instruction +0 stmt 2 "CREATE INDEX idx ON t1 (c1)" +DROP PROCEDURE p1; +drop table if exists t1; +drop procedure if exists proc_26977_broken; +drop procedure if exists proc_26977_works; +create table t1(a int unique); +create procedure proc_26977_broken(v int) +begin +declare i int default 5; +declare continue handler for sqlexception +begin +select 'caught something'; +retry: +while i > 0 do +begin +set i = i - 1; +select 'looping', i; +end; +end while retry; +end; +select 'do something'; +insert into t1 values (v); +select 'do something again'; +insert into t1 values (v); +end// +create procedure proc_26977_works(v int) +begin +declare i int default 5; +declare continue handler for sqlexception +begin +select 'caught something'; +retry: +while i > 0 do +begin +set i = i - 1; +select 'looping', i; +end; +end while retry; +select 'optimizer: keep hreturn'; +end; +select 'do something'; +insert into t1 values (v); +select 'do something again'; +insert into t1 values (v); +end// +show procedure code proc_26977_broken; +Pos Instruction +0 set i@1 5 +1 hpush_jump 8 2 CONTINUE +2 stmt 0 "select 'caught something'" +3 jump_if_not 7(7) i@1 > 0 +4 set i@1 i@1 - 1 +5 stmt 0 "select 'looping', i" +6 jump 3 +7 hreturn 2 +8 stmt 0 "select 'do something'" +9 stmt 5 "insert into t1 values (v)" +10 stmt 0 "select 'do something again'" +11 stmt 5 "insert into t1 values (v)" +12 hpop 1 +show procedure code proc_26977_works; +Pos Instruction +0 set i@1 5 +1 hpush_jump 9 2 CONTINUE +2 stmt 0 "select 'caught something'" +3 jump_if_not 7(7) i@1 > 0 +4 set i@1 i@1 - 1 +5 stmt 0 "select 'looping', i" +6 jump 3 +7 stmt 0 "select 'optimizer: keep hreturn'" +8 hreturn 2 +9 stmt 0 "select 'do something'" +10 stmt 5 "insert into t1 values (v)" +11 stmt 0 "select 'do something again'" +12 stmt 5 "insert into t1 values (v)" +13 hpop 1 +call proc_26977_broken(1); +do something +do something +do something again +do something again +caught something +caught something +looping i +looping 4 +looping i +looping 3 +looping i +looping 2 +looping i +looping 1 +looping i +looping 0 +call proc_26977_works(2); +do something +do something +do something again +do something again +caught something +caught something +looping i +looping 4 +looping i +looping 3 +looping i +looping 2 +looping i +looping 1 +looping i +looping 0 +optimizer: keep hreturn +optimizer: keep hreturn +drop table t1; +drop procedure proc_26977_broken; +drop procedure proc_26977_works; +drop procedure if exists proc_33618_h; +drop procedure if exists proc_33618_c; +create procedure proc_33618_h(num int) +begin +declare count1 int default '0'; +declare vb varchar(30); +declare last_row int; +while(num>=1) do +set num=num-1; +begin +declare cur1 cursor for select `a` from t_33618; +declare continue handler for not found set last_row = 1; +set last_row:=0; +open cur1; +rep1: +repeat +begin +declare exit handler for 1062 begin end; +fetch cur1 into vb; +if (last_row = 1) then +## should generate a hpop instruction here +leave rep1; +end if; +end; +until last_row=1 +end repeat; +close cur1; +end; +end while; +end// +create procedure proc_33618_c(num int) +begin +declare count1 int default '0'; +declare vb varchar(30); +declare last_row int; +while(num>=1) do +set num=num-1; +begin +declare cur1 cursor for select `a` from t_33618; +declare continue handler for not found set last_row = 1; +set last_row:=0; +open cur1; +rep1: +repeat +begin +declare cur2 cursor for select `b` from t_33618; +fetch cur1 into vb; +if (last_row = 1) then +## should generate a cpop instruction here +leave rep1; +end if; +end; +until last_row=1 +end repeat; +close cur1; +end; +end while; +end// +show procedure code proc_33618_h; +Pos Instruction +0 set count1@1 '0' +1 set vb@2 NULL +2 set last_row@3 NULL +3 jump_if_not 24(24) num@0 >= 1 +4 set num@0 num@0 - 1 +5 cpush cur1@0 +6 hpush_jump 9 4 CONTINUE +7 set last_row@3 1 +8 hreturn 4 +9 set last_row@3 0 +10 copen cur1@0 +11 hpush_jump 13 4 EXIT +12 hreturn 0 17 +13 cfetch cur1@0 vb@2 +14 jump_if_not 17(17) last_row@3 = 1 +15 hpop 1 +16 jump 19 +17 hpop 1 +18 jump_if_not 11(19) last_row@3 = 1 +19 cclose cur1@0 +20 hpop 1 +21 cpop 1 +22 jump 3 +show procedure code proc_33618_c; +Pos Instruction +0 set count1@1 '0' +1 set vb@2 NULL +2 set last_row@3 NULL +3 jump_if_not 23(23) num@0 >= 1 +4 set num@0 num@0 - 1 +5 cpush cur1@0 +6 hpush_jump 9 4 CONTINUE +7 set last_row@3 1 +8 hreturn 4 +9 set last_row@3 0 +10 copen cur1@0 +11 cpush cur2@1 +12 cfetch cur1@0 vb@2 +13 jump_if_not 16(16) last_row@3 = 1 +14 cpop 1 +15 jump 18 +16 cpop 1 +17 jump_if_not 11(18) last_row@3 = 1 +18 cclose cur1@0 +19 hpop 1 +20 cpop 1 +21 jump 3 +drop procedure proc_33618_h; +drop procedure proc_33618_c; +drop procedure if exists p_20906_a; +drop procedure if exists p_20906_b; +create procedure p_20906_a() SET @a=@a+1, @b=@b+1; +show procedure code p_20906_a; +Pos Instruction +0 stmt 31 "SET @a=@a+1" +1 stmt 31 "SET @b=@b+1" +set @a=1; +set @b=1; +call p_20906_a(); +select @a, @b; +@a @b +2 2 +create procedure p_20906_b() SET @a=@a+1, @b=@b+1, @c=@c+1; +show procedure code p_20906_b; +Pos Instruction +0 stmt 31 "SET @a=@a+1" +1 stmt 31 "SET @b=@b+1" +2 stmt 31 "SET @c=@c+1" +set @a=1; +set @b=1; +set @c=1; +call p_20906_b(); +select @a, @b, @c; +@a @b @c +2 2 2 +drop procedure p_20906_a; +drop procedure p_20906_b; +End of 5.0 tests. +CREATE PROCEDURE p1() +BEGIN +DECLARE dummy int default 0; +CASE 12 +WHEN 12 +THEN SET dummy = 0; +END CASE; +END// +SHOW PROCEDURE CODE p1; +Pos Instruction +0 set dummy@0 0 +1 set_case_expr (6) 0 12 +2 jump_if_not 5(6) case_expr@0 = 12 +3 set dummy@0 0 +4 jump 6 +5 error 1339 +DROP PROCEDURE p1; +# +# Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP +# + +# - Case 4: check that "No Data trumps Warning". + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE PROCEDURE p1() +BEGIN +DECLARE c CURSOR FOR SELECT a FROM t1; +OPEN c; +BEGIN +DECLARE v INT; +DECLARE CONTINUE HANDLER FOR SQLWARNING +BEGIN +SELECT "Warning found!"; +SHOW WARNINGS; +END; +DECLARE EXIT HANDLER FOR NOT FOUND +BEGIN +SELECT "End of Result Set found!"; +SHOW WARNINGS; +END; +WHILE TRUE DO +FETCH c INTO v; +END WHILE; +END; +CLOSE c; +SELECT a INTO @foo FROM t1 LIMIT 1; # Clear warning stack +END| +SET SESSION debug_dbug="+d,bug23032_emit_warning"; +CALL p1(); +Warning found! +Warning found! +Level Code Message +Warning 1105 Unknown error +Warning found! +Warning found! +Level Code Message +Warning 1105 Unknown error +Warning found! +Warning found! +Level Code Message +Warning 1105 Unknown error +End of Result Set found! +End of Result Set found! +Level Code Message +Warning 1105 Unknown error +Error 1329 No data - zero rows fetched, selected, or processed +SET SESSION debug_dbug="-d,bug23032_emit_warning"; +DROP PROCEDURE p1; +DROP TABLE t1; +# +# Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE +# +SET @@SQL_MODE = ''; +CREATE FUNCTION testf_bug11763507() RETURNS INT +BEGIN +RETURN 0; +END +$ +CREATE PROCEDURE testp_bug11763507() +BEGIN +SELECT "PROCEDURE testp_bug11763507"; +END +$ +SHOW FUNCTION CODE testf_bug11763507; +Pos Instruction +0 freturn int 0 +SHOW FUNCTION CODE TESTF_bug11763507; +Pos Instruction +0 freturn int 0 +SHOW PROCEDURE CODE testp_bug11763507; +Pos Instruction +0 stmt 0 "SELECT "PROCEDURE testp_bug11763507"" +SHOW PROCEDURE CODE TESTP_bug11763507; +Pos Instruction +0 stmt 0 "SELECT "PROCEDURE testp_bug11763507"" +DROP PROCEDURE testp_bug11763507; +DROP FUNCTION testf_bug11763507; +#END OF BUG#11763507 test. +# +# MDEV-13581 ROW TYPE OF t1 and t1%ROWTYPE for routine parameters +# +CREATE TABLE t1 (a INT, b TEXT); +CREATE PROCEDURE p1(a ROW TYPE OF t1, OUT b ROW TYPE OF t1) +BEGIN +SET a.a = 100; +SET a.b = 'aaa'; +SET b.a = 200; +SET b.b = 'bbb'; +SET a = b; +SET b = a; +SET a.a = b.a; +SET b.a = a.a; +END; +$$ +SHOW PROCEDURE CODE p1; +Pos Instruction +0 set a.a@0["a"] 100 +1 set a.b@0["b"] 'aaa' +2 set b.a@1["a"] 200 +3 set b.b@1["b"] 'bbb' +4 set a@0 b@1 +5 set b@1 a@0 +6 set a.a@0["a"] b.a@1["a"] +7 set b.a@1["a"] a.a@0["a"] +DROP PROCEDURE p1; +DROP TABLE t1; +# +# MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT +# +# Integer range FOR loop +CREATE PROCEDURE p1() +BEGIN +FOR i IN 1..3 +DO +SELECT i; +END FOR; +END; +$$ +CALL p1; +i +1 +i +2 +i +3 +SHOW PROCEDURE CODE p1; +Pos Instruction +0 set i@0 1 +1 set [upper_bound]@1 3 +2 jump_if_not 6(6) i@0 <= [upper_bound]@1 +3 stmt 0 "SELECT i" +4 set i@0 i@0 + 1 +5 jump 2 +DROP PROCEDURE p1; +# Nested integer range FOR loops +CREATE PROCEDURE p1() +BEGIN +fori: +FOR i IN 1..3 +DO +forj: +FOR j IN 1..3 +DO +IF i = 3 THEN +LEAVE fori; +END IF; +IF j = 3 THEN +LEAVE forj; +END IF; +SELECT i,j; +END FOR; +END FOR; +END; +$$ +CALL p1; +i j +1 1 +i j +1 2 +i j +2 1 +i j +2 2 +SHOW PROCEDURE CODE p1; +Pos Instruction +0 set i@0 1 +1 set [upper_bound]@1 3 +2 jump_if_not 17(17) i@0 <= [upper_bound]@1 +3 set j@2 1 +4 set [upper_bound]@3 3 +5 jump_if_not 13(13) j@2 <= [upper_bound]@3 +6 jump_if_not 8(8) i@0 = 3 +7 jump 17 +8 jump_if_not 10(10) j@2 = 3 +9 jump 13 +10 stmt 0 "SELECT i,j" +11 set j@2 j@2 + 1 +12 jump 5 +13 set i@0 i@0 + 1 +14 jump 2 +DROP PROCEDURE p1; +# Explicit cursor FOR loops +CREATE PROCEDURE p1() +BEGIN +DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b; +DECLARE cur1 CURSOR FOR SELECT 10 AS a, 'b0' AS b; +DECLARE cur2 CURSOR FOR SELECT 10 AS a, 'b0' AS b; +FOR rec1 IN cur1 +DO +SELECT rec1.a, rec1.b; +SET rec1.a= 11; +SET rec1.b= 'b1'; +SELECT rec1.a, rec1.b; +END FOR; +FOR rec0 IN cur0 +DO +SET rec0.a= 10; +SET rec0.b='b0'; +END FOR; +FOR rec2 IN cur2 +DO +SET rec2.a= 10; +SET rec2.b='b0'; +END FOR; +END; +$$ +SHOW PROCEDURE CODE p1; +Pos Instruction +0 cpush cur0@0 +1 cpush cur1@1 +2 cpush cur2@2 +3 cursor_copy_struct cur1 rec1@0 +4 copen cur1@1 +5 cfetch cur1@1 rec1@0 +6 jump_if_not 13(13) `cur1`%FOUND +7 stmt 0 "SELECT rec1.a, rec1.b" +8 set rec1.a@0["a"] 11 +9 set rec1.b@0["b"] 'b1' +10 stmt 0 "SELECT rec1.a, rec1.b" +11 cfetch cur1@1 rec1@0 +12 jump 6 +13 cursor_copy_struct cur0 rec0@1 +14 copen cur0@0 +15 cfetch cur0@0 rec0@1 +16 jump_if_not 21(21) `cur0`%FOUND +17 set rec0.a@1["a"] 10 +18 set rec0.b@1["b"] 'b0' +19 cfetch cur0@0 rec0@1 +20 jump 16 +21 cursor_copy_struct cur2 rec2@2 +22 copen cur2@2 +23 cfetch cur2@2 rec2@2 +24 jump_if_not 29(29) `cur2`%FOUND +25 set rec2.a@2["a"] 10 +26 set rec2.b@2["b"] 'b0' +27 cfetch cur2@2 rec2@2 +28 jump 24 +29 cpop 3 +DROP PROCEDURE p1; +# Nested explicit cursor FOR loops +CREATE PROCEDURE p1() +BEGIN +DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b; +FOR rec0 IN cur0 +DO +BEGIN +DECLARE cur1 CURSOR FOR SELECT 11 AS a, 'b1' AS b; +SET rec0.a= 11; +SET rec0.b= 'b0'; +FOR rec1 IN cur1 +DO +SET rec1.a= 11; +SET rec1.b= 'b1'; +BEGIN +DECLARE cur2 CURSOR FOR SELECT 12 AS a, 'b2' AS b; +FOR rec2 IN cur2 +DO +SET rec2.a=12; +SET rec2.b='b2'; +END FOR; +END; +END FOR; +END; +END FOR; +END; +$$ +SHOW PROCEDURE CODE p1; +Pos Instruction +0 cpush cur0@0 +1 cursor_copy_struct cur0 rec0@0 +2 copen cur0@0 +3 cfetch cur0@0 rec0@0 +4 jump_if_not 29(29) `cur0`%FOUND +5 cpush cur1@1 +6 set rec0.a@0["a"] 11 +7 set rec0.b@0["b"] 'b0' +8 cursor_copy_struct cur1 rec1@1 +9 copen cur1@1 +10 cfetch cur1@1 rec1@1 +11 jump_if_not 26(26) `cur1`%FOUND +12 set rec1.a@1["a"] 11 +13 set rec1.b@1["b"] 'b1' +14 cpush cur2@2 +15 cursor_copy_struct cur2 rec2@2 +16 copen cur2@2 +17 cfetch cur2@2 rec2@2 +18 jump_if_not 23(23) `cur2`%FOUND +19 set rec2.a@2["a"] 12 +20 set rec2.b@2["b"] 'b2' +21 cfetch cur2@2 rec2@2 +22 jump 18 +23 cpop 1 +24 cfetch cur1@1 rec1@1 +25 jump 11 +26 cpop 1 +27 cfetch cur0@0 rec0@0 +28 jump 4 +29 cpop 1 +DROP PROCEDURE p1; +# Implicit cursor FOR loops +CREATE PROCEDURE p1() +BEGIN +FOR rec1 IN (SELECT 11 AS a, 'b1' AS b) +DO +SELECT rec1.a, rec1.b; +SET rec1.a= 11; +SET rec1.b= 'b1'; +SELECT rec1.a, rec1.b; +END FOR; +FOR rec0 IN (SELECT 10 AS a, 'b0' AS b) +DO +SET rec0.a= 10; +SET rec0.b='b0'; +END FOR; +FOR rec2 IN (SELECT 12 AS a, 'b2' AS b) +DO +SET rec2.a= 10; +SET rec2.b='b0'; +END FOR; +END; +$$ +SHOW PROCEDURE CODE p1; +Pos Instruction +0 cpush [implicit_cursor]@0 +1 cursor_copy_struct [implicit_cursor] rec1@0 +2 copen [implicit_cursor]@0 +3 cfetch [implicit_cursor]@0 rec1@0 +4 jump_if_not 11(11) `[implicit_cursor]`%FOUND +5 stmt 0 "SELECT rec1.a, rec1.b" +6 set rec1.a@0["a"] 11 +7 set rec1.b@0["b"] 'b1' +8 stmt 0 "SELECT rec1.a, rec1.b" +9 cfetch [implicit_cursor]@0 rec1@0 +10 jump 4 +11 cpop 1 +12 cpush [implicit_cursor]@0 +13 cursor_copy_struct [implicit_cursor] rec0@1 +14 copen [implicit_cursor]@0 +15 cfetch [implicit_cursor]@0 rec0@1 +16 jump_if_not 21(21) `[implicit_cursor]`%FOUND +17 set rec0.a@1["a"] 10 +18 set rec0.b@1["b"] 'b0' +19 cfetch [implicit_cursor]@0 rec0@1 +20 jump 16 +21 cpop 1 +22 cpush [implicit_cursor]@0 +23 cursor_copy_struct [implicit_cursor] rec2@2 +24 copen [implicit_cursor]@0 +25 cfetch [implicit_cursor]@0 rec2@2 +26 jump_if_not 31(31) `[implicit_cursor]`%FOUND +27 set rec2.a@2["a"] 10 +28 set rec2.b@2["b"] 'b0' +29 cfetch [implicit_cursor]@0 rec2@2 +30 jump 26 +31 cpop 1 +DROP PROCEDURE p1; +# Nested implicit cursor FOR loops +CREATE PROCEDURE p1() +BEGIN +FOR rec0 IN (SELECT 10 AS a, 'b0' AS b) +DO +SET rec0.a= 11; +SET rec0.b= 'b0'; +FOR rec1 IN (SELECT 11 AS a, 'b1' AS b) +DO +SET rec1.a= 11; +SET rec1.b= 'b1'; +FOR rec2 IN (SELECT 12 AS a, 'b2' AS b) +DO +SET rec2.a=12; +SET rec2.b='b2'; +END FOR; +END FOR; +END FOR; +END; +$$ +SHOW PROCEDURE CODE p1; +Pos Instruction +0 cpush [implicit_cursor]@0 +1 cursor_copy_struct [implicit_cursor] rec0@0 +2 copen [implicit_cursor]@0 +3 cfetch [implicit_cursor]@0 rec0@0 +4 jump_if_not 29(29) `[implicit_cursor]`%FOUND +5 set rec0.a@0["a"] 11 +6 set rec0.b@0["b"] 'b0' +7 cpush [implicit_cursor]@1 +8 cursor_copy_struct [implicit_cursor] rec1@1 +9 copen [implicit_cursor]@1 +10 cfetch [implicit_cursor]@1 rec1@1 +11 jump_if_not 26(26) `[implicit_cursor]`%FOUND +12 set rec1.a@1["a"] 11 +13 set rec1.b@1["b"] 'b1' +14 cpush [implicit_cursor]@2 +15 cursor_copy_struct [implicit_cursor] rec2@2 +16 copen [implicit_cursor]@2 +17 cfetch [implicit_cursor]@2 rec2@2 +18 jump_if_not 23(23) `[implicit_cursor]`%FOUND +19 set rec2.a@2["a"] 12 +20 set rec2.b@2["b"] 'b2' +21 cfetch [implicit_cursor]@2 rec2@2 +22 jump 18 +23 cpop 1 +24 cfetch [implicit_cursor]@1 rec1@1 +25 jump 11 +26 cpop 1 +27 cfetch [implicit_cursor]@0 rec0@0 +28 jump 4 +29 cpop 1 +DROP PROCEDURE p1; |