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.

1 files changed, 139 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;