Create 'main' test directory and move 't' and 'r' there

1 files changed, 1050 insertions, 0 deletions

diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test
new file mode 100644
index 00000000000..3e60f242083
--- /dev/null
+++ b/mysql-test/main/selectivity.test
@@ -0,0 +1,1050 @@
+--source include/have_stat_tables.inc
+
+--disable_warnings
+drop table if exists t0,t1,t2,t3;
+--enable_warnings
+
+select @@global.use_stat_tables;
+select @@session.use_stat_tables;
+
+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;
+
+# check that statistics on nulls is used
+
+set optimizer_use_condition_selectivity=3;
+
+create table t1 (a int);
+insert into t1 values
+  (9), (3), (2), (NULL), (NULL), (2), (NULL), (1), (5), (NULL);
+
+analyze table t1;
+
+select * from mysql.column_stats;
+
+flush table t1;
+
+explain extended 
+select * from t1 where a is null;
+
+explain extended 
+select * from t1 where a is not null;
+
+drop table t1;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--disable_warnings
+DROP DATABASE IF EXISTS dbt3_s001;
+--enable_warnings
+
+CREATE DATABASE dbt3_s001;
+
+use dbt3_s001;
+
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/dbt3_s001.inc
+
+ANALYZE TABLE
+customer, lineitem, nation, orders, part, partsupp, region, supplier;
+FLUSH TABLE
+customer, lineitem, nation, orders, part, partsupp, region, supplier;
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+--echo === Q2 ===
+
+set optimizer_use_condition_selectivity=5;
+explain extended
+select
+	s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
+from
+	part, supplier, partsupp, nation, region
+where
+	p_partkey = ps_partkey
+	and s_suppkey = ps_suppkey
+	and p_size = 9
+	and p_type like '%TIN'
+	and s_nationkey = n_nationkey
+	and n_regionkey = r_regionkey
+	and r_name = 'ASIA'
+	and ps_supplycost = (
+		select
+			min(ps_supplycost)
+		from
+			partsupp, supplier, nation, region
+		where
+			p_partkey = ps_partkey
+			and s_suppkey = ps_suppkey
+			and s_nationkey = n_nationkey
+			and n_regionkey = r_regionkey
+			and r_name = 'ASIA'
+	)
+order by
+	s_acctbal desc, n_name, s_name, p_partkey;
+set optimizer_use_condition_selectivity=4;
+explain extended
+select
+	s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
+from
+	part, supplier, partsupp, nation, region
+where
+	p_partkey = ps_partkey
+	and s_suppkey = ps_suppkey
+	and p_size = 9
+	and p_type like '%TIN'
+	and s_nationkey = n_nationkey
+	and n_regionkey = r_regionkey
+	and r_name = 'ASIA'
+	and ps_supplycost = (
+		select
+			min(ps_supplycost)
+		from
+			partsupp, supplier, nation, region
+		where
+			p_partkey = ps_partkey
+			and s_suppkey = ps_suppkey
+			and s_nationkey = n_nationkey
+			and n_regionkey = r_regionkey
+			and r_name = 'ASIA'
+	)
+order by
+	s_acctbal desc, n_name, s_name, p_partkey;
+
+--echo === Q15 ===
+
+create view revenue0 (supplier_no, total_revenue) as
+  select l_suppkey, sum(l_extendedprice * (1 - l_discount))
+  from lineitem
+  where
+    l_shipdate >= '1995-08-01'
+    and l_shipdate < date_add('1995-08-01', interval 90 day)
+  group by l_suppkey;
+
+let $Q15=
+select s_suppkey, s_name, s_address, s_phone, total_revenue
+from supplier, revenue0
+where s_suppkey = supplier_no
+  and total_revenue = (select max(total_revenue) from revenue0)
+order by s_suppkey;
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=off';
+
+set optimizer_use_condition_selectivity=1;
+eval EXPLAIN EXTENDED $Q15;
+eval $Q15;
+
+set optimizer_use_condition_selectivity=3;
+eval EXPLAIN EXTENDED $Q15;
+eval $Q15;
+
+set optimizer_switch=@save_optimizer_switch;
+
+drop view revenue0;
+
+
+--echo === Q16 ===
+
+let $Q16=
+select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt
+from partsupp, part
+where p_partkey = ps_partkey
+  and p_brand <> 'Brand#11'
+  and p_type not like 'SMALL POLISHED%'
+  and p_size in (49, 37, 27, 5, 40, 6, 22, 8)
+  and ps_suppkey not in (select s_suppkey from supplier
+                           where s_comment like '%Customer%Complaints%')
+group by p_brand, p_type, p_size
+order by supplier_cnt desc, p_brand, p_type, p_size;
+
+set optimizer_use_condition_selectivity=1;
+eval EXPLAIN EXTENDED $Q16;
+eval $Q16;
+
+set optimizer_use_condition_selectivity=3;
+eval EXPLAIN EXTENDED $Q16;
+eval $Q16;
+
+set optimizer_use_condition_selectivity=4;
+eval EXPLAIN EXTENDED $Q16;
+eval $Q16;
+
+
+--echo === Q18 ===
+
+let $Q18=
+select 
+  c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
+from customer, orders, lineitem
+where
+  o_orderkey in (select l_orderkey from lineitem
+                 group by l_orderkey having sum(l_quantity) > 250)
+  and c_custkey = o_custkey
+  and o_orderkey = l_orderkey
+group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
+order by o_totalprice desc, o_orderdate;
+
+set optimizer_use_condition_selectivity=1;
+eval EXPLAIN EXTENDED $Q18;
+eval $Q18;
+
+set optimizer_use_condition_selectivity=3;
+eval EXPLAIN EXTENDED $Q18;
+eval $Q18;
+
+
+--echo === Q22 ===
+
+let $Q22=
+select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal
+from  (
+    select substr(c_phone, 1, 2) as cntrycode, c_acctbal
+    from customer
+    where
+      substr(c_phone, 1, 2) in ('10', '20', '14', '19', '11', '28', '25')
+      and c_acctbal > (select avg(c_acctbal) from customer
+                        where c_acctbal > 0.00
+                          and substr(c_phone, 1, 2) in
+                              ('10', '20', '14', '19', '11', '28', '25'))
+      and not exists (select * from orders where o_custkey = c_custkey)
+  ) as vip
+group by cntrycode
+order by cntrycode;
+
+set optimizer_use_condition_selectivity=1;
+eval EXPLAIN EXTENDED $Q22;
+eval $Q22;
+
+set optimizer_use_condition_selectivity=3;
+eval EXPLAIN EXTENDED $Q22;
+eval $Q22;
+
+
+--echo === Q20 ===
+
+let $Q20=
+select sql_calc_found_rows
+       s_name, s_address
+from supplier, nation
+where s_suppkey in (select ps_suppkey from partsupp
+                    where ps_partkey in (select p_partkey from part
+                                         where p_name like 'g%')
+                          and ps_availqty >
+                              (select 0.5 * sum(l_quantity)
+                               from lineitem
+                               where l_partkey = ps_partkey
+                                     and l_suppkey = ps_suppkey
+                                     and l_shipdate >= date('1993-01-01')
+                                     and l_shipdate < date('1993-01-01') +
+                                         interval '1' year ))
+and s_nationkey = n_nationkey
+and n_name = 'UNITED STATES'
+order by s_name
+limit 10;
+
+set optimizer_use_condition_selectivity=1;
+eval EXPLAIN EXTENDED $Q20;
+eval $Q20;
+
+SELECT ((SELECT COUNT(*) FROM part WHERE p_name LIKE 'g%') /
+        (SELECT COUNT(*) FROM part)) AS sel;
+
+set optimizer_use_condition_selectivity=3;
+eval EXPLAIN EXTENDED $Q20;
+eval $Q20;
+
+set histogram_size=127;
+
+ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
+
+flush table part;
+
+set optimizer_use_condition_selectivity=4;
+eval EXPLAIN EXTENDED $Q20;
+eval $Q20; 
+
+set histogram_type='DOUBLE_PREC_HB';
+set histogram_size=126;
+
+ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES();
+
+flush table part;
+
+eval EXPLAIN EXTENDED $Q20;
+eval $Q20; 
+
+set histogram_type='SINGLE_PREC_HB';
+set histogram_size=24;
+
+ANALYZE TABLE nation PERSISTENT FOR COLUMNS(n_name) INDEXES();
+
+flush table nation;
+
+eval EXPLAIN EXTENDED $Q20;
+eval $Q20; 
+
+DROP DATABASE dbt3_s001;
+
+set histogram_type=@save_histogram_type;
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+use test;
+
+--echo #
+--echo # Bug mdev-4348: using view with use_condition_selectivity > 1 
+--echo #
+
+set @tmp_use_stat_tables=@@use_stat_tables; 
+set use_stat_tables='never';
+set optimizer_use_condition_selectivity=3;
+
+CREATE TABLE t1 (a int, b int);
+INSERT t1 VALUES (7,1), (0,7);
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1;
+
+CREATE TABLE t2 (c int, d int, index idx(d));
+INSERT INTO t2 VALUES 
+  (0,4), (8,6), (1,3), (8,5), (9,3), (2,2), (6,2),
+  (1,9), (6,3), (2,8), (4,1), (0,7), (4,8), (4,5);
+
+EXPLAIN EXTENDED
+SELECT * FROM v1 INNER JOIN t2 ON ( a = c AND b = d );
+
+SELECT * FROM v1 INNER JOIN t2 ON ( a = c AND b = d );
+
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set use_stat_tables=@tmp_use_stat_tables; 
+
+--echo #
+--echo # Bug mdev-4349: impossible range for non-indexed column 
+--echo #
+
+set optimizer_use_condition_selectivity=3;
+
+create table t1 (a int);
+insert into t1 values
+  (3), (7), (2), (5), (7), (1), (2), (2);
+
+set optimizer_use_condition_selectivity=1;
+explain extended
+select * from t1 where a < 1 and a > 7;
+select * from t1 where a < 1 and a > 7;
+
+set optimizer_use_condition_selectivity=3;
+explain extended
+select * from t1 where a < 1 and a > 7;
+select * from t1 where a < 1 and a > 7;
+
+drop table t1;
+
+create table t1 (a int);
+insert into t1 values (1);
+
+create table t2 (b int);
+insert into t2 values (2),(3);
+
+explain extended 
+select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
+select * from t1 where a in ( select b from t2 ) AND ( a > 3 );
+
+drop table t1,t2;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # Bug mdev-4350: erroneous negative selectivity 
+--echo #
+
+create table t1 (a int);
+insert into t1 values (1), (1);
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 select * from t1; 
+insert into t1 values (0);
+select count(*) from t1;
+
+set use_stat_tables='preferably';
+set histogram_size=127;
+set histogram_type='SINGLE_PREC_HB';
+analyze table t1;
+flush table t1;
+
+set optimizer_use_condition_selectivity=4;
+explain extended select * from t1 where a=0;
+
+drop table t1;
+
+set histogram_size=@save_histogram_size;
+set histogram_type=@save_histogram_type;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # Bug mdev-4367: 2-way join with an empty table 
+--echo #                when optimizer_use_condition_selectivity=3
+--echo #
+
+set optimizer_use_condition_selectivity=3;
+
+CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('j'),('k');
+
+CREATE TABLE t2 (b varchar(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('x'),('y');
+
+CREATE TABLE t3 (c varchar(1), KEY(c)) ENGINE=MyISAM;
+
+SELECT * FROM t1 STRAIGHT_JOIN (t2 JOIN t3 ON c = b AND b > 'z');
+
+DROP TABLE t1,t2,t3;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # Bug mdev-4366: impossible condition on an indexed column discovered after
+--echo #                substitution of constant tables
+--echo #                with optimizer_use_condition_selectivity=3
+--echo #
+
+CREATE TABLE t1 (pk int PRIMARY KEY, a int);
+INSERT INTO t1 VALUES 
+  (1,4), (2,6), (3,3), (4,5);
+
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (1), (7);
+
+set optimizer_use_condition_selectivity=1;
+EXPLAIN EXTENDED
+SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10;
+SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10;
+
+set optimizer_use_condition_selectivity=3;
+EXPLAIN EXTENDED
+SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10;
+SELECT 1 FROM t1, t2 WHERE pk = 6 AND a = 2 AND b = 10;
+
+DROP TABLE t1,t2;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # Bug mdev-4370: Histograms have bean created, but the query is run after
+--echo #                FLUSH TABLES with optimizer_use_condition_selectivity=3
+--echo #
+
+set use_stat_tables=PREFERABLY; 
+set histogram_size=10;
+set histogram_type='SINGLE_PREC_HB';
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (9), (1);
+ANALYZE TABLE t1;
+FLUSH TABLES;
+
+set optimizer_use_condition_selectivity=3;
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a > 3;
+SELECT * FROM t1 WHERE a > 3;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1;
+
+set histogram_size=@save_histogram_size;
+set histogram_type=@save_histogram_type;
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-4371: Join with condition supported by index on an empty table  
+--echo #                with optimizer_use_condition_selectivity=3
+--echo #
+
+set use_stat_tables=PREFERABLY; 
+
+CREATE TABLE t1 (a int, b int, INDEX(a));
+
+CREATE TABLE t2 (c int);
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+
+ANALYZE TABLE t1, t2;
+FLUSH TABLES;
+
+set optimizer_use_condition_selectivity=3;
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=off';
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE a > 9;
+SELECT * FROM t1, t2 WHERE a > 9;
+
+set optimizer_switch=@save_optimizer_switch;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-4373: condition on a short varchar column   
+--echo #                with optimizer_use_condition_selectivity=3
+--echo #
+
+set use_stat_tables=PREFERABLY;
+
+CREATE TABLE t1 (a varchar(1));
+INSERT INTO t1 VALUES ('x'), ('y');
+
+ANALYZE TABLE t1;
+FLUSH TABLES;
+
+set optimizer_use_condition_selectivity=3;
+
+SELECT * FROM t1 WHERE a <= 'w';
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-4372: exists subquery in WHERE   
+--echo #                with optimizer_use_condition_selectivity=3
+--echo #
+
+set use_stat_tables = PREFERABLY;
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES 
+  (1),(7),(4),(7),(0),(2),(9),(4),(0),(9),(1),(3),(8),(8);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (4),(5),(2),(5),(1),(1),(2);
+
+ANALYZE TABLE t1, t2;
+FLUSH TABLES;
+
+set optimizer_use_condition_selectivity=3;
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE EXISTS ( SELECT 1 FROM t1, t2 ) AND a != b OR a <= 4;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2;
+
+set use_stat_tables=@save_use_stat_tables;
+
+
+--echo #
+--echo # Bug mdev-4363: selectivity of the condition a IS NULL OR IS NOT NULL   
+--echo #                with optimizer_use_condition_selectivity=3
+--echo #
+
+set use_stat_tables = PREFERABLY;
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES 
+  (1),(7),(4),(7),(NULL),(2),(NULL),(4),(NULL),(NULL),(1),(3),(8),(8);
+
+ANALYZE TABLE t1;
+FLUSH TABLE t1;
+
+set optimizer_use_condition_selectivity=3;
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a IS NULL;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a IS NOT NULL;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a IS NULL OR a IS NOT NULL;
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a IS NULL OR a < 5;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-4378: 2-way join with a materialized IN subquery in WHERE   
+--echo #                when optimizer_use_condition_selectivity=4
+--echo #
+
+set use_stat_tables=PREFERABLY;
+set histogram_size=50;
+set histogram_type=SINGLE_PREC_HB;
+
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(9),(6);
+
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8),(1),(8),(9),(24),(6),(1),(6),(2),(4);
+
+CREATE TABLE t3 (ln VARCHAR(16)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES 
+('smith'),('black'),('white'),('jones'),
+('brown'),('taylor'),('anderson'),('taylor');
+
+ANALYZE TABLE t1, t2, t3;
+FLUSH TABLES;
+
+set  optimizer_use_condition_selectivity=4;
+
+SELECT * FROM t1, t2 WHERE 'garcia' IN ( SELECT MIN( ln ) FROM t3 WHERE ln = 'sun' );
+
+set histogram_size=@save_histogram_size;
+set histogram_type=@save_histogram_type;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2,t3;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-4380: 2-way join with a materialized IN subquery in WHERE   
+--echo #                when optimizer_use_condition_selectivity=3
+--echo #
+
+set  use_stat_tables=PREFERABLY;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (5),(9);
+
+CREATE TABLE t2 (b VARCHAR(8));
+INSERT INTO t2 VALUES ('red'),('blue');
+
+CREATE TABLE t3 (c VARCHAR(8), d VARCHAR(8));
+INSERT INTO t3 VALUES ('white','black'),('cyan','yellow');
+
+ANALYZE TABLE t1, t2, t3;
+FLUSH TABLES;
+
+set optimizer_use_condition_selectivity=3;
+
+SELECT * FROM t1, t2 WHERE ( 'orange', 'green' ) IN ( 
+  SELECT MAX(c), MAX(d) FROM t3, t2 WHERE c >= d AND b = c 
+);
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2,t3;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-4389: join with degenerated range condition in WHERE   
+--echo #                when optimizer_use_condition_selectivity=3
+--echo #
+
+set use_stat_tables=PREFERABLY;
+
+CREATE TABLE t1 (f1 VARCHAR(1));
+INSERT t1 VALUES ('p'),('q');
+
+CREATE TABLE t2 (f2 VARCHAR(1));
+INSERT INTO t2 VALUES 
+  ('o'),('v'),('f'),('f'),('e'),('l'),('j'),('p'),('r'),('j'),
+  ('j'),('u'),('i'),('r'),('x'),('a'),('x'),('s');
+
+ANALYZE TABLE t1, t2;
+FLUSH TABLES;
+
+SET optimizer_use_condition_selectivity=3;
+
+SELECT * FROM t1, t2 AS t2a, t2 AS t2b WHERE f1 <= 'a' AND t2a.f2 = f1;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-4406: range condition for non-nullable column   
+--echo #                when optimizer_use_condition_selectivity=3
+--echo #
+
+create table t1 (a int not null);
+insert into t1 values
+  (7), (6), (4), (9), (1), (5), (2), (1), (3), (8);
+
+set use_stat_tables='preferably';
+
+analyze table t1;
+flush table t1;
+
+set optimizer_use_condition_selectivity=3;
+
+select count(*) from t1 where a between 5 and 7;
+explain extended select * from t1 where a between 5 and 7;
+
+alter table t1 change column a a int;
+analyze table t1;
+flush table t1;
+
+explain extended select * from t1 where a between 5 and 7;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+drop table t1;
+
+set use_stat_tables=@save_use_stat_tables;
+
+
+--echo #
+--echo # Bug mdev-5200: impossible where with a semijoin subquery   
+--echo #                when optimizer_use_condition_selectivity=2
+--echo #
+
+set use_stat_tables = 'preferably';
+set optimizer_use_condition_selectivity = 2;
+
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0), (1);
+
+CREATE TABLE t2 (pk2 int, i2 int, c2 char(1), PRIMARY KEY(pk2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,8,'m'), (2,9,'b');
+
+CREATE TABLE t3 (c3 char(1), INDEX(c3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('v'), ('c');
+
+ANALYZE TABLE t1,t2,t3;
+
+SELECT * FROM t1
+  WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+EXPLAIN EXTENDED
+SELECT * FROM t1
+  WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug mdev-5415: query over an information schema table   
+--echo #                when optimizer_use_condition_selectivity=3
+--echo #
+
+set optimizer_use_condition_selectivity = 3;
+
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE SQL_MODE != '';
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+--echo #
+--echo # Bug mdev-5630: always true conjunctive condition   
+--echo #                when optimizer_use_condition_selectivity=3
+--echo #
+
+set use_stat_tables = 'preferably';
+set optimizer_use_condition_selectivity = 3;
+
+CREATE TABLE t1 (a int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10);
+
+CREATE TABLE t2 (id int, flag char(1), INDEX(id)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (100,'0'),(101,'1');
+
+ANALYZE TABLE t1, t2;
+
+SELECT * FROM t1, t2 WHERE id = a AND ( a = 16 OR flag AND a != 6 );
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug mdev-4429: join with  range condition whose selectivity == 0   
+--echo #                when optimizer_use_condition_selectivity=3
+--echo #
+
+CREATE TABLE language (lang_group INT, lang VARCHAR(16) PRIMARY KEY);
+INSERT INTO language VALUES 
+  (1,'Chinese'),(6,'English'),(1,'French'),
+  (1,'German'),(1,'Italian'),(0,'Japanese');
+
+CREATE TABLE country (code varchar(3) PRIMARY KEY,
+                      country_group INT DEFAULT NULL);
+INSERT INTO country VALUES ('USA',3),('FRA',5);
+
+CREATE TABLE continent (cont_group INT, cont varchar(16) PRIMARY KEY);
+INSERT INTO continent VALUES
+  (1,'N.America'),(1,'S.America'),(3,'Australia'),
+  (4,'Africa'),(5,'Antarctica'),(6,'Eurasia');
+
+SET use_stat_tables=PREFERABLY;
+ANALYZE TABLE country, language, continent;
+FLUSH TABLES;
+SET optimizer_use_condition_selectivity=3;
+
+SELECT * FROM language, country, continent
+  WHERE country_group = lang_group AND lang_group IS NULL;
+EXPLAIN EXTENDED
+SELECT * FROM language, country, continent
+  WHERE country_group = lang_group AND lang_group IS NULL;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+drop table language, country, continent;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-5191: performance degradation due to a suboptimal chosen plan   
+--echo #                when optimizer_use_condition_selectivity=3
+--echo #
+
+set use_stat_tables = 'preferably';
+
+set @@RAND_SEED1=810763568, @@RAND_SEED2=600681772;
+set TIMESTAMP=1394806993;
+
+create table t1 (a int, b int) engine=myisam;
+insert t1 values (rand()*1e5, rand()*1e5);
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+insert t1 select rand()*1e5, rand()*1e5 from t1;
+
+create table t2 (c int, d int, key(c), key(d)) engine=myisam;
+insert t2 select floor(rand()*1e5/2)*2, floor(rand()*1e5/3)*3 from t1;
+
+analyze table t1,t2;
+
+set optimizer_use_condition_selectivity=1;
+explain extended 
+select * from t1, t2, t1 as t3
+  where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
+--sorted_result
+select * from t1, t2, t1 as t3
+  where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
+
+set optimizer_use_condition_selectivity=3;
+explain extended
+select * from t1, t2, t1 as t3
+  where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
+--sorted_result
+select * from t1, t2, t1 as t3
+  where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+drop table t1,t2;
+
+set histogram_type=@save_histogram_type;
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+
+--echo #
+--echo # Bug mdev-5931: no where condition after constant table row substitution 
+--echo #                with optimizer_use_condition_selectivity=3
+--echo #
+
+CREATE TABLE t1 (a varchar(3), b varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('foo', 'foo');
+
+CREATE TABLE t2 (c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1), (2);
+
+set optimizer_use_condition_selectivity=3;
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ;
+
+SELECT * FROM t1, t2 WHERE c >= 0 OR a = b ;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug mdev-6325: wrong selectivity of a column with ref access 
+--echo #
+
+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 + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t2 (a int, b int, key(a));
+insert into t2 select A.a + 10*B.a, 12345 from t0 A, t0 B, t0 C;
+
+set use_stat_tables='preferably';
+set histogram_size=100;
+
+set optimizer_use_condition_selectivity=4;
+analyze table t1 persistent for all;
+analyze table t2 persistent for all;
+
+explain extended 
+select * from t1 straight_join t2 where t1.a=t2.a and t1.a<10;
+explain extended 
+select * from t1 straight_join t2 where t1.a=t2.a and t2.a<10;
+
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+drop table t0,t1,t2;
+
+--echo #
+--echo # Bug mdev-6843: col IS NULL in where condition when col is always NULL
+--echo #
+
+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 + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t2 (a int, b int);
+insert into t2 select NULL, a from t1;
+
+set use_stat_tables='preferably';
+set histogram_size=100;
+
+set optimizer_use_condition_selectivity=4;
+analyze table t2 persistent for all;
+
+explain extended
+select * from t2 a straight_join t2 b where a.a is null;
+
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+drop table t0,t1,t2;
+
+--echo #
+--echo # Bug mdev-7316: a conjunct in WHERE with selectivity == 0
+--echo #
+
+CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b));
+INSERT INTO t1 VALUES
+  ('USAChinese',10), ('USAEnglish',20), ('USAFrench',30);
+
+CREATE TABLE t2 (i int);
+INSERT INTO t2 VALUES
+  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4);
+
+ANALYZE TABLE t1, t2;
+
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=3;
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+  WHERE  a <> 'USARussian' AND b IS NULL;
+
+SELECT * FROM t1, t2
+  WHERE a <> 'USARussian' AND b IS NULL;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2;
+
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-11096: range condition over column without statistical data
+--echo #
+
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=3;
+
+create table t1(col1 char(32));
+insert into t1 values ('a'),('b'),('c'),('d'), ('e'),('f'),('g'),('h');
+analyze table t1 persistent for columns () indexes ();
+
+explain extended 
+select * from t1 where col1 > 'b' and col1 < 'e';
+select * from t1 where col1 > 'b' and col1 < 'e';
+
+drop table t1;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-9628: unindexed blob column without min-max statistics 
+--echo #                with optimizer_use_condition_selectivity=3
+--echo #
+
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=3;
+
+create table t1(col1 char(32));
+insert into t1 values ('a'),('b'),('c'),('d'), ('e'),('f'),('g'),('h');
+analyze table t1;
+
+create table t2(col1 text);
+insert into t2 values ('a'),('b'),('c'),('d'), ('e'),('f'),('g'),('h');
+analyze table t2;
+
+select * from t1 where col1 > 'b' and col1 < 'd';
+explain extended 
+select * from t1 where col1 > 'b' and col1 < 'd';
+
+select * from t2 where col1 > 'b' and col1 < 'd';
+explain extended 
+select * from t2 where col1 > 'b' and col1 < 'd';
+
+select * from t2 where col1 < 'b' and col1 > 'd';
+explain extended 
+select * from t2 where col1 < 'b' and col1 > 'd';
+
+drop table t1,t2;
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # Bug mdev-11364: IS NULL over not nullable datetime column
+--echo #                 in mergeable derived
+--echo #
+
+set use_stat_tables='preferably';
+set optimizer_use_condition_selectivity=4;
+set HISTOGRAM_SIZE = 255;
+
+CREATE TABLE t1 (t TIME, d DATE NOT NULL);
+INSERT INTO t1 VALUES ('10:00:00', '0000-00-00'),('11:00:00','0000-00-00');
+ 
+ANALYZE TABLE t1;
+
+SELECT * FROM (SELECT t FROM t1 WHERE d IS NULL) sq;
+
+DROP TABLE t1;
+
+set histogram_size=@save_histogram_size;
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set use_stat_tables=@save_use_stat_tables;