You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1299 lines
30 KiB
1299 lines
30 KiB
5 months ago
|
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 3 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: select name from t1
|
||
|
|
3 hpush_jump 6 4 EXIT HANDLER FOR NOT FOUND
|
||
|
|
4 cclose c@0
|
||
|
|
5 hreturn 0 19
|
||
|
|
6 copen c@0
|
||
|
|
7 set n@4 NULL
|
||
|
|
8 hpush_jump 11 5 CONTINUE HANDLER FOR SQLEXCEPTION
|
||
|
|
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 "update t2 set idx = count where name=n"
|
||
|
|
17 hpop
|
||
|
|
18 jump 7
|
||
|
|
19 hpop
|
||
|
|
20 cpop 1
|
||
|
|
21 stmt "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 "drop temporary table if exists sudoku..."
|
||
|
|
1 stmt "create temporary table sudoku_work ( ..."
|
||
|
|
2 stmt "create temporary table sudoku_schedul..."
|
||
|
|
3 stmt "call sudoku_init()"
|
||
|
|
4 jump_if_not 7(8) p_naive@0
|
||
|
|
5 stmt "update sudoku_work set cnt = 0 where ..."
|
||
|
|
6 jump 8
|
||
|
|
7 stmt "call sudoku_count()"
|
||
|
|
8 stmt "insert into sudoku_schedule (`row`,co..."
|
||
|
|
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 "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 "select `row`,col into v_row,v_col fro..."
|
||
|
|
19 stmt "select dig into v_dig from sudoku_wor..."
|
||
|
|
20 set_case_expr (34) 0 v_dig@4
|
||
|
|
21 jump_if_not_case_when 25(34) (case_expr@0 = 0)
|
||
|
|
22 set v_dig@4 1
|
||
|
|
23 stmt "update sudoku_work set dig = 1 where ..."
|
||
|
|
24 jump 34
|
||
|
|
25 jump_if_not_case_when 32(34) (case_expr@0 = 9)
|
||
|
|
26 jump_if_not 30(34) (v_i@3 > 0)
|
||
|
|
27 stmt "update sudoku_work set dig = 0 where ..."
|
||
|
|
28 set v_i@3 (v_i@3 - 1)
|
||
|
|
29 jump 15
|
||
|
|
30 stmt "select v_scounter as 'Solutions'"
|
||
|
|
31 jump 45
|
||
|
|
32 set v_dig@4 (v_dig@4 + 1)
|
||
|
|
33 stmt "update sudoku_work set dig = v_dig wh..."
|
||
|
|
34 set v_tcounter@6 (v_tcounter@6 + 1)
|
||
|
|
35 jump_if_not 37(37) (0 = `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 "select dig from sudoku_work"
|
||
|
|
40 stmt "select v_tcounter as 'Tests'"
|
||
|
|
41 set v_scounter@2 (v_scounter@2 + 1)
|
||
|
|
42 jump_if_not 45(14) ((0 <> p_all@1) and (v_i@3 > 0))
|
||
|
|
43 set v_i@3 (v_i@3 - 1)
|
||
|
|
44 jump 14
|
||
|
|
45 stmt "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_case_when 5(12) (case_expr@0 = 1)
|
||
|
|
3 set str@1 '1'
|
||
|
|
4 jump 12
|
||
|
|
5 jump_if_not_case_when 8(12) (case_expr@0 = 2)
|
||
|
|
6 set str@1 '2'
|
||
|
|
7 jump 12
|
||
|
|
8 jump_if_not_case_when 11(12) (case_expr@0 = 3)
|
||
|
|
9 set str@1 '3'
|
||
|
|
10 jump 12
|
||
|
|
11 set str@1 'unknown'
|
||
|
|
12 stmt "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 "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_case_when 6(27) (case_expr@0 = 10)
|
||
|
|
4 set str_i@2 '10'
|
||
|
|
5 jump 27
|
||
|
|
6 jump_if_not_case_when 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 "select "i was 20""
|
||
|
|
19 jump 27
|
||
|
|
20 jump_if_not_case_when 23(27) (case_expr@0 = 30)
|
||
|
|
21 set str_i@2 '30'
|
||
|
|
22 jump 27
|
||
|
|
23 jump_if_not_case_when 26(27) (case_expr@0 = 40)
|
||
|
|
24 set str_i@2 '40'
|
||
|
|
25 jump 27
|
||
|
|
26 set str_i@2 'unknown'
|
||
|
|
27 stmt "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_case_when 11(18) (case_expr@0 = 1)
|
||
|
|
9 set str_j@3 '1'
|
||
|
|
10 jump 18
|
||
|
|
11 jump_if_not_case_when 14(18) (case_expr@0 = 2)
|
||
|
|
12 set str_j@3 '2'
|
||
|
|
13 jump 18
|
||
|
|
14 jump_if_not_case_when 17(18) (case_expr@0 = 3)
|
||
|
|
15 set str_j@3 '3'
|
||
|
|
16 jump 18
|
||
|
|
17 set str_j@3 'unknown'
|
||
|
|
18 stmt "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 "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_case_when 6(28) (case_expr@0 = 10)
|
||
|
|
4 set str_i@2 '10'
|
||
|
|
5 jump 28
|
||
|
|
6 jump_if_not_case_when 21(28) (case_expr@0 = 20)
|
||
|
|
7 set str_i@2 '20'
|
||
|
|
8 set_case_expr (19) 1 j@1
|
||
|
|
9 jump_if_not_case_when 12(19) (case_expr@1 = 1)
|
||
|
|
10 set str_j@3 '1'
|
||
|
|
11 jump 19
|
||
|
|
12 jump_if_not_case_when 15(19) (case_expr@1 = 2)
|
||
|
|
13 set str_j@3 '2'
|
||
|
|
14 jump 19
|
||
|
|
15 jump_if_not_case_when 18(19) (case_expr@1 = 3)
|
||
|
|
16 set str_j@3 '3'
|
||
|
|
17 jump 19
|
||
|
|
18 set str_j@3 'unknown'
|
||
|
|
19 stmt "select "i was 20""
|
||
|
|
20 jump 28
|
||
|
|
21 jump_if_not_case_when 24(28) (case_expr@0 = 30)
|
||
|
|
22 set str_i@2 '30'
|
||
|
|
23 jump 28
|
||
|
|
24 jump_if_not_case_when 27(28) (case_expr@0 = 40)
|
||
|
|
25 set str_i@2 '40'
|
||
|
|
26 jump 28
|
||
|
|
27 set str_i@2 'unknown'
|
||
|
|
28 stmt "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 "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 "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 "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 HANDLER FOR SQLEXCEPTION
|
||
|
|
2 stmt "select 'caught something'"
|
||
|
|
3 jump_if_not 7(7) (i@1 > 0)
|
||
|
|
4 set i@1 (i@1 - 1)
|
||
|
|
5 stmt "select 'looping', i"
|
||
|
|
6 jump 3
|
||
|
|
7 hreturn 2
|
||
|
|
8 stmt "select 'do something'"
|
||
|
|
9 stmt "insert into t1 values (v)"
|
||
|
|
10 stmt "select 'do something again'"
|
||
|
|
11 stmt "insert into t1 values (v)"
|
||
|
|
12 hpop
|
||
|
|
show procedure code proc_26977_works;
|
||
|
|
Pos Instruction
|
||
|
|
0 set i@1 5
|
||
|
|
1 hpush_jump 9 2 CONTINUE HANDLER FOR SQLEXCEPTION
|
||
|
|
2 stmt "select 'caught something'"
|
||
|
|
3 jump_if_not 7(7) (i@1 > 0)
|
||
|
|
4 set i@1 (i@1 - 1)
|
||
|
|
5 stmt "select 'looping', i"
|
||
|
|
6 jump 3
|
||
|
|
7 stmt "select 'optimizer: keep hreturn'"
|
||
|
|
8 hreturn 2
|
||
|
|
9 stmt "select 'do something'"
|
||
|
|
10 stmt "insert into t1 values (v)"
|
||
|
|
11 stmt "select 'do something again'"
|
||
|
|
12 stmt "insert into t1 values (v)"
|
||
|
|
13 hpop
|
||
|
|
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: select `a` from t_33618
|
||
|
|
6 hpush_jump 9 4 CONTINUE HANDLER FOR NOT FOUND
|
||
|
|
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 HANDLER FOR 1062
|
||
|
|
12 hreturn 0 17
|
||
|
|
13 cfetch cur1@0 vb@2
|
||
|
|
14 jump_if_not 17(17) (last_row@3 = 1)
|
||
|
|
15 hpop
|
||
|
|
16 jump 19
|
||
|
|
17 hpop
|
||
|
|
18 jump_if_not 11(19) (last_row@3 = 1)
|
||
|
|
19 cclose cur1@0
|
||
|
|
20 hpop
|
||
|
|
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: select `a` from t_33618
|
||
|
|
6 hpush_jump 9 4 CONTINUE HANDLER FOR NOT FOUND
|
||
|
|
7 set last_row@3 1
|
||
|
|
8 hreturn 4
|
||
|
|
9 set last_row@3 0
|
||
|
|
10 copen cur1@0
|
||
|
|
11 cpush cur2@1: select `b` from t_33618
|
||
|
|
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
|
||
|
|
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 "SET @a=@a+1"
|
||
|
|
1 stmt "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 "SET @a=@a+1"
|
||
|
|
1 stmt "SET @b=@b+1"
|
||
|
|
2 stmt "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_case_when 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
|
||
|
|
GET DIAGNOSTICS @n = NUMBER;
|
||
|
|
GET DIAGNOSTICS CONDITION @n @err_no = MYSQL_ERRNO, @err_txt = MESSAGE_TEXT;
|
||
|
|
SELECT "Warning found!";
|
||
|
|
SELECT @err_no, @err_txt;
|
||
|
|
END;
|
||
|
|
DECLARE EXIT HANDLER FOR NOT FOUND
|
||
|
|
BEGIN
|
||
|
|
GET DIAGNOSTICS @n = NUMBER;
|
||
|
|
GET DIAGNOSTICS CONDITION @n @err_no = MYSQL_ERRNO, @err_txt = MESSAGE_TEXT;
|
||
|
|
SELECT "End of Result Set found!";
|
||
|
|
SELECT @err_no, @err_txt;
|
||
|
|
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="+d,bug23032_emit_warning";
|
||
|
|
CALL p1();
|
||
|
|
Warning found!
|
||
|
|
Warning found!
|
||
|
|
@err_no @err_txt
|
||
|
|
1105 Unknown error
|
||
|
|
Warning found!
|
||
|
|
Warning found!
|
||
|
|
@err_no @err_txt
|
||
|
|
1105 Unknown error
|
||
|
|
Warning found!
|
||
|
|
Warning found!
|
||
|
|
@err_no @err_txt
|
||
|
|
1105 Unknown error
|
||
|
|
End of Result Set found!
|
||
|
|
End of Result Set found!
|
||
|
|
@err_no @err_txt
|
||
|
|
1329 No data - zero rows fetched, selected, or processed
|
||
|
|
SET SESSION debug="-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 3 0
|
||
|
|
SHOW FUNCTION CODE TESTF_bug11763507;
|
||
|
|
Pos Instruction
|
||
|
|
0 freturn 3 0
|
||
|
|
SHOW PROCEDURE CODE testp_bug11763507;
|
||
|
|
Pos Instruction
|
||
|
|
0 stmt "SELECT "PROCEDURE testp_bug11763507""
|
||
|
|
SHOW PROCEDURE CODE TESTP_bug11763507;
|
||
|
|
Pos Instruction
|
||
|
|
0 stmt "SELECT "PROCEDURE testp_bug11763507""
|
||
|
|
DROP PROCEDURE testp_bug11763507;
|
||
|
|
DROP FUNCTION testf_bug11763507;
|
||
|
|
#END OF BUG#11763507 test.
|
||
|
|
|
||
|
|
# WL#4179: Stored programs: validation of stored program statements.
|
||
|
|
#
|
||
|
|
# Check that query string is stored only for the expressions, which
|
||
|
|
# reference tables or stored functions.
|
||
|
|
#
|
||
|
|
# Test cases in this file require SHOW ... CODE, which is available only
|
||
|
|
# in the debug mode.
|
||
|
|
|
||
|
|
CREATE TABLE t1(a INT);
|
||
|
|
INSERT INTO t1 VALUES (10);
|
||
|
|
CREATE TEMPORARY TABLE t2(a INT);
|
||
|
|
INSERT INTO t2 VALUES (20);
|
||
|
|
CREATE VIEW t3 AS SELECT 30;
|
||
|
|
CREATE FUNCTION f() RETURNS INT
|
||
|
|
RETURN 1|
|
||
|
|
CREATE PROCEDURE p1()
|
||
|
|
BEGIN
|
||
|
|
# DEFAULT-expression
|
||
|
|
DECLARE x1 INT DEFAULT (SELECT 1 + 2);
|
||
|
|
DECLARE x2 INT DEFAULT (SELECT * FROM (SELECT 1 + 2) t1);
|
||
|
|
DECLARE x3 INT DEFAULT (SELECT * FROM t1);
|
||
|
|
DECLARE x4 INT DEFAULT (SELECT * FROM t2);
|
||
|
|
DECLARE x5 INT DEFAULT (SELECT * FROM t3);
|
||
|
|
DECLARE x6 INT DEFAULT (SELECT f());
|
||
|
|
# CURSOR-query.
|
||
|
|
DECLARE c1 CURSOR FOR SELECT (1 + 2) FROM dual;
|
||
|
|
DECLARE c2 CURSOR FOR SELECT * FROM (SELECT 1 + 2) t1;
|
||
|
|
DECLARE c3 CURSOR FOR SELECT * FROM t1;
|
||
|
|
DECLARE c4 CURSOR FOR SELECT * FROM t2;
|
||
|
|
DECLARE c5 CURSOR FOR SELECT * FROM t3;
|
||
|
|
DECLARE c6 CURSOR FOR SELECT f();
|
||
|
|
# IF-expression.
|
||
|
|
IF (SELECT 1 + 2) THEN
|
||
|
|
SET @dummy = 1;
|
||
|
|
END IF;
|
||
|
|
IF (SELECT * FROM (SELECT 1 + 2) t1) THEN
|
||
|
|
SET @dummy = 1;
|
||
|
|
END IF;
|
||
|
|
IF (SELECT * FROM t1) THEN
|
||
|
|
SET @dummy = 1;
|
||
|
|
END IF;
|
||
|
|
IF (SELECT * FROM t2) THEN
|
||
|
|
SET @dummy = 1;
|
||
|
|
END IF;
|
||
|
|
IF (SELECT * FROM t3) THEN
|
||
|
|
SET @dummy = 1;
|
||
|
|
END IF;
|
||
|
|
IF (SELECT f()) THEN
|
||
|
|
SET @dummy = 1;
|
||
|
|
END IF;
|
||
|
|
# SET-expression.
|
||
|
|
SET x1 = (SELECT 1 + 2);
|
||
|
|
SET x1 = (SELECT * FROM (SELECT 1 + 2) t1);
|
||
|
|
SET x1 = (SELECT * FROM t1);
|
||
|
|
SET x1 = (SELECT * FROM t2);
|
||
|
|
SET x1 = (SELECT * FROM t3);
|
||
|
|
SET x1 = (SELECT f());
|
||
|
|
# CASE-expressions.
|
||
|
|
CASE
|
||
|
|
WHEN (SELECT 1 + 2) = 1 THEN SET @dummy = 1;
|
||
|
|
WHEN (SELECT * FROM (SELECT 1 + 2) t1) = 2 THEN SET @dummy = 1;
|
||
|
|
WHEN (SELECT * FROM t1) = 3 THEN SET @dummy = 1;
|
||
|
|
WHEN (SELECT * FROM t2) = 3 THEN SET @dummy = 1;
|
||
|
|
WHEN (SELECT * FROM t3) = 3 THEN SET @dummy = 1;
|
||
|
|
WHEN (SELECT f()) = 3 THEN SET @dummy = 1;
|
||
|
|
END CASE;
|
||
|
|
CASE (SELECT 1 + 2)
|
||
|
|
WHEN 1 THEN SET @dummy = 1;
|
||
|
|
ELSE SET @dummy = 1;
|
||
|
|
END CASE;
|
||
|
|
CASE (SELECT * FROM (SELECT 1 + 2) t1)
|
||
|
|
WHEN 1 THEN SET @dummy = 1;
|
||
|
|
ELSE SET @dummy = 1;
|
||
|
|
END CASE;
|
||
|
|
CASE (SELECT * FROM t1)
|
||
|
|
WHEN 1 THEN SET @dummy = 1;
|
||
|
|
ELSE SET @dummy = 1;
|
||
|
|
END CASE;
|
||
|
|
CASE (SELECT * FROM t2)
|
||
|
|
WHEN 1 THEN SET @dummy = 1;
|
||
|
|
ELSE SET @dummy = 1;
|
||
|
|
END CASE;
|
||
|
|
CASE (SELECT * FROM t3)
|
||
|
|
WHEN 1 THEN SET @dummy = 1;
|
||
|
|
ELSE SET @dummy = 1;
|
||
|
|
END CASE;
|
||
|
|
CASE (SELECT f())
|
||
|
|
WHEN 1 THEN SET @dummy = 1;
|
||
|
|
ELSE SET @dummy = 1;
|
||
|
|
END CASE;
|
||
|
|
# WHILE-expression.
|
||
|
|
WHILE (SELECT 1 - 1) DO
|
||
|
|
SET @dummy = 1;
|
||
|
|
END WHILE;
|
||
|
|
WHILE (SELECT * FROM (SELECT 1 - 1) t1) DO
|
||
|
|
SET @dummy = 1;
|
||
|
|
END WHILE;
|
||
|
|
WHILE (SELECT * FROM t1) - 10 DO
|
||
|
|
SET @dummy = 1;
|
||
|
|
END WHILE;
|
||
|
|
WHILE (SELECT * FROM t2) - 10 DO
|
||
|
|
SET @dummy = 1;
|
||
|
|
END WHILE;
|
||
|
|
WHILE (SELECT * FROM t3) - 10 DO
|
||
|
|
SET @dummy = 1;
|
||
|
|
END WHILE;
|
||
|
|
WHILE (SELECT f()) - 1 DO
|
||
|
|
SET @dummy = 1;
|
||
|
|
END WHILE;
|
||
|
|
# REPEAT-expression.
|
||
|
|
REPEAT
|
||
|
|
SET @dummy = 1;
|
||
|
|
UNTIL (SELECT 1 - 1) END REPEAT;
|
||
|
|
REPEAT
|
||
|
|
SET @dummy = 1;
|
||
|
|
UNTIL (SELECT * FROM (SELECT 1 - 1) t1) END REPEAT;
|
||
|
|
REPEAT
|
||
|
|
SET @dummy = 1;
|
||
|
|
UNTIL (SELECT * FROM t1) - 10 END REPEAT;
|
||
|
|
REPEAT
|
||
|
|
SET @dummy = 1;
|
||
|
|
UNTIL (SELECT * FROM t2) - 10 END REPEAT;
|
||
|
|
REPEAT
|
||
|
|
SET @dummy = 1;
|
||
|
|
UNTIL (SELECT * FROM t3) - 10 END REPEAT;
|
||
|
|
REPEAT
|
||
|
|
SET @dummy = 1;
|
||
|
|
UNTIL (SELECT f()) - 1 END REPEAT;
|
||
|
|
END|
|
||
|
|
CREATE FUNCTION f1() RETURNS INT
|
||
|
|
RETURN (SELECT 1 + 2)|
|
||
|
|
CREATE FUNCTION f2() RETURNS INT
|
||
|
|
RETURN (SELECT * FROM (SELECT 1 + 2) t1)|
|
||
|
|
CREATE FUNCTION f3() RETURNS INT
|
||
|
|
RETURN (SELECT * FROM t1)|
|
||
|
|
CREATE FUNCTION f4() RETURNS INT
|
||
|
|
RETURN (SELECT * FROM t2)|
|
||
|
|
CREATE FUNCTION f5() RETURNS INT
|
||
|
|
RETURN (SELECT * FROM t3)|
|
||
|
|
CREATE FUNCTION f6() RETURNS INT
|
||
|
|
RETURN f()|
|
||
|
|
|
||
|
|
SHOW PROCEDURE CODE p1;
|
||
|
|
Pos Instruction
|
||
|
|
0 set x1@0 (select (1 + 2))
|
||
|
|
1 set x2@1 (select `*` from (select (1 + 2) AS `1 + 2`) `t1`)
|
||
|
|
2 set x3@2 (select `*` from `test`.`t1`)
|
||
|
|
3 set x4@3 (select `*` from `test`.`t2`)
|
||
|
|
4 set x5@4 (select `*` from `test`.`t3`)
|
||
|
|
5 set x6@5 (select `f`())
|
||
|
|
6 cpush c1@0:
|
||
|
|
7 cpush c2@1: SELECT * FROM (SELECT 1 + 2) t1
|
||
|
|
8 cpush c3@2: SELECT * FROM t1
|
||
|
|
9 cpush c4@3: SELECT * FROM t2
|
||
|
|
10 cpush c5@4: SELECT * FROM t3
|
||
|
|
11 cpush c6@5: SELECT f()
|
||
|
|
12 jump_if_not 14(14) (select (1 + 2))
|
||
|
|
13 stmt "SET @dummy = 1"
|
||
|
|
14 jump_if_not 16(16) (select `*` from (select (1 + 2) AS `1 + 2`) `t1`)
|
||
|
|
15 stmt "SET @dummy = 1"
|
||
|
|
16 jump_if_not 18(18) (select `*` from `test`.`t1`)
|
||
|
|
17 stmt "SET @dummy = 1"
|
||
|
|
18 jump_if_not 20(20) (select `*` from `test`.`t2`)
|
||
|
|
19 stmt "SET @dummy = 1"
|
||
|
|
20 jump_if_not 22(22) (select `*` from `test`.`t3`)
|
||
|
|
21 stmt "SET @dummy = 1"
|
||
|
|
22 jump_if_not 24(24) (select `f`())
|
||
|
|
23 stmt "SET @dummy = 1"
|
||
|
|
24 set x1@0 (select (1 + 2))
|
||
|
|
25 set x1@0 (select `*` from (select (1 + 2) AS `1 + 2`) `t1`)
|
||
|
|
26 set x1@0 (select `*` from `test`.`t1`)
|
||
|
|
27 set x1@0 (select `*` from `test`.`t2`)
|
||
|
|
28 set x1@0 (select `*` from `test`.`t3`)
|
||
|
|
29 set x1@0 (select `f`())
|
||
|
|
30 jump_if_not 33(49) ((select (1 + 2)) = 1)
|
||
|
|
31 stmt "SET @dummy = 1"
|
||
|
|
32 jump 49
|
||
|
|
33 jump_if_not 36(49) ((select `*` from (select (1 + 2) AS `1 + 2`) `t1`) = 2)
|
||
|
|
34 stmt "SET @dummy = 1"
|
||
|
|
35 jump 49
|
||
|
|
36 jump_if_not 39(49) ((select `*` from `test`.`t1`) = 3)
|
||
|
|
37 stmt "SET @dummy = 1"
|
||
|
|
38 jump 49
|
||
|
|
39 jump_if_not 42(49) ((select `*` from `test`.`t2`) = 3)
|
||
|
|
40 stmt "SET @dummy = 1"
|
||
|
|
41 jump 49
|
||
|
|
42 jump_if_not 45(49) ((select `*` from `test`.`t3`) = 3)
|
||
|
|
43 stmt "SET @dummy = 1"
|
||
|
|
44 jump 49
|
||
|
|
45 jump_if_not 48(49) ((select `f`()) = 3)
|
||
|
|
46 stmt "SET @dummy = 1"
|
||
|
|
47 jump 49
|
||
|
|
48 error 1339
|
||
|
|
49 set_case_expr (54) 0 (select (1 + 2))
|
||
|
|
50 jump_if_not_case_when 53(54) (case_expr@0 = 1)
|
||
|
|
51 stmt "SET @dummy = 1"
|
||
|
|
52 jump 54
|
||
|
|
53 stmt "SET @dummy = 1"
|
||
|
|
54 set_case_expr (59) 1 (select `*` from (select (1 + 2) AS `1 + 2`) `t1`)
|
||
|
|
55 jump_if_not_case_when 58(59) (case_expr@1 = 1)
|
||
|
|
56 stmt "SET @dummy = 1"
|
||
|
|
57 jump 59
|
||
|
|
58 stmt "SET @dummy = 1"
|
||
|
|
59 set_case_expr (64) 2 (select `*` from `test`.`t1`)
|
||
|
|
60 jump_if_not_case_when 63(64) (case_expr@2 = 1)
|
||
|
|
61 stmt "SET @dummy = 1"
|
||
|
|
62 jump 64
|
||
|
|
63 stmt "SET @dummy = 1"
|
||
|
|
64 set_case_expr (69) 3 (select `*` from `test`.`t2`)
|
||
|
|
65 jump_if_not_case_when 68(69) (case_expr@3 = 1)
|
||
|
|
66 stmt "SET @dummy = 1"
|
||
|
|
67 jump 69
|
||
|
|
68 stmt "SET @dummy = 1"
|
||
|
|
69 set_case_expr (74) 4 (select `*` from `test`.`t3`)
|
||
|
|
70 jump_if_not_case_when 73(74) (case_expr@4 = 1)
|
||
|
|
71 stmt "SET @dummy = 1"
|
||
|
|
72 jump 74
|
||
|
|
73 stmt "SET @dummy = 1"
|
||
|
|
74 set_case_expr (79) 5 (select `f`())
|
||
|
|
75 jump_if_not_case_when 78(79) (case_expr@5 = 1)
|
||
|
|
76 stmt "SET @dummy = 1"
|
||
|
|
77 jump 79
|
||
|
|
78 stmt "SET @dummy = 1"
|
||
|
|
79 jump_if_not 82(82) (select (1 - 1))
|
||
|
|
80 stmt "SET @dummy = 1"
|
||
|
|
81 jump 79
|
||
|
|
82 jump_if_not 85(85) (select `*` from (select (1 - 1) AS `1 - 1`) `t1`)
|
||
|
|
83 stmt "SET @dummy = 1"
|
||
|
|
84 jump 82
|
||
|
|
85 jump_if_not 88(88) ((select `*` from `test`.`t1`) - 10)
|
||
|
|
86 stmt "SET @dummy = 1"
|
||
|
|
87 jump 85
|
||
|
|
88 jump_if_not 91(91) ((select `*` from `test`.`t2`) - 10)
|
||
|
|
89 stmt "SET @dummy = 1"
|
||
|
|
90 jump 88
|
||
|
|
91 jump_if_not 94(94) ((select `*` from `test`.`t3`) - 10)
|
||
|
|
92 stmt "SET @dummy = 1"
|
||
|
|
93 jump 91
|
||
|
|
94 jump_if_not 97(97) ((select `f`()) - 1)
|
||
|
|
95 stmt "SET @dummy = 1"
|
||
|
|
96 jump 94
|
||
|
|
97 stmt "SET @dummy = 1"
|
||
|
|
98 jump_if_not 97(99) (select (1 - 1))
|
||
|
|
99 stmt "SET @dummy = 1"
|
||
|
|
100 jump_if_not 99(101) (select `*` from (select (1 - 1) AS `1 - 1`) `t1`)
|
||
|
|
101 stmt "SET @dummy = 1"
|
||
|
|
102 jump_if_not 101(103) ((select `*` from `test`.`t1`) - 10)
|
||
|
|
103 stmt "SET @dummy = 1"
|
||
|
|
104 jump_if_not 103(105) ((select `*` from `test`.`t2`) - 10)
|
||
|
|
105 stmt "SET @dummy = 1"
|
||
|
|
106 jump_if_not 105(107) ((select `*` from `test`.`t3`) - 10)
|
||
|
|
107 stmt "SET @dummy = 1"
|
||
|
|
108 jump_if_not 107(109) ((select `f`()) - 1)
|
||
|
|
109 cpop 6
|
||
|
|
|
||
|
|
SHOW FUNCTION CODE f1;
|
||
|
|
Pos Instruction
|
||
|
|
0 freturn 3 (select (1 + 2))
|
||
|
|
SHOW FUNCTION CODE f2;
|
||
|
|
Pos Instruction
|
||
|
|
0 freturn 3 (select `*` from (select (1 + 2) AS `1 + 2`) `t1`)
|
||
|
|
SHOW FUNCTION CODE f3;
|
||
|
|
Pos Instruction
|
||
|
|
0 freturn 3 (select `*` from `test`.`t1`)
|
||
|
|
SHOW FUNCTION CODE f4;
|
||
|
|
Pos Instruction
|
||
|
|
0 freturn 3 (select `*` from `test`.`t2`)
|
||
|
|
SHOW FUNCTION CODE f5;
|
||
|
|
Pos Instruction
|
||
|
|
0 freturn 3 (select `*` from `test`.`t3`)
|
||
|
|
SHOW FUNCTION CODE f6;
|
||
|
|
Pos Instruction
|
||
|
|
0 freturn 3 `f`()
|
||
|
|
|
||
|
|
DROP FUNCTION f;
|
||
|
|
DROP PROCEDURE p1;
|
||
|
|
DROP FUNCTION f1;
|
||
|
|
DROP FUNCTION f2;
|
||
|
|
DROP FUNCTION f3;
|
||
|
|
DROP FUNCTION f4;
|
||
|
|
DROP FUNCTION f5;
|
||
|
|
DROP FUNCTION f6;
|
||
|
|
DROP TABLE t1;
|
||
|
|
DROP TEMPORARY TABLE t2;
|
||
|
|
DROP VIEW t3;
|
||
|
|
|
||
|
|
# SHOW ... CODE
|
||
|
|
#
|
||
|
|
CREATE PROCEDURE p11_many_handlers ()
|
||
|
|
BEGIN
|
||
|
|
DECLARE CONTINUE HANDLER FOR 1050 SELECT "1050 for 401a, please";
|
||
|
|
DECLARE EXIT HANDLER FOR NOT FOUND SELECT "a place not found";
|
||
|
|
DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT "a warn place";
|
||
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT "an exceptional place";
|
||
|
|
DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' SELECT "state of the nation";
|
||
|
|
BEGIN
|
||
|
|
DECLARE EXIT HANDLER FOR NOT FOUND, 1,2 SELECT "multi multi";
|
||
|
|
END;
|
||
|
|
END|
|
||
|
|
SHOW PROCEDURE CODE p11_many_handlers;
|
||
|
|
Pos Instruction
|
||
|
|
0 hpush_jump 3 0 CONTINUE HANDLER FOR 1050
|
||
|
|
1 stmt "SELECT "1050 for 401a, please""
|
||
|
|
2 hreturn 0
|
||
|
|
3 hpush_jump 6 0 EXIT HANDLER FOR NOT FOUND
|
||
|
|
4 stmt "SELECT "a place not found""
|
||
|
|
5 hreturn 0 19
|
||
|
|
6 hpush_jump 9 0 CONTINUE HANDLER FOR SQLWARNING
|
||
|
|
7 stmt "SELECT "a warn place""
|
||
|
|
8 hreturn 0
|
||
|
|
9 hpush_jump 12 0 EXIT HANDLER FOR SQLEXCEPTION
|
||
|
|
10 stmt "SELECT "an exceptional place""
|
||
|
|
11 hreturn 0 19
|
||
|
|
12 hpush_jump 15 0 CONTINUE HANDLER FOR SQLSTATE '01000'
|
||
|
|
13 stmt "SELECT "state of the nation""
|
||
|
|
14 hreturn 0
|
||
|
|
15 hpush_jump 18 0 EXIT HANDLER FOR NOT FOUND, 1, 2
|
||
|
|
16 stmt "SELECT "multi multi""
|
||
|
|
17 hreturn 0 18
|
||
|
|
18 hpop
|
||
|
|
19 hpop
|
||
|
|
DROP PROCEDURE p11_many_handlers;
|