set rand_seed1:=1919292;
set rand_seed2:=5635345;

drop table if exists table1,table2;

create table table1(col1 int auto_increment, col2 int, col3 varchar(100), primary key(col1),unique keycol2_UNIQUE(col2));
create table table2(col1 int, col4 int, primary key(col1), key(col4), key(col4,col1));
set @v_pk=0;
replace into table1(col2,col3) select @v_pk:=@v_pk+1,@v_pk;
replace into table1(col2,col3) select @v_pk:=@v_pk+1,@v_pk from table1;
replace into table1(col2,col3) select @v_pk:=@v_pk+1,@v_pk from table1 a,table1 b,table1 c;
replace into table1(col2,col3) select @v_pk:=@v_pk+1,@v_pk from table1 a,table1 b,table1 c;
replace into table2(col1,col4) select col2,floor(rand()*100000) from table1;

analyze table table1,table2;


drop function if exists udf1;
delimiter $
create function udf1(par1 int) returns varchar(100) deterministic
begin 
  return (
    select t1.col3 
    from table1 t1
    inner join table2 t2 on t2.col1 = t1.col2 and t2.col4 = par1
    order by t1.col1 desc 
    limit 1  
  );  
end $

delimiter ;

drop procedure if exists p;
delimiter $
create procedure p(p_lim int)
begin
 declare v_i int default 0;
 declare v_j int default 0;
 declare v_pk int default 0;
 declare v_go int default 0;
 declare continue handler for 1213 begin select 'deadlock, rolling back!';rollback; set v_go:=1; end;
 declare continue handler for 1205 begin select 'lock wait timeout, rolling back!';rollback; set v_go:=1; end;
 set v_pk:=0;
 set session transaction_isolation='repeatable-read';
 rep: repeat
   
  set transaction isolation level repeatable read;
  start transaction;
  set @a:=floor(rand()*10000);
  set @b=null;
  set @c=null;
  select t1.col3 into @b from table1 t1 inner join table2 t2 on t2.col1 = t1.col2 and t2.col4 = @a order by t1.col1 desc limit 1  ;
  select udf1(@a) into @c;
  
  if @b<>@c then
     select 'FAILED', @a,@b,@c,v_i;
     rollback;
     leave rep;
  end if;
  commit;

   if v_i % 1000 = 0 then  select v_i as 'iteration';flush tables; end if;
   
   commit;
   set v_i:=v_i+1;
 until v_i > p_lim end repeat;


end $
delimiter ;


call p(300000);
select version();

-- cross check a few items


select t1.col3 from table1 t1 inner join table2 t2 on t2.col1 = t1.col2 and t2.col4 = 73449 order by t1.col1 desc limit 1  ;
select udf1(73449) ;

flush status;
select udf1(73449) ;
show status like 'handler%';

create function f() returns int deterministic return 1;
drop function f;

flush status;
select udf1(73449) ;
show status like 'handler%';