--source include/have_debug.inc

use test;
create table p(
  id int primary key,
  b int,
  c varchar(100),
  key c_1(b)
 );

create table t(
  id int primary key,
  b int,
  c varchar(100)
 );

 create table part(
  id int primary key,
  b int,
  c varchar(100)
 )
 PARTITION BY RANGE(id) (
  PARTITION p0 VALUES LESS THAN (1000),
  PARTITION p1 VALUES LESS THAN (9999)
);

create view v as select * from p;

delimiter ||;
CREATE PROCEDURE generate_data()
BEGIN
  DECLARE i BIGINT UNSIGNED DEFAULT 0;
  WHILE i < 1000 DO
    INSERT INTO t VALUES(i, 1, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
    SET i = i + 1;
  END WHILE;
END||
delimiter ;||

### incorrect usage ###

# cte
with kk as (select *from p) select /*+ sample_percentage(10.0) */ * from kk;
show warnings;

# subquery
select * from (select /*+ sample_percentage(10.0) */ * from p) a;
show warnings;

# subquery
select /*+ sample_percentage(10.0) */ * from (select * from p) a;
show warnings;

# union
select /*+ sample_percentage(10.0) */ * from t union select * from p;
show warnings;

# union
select * from p union select /*+ sample_percentage(10.0) */ * from p;
show warnings;

# order by
select /*+ sample_percentage(100.0) */ b from p order by b;
show warnings;

# limit
select /*+ sample_percentage(100.0) */ b from p limit 10;
show warnings;

# more than one table
select /*+ sample_percentage(100.0) */ p.b from p,t;
show warnings;

# view
select /*+ sample_percentage(100.0) */ * from v;
show warnings;

SET SESSION debug = "+d,fixed_sample_seed";

# sampling by recrod: empty table
select /*+ sample_percentage(10.0) */ * from p;

# sampling by recrod: have data
insert into p values (1,1,'1'),(2,1,'1'),(3,1,'1'),(4,1,'1'),(5,1,'1'),(6,1,'1');
select /*+ sample_percentage(90.0) */ sum(id) from p;

# 1 root + 7 leaves
call generate_data();
set global innodb_sample_advise_pages = 1;

# sampling by block: open cursor at left
select /*+ sample_percentage(100.0) */ sum(id) from t;

# sampling by block: open cursor at right, ie. skipping all leaves
select /*+ sample_percentage(0.0001) */ sum(id) from t;

# sampling by block: all optimistic
select /*+ sample_percentage(20.0) */ sum(id) from t;

# sampling by block: test restore_leaf_pessimistic
SET SESSION debug = "+d,force_leaf_pcur_restore_fail";
select /*+ sample_percentage(20.0) */ sum(id) from t;
SET SESSION debug = "-d,force_leaf_pcur_restore_fail";

# sampling by block: test move_to_next_page
SET SESSION debug = "+d,force_branch_pcur_restore_fail";
select /*+ sample_percentage(20.0) */ sum(id) from t;
SET SESSION debug = "-d,force_branch_pcur_restore_fail";

# partition table
insert into part values(1,1,'1'),(2,2,'2'),(1001,1,'1'),(1002,2,'2');
insert into part values(10,1,'1'),(20,2,'2'),(2001,1,'1'),(2002,2,'2');
insert into part values(30,1,'1'),(40,2,'2'),(3001,1,'1'),(3002,2,'2');
select /*+ sample_percentage(80.0) */ sum(id) from part;

set global innodb_sample_advise_pages = DEFAULT;

SET SESSION debug = "-d,fixed_sample_seed";

drop view v;
drop table t;
drop table p;
drop table part;
drop procedure generate_data;