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;
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||
with kk as (select *from p) select /*+ sample_percentage(10.0) */ * from kk;
id	b	c
Warnings:
Warning	3123	Optimizer hint syntax error
show warnings;
Level	Code	Message
Warning	3123	Optimizer hint syntax error
select * from (select /*+ sample_percentage(10.0) */ * from p) a;
id	b	c
Warnings:
Warning	3123	Optimizer hint syntax error
show warnings;
Level	Code	Message
Warning	3123	Optimizer hint syntax error
select /*+ sample_percentage(10.0) */ * from (select * from p) a;
id	b	c
Warnings:
Warning	3123	Optimizer hint syntax error
show warnings;
Level	Code	Message
Warning	3123	Optimizer hint syntax error
select /*+ sample_percentage(10.0) */ * from t union select * from p;
id	b	c
Warnings:
Warning	3123	Optimizer hint syntax error
show warnings;
Level	Code	Message
Warning	3123	Optimizer hint syntax error
select * from p union select /*+ sample_percentage(10.0) */ * from p;
id	b	c
Warnings:
Warning	3123	Optimizer hint syntax error
show warnings;
Level	Code	Message
Warning	3123	Optimizer hint syntax error
select /*+ sample_percentage(100.0) */ b from p order by b;
b
Warnings:
Warning	3123	Optimizer hint syntax error
show warnings;
Level	Code	Message
Warning	3123	Optimizer hint syntax error
select /*+ sample_percentage(100.0) */ b from p limit 10;
b
Warnings:
Warning	3123	Optimizer hint syntax error
show warnings;
Level	Code	Message
Warning	3123	Optimizer hint syntax error
select /*+ sample_percentage(100.0) */ p.b from p,t;
b
Warnings:
Warning	3123	Optimizer hint syntax error
show warnings;
Level	Code	Message
Warning	3123	Optimizer hint syntax error
select /*+ sample_percentage(100.0) */ * from v;
id	b	c
Warnings:
Warning	3123	Optimizer hint syntax error
show warnings;
Level	Code	Message
Warning	3123	Optimizer hint syntax error
SET SESSION debug = "+d,fixed_sample_seed";
select /*+ sample_percentage(10.0) */ * from p;
id	b	c
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;
sum(id)
11
call generate_data();
set global innodb_sample_advise_pages = 1;
select /*+ sample_percentage(100.0) */ sum(id) from t;
sum(id)
499500
select /*+ sample_percentage(0.0001) */ sum(id) from t;
sum(id)
NULL
select /*+ sample_percentage(20.0) */ sum(id) from t;
sum(id)
59082
SET SESSION debug = "+d,force_leaf_pcur_restore_fail";
select /*+ sample_percentage(20.0) */ sum(id) from t;
sum(id)
59082
SET SESSION debug = "-d,force_leaf_pcur_restore_fail";
SET SESSION debug = "+d,force_branch_pcur_restore_fail";
select /*+ sample_percentage(20.0) */ sum(id) from t;
sum(id)
59082
SET SESSION debug = "-d,force_branch_pcur_restore_fail";
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;
sum(id)
7067
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;