Fixed BUG#15737: Stored procedure optimizer bug with LEAVE

  Second version.
  The problem was that the optimizer didn't work correctly with forwards jumps
  to "no-op" hpop and cpop instructions.
  Don't generate "no-op" instructions (hpop 0 and cpop 0), it isn't actually
  necessary.


mysql-test/r/sp-code.result:
  Updated results for new test case (BUG#15737)
mysql-test/t/sp-code.test:
  New test case (BUG#15737)
sql/sp_head.cc:
  Removed backpatch methods from sp_instr_hpop/cpop, since they're not needed any more.
  Added more documentation to sp_head::optimize()
sql/sp_head.h:
  Removed backpatch and opt_mark methods from sp_instr_hpop/cpop, since they're not needed
  any more.
  Added comments to optimizer methods in sp_instr.
sql/sql_yacc.yy:
  Don't generate "no-op" hpop and cpop instructions for LEAVE, it's not necessary.
  Just generate them when needed.

2 files changed, 282 insertions, 0 deletions

diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result
index 943471c2261..c9fe170dda6 100644
--- a/mysql-test/r/sp-code.result
+++ b/mysql-test/r/sp-code.result
@@ -1,3 +1,5 @@
+drop procedure if exists empty;
+drop procedure if exists code_sample;
 create procedure empty()
 begin
 end;
@@ -60,3 +62,140 @@ Pos	Instruction
 20	cpop 1
 21	stmt 0 "select t.name, t.idx from t2 t order ..."
 drop procedure code_sample;
+drop procedure if exists sudoku_solve;
+create procedure sudoku_solve(p_naive boolean, p_all boolean)
+deterministic
+modifies sql data
+begin
+drop temporary table if exists sudoku_work, sudoku_schedule;
+create temporary table sudoku_work
+(
+row smallint not null,
+col smallint not null,
+dig smallint not null,
+cnt smallint,
+key using btree (cnt),
+key using btree (row),
+key using btree (col),
+unique key using hash (row,col)
+);
+create temporary table sudoku_schedule
+(
+idx int not null auto_increment primary key,
+row smallint not null,
+col smallint not null
+);
+call sudoku_init();
+if p_naive then
+update sudoku_work set cnt = 0 where dig = 0;
+else
+call sudoku_count();
+end if;
+insert into sudoku_schedule (row,col)
+select row,col from sudoku_work where cnt is not null order by cnt desc;
+begin
+declare v_scounter bigint default 0;
+declare v_i smallint default 1;
+declare v_dig smallint;
+declare v_schedmax smallint;
+select count(*) into v_schedmax from sudoku_schedule;
+more: 
+loop
+begin
+declare v_tcounter bigint default 0;
+sched:
+while v_i <= v_schedmax do
+begin
+declare v_row, v_col smallint;
+select row,col into v_row,v_col from sudoku_schedule where v_i = idx;
+select dig into v_dig from sudoku_work
+where v_row = row and v_col = col;
+case v_dig
+when 0 then
+set v_dig = 1;
+update sudoku_work set dig = 1
+where v_row = row and v_col = col;
+when 9 then
+if v_i > 0 then
+update sudoku_work set dig = 0
+where v_row = row and v_col = col;
+set v_i = v_i - 1;
+iterate sched;
+else
+select v_scounter as 'Solutions';
+leave more;
+end if;
+else
+set v_dig = v_dig + 1;
+update sudoku_work set dig = v_dig
+where v_row = row and v_col = col;
+end case;
+set v_tcounter = v_tcounter + 1;
+if not sudoku_digit_ok(v_row, v_col, v_dig) then
+iterate sched;
+end if;
+set v_i = v_i + 1;
+end;
+end while sched;
+select dig from sudoku_work;
+select v_tcounter as 'Tests';
+set v_scounter = v_scounter + 1;
+if p_all and v_i > 0 then
+set v_i = v_i - 1;
+else
+leave more;
+end if;
+end;
+end loop more;
+end;
+drop temporary table sudoku_work, sudoku_schedule;
+end//
+show procedure code sudoku_solve;
+Pos	Instruction
+0	stmt 9 "drop temporary table if exists sudoku..."
+1	stmt 1 "create temporary table sudoku_work ( ..."
+2	stmt 1 "create temporary table sudoku_schedul..."
+3	stmt 95 "call sudoku_init("
+4	jump_if_not 7(8) p_naive@0
+5	stmt 4 "update sudoku_work set cnt = 0 where ..."
+6	jump 8
+7	stmt 95 "call sudoku_count("
+8	stmt 6 "insert into sudoku_schedule (row,col)..."
+9	set v_scounter@2 0
+10	set v_i@3 1
+11	set v_dig@4 NULL
+12	set v_schedmax@5 NULL
+13	stmt 0 "select count(*) into v_schedmax from ..."
+14	set v_tcounter@6 0
+15	jump_if_not 39(39) (v_i@3 <= v_schedmax@5)
+16	set v_row@7 NULL
+17	set v_col@8 NULL
+18	stmt 0 "select row,col into v_row,v_col from ..."
+19	stmt 0 "select dig into v_dig from sudoku_wor..."
+20	set_case_expr 0 v_dig@4
+21	jump_if_not 25(34) (case_expr@0 = 0)
+22	set v_dig@4 1
+23	stmt 4 "update sudoku_work set dig = 1 where ..."
+24	jump 34
+25	jump_if_not 32(34) (case_expr@0 = 9)
+26	jump_if_not 30(34) (v_i@3 > 0)
+27	stmt 4 "update sudoku_work set dig = 0 where ..."
+28	set v_i@3 (v_i@3 - 1)
+29	jump 15
+30	stmt 0 "select v_scounter as 'Solutions'"
+31	jump 45
+32	set v_dig@4 (v_dig@4 + 1)
+33	stmt 4 "update sudoku_work set dig = v_dig wh..."
+34	set v_tcounter@6 (v_tcounter@6 + 1)
+35	jump_if_not 37(37) not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4))
+36	jump 15
+37	set v_i@3 (v_i@3 + 1)
+38	jump 15
+39	stmt 0 "select dig from sudoku_work"
+40	stmt 0 "select v_tcounter as 'Tests'"
+41	set v_scounter@2 (v_scounter@2 + 1)
+42	jump_if_not 45(14) (p_all@1 and (v_i@3 > 0))
+43	set v_i@3 (v_i@3 - 1)
+44	jump 14
+45	stmt 9 "drop temporary table sudoku_work, sud..."
+drop procedure sudoku_solve;
diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test
index 6644bc3ab43..b7b1fdbbb20 100644
--- a/mysql-test/t/sp-code.test
+++ b/mysql-test/t/sp-code.test
@@ -4,6 +4,11 @@
 
 -- 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;
@@ -47,3 +52,141 @@ 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 not 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;