| blob | bb30cf5bb495d8d4598f605cf6635a6235dccc77 |
1 drop procedure if exists empty;
2 drop procedure if exists code_sample;
3 create procedure empty()
4 begin
5 end;
6 show procedure code empty;
7 Pos Instruction
8 drop procedure empty;
9 create function almost_empty()
10 returns int
11 return 0;
12 show function code almost_empty;
13 Pos Instruction
14 0 freturn 3 0
15 drop function almost_empty;
16 create procedure code_sample(x int, out err int, out nulls int)
17 begin
18 declare count int default 0;
19 set nulls = 0;
20 begin
21 declare c cursor for select name from t1;
22 declare exit handler for not found close c;
23 open c;
24 loop
25 begin
26 declare n varchar(20);
27 declare continue handler for sqlexception set err=1;
28 fetch c into n;
29 if isnull(n) then
30 set nulls = nulls + 1;
31 else
32 set count = count + 1;
33 update t2 set idx = count where name=n;
34 end if;
35 end;
36 end loop;
37 end;
38 select t.name, t.idx from t2 t order by idx asc;
39 end//
40 show procedure code code_sample;
41 Pos Instruction
42 0 set count@3 0
43 1 set nulls@2 0
44 2 cpush c@0
45 3 hpush_jump 6 4 EXIT
46 4 cclose c@0
47 5 hreturn 0 19
48 6 copen c@0
49 7 set n@4 NULL
50 8 hpush_jump 11 5 CONTINUE
51 9 set err@1 1
52 10 hreturn 5
53 11 cfetch c@0 n@4
54 12 jump_if_not 15(17) isnull(n@4)
55 13 set nulls@2 (nulls@2 + 1)
56 14 jump 17
57 15 set count@3 (count@3 + 1)
58 16 stmt 4 "update t2 set idx = count where name=n"
59 17 hpop 1
60 18 jump 7
61 19 hpop 1
62 20 cpop 1
63 21 stmt 0 "select t.name, t.idx from t2 t order ..."
64 drop procedure code_sample;
65 drop procedure if exists sudoku_solve;
66 create procedure sudoku_solve(p_naive boolean, p_all boolean)
67 deterministic
68 modifies sql data
69 begin
70 drop temporary table if exists sudoku_work, sudoku_schedule;
71 create temporary table sudoku_work
72 (
73 row smallint not null,
74 col smallint not null,
75 dig smallint not null,
76 cnt smallint,
77 key using btree (cnt),
78 key using btree (row),
79 key using btree (col),
80 unique key using hash (row,col)
81 );
82 create temporary table sudoku_schedule
83 (
84 idx int not null auto_increment primary key,
85 row smallint not null,
86 col smallint not null
87 );
88 call sudoku_init();
89 if p_naive then
90 update sudoku_work set cnt = 0 where dig = 0;
91 else
92 call sudoku_count();
93 end if;
94 insert into sudoku_schedule (row,col)
95 select row,col from sudoku_work where cnt is not null order by cnt desc;
96 begin
97 declare v_scounter bigint default 0;
98 declare v_i smallint default 1;
99 declare v_dig smallint;
100 declare v_schedmax smallint;
101 select count(*) into v_schedmax from sudoku_schedule;
102 more:
103 loop
104 begin
105 declare v_tcounter bigint default 0;
106 sched:
107 while v_i <= v_schedmax do
108 begin
109 declare v_row, v_col smallint;
110 select row,col into v_row,v_col from sudoku_schedule where v_i = idx;
111 select dig into v_dig from sudoku_work
112 where v_row = row and v_col = col;
113 case v_dig
114 when 0 then
115 set v_dig = 1;
116 update sudoku_work set dig = 1
117 where v_row = row and v_col = col;
118 when 9 then
119 if v_i > 0 then
120 update sudoku_work set dig = 0
121 where v_row = row and v_col = col;
122 set v_i = v_i - 1;
123 iterate sched;
124 else
125 select v_scounter as 'Solutions';
126 leave more;
127 end if;
128 else
129 set v_dig = v_dig + 1;
130 update sudoku_work set dig = v_dig
131 where v_row = row and v_col = col;
132 end case;
133 set v_tcounter = v_tcounter + 1;
134 if not sudoku_digit_ok(v_row, v_col, v_dig) then
135 iterate sched;
136 end if;
137 set v_i = v_i + 1;
138 end;
139 end while sched;
140 select dig from sudoku_work;
141 select v_tcounter as 'Tests';
142 set v_scounter = v_scounter + 1;
143 if p_all and v_i > 0 then
144 set v_i = v_i - 1;
145 else
146 leave more;
147 end if;
148 end;
149 end loop more;
150 end;
151 drop temporary table sudoku_work, sudoku_schedule;
152 end//
153 show procedure code sudoku_solve;
154 Pos Instruction
155 0 stmt 9 "drop temporary table if exists sudoku..."
156 1 stmt 1 "create temporary table sudoku_work ( ..."
157 2 stmt 1 "create temporary table sudoku_schedul..."
158 3 stmt 94 "call sudoku_init()"
159 4 jump_if_not 7(8) p_naive@0
160 5 stmt 4 "update sudoku_work set cnt = 0 where ..."
161 6 jump 8
162 7 stmt 94 "call sudoku_count()"
163 8 stmt 6 "insert into sudoku_schedule (row,col)..."
164 9 set v_scounter@2 0
165 10 set v_i@3 1
166 11 set v_dig@4 NULL
167 12 set v_schedmax@5 NULL
168 13 stmt 0 "select count(*) into v_schedmax from ..."
169 14 set v_tcounter@6 0
170 15 jump_if_not 39(39) (v_i@3 <= v_schedmax@5)
171 16 set v_row@7 NULL
172 17 set v_col@8 NULL
173 18 stmt 0 "select row,col into v_row,v_col from ..."
174 19 stmt 0 "select dig into v_dig from sudoku_wor..."
175 20 set_case_expr (34) 0 v_dig@4
176 21 jump_if_not 25(34) (case_expr@0 = 0)
177 22 set v_dig@4 1
178 23 stmt 4 "update sudoku_work set dig = 1 where ..."
179 24 jump 34
180 25 jump_if_not 32(34) (case_expr@0 = 9)
181 26 jump_if_not 30(34) (v_i@3 > 0)
182 27 stmt 4 "update sudoku_work set dig = 0 where ..."
183 28 set v_i@3 (v_i@3 - 1)
184 29 jump 15
185 30 stmt 0 "select v_scounter as 'Solutions'"
186 31 jump 45
187 32 set v_dig@4 (v_dig@4 + 1)
188 33 stmt 4 "update sudoku_work set dig = v_dig wh..."
189 34 set v_tcounter@6 (v_tcounter@6 + 1)
190 35 jump_if_not 37(37) (not(`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4)))
191 36 jump 15
192 37 set v_i@3 (v_i@3 + 1)
193 38 jump 15
194 39 stmt 0 "select dig from sudoku_work"
195 40 stmt 0 "select v_tcounter as 'Tests'"
196 41 set v_scounter@2 (v_scounter@2 + 1)
197 42 jump_if_not 45(14) (p_all@1 and (v_i@3 > 0))
198 43 set v_i@3 (v_i@3 - 1)
199 44 jump 14
200 45 stmt 9 "drop temporary table sudoku_work, sud..."
201 drop procedure sudoku_solve;
202 DROP PROCEDURE IF EXISTS proc_19194_simple;
203 DROP PROCEDURE IF EXISTS proc_19194_searched;
204 DROP PROCEDURE IF EXISTS proc_19194_nested_1;
205 DROP PROCEDURE IF EXISTS proc_19194_nested_2;
206 DROP PROCEDURE IF EXISTS proc_19194_nested_3;
207 DROP PROCEDURE IF EXISTS proc_19194_nested_4;
208 CREATE PROCEDURE proc_19194_simple(i int)
209 BEGIN
210 DECLARE str CHAR(10);
211 CASE i
212 WHEN 1 THEN SET str="1";
213 WHEN 2 THEN SET str="2";
214 WHEN 3 THEN SET str="3";
215 ELSE SET str="unknown";
216 END CASE;
217 SELECT str;
218 END|
219 CREATE PROCEDURE proc_19194_searched(i int)
220 BEGIN
221 DECLARE str CHAR(10);
222 CASE
223 WHEN i=1 THEN SET str="1";
224 WHEN i=2 THEN SET str="2";
225 WHEN i=3 THEN SET str="3";
226 ELSE SET str="unknown";
227 END CASE;
228 SELECT str;
229 END|
230 CREATE PROCEDURE proc_19194_nested_1(i int, j int)
231 BEGIN
232 DECLARE str_i CHAR(10);
233 DECLARE str_j CHAR(10);
234 CASE i
235 WHEN 10 THEN SET str_i="10";
236 WHEN 20 THEN
237 BEGIN
238 set str_i="20";
239 CASE
240 WHEN j=1 THEN SET str_j="1";
241 WHEN j=2 THEN SET str_j="2";
242 WHEN j=3 THEN SET str_j="3";
243 ELSE SET str_j="unknown";
244 END CASE;
245 select "i was 20";
246 END;
247 WHEN 30 THEN SET str_i="30";
248 WHEN 40 THEN SET str_i="40";
249 ELSE SET str_i="unknown";
250 END CASE;
251 SELECT str_i, str_j;
252 END|
253 CREATE PROCEDURE proc_19194_nested_2(i int, j int)
254 BEGIN
255 DECLARE str_i CHAR(10);
256 DECLARE str_j CHAR(10);
257 CASE
258 WHEN i=10 THEN SET str_i="10";
259 WHEN i=20 THEN
260 BEGIN
261 set str_i="20";
262 CASE j
263 WHEN 1 THEN SET str_j="1";
264 WHEN 2 THEN SET str_j="2";
265 WHEN 3 THEN SET str_j="3";
266 ELSE SET str_j="unknown";
267 END CASE;
268 select "i was 20";
269 END;
270 WHEN i=30 THEN SET str_i="30";
271 WHEN i=40 THEN SET str_i="40";
272 ELSE SET str_i="unknown";
273 END CASE;
274 SELECT str_i, str_j;
275 END|
276 CREATE PROCEDURE proc_19194_nested_3(i int, j int)
277 BEGIN
278 DECLARE str_i CHAR(10);
279 DECLARE str_j CHAR(10);
280 CASE i
281 WHEN 10 THEN SET str_i="10";
282 WHEN 20 THEN
283 BEGIN
284 set str_i="20";
285 CASE j
286 WHEN 1 THEN SET str_j="1";
287 WHEN 2 THEN SET str_j="2";
288 WHEN 3 THEN SET str_j="3";
289 ELSE SET str_j="unknown";
290 END CASE;
291 select "i was 20";
292 END;
293 WHEN 30 THEN SET str_i="30";
294 WHEN 40 THEN SET str_i="40";
295 ELSE SET str_i="unknown";
296 END CASE;
297 SELECT str_i, str_j;
298 END|
299 CREATE PROCEDURE proc_19194_nested_4(i int, j int)
300 BEGIN
301 DECLARE str_i CHAR(10);
302 DECLARE str_j CHAR(10);
303 CASE
304 WHEN i=10 THEN SET str_i="10";
305 WHEN i=20 THEN
306 BEGIN
307 set str_i="20";
308 CASE
309 WHEN j=1 THEN SET str_j="1";
310 WHEN j=2 THEN SET str_j="2";
311 WHEN j=3 THEN SET str_j="3";
312 ELSE SET str_j="unknown";
313 END CASE;
314 select "i was 20";
315 END;
316 WHEN i=30 THEN SET str_i="30";
317 WHEN i=40 THEN SET str_i="40";
318 ELSE SET str_i="unknown";
319 END CASE;
320 SELECT str_i, str_j;
321 END|
322 SHOW PROCEDURE CODE proc_19194_simple;
323 Pos Instruction
324 0 set str@1 NULL
325 1 set_case_expr (12) 0 i@0
326 2 jump_if_not 5(12) (case_expr@0 = 1)
327 3 set str@1 '1'
328 4 jump 12
329 5 jump_if_not 8(12) (case_expr@0 = 2)
330 6 set str@1 '2'
331 7 jump 12
332 8 jump_if_not 11(12) (case_expr@0 = 3)
333 9 set str@1 '3'
334 10 jump 12
335 11 set str@1 'unknown'
336 12 stmt 0 "SELECT str"
337 SHOW PROCEDURE CODE proc_19194_searched;
338 Pos Instruction
339 0 set str@1 NULL
340 1 jump_if_not 4(11) (i@0 = 1)
341 2 set str@1 '1'
342 3 jump 11
343 4 jump_if_not 7(11) (i@0 = 2)
344 5 set str@1 '2'
345 6 jump 11
346 7 jump_if_not 10(11) (i@0 = 3)
347 8 set str@1 '3'
348 9 jump 11
349 10 set str@1 'unknown'
350 11 stmt 0 "SELECT str"
351 SHOW PROCEDURE CODE proc_19194_nested_1;
352 Pos Instruction
353 0 set str_i@2 NULL
354 1 set str_j@3 NULL
355 2 set_case_expr (27) 0 i@0
356 3 jump_if_not 6(27) (case_expr@0 = 10)
357 4 set str_i@2 '10'
358 5 jump 27
359 6 jump_if_not 20(27) (case_expr@0 = 20)
360 7 set str_i@2 '20'
361 8 jump_if_not 11(18) (j@1 = 1)
362 9 set str_j@3 '1'
363 10 jump 18
364 11 jump_if_not 14(18) (j@1 = 2)
365 12 set str_j@3 '2'
366 13 jump 18
367 14 jump_if_not 17(18) (j@1 = 3)
368 15 set str_j@3 '3'
369 16 jump 18
370 17 set str_j@3 'unknown'
371 18 stmt 0 "select "i was 20""
372 19 jump 27
373 20 jump_if_not 23(27) (case_expr@0 = 30)
374 21 set str_i@2 '30'
375 22 jump 27
376 23 jump_if_not 26(27) (case_expr@0 = 40)
377 24 set str_i@2 '40'
378 25 jump 27
379 26 set str_i@2 'unknown'
380 27 stmt 0 "SELECT str_i, str_j"
381 SHOW PROCEDURE CODE proc_19194_nested_2;
382 Pos Instruction
383 0 set str_i@2 NULL
384 1 set str_j@3 NULL
385 2 jump_if_not 5(27) (i@0 = 10)
386 3 set str_i@2 '10'
387 4 jump 27
388 5 jump_if_not 20(27) (i@0 = 20)
389 6 set str_i@2 '20'
390 7 set_case_expr (18) 0 j@1
391 8 jump_if_not 11(18) (case_expr@0 = 1)
392 9 set str_j@3 '1'
393 10 jump 18
394 11 jump_if_not 14(18) (case_expr@0 = 2)
395 12 set str_j@3 '2'
396 13 jump 18
397 14 jump_if_not 17(18) (case_expr@0 = 3)
398 15 set str_j@3 '3'
399 16 jump 18
400 17 set str_j@3 'unknown'
401 18 stmt 0 "select "i was 20""
402 19 jump 27
403 20 jump_if_not 23(27) (i@0 = 30)
404 21 set str_i@2 '30'
405 22 jump 27
406 23 jump_if_not 26(27) (i@0 = 40)
407 24 set str_i@2 '40'
408 25 jump 27
409 26 set str_i@2 'unknown'
410 27 stmt 0 "SELECT str_i, str_j"
411 SHOW PROCEDURE CODE proc_19194_nested_3;
412 Pos Instruction
413 0 set str_i@2 NULL
414 1 set str_j@3 NULL
415 2 set_case_expr (28) 0 i@0
416 3 jump_if_not 6(28) (case_expr@0 = 10)
417 4 set str_i@2 '10'
418 5 jump 28
419 6 jump_if_not 21(28) (case_expr@0 = 20)
420 7 set str_i@2 '20'
421 8 set_case_expr (19) 1 j@1
422 9 jump_if_not 12(19) (case_expr@1 = 1)
423 10 set str_j@3 '1'
424 11 jump 19
425 12 jump_if_not 15(19) (case_expr@1 = 2)
426 13 set str_j@3 '2'
427 14 jump 19
428 15 jump_if_not 18(19) (case_expr@1 = 3)
429 16 set str_j@3 '3'
430 17 jump 19
431 18 set str_j@3 'unknown'
432 19 stmt 0 "select "i was 20""
433 20 jump 28
434 21 jump_if_not 24(28) (case_expr@0 = 30)
435 22 set str_i@2 '30'
436 23 jump 28
437 24 jump_if_not 27(28) (case_expr@0 = 40)
438 25 set str_i@2 '40'
439 26 jump 28
440 27 set str_i@2 'unknown'
441 28 stmt 0 "SELECT str_i, str_j"
442 SHOW PROCEDURE CODE proc_19194_nested_4;
443 Pos Instruction
444 0 set str_i@2 NULL
445 1 set str_j@3 NULL
446 2 jump_if_not 5(26) (i@0 = 10)
447 3 set str_i@2 '10'
448 4 jump 26
449 5 jump_if_not 19(26) (i@0 = 20)
450 6 set str_i@2 '20'
451 7 jump_if_not 10(17) (j@1 = 1)
452 8 set str_j@3 '1'
453 9 jump 17
454 10 jump_if_not 13(17) (j@1 = 2)
455 11 set str_j@3 '2'
456 12 jump 17
457 13 jump_if_not 16(17) (j@1 = 3)
458 14 set str_j@3 '3'
459 15 jump 17
460 16 set str_j@3 'unknown'
461 17 stmt 0 "select "i was 20""
462 18 jump 26
463 19 jump_if_not 22(26) (i@0 = 30)
464 20 set str_i@2 '30'
465 21 jump 26
466 22 jump_if_not 25(26) (i@0 = 40)
467 23 set str_i@2 '40'
468 24 jump 26
469 25 set str_i@2 'unknown'
470 26 stmt 0 "SELECT str_i, str_j"
471 CALL proc_19194_nested_1(10, 1);
472 str_i str_j
473 10 NULL
474 CALL proc_19194_nested_1(25, 1);
475 str_i str_j
476 unknown NULL
477 CALL proc_19194_nested_1(20, 1);
478 i was 20
479 i was 20
480 str_i str_j
481 20 1
482 CALL proc_19194_nested_1(20, 2);
483 i was 20
484 i was 20
485 str_i str_j
486 20 2
487 CALL proc_19194_nested_1(20, 3);
488 i was 20
489 i was 20
490 str_i str_j
491 20 3
492 CALL proc_19194_nested_1(20, 4);
493 i was 20
494 i was 20
495 str_i str_j
496 20 unknown
497 CALL proc_19194_nested_1(30, 1);
498 str_i str_j
499 30 NULL
500 CALL proc_19194_nested_1(40, 1);
501 str_i str_j
502 40 NULL
503 CALL proc_19194_nested_1(0, 0);
504 str_i str_j
505 unknown NULL
506 CALL proc_19194_nested_2(10, 1);
507 str_i str_j
508 10 NULL
509 CALL proc_19194_nested_2(25, 1);
510 str_i str_j
511 unknown NULL
512 CALL proc_19194_nested_2(20, 1);
513 i was 20
514 i was 20
515 str_i str_j
516 20 1
517 CALL proc_19194_nested_2(20, 2);
518 i was 20
519 i was 20
520 str_i str_j
521 20 2
522 CALL proc_19194_nested_2(20, 3);
523 i was 20
524 i was 20
525 str_i str_j
526 20 3
527 CALL proc_19194_nested_2(20, 4);
528 i was 20
529 i was 20
530 str_i str_j
531 20 unknown
532 CALL proc_19194_nested_2(30, 1);
533 str_i str_j
534 30 NULL
535 CALL proc_19194_nested_2(40, 1);
536 str_i str_j
537 40 NULL
538 CALL proc_19194_nested_2(0, 0);
539 str_i str_j
540 unknown NULL
541 CALL proc_19194_nested_3(10, 1);
542 str_i str_j
543 10 NULL
544 CALL proc_19194_nested_3(25, 1);
545 str_i str_j
546 unknown NULL
547 CALL proc_19194_nested_3(20, 1);
548 i was 20
549 i was 20
550 str_i str_j
551 20 1
552 CALL proc_19194_nested_3(20, 2);
553 i was 20
554 i was 20
555 str_i str_j
556 20 2
557 CALL proc_19194_nested_3(20, 3);
558 i was 20
559 i was 20
560 str_i str_j
561 20 3
562 CALL proc_19194_nested_3(20, 4);
563 i was 20
564 i was 20
565 str_i str_j
566 20 unknown
567 CALL proc_19194_nested_3(30, 1);
568 str_i str_j
569 30 NULL
570 CALL proc_19194_nested_3(40, 1);
571 str_i str_j
572 40 NULL
573 CALL proc_19194_nested_3(0, 0);
574 str_i str_j
575 unknown NULL
576 CALL proc_19194_nested_4(10, 1);
577 str_i str_j
578 10 NULL
579 CALL proc_19194_nested_4(25, 1);
580 str_i str_j
581 unknown NULL
582 CALL proc_19194_nested_4(20, 1);
583 i was 20
584 i was 20
585 str_i str_j
586 20 1
587 CALL proc_19194_nested_4(20, 2);
588 i was 20
589 i was 20
590 str_i str_j
591 20 2
592 CALL proc_19194_nested_4(20, 3);
593 i was 20
594 i was 20
595 str_i str_j
596 20 3
597 CALL proc_19194_nested_4(20, 4);
598 i was 20
599 i was 20
600 str_i str_j
601 20 unknown
602 CALL proc_19194_nested_4(30, 1);
603 str_i str_j
604 30 NULL
605 CALL proc_19194_nested_4(40, 1);
606 str_i str_j
607 40 NULL
608 CALL proc_19194_nested_4(0, 0);
609 str_i str_j
610 unknown NULL
611 DROP PROCEDURE proc_19194_simple;
612 DROP PROCEDURE proc_19194_searched;
613 DROP PROCEDURE proc_19194_nested_1;
614 DROP PROCEDURE proc_19194_nested_2;
615 DROP PROCEDURE proc_19194_nested_3;
616 DROP PROCEDURE proc_19194_nested_4;
617 DROP PROCEDURE IF EXISTS p1;
618 CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1);
619 SHOW PROCEDURE CODE p1;
620 Pos Instruction
621 0 stmt 2 "CREATE INDEX idx ON t1 (c1)"
622 DROP PROCEDURE p1;
623 drop table if exists t1;
624 drop procedure if exists proc_26977_broken;
625 drop procedure if exists proc_26977_works;
626 create table t1(a int unique);
627 create procedure proc_26977_broken(v int)
628 begin
629 declare i int default 5;
630 declare continue handler for sqlexception
631 begin
632 select 'caught something';
633 retry:
634 while i > 0 do
635 begin
636 set i = i - 1;
637 select 'looping', i;
638 end;
639 end while retry;
640 end;
641 select 'do something';
642 insert into t1 values (v);
643 select 'do something again';
644 insert into t1 values (v);
645 end//
646 create procedure proc_26977_works(v int)
647 begin
648 declare i int default 5;
649 declare continue handler for sqlexception
650 begin
651 select 'caught something';
652 retry:
653 while i > 0 do
654 begin
655 set i = i - 1;
656 select 'looping', i;
657 end;
658 end while retry;
659 select 'optimizer: keep hreturn';
660 end;
661 select 'do something';
662 insert into t1 values (v);
663 select 'do something again';
664 insert into t1 values (v);
665 end//
666 show procedure code proc_26977_broken;
667 Pos Instruction
668 0 set i@1 5
669 1 hpush_jump 8 2 CONTINUE
670 2 stmt 0 "select 'caught something'"
671 3 jump_if_not 7(7) (i@1 > 0)
672 4 set i@1 (i@1 - 1)
673 5 stmt 0 "select 'looping', i"
674 6 jump 3
675 7 hreturn 2
676 8 stmt 0 "select 'do something'"
677 9 stmt 5 "insert into t1 values (v)"
678 10 stmt 0 "select 'do something again'"
679 11 stmt 5 "insert into t1 values (v)"
680 12 hpop 1
681 show procedure code proc_26977_works;
682 Pos Instruction
683 0 set i@1 5
684 1 hpush_jump 9 2 CONTINUE
685 2 stmt 0 "select 'caught something'"
686 3 jump_if_not 7(7) (i@1 > 0)
687 4 set i@1 (i@1 - 1)
688 5 stmt 0 "select 'looping', i"
689 6 jump 3
690 7 stmt 0 "select 'optimizer: keep hreturn'"
691 8 hreturn 2
692 9 stmt 0 "select 'do something'"
693 10 stmt 5 "insert into t1 values (v)"
694 11 stmt 0 "select 'do something again'"
695 12 stmt 5 "insert into t1 values (v)"
696 13 hpop 1
697 call proc_26977_broken(1);
698 do something
699 do something
700 do something again
701 do something again
702 caught something
703 caught something
704 looping i
705 looping 4
706 looping i
707 looping 3
708 looping i
709 looping 2
710 looping i
711 looping 1
712 looping i
713 looping 0
714 call proc_26977_works(2);
715 do something
716 do something
717 do something again
718 do something again
719 caught something
720 caught something
721 looping i
722 looping 4
723 looping i
724 looping 3
725 looping i
726 looping 2
727 looping i
728 looping 1
729 looping i
730 looping 0
731 optimizer: keep hreturn
732 optimizer: keep hreturn
733 drop table t1;
734 drop procedure proc_26977_broken;
735 drop procedure proc_26977_works;
736 drop procedure if exists proc_33618_h;
737 drop procedure if exists proc_33618_c;
738 create procedure proc_33618_h(num int)
739 begin
740 declare count1 int default '0';
741 declare vb varchar(30);
742 declare last_row int;
743 while(num>=1) do
744 set num=num-1;
745 begin
746 declare cur1 cursor for select `a` from t_33618;
747 declare continue handler for not found set last_row = 1;
748 set last_row:=0;
749 open cur1;
750 rep1:
751 repeat
752 begin
753 declare exit handler for 1062 begin end;
754 fetch cur1 into vb;
755 if (last_row = 1) then
756 ## should generate a hpop instruction here
757 leave rep1;
758 end if;
759 end;
760 until last_row=1
761 end repeat;
762 close cur1;
763 end;
764 end while;
765 end//
766 create procedure proc_33618_c(num int)
767 begin
768 declare count1 int default '0';
769 declare vb varchar(30);
770 declare last_row int;
771 while(num>=1) do
772 set num=num-1;
773 begin
774 declare cur1 cursor for select `a` from t_33618;
775 declare continue handler for not found set last_row = 1;
776 set last_row:=0;
777 open cur1;
778 rep1:
779 repeat
780 begin
781 declare cur2 cursor for select `b` from t_33618;
782 fetch cur1 into vb;
783 if (last_row = 1) then
784 ## should generate a cpop instruction here
785 leave rep1;
786 end if;
787 end;
788 until last_row=1
789 end repeat;
790 close cur1;
791 end;
792 end while;
793 end//
794 show procedure code proc_33618_h;
795 Pos Instruction
796 0 set count1@1 '0'
797 1 set vb@2 NULL
798 2 set last_row@3 NULL
799 3 jump_if_not 24(24) (num@0 >= 1)
800 4 set num@0 (num@0 - 1)
801 5 cpush cur1@0
802 6 hpush_jump 9 4 CONTINUE
803 7 set last_row@3 1
804 8 hreturn 4
805 9 set last_row@3 0
806 10 copen cur1@0
807 11 hpush_jump 13 4 EXIT
808 12 hreturn 0 17
809 13 cfetch cur1@0 vb@2
810 14 jump_if_not 17(17) (last_row@3 = 1)
811 15 hpop 1
812 16 jump 19
813 17 hpop 1
814 18 jump_if_not 11(19) (last_row@3 = 1)
815 19 cclose cur1@0
816 20 hpop 1
817 21 cpop 1
818 22 jump 3
819 show procedure code proc_33618_c;
820 Pos Instruction
821 0 set count1@1 '0'
822 1 set vb@2 NULL
823 2 set last_row@3 NULL
824 3 jump_if_not 23(23) (num@0 >= 1)
825 4 set num@0 (num@0 - 1)
826 5 cpush cur1@0
827 6 hpush_jump 9 4 CONTINUE
828 7 set last_row@3 1
829 8 hreturn 4
830 9 set last_row@3 0
831 10 copen cur1@0
832 11 cpush cur2@1
833 12 cfetch cur1@0 vb@2
834 13 jump_if_not 16(16) (last_row@3 = 1)
835 14 cpop 1
836 15 jump 18
837 16 cpop 1
838 17 jump_if_not 11(18) (last_row@3 = 1)
839 18 cclose cur1@0
840 19 hpop 1
841 20 cpop 1
842 21 jump 3
843 drop procedure proc_33618_h;
844 drop procedure proc_33618_c;
845 drop procedure if exists p_20906_a;
846 drop procedure if exists p_20906_b;
847 create procedure p_20906_a() SET @a=@a+1, @b=@b+1;
848 show procedure code p_20906_a;
849 Pos Instruction
850 0 stmt 31 "SET @a=@a+1"
851 1 stmt 31 "SET @b=@b+1"
852 set @a=1;
853 set @b=1;
854 call p_20906_a();
855 select @a, @b;
856 @a @b
857 2 2
858 create procedure p_20906_b() SET @a=@a+1, @b=@b+1, @c=@c+1;
859 show procedure code p_20906_b;
860 Pos Instruction
861 0 stmt 31 "SET @a=@a+1"
862 1 stmt 31 "SET @b=@b+1"
863 2 stmt 31 "SET @c=@c+1"
864 set @a=1;
865 set @b=1;
866 set @c=1;
867 call p_20906_b();
868 select @a, @b, @c;
869 @a @b @c
870 2 2 2
871 drop procedure p_20906_a;
872 drop procedure p_20906_b;
873 End of 5.0 tests.
874 CREATE PROCEDURE p1()
875 BEGIN
876 DECLARE dummy int default 0;
877 CASE 12
878 WHEN 12
879 THEN SET dummy = 0;
880 END CASE;
881 END//
882 SHOW PROCEDURE CODE p1;
883 Pos Instruction
884 0 set dummy@0 0
885 1 set_case_expr (6) 0 12
886 2 jump_if_not 5(6) (case_expr@0 = 12)
887 3 set dummy@0 0
888 4 jump 6
889 5 error 1339
890 DROP PROCEDURE p1;
891 #
892 # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE
893 #
894 SET @@SQL_MODE = '';
895 CREATE FUNCTION testf_bug11763507() RETURNS INT
896 BEGIN
897 RETURN 0;
898 END
899 $
900 CREATE PROCEDURE testp_bug11763507()
901 BEGIN
902 SELECT "PROCEDURE testp_bug11763507";
903 END
904 $
905 SHOW FUNCTION CODE testf_bug11763507;
906 Pos Instruction
907 0 freturn 3 0
908 SHOW FUNCTION CODE TESTF_bug11763507;
909 Pos Instruction
910 0 freturn 3 0
911 SHOW PROCEDURE CODE testp_bug11763507;
912 Pos Instruction
913 0 stmt 0 "SELECT "PROCEDURE testp_bug11763507""
914 SHOW PROCEDURE CODE TESTP_bug11763507;
915 Pos Instruction
916 0 stmt 0 "SELECT "PROCEDURE testp_bug11763507""
917 DROP PROCEDURE testp_bug11763507;
918 DROP FUNCTION testf_bug11763507;
919 #END OF BUG#11763507 test.