diff options
Diffstat (limited to 'mysql-test/r/selectivity_no_engine.result')
| -rw-r--r-- | mysql-test/r/selectivity_no_engine.result | 123 |
1 files changed, 123 insertions, 0 deletions
diff --git a/mysql-test/r/selectivity_no_engine.result b/mysql-test/r/selectivity_no_engine.result new file mode 100644 index 00000000000..107d4b76903 --- /dev/null +++ b/mysql-test/r/selectivity_no_engine.result @@ -0,0 +1,123 @@ +# +# Engine-agnostic tests for statistics-based selectivity calculations. +# - selectivity tests that depend on the engine should go into +# t/selectivity.test. That test is run with myisam/innodb/xtradb. +# - this file is for tests that don't depend on the engine. +# +drop table if exists t0,t1,t2,t3; +select @@global.use_stat_tables; +@@global.use_stat_tables +COMPLEMENTARY +select @@session.use_stat_tables; +@@session.use_stat_tables +COMPLEMENTARY +set @save_use_stat_tables=@@use_stat_tables; +set use_stat_tables='preferably'; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +# +# MDEV-5917: EITS: different order of predicates in IN (...) causes different estimates +# +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (col1 int); +# one value in 1..100 range +insert into t2 select A.a + B.a*10 from t1 A, t1 B; +# ten values in 100...200 range +insert into t2 select 100 + A.a + B.a*10 from t1 A, t1 B, t1 C; +set histogram_type='SINGLE_PREC_HB'; +set histogram_size=100; +set optimizer_use_condition_selectivity=4; +analyze table t2 persistent for all; +Table Op Msg_type Msg_text +test.t2 analyze status OK +# The following two must have the same in 'Extra' column: +explain extended select * from t2 where col1 IN (20, 180); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 1100 1.35 Using where +Warnings: +Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where (`test`.`t2`.`col1` in (20,180)) +explain extended select * from t2 where col1 IN (180, 20); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 1100 1.35 Using where +Warnings: +Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where (`test`.`t2`.`col1` in (180,20)) +drop table t1, t2; +# +# MDEV-5926: EITS: Histogram estimates for column=least_possible_value are wrong +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a from t0 A, t0 B, t0 C; +set histogram_size=20; +set histogram_type='single_prec_hb'; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status OK +set use_stat_tables='preferably'; +set optimizer_use_condition_selectivity=4; +# Should select about 10%: +explain extended select * from t1 where a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 9.52 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 2) +# Should select about 10%: +explain extended select * from t1 where a=1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 9.52 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 1) +# Must not have filtered=100%: +explain extended select * from t1 where a=0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 9.52 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 0) +# Again, must not have filtered=100%: +explain extended select * from t1 where a=-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 9.52 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>(-(1))) +drop table t0, t1; +# +# MDEV-4362: Selectivity estimates for IN (...) do not depend on whether the values are in range +# +create table t1 (col1 int); +set @a=-1; +create table t2 (a int) select (@a:=@a+1) as a from information_schema.session_variables A limit 100; +insert into t1 select A.a from t2 A, t2 B where A.a < 100 and B.a < 100; +select min(col1), max(col1), count(*) from t1; +min(col1) max(col1) count(*) +0 99 10000 +set histogram_size=100; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status OK +explain extended select * from t1 where col1 in (1,2,3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 3.37 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` in (1,2,3)) +# Must not cause fp division by zero, or produce nonsense numbers: +explain extended select * from t1 where col1 in (-1,-2,-3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 5.94 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` in (<cache>(-(1)),<cache>(-(2)),<cache>(-(3)))) +explain extended select * from t1 where col1<=-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10000 1.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where (`test`.`t1`.`col1` <= <cache>(-(1))) +drop table t1, t2; +# +# End of the test file +# +set use_stat_tables= @save_use_stat_tables; +set histogram_type=@save_histogram_type; +set histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; |