1--source include/have_stat_tables.inc 2--source include/default_optimizer_switch.inc 3 4--echo # 5--echo # Engine-agnostic tests for statistics-based selectivity calculations. 6--echo # - selectivity tests that depend on the engine should go into 7--echo # t/selectivity.test. That test is run with myisam/innodb/xtradb. 8--echo # - this file is for tests that don't depend on the engine. 9--echo # 10 11--disable_warnings 12drop table if exists t0,t1,t2,t3; 13--enable_warnings 14 15select @@global.use_stat_tables; 16select @@session.use_stat_tables; 17 18set @save_use_stat_tables=@@use_stat_tables; 19 20set use_stat_tables='preferably'; 21 22set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; 23set @save_histogram_size=@@histogram_size; 24set @save_histogram_type=@@histogram_type; 25 26--echo # 27--echo # MDEV-5917: EITS: different order of predicates in IN (...) causes different estimates 28--echo # 29create table t1(a int); 30insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 31 32create table t2 (col1 int); 33--echo # one value in 1..100 range 34insert into t2 select ta.a + tb.a*10 from t1 ta, t1 tb; 35--echo # ten values in 100...200 range 36insert into t2 select 100 + ta.a + tb.a*10 from t1 ta, t1 tb, t1 tc; 37 38set histogram_type='SINGLE_PREC_HB'; 39set histogram_size=100; 40set optimizer_use_condition_selectivity=4; 41analyze table t2 persistent for all; 42 43--echo # The following two must have the same in 'Extra' column: 44explain extended select * from t2 where col1 IN (20, 180); 45explain extended select * from t2 where col1 IN (180, 20); 46 47drop table t1, t2; 48 49--echo # 50--echo # MDEV-5926: EITS: Histogram estimates for column=least_possible_value are wrong 51--echo # 52create table t0(a int); 53insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 54create table t1(a int); 55insert into t1 select ta.a from t0 ta, t0 tb, t0 tc; 56set histogram_size=20; 57set histogram_type='single_prec_hb'; 58analyze table t1 persistent for all; 59set use_stat_tables='preferably'; 60set optimizer_use_condition_selectivity=4; 61--echo # Should select about 10%: 62explain extended select * from t1 where a=2; 63--echo # Should select about 10%: 64explain extended select * from t1 where a=1; 65--echo # Must not have filtered=100%: 66explain extended select * from t1 where a=0; 67--echo # Again, must not have filtered=100%: 68explain extended select * from t1 where a=-1; 69 70drop table t0, t1; 71 72--echo # 73--echo # MDEV-4362: Selectivity estimates for IN (...) do not depend on whether the values are in range 74--echo # 75create table t1 (col1 int); 76set @a=-1; 77create table t2 (a int) select (@a:=@a+1) as a from information_schema.session_variables ta limit 100; 78insert into t1 select ta.a from t2 ta, t2 tb where ta.a < 100 and tb.a < 100; 79select min(col1), max(col1), count(*) from t1; 80set histogram_size=100; 81analyze table t1 persistent for all; 82explain extended select * from t1 where col1 in (1,2,3); 83--echo # Must not cause fp division by zero, or produce nonsense numbers: 84explain extended select * from t1 where col1 in (-1,-2,-3); 85explain extended select * from t1 where col1<=-1; 86drop table t1, t2; 87 88--echo # 89--echo # MDEV-5984: EITS: Incorrect filtered% value for single-table select with range access 90--echo # 91create table t1(a int); 92insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 93 94create table t2 (a int, b int, col1 varchar(64), col2 varchar(64), key(a,b)); 95insert into t2 select ta.a+10*tb.a, tc.a+10*td.a, 'filler-data1', 'filler-data2' from t1 ta, t1 tb, t1 tc, t1 td; 96 97set histogram_size=100; 98set optimizer_use_condition_selectivity=4; 99set use_stat_tables='preferably'; 100analyze table t2 persistent for all; 101--echo # This must show filtered=100%: 102explain extended select * from t2 where a in (1,2,3) and b in (1,2,3); 103 104drop table t2, t1; 105 106--echo # 107--echo # MDEV-5980: EITS: if condition is used for REF access, its selectivity is still in filtered% 108--echo # 109create table t0(a int); 110insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 111create table t1(key1 int, col1 int, key(key1)); 112insert into t1 select ta.a, ta.a from t0 ta, t0 tb, t0 tc; 113 114set histogram_size=100; 115set use_stat_tables='preferably'; 116set optimizer_use_condition_selectivity=4; 117analyze table t1 persistent for all; 118--echo # 10% is ok 119explain extended select * from t1 where col1=2; 120--echo # Must show 100%, not 10% 121explain extended select * from t1 where key1=2; 122drop table t0, t1; 123 124--echo # MDEV-6003: EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value 125--echo # 126create table t0(a int); 127insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 128create table t1 ( 129 kp1 int, kp2 int, 130 filler1 char(100), 131 filler2 char(100), 132 key(kp1, kp2) 133); 134 135insert into t1 136select 137 ta.a, 138 tb.a, 139 'filler-data-1', 140 'filler-data-2' 141from t0 ta, t0 tb, t0 tc; 142set histogram_size=100; 143set use_stat_tables='preferably'; 144set optimizer_use_condition_selectivity=4; 145analyze table t1 persistent for all; 146--echo # NOTE: 10*100%, 10*100% rows is ok 147explain extended select * from t0, t1 where t1.kp1=t0.a and t1.kp2=t0.a+1; 148 149--echo # NOTE: t0: 10*100% is ok, t1: 10*9.90% is bad. t1 should have 10*100%. 150explain extended select * from t0, t1 where t1.kp1=t0.a and t1.kp2=4; 151drop table t0, t1; 152 153--echo # 154--echo # MDEV-6209: Assertion `join->best_read < double(1.79769313486231570815e+308L)' 155--echo # failed in bool greedy_search with optimizer_use_condition_selectivity>1 156--echo # 157SET optimizer_use_condition_selectivity = 2; 158 159CREATE TABLE t1 (a CHAR(6), b INT, PRIMARY KEY (a,b)) ENGINE=MyISAM; 160INSERT INTO t1 VALUES ('foo',1),('bar',2); 161 162SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE NOT ( t1_1.a <> 'baz'); 163DROP TABLE t1; 164 165--echo # 166--echo # MDEV-6308: Server crashes in table_multi_eq_cond_selectivity with ... 167--echo # 168CREATE TABLE t1 ( 169 id varchar(40) COLLATE utf8_bin, 170 dt datetime, 171 PRIMARY KEY (id) 172); 173 174INSERT INTO t1 VALUES ('foo','2011-04-12 05:18:08'), 175 ('bar','2013-09-19 11:37:03'); 176 177CREATE TABLE t2 ( 178 t1_id varchar(40) COLLATE utf8_bin, 179 f1 varchar(64), 180 f2 varchar(1024), 181 KEY (f1,f2(255)) 182); 183 184INSERT INTO t2 VALUES ('foo','baz','qux'),('bar','baz','qux'); 185INSERT INTO t2 VALUES ('foo','bazz','qux'),('bar','bazz','qux'); 186 187set optimizer_use_condition_selectivity=2; 188explain 189select * from t1,t2 where t1.id = t2.t1_id and t2.f2='qux' and t2.f1='baz'; 190select * from t1,t2 where t1.id = t2.t1_id and t2.f2='qux' and t2.f1='baz'; 191 192drop table t1,t2; 193 194--echo # 195--echo # MDEV-5985: EITS: selectivity estimates look illogical for join and non-key equalities 196--echo # 197 198create table t0(a int); 199insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 200create table t1(a int); 201insert into t1 select ta.a + tb.a* 10 + tc.a * 100 from t0 ta, t0 tb, t0 tc; 202create table t2 as select * from t1; 203set histogram_size=100; 204set use_stat_tables='preferably'; 205set optimizer_use_condition_selectivity=4; 206analyze table t1 persistent for all; 207analyze table t2 persistent for all; 208--echo # Filtered will be 4.95, 9.90 209explain extended select * from t1 ta, t2 tb where ta.a < 40 and tb.a < 100; 210--echo # Here, tb.filtered should not become 100%: 211explain extended select * from t1 ta, t2 tb where ta.a < 40 and tb.a < 100 and tb.a=ta.a; 212 213drop table t0,t1,t2; 214 215--echo # 216--echo # MDEV-8779: mysqld got signal 11 in sql/opt_range_mrr.cc:100(step_down_to) 217--echo # 218set @tmp_mdev8779=@@optimizer_use_condition_selectivity; 219set optimizer_use_condition_selectivity=5; 220CREATE TABLE t1 ( 221 i int(10) unsigned NOT NULL AUTO_INCREMENT, 222 n varchar(2048) NOT NULL, 223 d tinyint(1) unsigned NOT NULL, 224 p int(10) unsigned NOT NULL, 225 PRIMARY KEY (i) 226) DEFAULT CHARSET=utf8; 227insert into t1 values (1,'aaa',1,1), (2,'bbb',2,2); 228SELECT * FROM t1 WHERE t1.d = 0 AND t1.p = '1' AND t1.i != '-1' AND t1.n = 'some text'; 229set optimizer_use_condition_selectivity= @tmp_mdev8779; 230DROP TABLE t1; 231 232--echo # 233--echo # MDEV-23937: SIGSEGV in looped best_extension_by_limited_search from greedy_search 234--echo # (Testcase only) 235--echo # 236set 237 @tmp_jcl= @@join_cache_level, 238 @tmp_ucs= @@optimizer_use_condition_selectivity; 239set 240 join_cache_level=3, 241 optimizer_use_condition_selectivity=2; 242 243CREATE TABLE t1 AS SELECT * FROM mysql.user; 244CREATE TABLE t3 (b VARCHAR (1)); 245CREATE TABLE t2 (c2 INT); 246INSERT INTO t2 VALUES (1); 247 248EXPLAIN 249SELECT * FROM t1 AS a NATURAL JOIN t1 AS b; 250 251DROP TABLE t1,t2,t3; 252 253--echo # 254--echo # End of the test file 255--echo # 256 257set use_stat_tables= @save_use_stat_tables; 258set histogram_type=@save_histogram_type; 259set histogram_size=@save_histogram_size; 260set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; 261 262