1# 2# Test the debugging feature "show procedure/function code <name>" 3# 4 5-- source include/have_debug.inc 6 7--disable_warnings 8drop procedure if exists empty; 9drop procedure if exists code_sample; 10--enable_warnings 11 12create procedure empty() 13begin 14end; 15show procedure code empty; 16drop procedure empty; 17 18create function almost_empty() 19 returns int 20 return 0; 21show function code almost_empty; 22drop function almost_empty; 23 24delimiter //; 25create procedure code_sample(x int, out err int, out nulls int) 26begin 27 declare count int default 0; 28 29 set nulls = 0; 30 begin 31 declare c cursor for select name from t1; 32 declare exit handler for not found close c; 33 34 open c; 35 loop 36 begin 37 declare n varchar(20); 38 declare continue handler for sqlexception set err=1; 39 40 fetch c into n; 41 if isnull(n) then 42 set nulls = nulls + 1; 43 else 44 set count = count + 1; 45 update t2 set idx = count where name=n; 46 end if; 47 end; 48 end loop; 49 end; 50 select t.name, t.idx from t2 t order by idx asc; 51end// 52delimiter ;// 53show procedure code code_sample; 54drop procedure code_sample; 55 56 57# 58# BUG#15737: Stored procedure optimizer bug with LEAVE 59# 60# This is a much more extensive test case than is strictly needed, 61# but it was kept as is for two reasons: 62# - The bug occurs under some quite special circumstances, so it 63# wasn't trivial to create a smaller test, 64# - There's some value in having another more complex code sample 65# in this test file. This might catch future code generation bugs 66# that doesn't show in behaviour in any obvious way. 67 68--disable_warnings 69drop procedure if exists sudoku_solve; 70--enable_warnings 71 72delimiter //; 73create procedure sudoku_solve(p_naive boolean, p_all boolean) 74 deterministic 75 modifies sql data 76begin 77 drop temporary table if exists sudoku_work, sudoku_schedule; 78 79 create temporary table sudoku_work 80 ( 81 row smallint not null, 82 col smallint not null, 83 dig smallint not null, 84 cnt smallint, 85 key using btree (cnt), 86 key using btree (row), 87 key using btree (col), 88 unique key using hash (row,col) 89 ); 90 91 create temporary table sudoku_schedule 92 ( 93 idx int not null auto_increment primary key, 94 row smallint not null, 95 col smallint not null 96 ); 97 98 call sudoku_init(); 99 100 if p_naive then 101 update sudoku_work set cnt = 0 where dig = 0; 102 else 103 call sudoku_count(); 104 end if; 105 insert into sudoku_schedule (row,col) 106 select row,col from sudoku_work where cnt is not null order by cnt desc; 107 108 begin 109 declare v_scounter bigint default 0; 110 declare v_i smallint default 1; 111 declare v_dig smallint; 112 declare v_schedmax smallint; 113 114 select count(*) into v_schedmax from sudoku_schedule; 115 116 more: 117 loop 118 begin 119 declare v_tcounter bigint default 0; 120 121 sched: 122 while v_i <= v_schedmax do 123 begin 124 declare v_row, v_col smallint; 125 126 select row,col into v_row,v_col from sudoku_schedule where v_i = idx; 127 128 select dig into v_dig from sudoku_work 129 where v_row = row and v_col = col; 130 131 case v_dig 132 when 0 then 133 set v_dig = 1; 134 update sudoku_work set dig = 1 135 where v_row = row and v_col = col; 136 when 9 then 137 if v_i > 0 then 138 update sudoku_work set dig = 0 139 where v_row = row and v_col = col; 140 set v_i = v_i - 1; 141 iterate sched; 142 else 143 select v_scounter as 'Solutions'; 144 leave more; 145 end if; 146 else 147 set v_dig = v_dig + 1; 148 update sudoku_work set dig = v_dig 149 where v_row = row and v_col = col; 150 end case; 151 152 set v_tcounter = v_tcounter + 1; 153 if not sudoku_digit_ok(v_row, v_col, v_dig) then 154 iterate sched; 155 end if; 156 set v_i = v_i + 1; 157 end; 158 end while sched; 159 160 select dig from sudoku_work; 161 select v_tcounter as 'Tests'; 162 set v_scounter = v_scounter + 1; 163 164 if p_all and v_i > 0 then 165 set v_i = v_i - 1; 166 else 167 leave more; 168 end if; 169 end; 170 end loop more; 171 end; 172 173 drop temporary table sudoku_work, sudoku_schedule; 174end// 175delimiter ;// 176 177# The interestings parts are where the code for the two "leave" are: 178# ... 179#| 26 | jump_if_not 30 (v_i@3 > 0) | 180# ... 181#| 30 | stmt 0 "select v_scounter as 'Solutions'" | 182#| 31 | jump 45 | 183# ... 184#| 42 | jump_if_not 45 (p_all@1 and (v_i@3 > 0)) | 185#| 43 | set v_i@3 (v_i@3 - 1) | 186#| 44 | jump 14 | 187#| 45 | stmt 9 "drop temporary table sudoku_work, sud..." | 188#+-----+-----------------------------------------------------------------------+ 189# The bug appeared at position 42 (with the wrong destination). 190show procedure code sudoku_solve; 191 192drop procedure sudoku_solve; 193 194# 195# Bug#19194 (Right recursion in parser for CASE causes excessive stack 196# usage, limitation) 197# This bug also exposed a flaw in the generated code with nested case 198# statements 199# 200 201--disable_warnings 202DROP PROCEDURE IF EXISTS proc_19194_simple; 203DROP PROCEDURE IF EXISTS proc_19194_searched; 204DROP PROCEDURE IF EXISTS proc_19194_nested_1; 205DROP PROCEDURE IF EXISTS proc_19194_nested_2; 206DROP PROCEDURE IF EXISTS proc_19194_nested_3; 207DROP PROCEDURE IF EXISTS proc_19194_nested_4; 208--enable_warnings 209 210delimiter |; 211 212CREATE PROCEDURE proc_19194_simple(i int) 213BEGIN 214 DECLARE str CHAR(10); 215 216 CASE i 217 WHEN 1 THEN SET str="1"; 218 WHEN 2 THEN SET str="2"; 219 WHEN 3 THEN SET str="3"; 220 ELSE SET str="unknown"; 221 END CASE; 222 223 SELECT str; 224END| 225 226CREATE PROCEDURE proc_19194_searched(i int) 227BEGIN 228 DECLARE str CHAR(10); 229 230 CASE 231 WHEN i=1 THEN SET str="1"; 232 WHEN i=2 THEN SET str="2"; 233 WHEN i=3 THEN SET str="3"; 234 ELSE SET str="unknown"; 235 END CASE; 236 237 SELECT str; 238END| 239 240# Outer SIMPLE case, inner SEARCHED case 241CREATE PROCEDURE proc_19194_nested_1(i int, j int) 242BEGIN 243 DECLARE str_i CHAR(10); 244 DECLARE str_j CHAR(10); 245 246 CASE i 247 WHEN 10 THEN SET str_i="10"; 248 WHEN 20 THEN 249 BEGIN 250 set str_i="20"; 251 CASE 252 WHEN j=1 THEN SET str_j="1"; 253 WHEN j=2 THEN SET str_j="2"; 254 WHEN j=3 THEN SET str_j="3"; 255 ELSE SET str_j="unknown"; 256 END CASE; 257 select "i was 20"; 258 END; 259 WHEN 30 THEN SET str_i="30"; 260 WHEN 40 THEN SET str_i="40"; 261 ELSE SET str_i="unknown"; 262 END CASE; 263 264 SELECT str_i, str_j; 265END| 266 267# Outer SEARCHED case, inner SIMPLE case 268CREATE PROCEDURE proc_19194_nested_2(i int, j int) 269BEGIN 270 DECLARE str_i CHAR(10); 271 DECLARE str_j CHAR(10); 272 273 CASE 274 WHEN i=10 THEN SET str_i="10"; 275 WHEN i=20 THEN 276 BEGIN 277 set str_i="20"; 278 CASE j 279 WHEN 1 THEN SET str_j="1"; 280 WHEN 2 THEN SET str_j="2"; 281 WHEN 3 THEN SET str_j="3"; 282 ELSE SET str_j="unknown"; 283 END CASE; 284 select "i was 20"; 285 END; 286 WHEN i=30 THEN SET str_i="30"; 287 WHEN i=40 THEN SET str_i="40"; 288 ELSE SET str_i="unknown"; 289 END CASE; 290 291 SELECT str_i, str_j; 292END| 293 294# Outer SIMPLE case, inner SIMPLE case 295CREATE PROCEDURE proc_19194_nested_3(i int, j int) 296BEGIN 297 DECLARE str_i CHAR(10); 298 DECLARE str_j CHAR(10); 299 300 CASE i 301 WHEN 10 THEN SET str_i="10"; 302 WHEN 20 THEN 303 BEGIN 304 set str_i="20"; 305 CASE j 306 WHEN 1 THEN SET str_j="1"; 307 WHEN 2 THEN SET str_j="2"; 308 WHEN 3 THEN SET str_j="3"; 309 ELSE SET str_j="unknown"; 310 END CASE; 311 select "i was 20"; 312 END; 313 WHEN 30 THEN SET str_i="30"; 314 WHEN 40 THEN SET str_i="40"; 315 ELSE SET str_i="unknown"; 316 END CASE; 317 318 SELECT str_i, str_j; 319END| 320 321# Outer SEARCHED case, inner SEARCHED case 322CREATE PROCEDURE proc_19194_nested_4(i int, j int) 323BEGIN 324 DECLARE str_i CHAR(10); 325 DECLARE str_j CHAR(10); 326 327 CASE 328 WHEN i=10 THEN SET str_i="10"; 329 WHEN i=20 THEN 330 BEGIN 331 set str_i="20"; 332 CASE 333 WHEN j=1 THEN SET str_j="1"; 334 WHEN j=2 THEN SET str_j="2"; 335 WHEN j=3 THEN SET str_j="3"; 336 ELSE SET str_j="unknown"; 337 END CASE; 338 select "i was 20"; 339 END; 340 WHEN i=30 THEN SET str_i="30"; 341 WHEN i=40 THEN SET str_i="40"; 342 ELSE SET str_i="unknown"; 343 END CASE; 344 345 SELECT str_i, str_j; 346END| 347 348delimiter ;| 349 350SHOW PROCEDURE CODE proc_19194_simple; 351SHOW PROCEDURE CODE proc_19194_searched; 352SHOW PROCEDURE CODE proc_19194_nested_1; 353SHOW PROCEDURE CODE proc_19194_nested_2; 354SHOW PROCEDURE CODE proc_19194_nested_3; 355SHOW PROCEDURE CODE proc_19194_nested_4; 356 357CALL proc_19194_nested_1(10, 1); 358 359# 360# Before 19194, the generated code was: 361# 20 jump_if_not 23(27) 30 362# 21 set str_i@2 _latin1'30' 363# As opposed to the expected: 364# 20 jump_if_not 23(27) (case_expr@0 = 30) 365# 21 set str_i@2 _latin1'30' 366# 367# and as a result, this call returned "30", 368# because the expression 30 is always true, 369# masking the case 40, case 0 and the else. 370# 371CALL proc_19194_nested_1(25, 1); 372 373CALL proc_19194_nested_1(20, 1); 374CALL proc_19194_nested_1(20, 2); 375CALL proc_19194_nested_1(20, 3); 376CALL proc_19194_nested_1(20, 4); 377CALL proc_19194_nested_1(30, 1); 378CALL proc_19194_nested_1(40, 1); 379CALL proc_19194_nested_1(0, 0); 380 381CALL proc_19194_nested_2(10, 1); 382 383# 384# Before 19194, the generated code was: 385# 20 jump_if_not 23(27) (case_expr@0 = (i@0 = 30)) 386# 21 set str_i@2 _latin1'30' 387# As opposed to the expected: 388# 20 jump_if_not 23(27) (i@0 = 30) 389# 21 set str_i@2 _latin1'30' 390# and as a result, this call crashed the server, because there is no 391# such variable as "case_expr@0". 392# 393CALL proc_19194_nested_2(25, 1); 394 395CALL proc_19194_nested_2(20, 1); 396CALL proc_19194_nested_2(20, 2); 397CALL proc_19194_nested_2(20, 3); 398CALL proc_19194_nested_2(20, 4); 399CALL proc_19194_nested_2(30, 1); 400CALL proc_19194_nested_2(40, 1); 401CALL proc_19194_nested_2(0, 0); 402 403CALL proc_19194_nested_3(10, 1); 404CALL proc_19194_nested_3(25, 1); 405CALL proc_19194_nested_3(20, 1); 406CALL proc_19194_nested_3(20, 2); 407CALL proc_19194_nested_3(20, 3); 408CALL proc_19194_nested_3(20, 4); 409CALL proc_19194_nested_3(30, 1); 410CALL proc_19194_nested_3(40, 1); 411CALL proc_19194_nested_3(0, 0); 412 413CALL proc_19194_nested_4(10, 1); 414CALL proc_19194_nested_4(25, 1); 415CALL proc_19194_nested_4(20, 1); 416CALL proc_19194_nested_4(20, 2); 417CALL proc_19194_nested_4(20, 3); 418CALL proc_19194_nested_4(20, 4); 419CALL proc_19194_nested_4(30, 1); 420CALL proc_19194_nested_4(40, 1); 421CALL proc_19194_nested_4(0, 0); 422 423DROP PROCEDURE proc_19194_simple; 424DROP PROCEDURE proc_19194_searched; 425DROP PROCEDURE proc_19194_nested_1; 426DROP PROCEDURE proc_19194_nested_2; 427DROP PROCEDURE proc_19194_nested_3; 428DROP PROCEDURE proc_19194_nested_4; 429 430# 431# Bug#19207: Final parenthesis omitted for CREATE INDEX in Stored 432# Procedure 433# 434# Wrong criteria was used to distinguish the case when there was no 435# lookahead performed in the parser. Bug affected only statements 436# ending in one-character token without any optional tail, like CREATE 437# INDEX and CALL. 438# 439--disable_warnings 440DROP PROCEDURE IF EXISTS p1; 441--enable_warnings 442 443CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1); 444SHOW PROCEDURE CODE p1; 445 446DROP PROCEDURE p1; 447 448 449# 450# Bug#26977 exception handlers never hreturn 451# 452--disable_warnings 453drop table if exists t1; 454drop procedure if exists proc_26977_broken; 455drop procedure if exists proc_26977_works; 456--enable_warnings 457 458create table t1(a int unique); 459 460delimiter //; 461 462create procedure proc_26977_broken(v int) 463begin 464 declare i int default 5; 465 466 declare continue handler for sqlexception 467 begin 468 select 'caught something'; 469 retry: 470 while i > 0 do 471 begin 472 set i = i - 1; 473 select 'looping', i; 474 end; 475 end while retry; 476 end; 477 478 select 'do something'; 479 insert into t1 values (v); 480 select 'do something again'; 481 insert into t1 values (v); 482end// 483 484create procedure proc_26977_works(v int) 485begin 486 declare i int default 5; 487 488 declare continue handler for sqlexception 489 begin 490 select 'caught something'; 491 retry: 492 while i > 0 do 493 begin 494 set i = i - 1; 495 select 'looping', i; 496 end; 497 end while retry; 498 select 'optimizer: keep hreturn'; 499 end; 500 501 select 'do something'; 502 insert into t1 values (v); 503 select 'do something again'; 504 insert into t1 values (v); 505end// 506delimiter ;// 507 508show procedure code proc_26977_broken; 509 510show procedure code proc_26977_works; 511 512## This caust an error because of jump short cut 513## optimization. 514call proc_26977_broken(1); 515 516## This works 517call proc_26977_works(2); 518 519drop table t1; 520drop procedure proc_26977_broken; 521drop procedure proc_26977_works; 522 523# 524# Bug#33618 Crash in sp_rcontext 525# 526 527--disable_warnings 528drop procedure if exists proc_33618_h; 529drop procedure if exists proc_33618_c; 530--enable_warnings 531 532delimiter //; 533 534create procedure proc_33618_h(num int) 535begin 536 declare count1 int default '0'; 537 declare vb varchar(30); 538 declare last_row int; 539 540 while(num>=1) do 541 set num=num-1; 542 begin 543 declare cur1 cursor for select `a` from t_33618; 544 declare continue handler for not found set last_row = 1; 545 set last_row:=0; 546 open cur1; 547 rep1: 548 repeat 549 begin 550 declare exit handler for 1062 begin end; 551 fetch cur1 into vb; 552 if (last_row = 1) then 553 ## should generate a hpop instruction here 554 leave rep1; 555 end if; 556 end; 557 until last_row=1 558 end repeat; 559 close cur1; 560 end; 561 end while; 562end// 563 564create procedure proc_33618_c(num int) 565begin 566 declare count1 int default '0'; 567 declare vb varchar(30); 568 declare last_row int; 569 570 while(num>=1) do 571 set num=num-1; 572 begin 573 declare cur1 cursor for select `a` from t_33618; 574 declare continue handler for not found set last_row = 1; 575 set last_row:=0; 576 open cur1; 577 rep1: 578 repeat 579 begin 580 declare cur2 cursor for select `b` from t_33618; 581 fetch cur1 into vb; 582 if (last_row = 1) then 583 ## should generate a cpop instruction here 584 leave rep1; 585 end if; 586 end; 587 until last_row=1 588 end repeat; 589 close cur1; 590 end; 591 end while; 592end// 593delimiter ;// 594 595show procedure code proc_33618_h; 596show procedure code proc_33618_c; 597 598drop procedure proc_33618_h; 599drop procedure proc_33618_c; 600 601# 602# Bug#20906 (Multiple assignments in SET in stored routine produce incorrect 603# instructions) 604# 605 606--disable_warnings 607drop procedure if exists p_20906_a; 608drop procedure if exists p_20906_b; 609--enable_warnings 610 611create procedure p_20906_a() SET @a=@a+1, @b=@b+1; 612show procedure code p_20906_a; 613 614set @a=1; 615set @b=1; 616 617call p_20906_a(); 618select @a, @b; 619 620create procedure p_20906_b() SET @a=@a+1, @b=@b+1, @c=@c+1; 621show procedure code p_20906_b; 622 623set @a=1; 624set @b=1; 625set @c=1; 626 627call p_20906_b(); 628select @a, @b, @c; 629 630drop procedure p_20906_a; 631drop procedure p_20906_b; 632 633--echo End of 5.0 tests. 634 635# 636# Bug #26303: reserve() not called before qs_append() may lead to buffer 637# overflow 638# 639DELIMITER //; 640CREATE PROCEDURE p1() 641BEGIN 642 DECLARE dummy int default 0; 643 644 CASE 12 645 WHEN 12 646 THEN SET dummy = 0; 647 END CASE; 648END// 649DELIMITER ;// 650SHOW PROCEDURE CODE p1; 651DROP PROCEDURE p1; 652 653--echo # 654--echo # Bug#23032: Handlers declared in a SP do not handle warnings generated in sub-SP 655--echo # 656 657--echo 658--echo # - Case 4: check that "No Data trumps Warning". 659--echo 660 661CREATE TABLE t1(a INT); 662INSERT INTO t1 VALUES (1), (2), (3); 663 664delimiter |; 665 666CREATE PROCEDURE p1() 667BEGIN 668 DECLARE c CURSOR FOR SELECT a FROM t1; 669 670 OPEN c; 671 672 BEGIN 673 DECLARE v INT; 674 675 DECLARE CONTINUE HANDLER FOR SQLWARNING 676 BEGIN 677 GET DIAGNOSTICS @n = NUMBER; 678 GET DIAGNOSTICS CONDITION @n @err_no = MYSQL_ERRNO, @err_txt = MESSAGE_TEXT; 679 SELECT "Warning found!"; 680 SELECT @err_no, @err_txt; 681 END; 682 683 DECLARE EXIT HANDLER FOR NOT FOUND 684 BEGIN 685 GET DIAGNOSTICS @n = NUMBER; 686 GET DIAGNOSTICS CONDITION @n @err_no = MYSQL_ERRNO, @err_txt = MESSAGE_TEXT; 687 SELECT "End of Result Set found!"; 688 SELECT @err_no, @err_txt; 689 END; 690 691 WHILE TRUE DO 692 FETCH c INTO v; 693 END WHILE; 694 END; 695 696 CLOSE c; 697 698 SELECT a INTO @foo FROM t1 LIMIT 1; # Clear warning stack 699END| 700 701delimiter ;| 702 703SET SESSION debug="+d,bug23032_emit_warning"; 704CALL p1(); 705SET SESSION debug="-d,bug23032_emit_warning"; 706 707DROP PROCEDURE p1; 708DROP TABLE t1; 709 710--echo # 711--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE 712--echo # 713SET @@SQL_MODE = ''; 714DELIMITER $; 715CREATE FUNCTION testf_bug11763507() RETURNS INT 716BEGIN 717 RETURN 0; 718END 719$ 720 721CREATE PROCEDURE testp_bug11763507() 722BEGIN 723 SELECT "PROCEDURE testp_bug11763507"; 724END 725$ 726 727DELIMITER ;$ 728 729# STORED FUNCTIONS 730SHOW FUNCTION CODE testf_bug11763507; 731SHOW FUNCTION CODE TESTF_bug11763507; 732 733# STORED PROCEDURE 734SHOW PROCEDURE CODE testp_bug11763507; 735SHOW PROCEDURE CODE TESTP_bug11763507; 736 737DROP PROCEDURE testp_bug11763507; 738DROP FUNCTION testf_bug11763507; 739 740--echo #END OF BUG#11763507 test. 741 742--echo 743--echo # WL#4179: Stored programs: validation of stored program statements. 744--echo # 745--echo # Check that query string is stored only for the expressions, which 746--echo # reference tables or stored functions. 747--echo # 748--echo # Test cases in this file require SHOW ... CODE, which is available only 749--echo # in the debug mode. 750--echo 751 752CREATE TABLE t1(a INT); 753INSERT INTO t1 VALUES (10); 754 755CREATE TEMPORARY TABLE t2(a INT); 756INSERT INTO t2 VALUES (20); 757 758CREATE VIEW t3 AS SELECT 30; 759 760delimiter |; 761 762CREATE FUNCTION f() RETURNS INT 763 RETURN 1| 764 765CREATE PROCEDURE p1() 766BEGIN 767 768 # DEFAULT-expression 769 770 DECLARE x1 INT DEFAULT (SELECT 1 + 2); 771 DECLARE x2 INT DEFAULT (SELECT * FROM (SELECT 1 + 2) t1); 772 DECLARE x3 INT DEFAULT (SELECT * FROM t1); 773 DECLARE x4 INT DEFAULT (SELECT * FROM t2); 774 DECLARE x5 INT DEFAULT (SELECT * FROM t3); 775 DECLARE x6 INT DEFAULT (SELECT f()); 776 777 # CURSOR-query. 778 779 DECLARE c1 CURSOR FOR SELECT (1 + 2) FROM dual; 780 DECLARE c2 CURSOR FOR SELECT * FROM (SELECT 1 + 2) t1; 781 DECLARE c3 CURSOR FOR SELECT * FROM t1; 782 DECLARE c4 CURSOR FOR SELECT * FROM t2; 783 DECLARE c5 CURSOR FOR SELECT * FROM t3; 784 DECLARE c6 CURSOR FOR SELECT f(); 785 786 # IF-expression. 787 788 IF (SELECT 1 + 2) THEN 789 SET @dummy = 1; 790 END IF; 791 792 IF (SELECT * FROM (SELECT 1 + 2) t1) THEN 793 SET @dummy = 1; 794 END IF; 795 796 IF (SELECT * FROM t1) THEN 797 SET @dummy = 1; 798 END IF; 799 800 IF (SELECT * FROM t2) THEN 801 SET @dummy = 1; 802 END IF; 803 804 IF (SELECT * FROM t3) THEN 805 SET @dummy = 1; 806 END IF; 807 808 IF (SELECT f()) THEN 809 SET @dummy = 1; 810 END IF; 811 812 # SET-expression. 813 814 SET x1 = (SELECT 1 + 2); 815 SET x1 = (SELECT * FROM (SELECT 1 + 2) t1); 816 SET x1 = (SELECT * FROM t1); 817 SET x1 = (SELECT * FROM t2); 818 SET x1 = (SELECT * FROM t3); 819 SET x1 = (SELECT f()); 820 821 # CASE-expressions. 822 823 CASE 824 WHEN (SELECT 1 + 2) = 1 THEN SET @dummy = 1; 825 WHEN (SELECT * FROM (SELECT 1 + 2) t1) = 2 THEN SET @dummy = 1; 826 WHEN (SELECT * FROM t1) = 3 THEN SET @dummy = 1; 827 WHEN (SELECT * FROM t2) = 3 THEN SET @dummy = 1; 828 WHEN (SELECT * FROM t3) = 3 THEN SET @dummy = 1; 829 WHEN (SELECT f()) = 3 THEN SET @dummy = 1; 830 END CASE; 831 832 CASE (SELECT 1 + 2) 833 WHEN 1 THEN SET @dummy = 1; 834 ELSE SET @dummy = 1; 835 END CASE; 836 837 CASE (SELECT * FROM (SELECT 1 + 2) t1) 838 WHEN 1 THEN SET @dummy = 1; 839 ELSE SET @dummy = 1; 840 END CASE; 841 842 CASE (SELECT * FROM t1) 843 WHEN 1 THEN SET @dummy = 1; 844 ELSE SET @dummy = 1; 845 END CASE; 846 847 CASE (SELECT * FROM t2) 848 WHEN 1 THEN SET @dummy = 1; 849 ELSE SET @dummy = 1; 850 END CASE; 851 852 CASE (SELECT * FROM t3) 853 WHEN 1 THEN SET @dummy = 1; 854 ELSE SET @dummy = 1; 855 END CASE; 856 857 CASE (SELECT f()) 858 WHEN 1 THEN SET @dummy = 1; 859 ELSE SET @dummy = 1; 860 END CASE; 861 862 # WHILE-expression. 863 864 WHILE (SELECT 1 - 1) DO 865 SET @dummy = 1; 866 END WHILE; 867 868 WHILE (SELECT * FROM (SELECT 1 - 1) t1) DO 869 SET @dummy = 1; 870 END WHILE; 871 872 WHILE (SELECT * FROM t1) - 10 DO 873 SET @dummy = 1; 874 END WHILE; 875 876 WHILE (SELECT * FROM t2) - 10 DO 877 SET @dummy = 1; 878 END WHILE; 879 880 WHILE (SELECT * FROM t3) - 10 DO 881 SET @dummy = 1; 882 END WHILE; 883 884 WHILE (SELECT f()) - 1 DO 885 SET @dummy = 1; 886 END WHILE; 887 888 # REPEAT-expression. 889 890 REPEAT 891 SET @dummy = 1; 892 UNTIL (SELECT 1 - 1) END REPEAT; 893 894 REPEAT 895 SET @dummy = 1; 896 UNTIL (SELECT * FROM (SELECT 1 - 1) t1) END REPEAT; 897 898 REPEAT 899 SET @dummy = 1; 900 UNTIL (SELECT * FROM t1) - 10 END REPEAT; 901 902 REPEAT 903 SET @dummy = 1; 904 UNTIL (SELECT * FROM t2) - 10 END REPEAT; 905 906 REPEAT 907 SET @dummy = 1; 908 UNTIL (SELECT * FROM t3) - 10 END REPEAT; 909 910 REPEAT 911 SET @dummy = 1; 912 UNTIL (SELECT f()) - 1 END REPEAT; 913END| 914 915CREATE FUNCTION f1() RETURNS INT 916 RETURN (SELECT 1 + 2)| 917 918CREATE FUNCTION f2() RETURNS INT 919 RETURN (SELECT * FROM (SELECT 1 + 2) t1)| 920 921CREATE FUNCTION f3() RETURNS INT 922 RETURN (SELECT * FROM t1)| 923 924CREATE FUNCTION f4() RETURNS INT 925 RETURN (SELECT * FROM t2)| 926 927CREATE FUNCTION f5() RETURNS INT 928 RETURN (SELECT * FROM t3)| 929 930CREATE FUNCTION f6() RETURNS INT 931 RETURN f()| 932 933delimiter ;| 934 935--echo 936SHOW PROCEDURE CODE p1; 937--echo 938SHOW FUNCTION CODE f1; 939SHOW FUNCTION CODE f2; 940SHOW FUNCTION CODE f3; 941SHOW FUNCTION CODE f4; 942SHOW FUNCTION CODE f5; 943SHOW FUNCTION CODE f6; 944--echo 945 946DROP FUNCTION f; 947 948DROP PROCEDURE p1; 949DROP FUNCTION f1; 950DROP FUNCTION f2; 951DROP FUNCTION f3; 952DROP FUNCTION f4; 953DROP FUNCTION f5; 954DROP FUNCTION f6; 955 956DROP TABLE t1; 957DROP TEMPORARY TABLE t2; 958DROP VIEW t3; 959 960--echo 961--echo # SHOW ... CODE 962--echo # 963 964DELIMITER |; 965 966CREATE PROCEDURE p11_many_handlers () 967BEGIN 968 DECLARE CONTINUE HANDLER FOR 1050 SELECT "1050 for 401a, please"; 969 DECLARE EXIT HANDLER FOR NOT FOUND SELECT "a place not found"; 970 DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT "a warn place"; 971 DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT "an exceptional place"; 972 DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' SELECT "state of the nation"; 973 BEGIN 974 DECLARE EXIT HANDLER FOR NOT FOUND, 1,2 SELECT "multi multi"; 975 END; 976END| 977 978DELIMITER ;| 979 980SHOW PROCEDURE CODE p11_many_handlers; 981DROP PROCEDURE p11_many_handlers; 982