1 files changed, 524 insertions, 0 deletions
diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test
new file mode 100644
index 00000000000..0f249c95172
--- /dev/null
+++ b/mysql-test/t/sp-code.test
@@ -0,0 +1,524 @@
+#
+# Test the debugging feature "show procedure/function code <name>" 
+#
+
+-- source include/is_debug_build.inc
+
+--disable_warnings
+drop procedure if exists empty;
+drop procedure if exists code_sample;
+--enable_warnings
+
+create procedure empty()
+begin
+end;
+show procedure code empty;
+drop procedure empty;
+
+create function almost_empty()
+    returns int
+  return 0;
+show function code almost_empty;
+drop function almost_empty;
+
+delimiter //;
+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//
+delimiter ;//
+show procedure code code_sample;
+drop procedure code_sample;
+
+
+#
+# BUG#15737: Stored procedure optimizer bug with LEAVE
+#
+# This is a much more extensive test case than is strictly needed,
+# but it was kept as is for two reasons:
+# - The bug occurs under some quite special circumstances, so it
+#   wasn't trivial to create a smaller test,
+# - There's some value in having another more complex code sample
+#   in this test file. This might catch future code generation bugs
+#   that doesn't show in behaviour in any obvious way.
+
+--disable_warnings
+drop procedure if exists sudoku_solve;
+--enable_warnings
+
+delimiter //;
+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//
+delimiter ;//
+
+# The interestings parts are where the code for the two "leave" are:
+# ...
+#|  26 | jump_if_not 30 (v_i@3 > 0)                                            |
+# ...
+#|  30 | stmt 0 "select v_scounter as 'Solutions'"                             |
+#|  31 | jump 45                                                               |
+# ...
+#|  42 | jump_if_not 45 (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..."                     |
+#+-----+-----------------------------------------------------------------------+
+# The bug appeared at position 42 (with the wrong destination).
+show procedure code sudoku_solve;
+
+drop procedure sudoku_solve;
+
+#
+# Bug#19194 (Right recursion in parser for CASE causes excessive stack
+#   usage, limitation)
+# This bug also exposed a flaw in the generated code with nested case
+# statements
+#
+
+--disable_warnings
+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;
+--enable_warnings
+
+delimiter |;
+
+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|
+
+# Outer SIMPLE case, inner SEARCHED case
+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|
+
+# Outer SEARCHED case, inner SIMPLE case
+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|
+
+# Outer SIMPLE case, inner SIMPLE case
+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|
+
+# Outer SEARCHED case, inner SEARCHED case
+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|
+
+delimiter ;|
+
+SHOW PROCEDURE CODE proc_19194_simple;
+SHOW PROCEDURE CODE proc_19194_searched;
+SHOW PROCEDURE CODE proc_19194_nested_1;
+SHOW PROCEDURE CODE proc_19194_nested_2;
+SHOW PROCEDURE CODE proc_19194_nested_3;
+SHOW PROCEDURE CODE proc_19194_nested_4;
+
+CALL proc_19194_nested_1(10, 1);
+
+#
+# Before 19194, the generated code was:
+#   20      jump_if_not 23(27) 30
+#   21      set str_i@2 _latin1'30'
+# As opposed to the expected:
+#   20      jump_if_not 23(27) (case_expr@0 = 30)
+#   21      set str_i@2 _latin1'30'
+#
+# and as a result, this call returned "30",
+# because the expression 30 is always true,
+# masking the case 40, case 0 and the else.
+#
+CALL proc_19194_nested_1(25, 1);
+
+CALL proc_19194_nested_1(20, 1);
+CALL proc_19194_nested_1(20, 2);
+CALL proc_19194_nested_1(20, 3);
+CALL proc_19194_nested_1(20, 4);
+CALL proc_19194_nested_1(30, 1);
+CALL proc_19194_nested_1(40, 1);
+CALL proc_19194_nested_1(0, 0);
+
+CALL proc_19194_nested_2(10, 1);
+
+#
+# Before 19194, the generated code was:
+#   20      jump_if_not 23(27) (case_expr@0 = (i@0 = 30))
+#   21      set str_i@2 _latin1'30'
+# As opposed to the expected:
+#   20      jump_if_not 23(27) (i@0 = 30)
+#   21      set str_i@2 _latin1'30'
+# and as a result, this call crashed the server, because there is no
+# such variable as "case_expr@0".
+#
+CALL proc_19194_nested_2(25, 1);
+
+CALL proc_19194_nested_2(20, 1);
+CALL proc_19194_nested_2(20, 2);
+CALL proc_19194_nested_2(20, 3);
+CALL proc_19194_nested_2(20, 4);
+CALL proc_19194_nested_2(30, 1);
+CALL proc_19194_nested_2(40, 1);
+CALL proc_19194_nested_2(0, 0);
+
+CALL proc_19194_nested_3(10, 1);
+CALL proc_19194_nested_3(25, 1);
+CALL proc_19194_nested_3(20, 1);
+CALL proc_19194_nested_3(20, 2);
+CALL proc_19194_nested_3(20, 3);
+CALL proc_19194_nested_3(20, 4);
+CALL proc_19194_nested_3(30, 1);
+CALL proc_19194_nested_3(40, 1);
+CALL proc_19194_nested_3(0, 0);
+
+CALL proc_19194_nested_4(10, 1);
+CALL proc_19194_nested_4(25, 1);
+CALL proc_19194_nested_4(20, 1);
+CALL proc_19194_nested_4(20, 2);
+CALL proc_19194_nested_4(20, 3);
+CALL proc_19194_nested_4(20, 4);
+CALL proc_19194_nested_4(30, 1);
+CALL proc_19194_nested_4(40, 1);
+CALL proc_19194_nested_4(0, 0);
+
+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;
+
+#
+# Bug#19207: Final parenthesis omitted for CREATE INDEX in Stored
+# Procedure
+#
+# Wrong criteria was used to distinguish the case when there was no
+# lookahead performed in the parser.  Bug affected only statements
+# ending in one-character token without any optional tail, like CREATE
+# INDEX and CALL.
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE PROCEDURE p1() CREATE INDEX idx ON t1 (c1);
+SHOW PROCEDURE CODE p1;
+
+DROP PROCEDURE p1;
+
+
+#
+# Bug#26977 exception handlers never hreturn
+#
+--disable_warnings
+drop table if exists t1;
+drop procedure if exists proc_26977_broken;
+drop procedure if exists proc_26977_works;
+--enable_warnings
+
+create table t1(a int unique);
+
+delimiter //;
+
+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//
+delimiter ;//
+
+show procedure code proc_26977_broken;
+
+show procedure code proc_26977_works;
+
+## This caust an error because of jump short cut
+## optimization.
+call proc_26977_broken(1);
+
+## This works
+call proc_26977_works(2);
+
+drop table t1;
+drop procedure proc_26977_broken;
+drop procedure proc_26977_works;
+
+
+--echo End of 5.0 tests.