diff options
Diffstat (limited to 'mysql-test/t/selectivity.test')
| -rw-r--r-- | mysql-test/t/selectivity.test | 179 |
1 files changed, 178 insertions, 1 deletions
diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 3df49456332..c9e1bea1f12 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -1,4 +1,6 @@ --source include/have_stat_tables.inc +--source include/have_sequence.inc +--source include/default_charset.inc --disable_warnings drop table if exists t0,t1,t2,t3; @@ -8,12 +10,14 @@ select @@global.use_stat_tables; select @@session.use_stat_tables; set @save_use_stat_tables=@@use_stat_tables; - set use_stat_tables='preferably'; +--source include/default_optimizer_switch.inc set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; set @save_histogram_size=@@histogram_size; set @save_histogram_type=@@histogram_type; +set join_cache_level=2; +set @@global.histogram_size=0,@@local.histogram_size=0; # check that statistics on nulls is used @@ -1102,3 +1106,176 @@ set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectiv drop table t1; drop function f1; +--echo # +--echo # MDEV-19834 Selectivity of an equality condition discounted twice +--echo # +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables='preferably'; +set @save_histogram_size=@@histogram_size; +set @@histogram_size=0; +create table t1 (a int, b int, key (b), key (a)); +insert into t1 +select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000; +analyze table t1 ; + +--echo # Check what info the optimizer has about selectivities +explain extended select * from t1 use index () where a in (17,51,5); +explain extended select * from t1 use index () where b=2; + +--echo # Now, the equality is used for ref access, while the range condition +--echo # gives selectivity data +explain extended select * from t1 where a in (17,51,5) and b=2; +drop table t1; + +set use_stat_tables= @save_use_stat_tables; +set @@histogram_size=@save_histogram_size; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # MDEV-20576: failing assertion DBUG_ASSERT(0.0 < sel && sel <= 1) +--echo # + +set @@optimizer_use_condition_selectivity=2; + +set names utf8; + +CREATE DATABASE world; + +use world; + +--source include/world_schema.inc + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +CREATE INDEX Name ON City(Name); +CREATE INDEX CountryPopulation ON City(Country,Population); +CREATE INDEX CountryName ON City(Country,Name); + +--disable_query_log +--disable_result_log +--disable_warnings +ANALYZE TABLE City; +--enable_warnings +--enable_result_log +--enable_query_log + +set @@optimizer_use_condition_selectivity=2; + +EXPLAIN +SELECT * FROM City WHERE Country='FIN'; + +DROP DATABASE world; + +use test; + +CREATE TABLE t1 ( + a INT, + b INT NOT NULL, + c char(100), + KEY (b, c), + KEY (b, a, c) +) ENGINE=MyISAM +DEFAULT CHARSET = utf8; + +INSERT INTO t1 VALUES +(1, 1, 1), +(2, 2, 2), +(3, 3, 3), +(4, 4, 4), +(5, 5, 5), +(6, 6, 6), +(7, 7, 7), +(8, 8, 8), +(9, 9, 9); + +INSERT INTO t1 SELECT a + 10, b, c FROM t1; +INSERT INTO t1 SELECT a + 20, b, c FROM t1; +INSERT INTO t1 SELECT a + 40, b, c FROM t1; +INSERT INTO t1 SELECT a + 80, b, c FROM t1; +INSERT INTO t1 SELECT a + 160, b, c FROM t1; +INSERT INTO t1 SELECT a + 320, b, c FROM t1; +INSERT INTO t1 SELECT a + 640, b, c FROM t1; +INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80; + +EXPLAIN +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; +SELECT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 9; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +DROP TABLE t1; + +--echo # +--echo # MDEV-20424: New default value for optimizer_use_condition-selectivity +--echo # leads to bad plan +--echo # + +create table t1(a int, b int, c int, d int, key(a,b)); +insert into t1 select 50,seq-1,seq-1,seq from seq_1_to_10; +insert into t1 select seq-1,seq-1,seq-1,seq from seq_1_to_100 limit 90; + +create table t2(a int, b int, c int, primary key(a)); +insert into t2 select seq-1,seq-1,seq-1 from seq_1_to_100; + +create table t3(a int, b int, c int, primary key(a)); +insert into t3 select seq-1,seq-1,seq-1 from seq_1_to_100 limit 30; + +let $query= select t1.b,t2.a,t3.a,t3.b from t1,t2,t3 +where t1.c = t2.a AND t1.d = t3.a and t1.a = 50 and t1.b <= 100; + +set optimizer_use_condition_selectivity=1; +eval explain extended $query; +eval $query; + +set optimizer_use_condition_selectivity=2; +eval explain extended $query; +eval $query; +set optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; + +drop table t1,t2,t3; + + +--echo # +--echo # MDEV-20519: Query plan regression with optimizer_use_condition_selectivity=4 +--echo # + + + +create table t1 (id int, a int, PRIMARY KEY(id), key(a)); +insert into t1 select seq,seq from seq_1_to_100; + +create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b)); +insert into t2 select seq,seq,seq from seq_1_to_100; + +set optimizer_switch='exists_to_in=off'; +set optimizer_use_condition_selectivity=2; + +let $query= SELECT * FROM t1 + WHERE + EXISTS (SELECT * FROM t1 A INNER JOIN t2 ON t2.a = A.id + WHERE A.a=t1.a AND t2.b < 20); + +eval $query; +eval explain $query; + +EXPLAIN SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65; + +eval explain $query; + +set optimizer_switch= @save_optimizer_switch; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1,t2; + +--echo # End of 10.1 tests + +# +# Clean up +# +--source include/restore_charset.inc +set @@global.histogram_size=@save_histogram_size; |