126 lines
3.0 KiB
Plaintext
126 lines
3.0 KiB
Plaintext
--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;
|
|
|