diff options
26 files changed, 1377 insertions, 33 deletions
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 1c7e9cd839d..19b4319910b 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -678,7 +678,8 @@ The following specify which files/extra groups are read (specified before remain join_cache_hashed, join_cache_bka, optimize_join_buffer_size, table_elimination, extended_keys, exists_to_in, orderby_uses_equalities, - condition_pushdown_for_derived, split_materialized + condition_pushdown_for_derived, split_materialized, + not_null_range_scan --optimizer-use-condition-selectivity=# Controls selectivity of which conditions the optimizer takes into account to calculate cardinality of a partial diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 32e0cf2868c..807ca160ff0 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3024,3 +3024,288 @@ drop table t1; # # End of 10.2 tests # +# +# MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer +# +set @save_optimizer_switch= @@optimizer_switch; +set @@optimizer_switch='not_null_range_scan=on'; +create table ten(a int); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k(a int); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; +create table t1 ( +id int NOT NULL, +subset_id int DEFAULT NULL, +PRIMARY KEY (id), +KEY t1_subset_id (subset_id)); +create table t2 ( +id int, +col int NOT NULL, +key (id) +); +insert into t1 select a,a from one_k limit 5; +insert into t1 select a+5,NULL from one_k limit 995; +insert into t2 select a,a from one_k; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status Table is already up to date +# expected for t1: range access and rows = 4 (not 1000) +explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 +SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id; +id subset_id id col +0 0 0 0 +1 1 1 1 +2 2 2 2 +3 3 3 3 +4 4 4 4 +# with a subquery +# expected the same plan as above +explain SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition +1 PRIMARY t2 ref id id 5 test.t1.subset_id 1 Using index; FirstMatch(t1) +SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2); +id subset_id +0 0 +1 1 +2 2 +3 3 +4 4 +# non-mergable subquery +# expected for t1: range access and rows = 4 (not 1000) +explain SELECT * FROM t1 +WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.subset_id 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1000 Using temporary +SELECT * FROM t1 +WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col); +id subset_id +0 0 +1 1 +2 2 +3 3 +4 4 +create view v1 as SELECT t2.id FROM t2; +create view v2 as SELECT t2.id FROM t2 group by t2.col; +# with mergeable view +# expected for t1: range access and rows = 4 (not 1000) +explain SELECT * FROM t1, v1 where t1.subset_id=v1.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 Using index +SELECT * FROM t1, v1 where t1.subset_id=v1.id; +id subset_id id +0 0 0 +1 1 1 +2 2 2 +3 3 3 +4 4 4 +# with non-mergeable view +# expected for t1: range access and rows = 4 (not 1000) +explain SELECT * FROM t1, v2 where t1.subset_id=v2.id; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition +1 PRIMARY <derived2> ref key0 key0 5 test.t1.subset_id 10 +2 DERIVED t2 ALL NULL NULL NULL NULL 1000 Using temporary; Using filesort +SELECT * FROM t1, v2 where t1.subset_id=v2.id; +id subset_id id +0 0 0 +1 1 1 +2 2 2 +3 3 3 +4 4 4 +# expected for t2 and for t1: range access +explain SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range id id 5 NULL 3 Using index condition +1 SIMPLE t1 range t1_subset_id t1_subset_id 5 NULL 4 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4); +id col id subset_id +0 0 0 0 +2 2 0 0 +4 4 0 0 +0 0 1 1 +2 2 1 1 +4 4 1 1 +0 0 2 2 +2 2 2 2 +4 4 2 2 +0 0 3 3 +2 2 3 3 +4 4 3 3 +0 0 4 4 +2 2 4 4 +4 4 4 4 +# no range access expected for t1 +explain SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 Using where +SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10; +id subset_id id col +0 0 0 0 +1 1 1 1 +2 2 2 2 +3 3 3 3 +4 4 4 4 +5 NULL NULL NULL +6 NULL NULL NULL +7 NULL NULL NULL +8 NULL NULL NULL +9 NULL NULL NULL +# expected for t1: range access +explain SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE ten ALL NULL NULL NULL NULL 10 +1 SIMPLE t1 range t1_subset_id t1_subset_id 5 NULL 4 Using where +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 Using where +SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id; +a id subset_id id col +0 0 0 0 0 +1 1 1 1 1 +2 2 2 2 2 +3 3 3 3 3 +4 4 4 4 4 +5 NULL NULL NULL NULL +6 NULL NULL NULL NULL +7 NULL NULL NULL NULL +8 NULL NULL NULL NULL +9 NULL NULL NULL NULL +# no range access expected for t1 +explain SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 Using where +1 SIMPLE ten ALL NULL NULL NULL NULL 10 Using where +SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id +LIMIT 10; +id subset_id id col a +0 0 0 0 0 +1 1 1 1 1 +2 2 2 2 2 +3 3 3 3 3 +4 4 4 4 4 +5 NULL NULL NULL NULL +6 NULL NULL NULL NULL +7 NULL NULL NULL NULL +8 NULL NULL NULL NULL +9 NULL NULL NULL NULL +drop index id on t2; +# expected for t1: range access +explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition +1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id; +id subset_id id col +0 0 0 0 +1 1 1 1 +2 2 2 2 +3 3 3 3 +4 4 4 4 +# expected impossible where after reading const tables +explain SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL; +id subset_id id col +# expected impossible where after reading const tables +explain SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL; +id subset_id id col +drop index t1_subset_id on t1; +alter table t1 add column m int not null default 0; +alter table t1 add index idx(m,subset_id); +alter table t2 add index (id); +update t1 set m = id mod 2; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status Table is already up to date +# expected for t1: range access by idx (keylen=9) +explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 9 NULL 4 Using index condition +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 +SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ; +id subset_id m id col +0 0 0 0 0 +2 2 0 2 2 +4 4 0 4 4 +drop view v1,v2; +drop table t1,t2; +create table t1 ( +id int NOT NULL, +subset_id int DEFAULT NULL, +KEY key1(id, subset_id), +KEY t1_subset_id (subset_id) +); +create table t2 ( +id int NOT NULL, +col int NOT NULL, +key (id) +); +insert into t1 select 1,a from one_k limit 5; +insert into t1 select 1,NULL from one_k limit 495; +insert into t2 select a,a from one_k; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status Table is already up to date +# expected for t1 :range access by index key1 +# rows 4 instead of 500 +explain SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range key1,t1_subset_id key1 9 NULL 4 Using where; Using index +1 SIMPLE t2 ref id id 4 test.t1.subset_id 1 +SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id; +id subset_id id col +1 0 0 0 +1 1 1 1 +1 2 2 2 +1 3 3 3 +1 4 4 4 +drop table t1,t2; +create table t1 (id int unsigned,col int, KEY key1(id)); +create table t2 (id int unsigned,col int DEFAULT NULL,key (id)); +insert into t1 select a,2 from one_k limit 50; +insert into t1 select NULL,2 from one_k limit 450; +insert into t2 select a,a from one_k; +insert into t2 select a,a from one_k; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +# using key1 for range access on t1 and also using index for sorting, +# no filesort, rows should be 75 not 500 +explain SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range key1 key1 5 NULL 75 Using index condition; Using where +1 SIMPLE t2 ref id id 5 test.t1.id 2 +SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10; +id col id col +0 2 0 0 +0 2 0 0 +1 2 1 1 +1 2 1 1 +2 2 2 2 +2 2 2 2 +3 2 3 3 +3 2 3 3 +4 2 4 4 +4 2 4 4 +drop table t1,t2; +drop table ten,one_k; +set @@optimizer_switch= @save_optimizer_switch; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index bd2299bac5f..014916a4d7f 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2053,3 +2053,185 @@ drop table t1; --echo # --echo # End of 10.2 tests --echo # + +--echo # +--echo # MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer +--echo # + +set @save_optimizer_switch= @@optimizer_switch; +set @@optimizer_switch='not_null_range_scan=on'; +create table ten(a int); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table one_k(a int); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; + +create table t1 ( + id int NOT NULL, + subset_id int DEFAULT NULL, + PRIMARY KEY (id), + KEY t1_subset_id (subset_id)); + +create table t2 ( + id int, + col int NOT NULL, + key (id) +); + +insert into t1 select a,a from one_k limit 5; +insert into t1 select a+5,NULL from one_k limit 995; +insert into t2 select a,a from one_k; + +analyze table t1,t2; + +let $q= +SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id; +--echo # expected for t1: range access and rows = 4 (not 1000) +eval explain $q; +eval $q; + +let $q= +SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2); +--echo # with a subquery +--echo # expected the same plan as above +eval explain $q; +eval $q; + +let $q= +SELECT * FROM t1 + WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col); +--echo # non-mergable subquery +--echo # expected for t1: range access and rows = 4 (not 1000) +eval explain $q; +eval $q; + +create view v1 as SELECT t2.id FROM t2; +create view v2 as SELECT t2.id FROM t2 group by t2.col; + +let $q= +SELECT * FROM t1, v1 where t1.subset_id=v1.id; +--echo # with mergeable view +--echo # expected for t1: range access and rows = 4 (not 1000) +eval explain $q; +eval $q; + +let $q= SELECT * FROM t1, v2 where t1.subset_id=v2.id; +--echo # with non-mergeable view +--echo # expected for t1: range access and rows = 4 (not 1000) +eval explain $q; +eval $q; + +let $q= +SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4); +--echo # expected for t2 and for t1: range access +eval explain $q; +eval $q; + +let $q= +SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10; +--echo # no range access expected for t1 +eval explain $q; +eval $q; + +let $q= +SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id; +--echo # expected for t1: range access +eval explain $q; +eval $q; + +let $q= +SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id +LIMIT 10; +--echo # no range access expected for t1 +eval explain $q; +eval $q; + +drop index id on t2; + +let $q= +SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id; +--echo # expected for t1: range access +eval explain $q; +eval $q; + +let $q= +SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL; +--echo # expected impossible where after reading const tables +eval explain $q; +eval $q; + +let $q= +SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL; +--echo # expected impossible where after reading const tables +eval explain $q; +eval $q; + +drop index t1_subset_id on t1; +alter table t1 add column m int not null default 0; +alter table t1 add index idx(m,subset_id); +alter table t2 add index (id); +update t1 set m = id mod 2; +analyze table t1,t2; + +let $q= +SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ; +--echo # expected for t1: range access by idx (keylen=9) +eval explain $q; +eval $q; + + +drop view v1,v2; +drop table t1,t2; + +create table t1 ( + id int NOT NULL, + subset_id int DEFAULT NULL, + KEY key1(id, subset_id), + KEY t1_subset_id (subset_id) +); + +create table t2 ( + id int NOT NULL, + col int NOT NULL, + key (id) +); + +insert into t1 select 1,a from one_k limit 5; +insert into t1 select 1,NULL from one_k limit 495; +insert into t2 select a,a from one_k; + +analyze table t1,t2; + +let $q= +SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id; +--echo # expected for t1 :range access by index key1 +--echo # rows 4 instead of 500 +eval explain $q; +eval $q; + +drop table t1,t2; + +create table t1 (id int unsigned,col int, KEY key1(id)); +create table t2 (id int unsigned,col int DEFAULT NULL,key (id)); +insert into t1 select a,2 from one_k limit 50; +insert into t1 select NULL,2 from one_k limit 450; +insert into t2 select a,a from one_k; +insert into t2 select a,a from one_k; + +analyze table t1,t2; + +let $q= +SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10; +--echo # using key1 for range access on t1 and also using index for sorting, +--echo # no filesort, rows should be 75 not 500 +eval explain $q; +eval $q; + +drop table t1,t2; + +drop table ten,one_k; +set @@optimizer_switch= @save_optimizer_switch; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 6b5bf33239f..d484d512564 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3036,4 +3036,289 @@ drop table t1; # # End of 10.2 tests # +# +# MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer +# +set @save_optimizer_switch= @@optimizer_switch; +set @@optimizer_switch='not_null_range_scan=on'; +create table ten(a int); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k(a int); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; +create table t1 ( +id int NOT NULL, +subset_id int DEFAULT NULL, +PRIMARY KEY (id), +KEY t1_subset_id (subset_id)); +create table t2 ( +id int, +col int NOT NULL, +key (id) +); +insert into t1 select a,a from one_k limit 5; +insert into t1 select a+5,NULL from one_k limit 995; +insert into t2 select a,a from one_k; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status Table is already up to date +# expected for t1: range access and rows = 4 (not 1000) +explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition; Rowid-ordered scan +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 +SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id; +id subset_id id col +0 0 0 0 +1 1 1 1 +2 2 2 2 +3 3 3 3 +4 4 4 4 +# with a subquery +# expected the same plan as above +explain SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition; Rowid-ordered scan +1 PRIMARY t2 ref id id 5 test.t1.subset_id 1 Using index; FirstMatch(t1) +SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2); +id subset_id +0 0 +1 1 +2 2 +3 3 +4 4 +# non-mergable subquery +# expected for t1: range access and rows = 4 (not 1000) +explain SELECT * FROM t1 +WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition; Rowid-ordered scan +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.subset_id 1 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 1000 Using temporary +SELECT * FROM t1 +WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col); +id subset_id +0 0 +1 1 +2 2 +3 3 +4 4 +create view v1 as SELECT t2.id FROM t2; +create view v2 as SELECT t2.id FROM t2 group by t2.col; +# with mergeable view +# expected for t1: range access and rows = 4 (not 1000) +explain SELECT * FROM t1, v1 where t1.subset_id=v1.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition; Rowid-ordered scan +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 Using index +SELECT * FROM t1, v1 where t1.subset_id=v1.id; +id subset_id id +0 0 0 +1 1 1 +2 2 2 +3 3 3 +4 4 4 +# with non-mergeable view +# expected for t1: range access and rows = 4 (not 1000) +explain SELECT * FROM t1, v2 where t1.subset_id=v2.id; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition; Rowid-ordered scan +1 PRIMARY <derived2> ref key0 key0 5 test.t1.subset_id 10 +2 DERIVED t2 ALL NULL NULL NULL NULL 1000 Using temporary; Using filesort +SELECT * FROM t1, v2 where t1.subset_id=v2.id; +id subset_id id +0 0 0 +1 1 1 +2 2 2 +3 3 3 +4 4 4 +# expected for t2 and for t1: range access +explain SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range id id 5 NULL 3 Using index condition; Rowid-ordered scan +1 SIMPLE t1 range t1_subset_id t1_subset_id 5 NULL 4 Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) +SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4); +id col id subset_id +0 0 0 0 +2 2 0 0 +4 4 0 0 +0 0 1 1 +2 2 1 1 +4 4 1 1 +0 0 2 2 +2 2 2 2 +4 4 2 2 +0 0 3 3 +2 2 3 3 +4 4 3 3 +0 0 4 4 +2 2 4 4 +4 4 4 4 +# no range access expected for t1 +explain SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 Using where +SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10; +id subset_id id col +0 0 0 0 +1 1 1 1 +2 2 2 2 +3 3 3 3 +4 4 4 4 +5 NULL NULL NULL +6 NULL NULL NULL +7 NULL NULL NULL +8 NULL NULL NULL +9 NULL NULL NULL +# expected for t1: range access +explain SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE ten ALL NULL NULL NULL NULL 10 +1 SIMPLE t1 range t1_subset_id t1_subset_id 5 NULL 4 Using where; Rowid-ordered scan +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 Using where +SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id; +a id subset_id id col +0 0 0 0 0 +1 1 1 1 1 +2 2 2 2 2 +3 3 3 3 3 +4 4 4 4 4 +5 NULL NULL NULL NULL +6 NULL NULL NULL NULL +7 NULL NULL NULL NULL +8 NULL NULL NULL NULL +9 NULL NULL NULL NULL +# no range access expected for t1 +explain SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id +LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 Using where +1 SIMPLE ten ALL NULL NULL NULL NULL 10 Using where +SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id +LIMIT 10; +id subset_id id col a +0 0 0 0 0 +1 1 1 1 1 +2 2 2 2 2 +3 3 3 3 3 +4 4 4 4 4 +5 NULL NULL NULL NULL +6 NULL NULL NULL NULL +7 NULL NULL NULL NULL +8 NULL NULL NULL NULL +9 NULL NULL NULL NULL +drop index id on t2; +# expected for t1: range access +explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range t1_subset_id t1_subset_id 5 NULL 4 Using index condition; Rowid-ordered scan +1 SIMPLE t2 ALL NULL NULL NULL NULL 1000 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id; +id subset_id id col +0 0 0 0 +1 1 1 1 +2 2 2 2 +3 3 3 3 +4 4 4 4 +# expected impossible where after reading const tables +explain SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL; +id subset_id id col +# expected impossible where after reading const tables +explain SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL; +id subset_id id col +drop index t1_subset_id on t1; +alter table t1 add column m int not null default 0; +alter table t1 add index idx(m,subset_id); +alter table t2 add index (id); +update t1 set m = id mod 2; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status Table is already up to date +# expected for t1: range access by idx (keylen=9) +explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx idx 9 NULL 4 Using index condition; Rowid-ordered scan +1 SIMPLE t2 ref id id 5 test.t1.subset_id 1 +SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ; +id subset_id m id col +0 0 0 0 0 +2 2 0 2 2 +4 4 0 4 4 +drop view v1,v2; +drop table t1,t2; +create table t1 ( +id int NOT NULL, +subset_id int DEFAULT NULL, +KEY key1(id, subset_id), +KEY t1_subset_id (subset_id) +); +create table t2 ( +id int NOT NULL, +col int NOT NULL, +key (id) +); +insert into t1 select 1,a from one_k limit 5; +insert into t1 select 1,NULL from one_k limit 495; +insert into t2 select a,a from one_k; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status Table is already up to date +# expected for t1 :range access by index key1 +# rows 4 instead of 500 +explain SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range key1,t1_subset_id key1 9 NULL 4 Using where; Using index +1 SIMPLE t2 ref id id 4 test.t1.subset_id 1 +SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id; +id subset_id id col +1 0 0 0 +1 1 1 1 +1 2 2 2 +1 3 3 3 +1 4 4 4 +drop table t1,t2; +create table t1 (id int unsigned,col int, KEY key1(id)); +create table t2 (id int unsigned,col int DEFAULT NULL,key (id)); +insert into t1 select a,2 from one_k limit 50; +insert into t1 select NULL,2 from one_k limit 450; +insert into t2 select a,a from one_k; +insert into t2 select a,a from one_k; +analyze table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +# using key1 for range access on t1 and also using index for sorting, +# no filesort, rows should be 75 not 500 +explain SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range key1 key1 5 NULL 75 Using index condition; Using where +1 SIMPLE t2 ref id id 5 test.t1.id 2 +SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10; +id col id col +0 2 0 0 +0 2 0 0 +1 2 1 1 +1 2 1 1 +2 2 2 2 +2 2 2 2 +3 2 3 3 +3 2 3 3 +4 2 4 4 +4 2 4 4 +drop table t1,t2; +drop table ten,one_k; +set @@optimizer_switch= @save_optimizer_switch; +# +# End of 10.3 tests +# set optimizer_switch=@mrr_icp_extra_tmp; diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result index 87c837986ac..ba40512ea76 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -1,63 +1,63 @@ SET @start_global_value = @@global.optimizer_switch; SELECT @start_global_value; @start_global_value -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off set global optimizer_switch=10; set session optimizer_switch=5; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off set global optimizer_switch="index_merge_sort_union=on"; set session optimizer_switch="index_merge=off"; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off set session optimizer_switch="default"; select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off set optimizer_switch = replace(@@optimizer_switch, '=off', '=on'); Warnings: Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=on set global optimizer_switch=1.1; ERROR 42000: Incorrect argument type to variable 'optimizer_switch' set global optimizer_switch=1e1; @@ -69,4 +69,4 @@ ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'foobar' SET @@global.optimizer_switch = @start_global_value; SELECT @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 89e5fef60e6..714b55c53f7 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -2715,17 +2715,17 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SWITCH -SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on -GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off +GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +DEFAULT_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off VARIABLE_SCOPE SESSION VARIABLE_TYPE FLAGSET VARIABLE_COMMENT Fine-tune the optimizer behavior NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,default +ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,not_null_range_scan,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_USE_CONDITION_SELECTIVITY diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index ca875e7f644..dd9d9eb14f0 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2925,17 +2925,17 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SWITCH -SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on -GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off +GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +DEFAULT_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off VARIABLE_SCOPE SESSION VARIABLE_TYPE FLAGSET VARIABLE_COMMENT Fine-tune the optimizer behavior NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,default +ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,not_null_range_scan,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_USE_CONDITION_SELECTIVITY diff --git a/sql/item.cc b/sql/item.cc index 3584230fb2a..9c956116f72 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3556,6 +3556,16 @@ table_map Item_field::all_used_tables() const } +bool Item_field::find_not_null_fields(table_map allowed) +{ + if (field->table->const_table) + return false; + if (!get_depended_from() && field->real_maybe_null()) + bitmap_set_bit(&field->table->tmp_set, field->field_index); + return false; +} + + /* @Note thd->fatal_error can be set in case of OOM */ diff --git a/sql/item.h b/sql/item.h index 2adc111db03..c2dbdb135f1 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1818,6 +1818,44 @@ public: virtual bool set_fields_as_dependent_processor(void *arg) { return 0; } /*============== End of Item processor list ======================*/ + /* + Given a condition P from the WHERE clause or from an ON expression of + the processed SELECT S and a set of join tables from S marked in the + parameter 'allowed'={T} a call of P->find_not_null_fields({T}) has to + find the set fields {F} of the tables from 'allowed' such that: + - each field from {F} is declared as nullable + - each record of table t from {T} that contains NULL as the value for at + at least one field from {F} can be ignored when building the result set + for S + It is assumed here that the condition P is conjunctive and all its column + references belong to T. + + Examples: + CREATE TABLE t1 (a int, b int); + CREATE TABLE t2 (a int, b int); + + SELECT * FROM t1,t2 WHERE t1.a=t2.a and t1.b > 5; + A call of find_not_null_fields() for the whole WHERE condition and {t1,t2} + should find {t1.a,t1.b,t2.a} + + SELECT * FROM t1 LEFT JOIN ON (t1.a=t2.a and t2.a > t2.b); + A call of find_not_null_fields() for the ON expression and {t2} + should find {t2.a,t2.b} + + The function returns TRUE if it succeeds to prove that all records of + a table from {T} can be ignored. Otherwise it always returns FALSE. + + Example: + SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t2.a IS NULL; + A call of find_not_null_fields() for the WHERE condition and {t1,t2} + will return TRUE. + + It is assumed that the implementation of this virtual function saves + the info on the found set of fields in the structures associates with + tables from {T}. + */ + virtual bool find_not_null_fields(table_map allowed) { return false; } + virtual Item *get_copy(THD *thd)=0; bool cache_const_expr_analyzer(uchar **arg); @@ -3079,6 +3117,7 @@ public: bool is_result_field() { return false; } void save_in_result_field(bool no_conversions); Item *get_tmp_table_item(THD *thd); + bool find_not_null_fields(table_map allowed); bool collect_item_field_processor(void * arg); bool add_field_to_set_processor(void * arg); bool find_item_in_field_list_processor(void *arg); @@ -4858,6 +4897,10 @@ public: { return depended_from ? 0 : (*ref)->not_null_tables(); } + bool find_not_null_fields(table_map allowed) + { + return depended_from ? false : (*ref)->find_not_null_fields(allowed); + } void save_in_result_field(bool no_conversions) { (*ref)->save_in_field(result_field, no_conversions); diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 49e0a9466c0..c4b43a7459d 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1192,6 +1192,15 @@ bool Item_in_optimizer::eval_not_null_tables(void *opt_arg) } +bool Item_in_optimizer::find_not_null_fields(table_map allowed) +{ + if (!(~allowed & used_tables()) && is_top_level_item()) + { + return args[0]->find_not_null_fields(allowed); + } + return false; +} + void Item_in_optimizer::print(String *str, enum_query_type query_type) { restore_first_argument(); @@ -2035,7 +2044,17 @@ bool Item_func_between::eval_not_null_tables(void *opt_arg) (args[1]->not_null_tables() & args[2]->not_null_tables())); return 0; -} +} + + +bool Item_func_between::find_not_null_fields(table_map allowed) +{ + if (negated || !is_top_level_item() || (~allowed & used_tables())) + return false; + return args[0]->find_not_null_fields(allowed) || + args[1]->find_not_null_fields(allowed) || + args[3]->find_not_null_fields(allowed); +} bool Item_func_between::count_sargable_conds(void *arg) @@ -4151,6 +4170,15 @@ Item_func_in::eval_not_null_tables(void *opt_arg) } +bool +Item_func_in::find_not_null_fields(table_map allowed) +{ + if (negated || !is_top_level_item() || (~allowed & used_tables())) + return 0; + return args[0]->find_not_null_fields(allowed); +} + + void Item_func_in::fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge) { @@ -4705,6 +4733,82 @@ Item_cond::eval_not_null_tables(void *opt_arg) } +/** + @note + This implementation of the virtual function find_not_null_fields() + infers null-rejectedness if fields from tables marked in 'allowed' from + this condition. + Currently only top level AND conjuncts that are not disjunctions are used + for the inference. Usage of any top level and-or formula with l OR levels + would require a stack of bitmaps for fields of the height h=2*l+1 So we + would have to allocate h-1 additional field bitmaps for each table marked + in 'allowed'. +*/ + +bool +Item_cond::find_not_null_fields(table_map allowed) +{ + Item *item; + bool is_and_cond= functype() == Item_func::COND_AND_FUNC; + if (!is_and_cond) + { + /* Now only fields of top AND level conjuncts are taken into account */ + return false; + } + uint isnull_func_cnt= 0; + List_iterator<Item> li(list); + while ((item=li++)) + { + bool is_mult_eq= item->type() == Item::FUNC_ITEM && + ((Item_func *) item)->functype() == Item_func::MULT_EQUAL_FUNC; + if (is_mult_eq) + { + if (!item->find_not_null_fields(allowed)) + continue; + } + + if (~allowed & item->used_tables()) + continue; + + /* It is assumed that all constant conjuncts are already eliminated */ + + /* + First infer null-rejectedness of fields from all conjuncts but + IS NULL predicates + */ + bool isnull_func= item->type() == Item::FUNC_ITEM && + ((Item_func *) item)->functype() == Item_func::ISNULL_FUNC; + if (isnull_func) + { + isnull_func_cnt++; + continue; + } + if (!item->find_not_null_fields(allowed)) + continue; + } + + /* Now try no get contradictions using IS NULL conjuncts */ + if (isnull_func_cnt) + { + li.rewind(); + while ((item=li++) && isnull_func_cnt) + { + if (~allowed & item->used_tables()) + continue; + + bool isnull_func= item->type() == Item::FUNC_ITEM && + ((Item_func *) item)->functype() == Item_func::ISNULL_FUNC; + if (isnull_func) + { + if (item->find_not_null_fields(allowed)) + return true; + isnull_func_cnt--; + } + } + } + return false; +} + void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge) { @@ -5148,6 +5252,19 @@ longlong Item_func_isnull::val_int() } +bool Item_func_isnull::find_not_null_fields(table_map allowed) +{ + if (!(~allowed & used_tables()) && + args[0]->real_item()->type() == Item::FIELD_ITEM) + { + Field *field= ((Item_field *)(args[0]->real_item()))->field; + if (bitmap_is_set(&field->table->tmp_set, field->field_index)) + return true; + } + return false; +} + + void Item_func_isnull::print(String *str, enum_query_type query_type) { if (const_item() && !args[0]->maybe_null && @@ -6734,6 +6851,48 @@ void Item_equal::update_used_tables() } +/** + @note + This multiple equality can contains elements belonging not to tables {T} + marked in 'allowed' . So we can ascertain null-rejectedness of field f + belonging to table t from {T} only if one of the following equality + predicate can be extracted from this multiple equality: + - f=const + - f=f' where f' is a field of some table from {T} +*/ + +bool Item_equal::find_not_null_fields(table_map allowed) +{ + if (!(allowed & used_tables())) + return false; + bool checked= false; + Item_equal_fields_iterator it(*this); + Item *item; + while ((item= it++)) + { + if (~allowed & item->used_tables()) + continue; + if ((with_const || checked) && !item->find_not_null_fields(allowed)) + continue; + Item_equal_fields_iterator it1(*this); + Item *item1; + while ((item1= it1++) && item1 != item) + { + if (~allowed & item1->used_tables()) + continue; + if (!item->find_not_null_fields(allowed) && + !item1->find_not_null_fields(allowed)) + { + checked= true; + break; + } + } + } + return false; +} + + + bool Item_equal::count_sargable_conds(void *arg) { SELECT_LEX *sel= (SELECT_LEX *) arg; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 7d99cbdb936..8f01e4fa13f 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -284,6 +284,7 @@ public: Item_func_truth(thd, a, true, false) {} ~Item_func_isnottrue() {} virtual const char* func_name() const { return "isnottrue"; } + bool find_not_null_fields(table_map allowed) { return false; } Item *get_copy(THD *thd) { return get_item_copy<Item_func_isnottrue>(thd, this); } }; @@ -315,6 +316,7 @@ public: Item_func_truth(thd, a, false, false) {} ~Item_func_isnotfalse() {} virtual const char* func_name() const { return "isnotfalse"; } + bool find_not_null_fields(table_map allowed) { return false; } Item *get_copy(THD *thd) { return get_item_copy<Item_func_isnotfalse>(thd, this); } }; @@ -376,6 +378,7 @@ public: virtual void get_cache_parameters(List<Item> ¶meters); bool is_top_level_item(); bool eval_not_null_tables(void *opt_arg); + bool find_not_null_fields(table_map allowed); void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge); bool invisible_mode(); void reset_cache() { cache= NULL; } @@ -571,6 +574,7 @@ public: void print(String *str, enum_query_type query_type) { Item_func::print_op(str, query_type); } longlong val_int(); + bool find_not_null_fields(table_map allowed) { return false; } Item *neg_transformer(THD *thd); Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond) { @@ -592,6 +596,7 @@ public: longlong val_int(); enum Functype functype() const { return NOT_FUNC; } const char *func_name() const { return "not"; } + bool find_not_null_fields(table_map allowed) { return false; } enum precedence precedence() const { return BANG_PRECEDENCE; } Item *neg_transformer(THD *thd); bool fix_fields(THD *, Item **); @@ -736,6 +741,7 @@ public: longlong val_int(); bool fix_length_and_dec(); table_map not_null_tables() const { return 0; } + bool find_not_null_fields(table_map allowed) { return false; } enum Functype functype() const { return EQUAL_FUNC; } enum Functype rev_functype() const { return EQUAL_FUNC; } cond_result eq_cmp_result() const { return COND_TRUE; } @@ -912,6 +918,7 @@ public: bool fix_length_and_dec_numeric(THD *); virtual void print(String *str, enum_query_type query_type); bool eval_not_null_tables(void *opt_arg); + bool find_not_null_fields(table_map allowed); void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge); bool count_sargable_conds(void *arg); void add_key_fields(JOIN *join, KEY_FIELD **key_fields, @@ -2396,6 +2403,7 @@ public: const char *func_name() const { return "in"; } enum precedence precedence() const { return CMP_PRECEDENCE; } bool eval_not_null_tables(void *opt_arg); + bool find_not_null_fields(table_map allowed); void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge); bool count_sargable_conds(void *arg); Item *get_copy(THD *thd) @@ -2533,6 +2541,7 @@ public: COND *remove_eq_conds(THD *thd, Item::cond_result *cond_value, bool top_level); table_map not_null_tables() const { return 0; } + bool find_not_null_fields(table_map allowed); Item *neg_transformer(THD *thd); Item *get_copy(THD *thd) { return get_item_copy<Item_func_isnull>(thd, this); } @@ -2953,6 +2962,7 @@ public: Item *compile(THD *thd, Item_analyzer analyzer, uchar **arg_p, Item_transformer transformer, uchar *arg_t); bool eval_not_null_tables(void *opt_arg); + bool find_not_null_fields(table_map allowed); Item *build_clone(THD *thd); bool excl_dep_on_table(table_map tab_map); bool excl_dep_on_grouping_fields(st_select_lex *sel); @@ -3119,6 +3129,7 @@ public: eval_item= NULL; } void update_used_tables(); + bool find_not_null_fields(table_map allowed); COND *build_equal_items(THD *thd, COND_EQUAL *inherited, bool link_item_fields, COND_EQUAL **cond_equal_ref); diff --git a/sql/item_func.cc b/sql/item_func.cc index a030d2f8681..41bbd2ea8f0 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -403,6 +403,25 @@ Item_func::eval_not_null_tables(void *opt_arg) } +bool +Item_func::find_not_null_fields(table_map allowed) +{ + if (~allowed & used_tables()) + return false; + + Item **arg,**arg_end; + if (arg_count) + { + for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++) + { + if (!(*arg)->find_not_null_fields(allowed)) + continue; + } + } + return false; +} + + void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge) { diff --git a/sql/item_func.h b/sql/item_func.h index 6345dd41f71..325a79600e9 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -210,6 +210,7 @@ public: void traverse_cond(Cond_traverser traverser, void * arg, traverse_order order); bool eval_not_null_tables(void *opt_arg); + bool find_not_null_fields(table_map allowed); // bool is_expensive_processor(void *arg); // virtual bool is_expensive() { return 0; } inline void raise_numeric_overflow(const char *type_name) @@ -671,6 +672,7 @@ public: Item_func_case_expression(THD *thd, List<Item> &list): Item_func_hybrid_field_type(thd, list) { } + bool find_not_null_fields(table_map allowed) { return false; } }; @@ -1759,6 +1761,10 @@ public: not_null_tables_cache= 0; return false; } + bool find_not_null_fields(table_map allowed) + { + return false; + } Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond) { return this; } bool const_item() const { return true; } @@ -2119,6 +2125,10 @@ public: not_null_tables_cache= 0; return 0; } + bool find_not_null_fields(table_map allowed) + { + return false; + } bool is_expensive() { return 1; } virtual void print(String *str, enum_query_type query_type); bool check_vcol_func_processor(void *arg) @@ -2711,6 +2721,10 @@ public: not_null_tables_cache= 0; return 0; } + bool find_not_null_fields(table_map allowed) + { + return false; + } bool fix_fields(THD *thd, Item **ref); bool eq(const Item *, bool binary_cmp) const; /* The following should be safe, even if we compare doubles */ @@ -3000,6 +3014,10 @@ public: not_null_tables_cache= 0; return 0; } + bool find_not_null_fields(table_map allowed) + { + return false; + } }; @@ -3103,6 +3121,10 @@ public: not_null_tables_cache= 0; return 0; } + bool find_not_null_fields(table_map allowed) + { + return false; + } bool const_item() const { return 0; } void evaluate_sideeffects(); void update_used_tables() diff --git a/sql/item_row.cc b/sql/item_row.cc index 70a9fe50c16..0b55ae5d022 100644 --- a/sql/item_row.cc +++ b/sql/item_row.cc @@ -87,6 +87,25 @@ Item_row::eval_not_null_tables(void *opt_arg) } +bool +Item_row::find_not_null_fields(table_map allowed) +{ + if (~allowed & used_tables()) + return false; + + Item **arg,**arg_end; + if (arg_count) + { + for (arg= args, arg_end= args + arg_count; arg != arg_end ; arg++) + { + if (!(*arg)->find_not_null_fields(allowed)) + continue; + } + } + return false; +} + + void Item_row::cleanup() { DBUG_ENTER("Item_row::cleanup"); diff --git a/sql/item_row.h b/sql/item_row.h index 0d6a6db2e0e..0efa29f3d79 100644 --- a/sql/item_row.h +++ b/sql/item_row.h @@ -110,6 +110,7 @@ public: } Item *transform(THD *thd, Item_transformer transformer, uchar *arg); bool eval_not_null_tables(void *opt_arg); + bool find_not_null_fields(table_map allowed); uint cols() const { return arg_count; } Item* element_index(uint i) { return args[i]; } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index ec7b3dbbd7a..91103a5cf34 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2399,6 +2399,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, { uint idx; double scan_time; + Item *notnull_cond= NULL; DBUG_ENTER("SQL_SELECT::test_quick_select"); DBUG_PRINT("enter",("keys_to_use: %lu prev_tables: %lu const_tables: %lu", (ulong) keys_to_use.to_ulonglong(), (ulong) prev_tables, @@ -2412,6 +2413,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, if (keys_to_use.is_clear_all() || head->is_filled_at_execution()) DBUG_RETURN(0); records= head->stat_records(); + notnull_cond= head->notnull_cond; if (!records) records++; /* purecov: inspected */ scan_time= (double) records / TIME_FOR_COMPARE + 1; @@ -2419,7 +2421,10 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, if (head->force_index) scan_time= read_time= DBL_MAX; if (limit < records) + { read_time= (double) records + scan_time + 1; // Force to use index + notnull_cond= NULL; + } possible_keys.clear_all(); @@ -2431,6 +2436,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, uchar buff[STACK_BUFF_ALLOC]; MEM_ROOT alloc; SEL_TREE *tree= NULL; + SEL_TREE *notnull_cond_tree= NULL; KEY_PART *key_parts; KEY *key_info; PARAM param; @@ -2539,6 +2545,9 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, TRP_GROUP_MIN_MAX *group_trp; double best_read_time= read_time; + if (notnull_cond) + notnull_cond_tree= notnull_cond->get_mm_tree(¶m, ¬null_cond); + if (cond) { if ((tree= cond->get_mm_tree(¶m, &cond))) @@ -2557,6 +2566,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, tree= NULL; } } + tree= tree_and(¶m, tree, notnull_cond_tree); /* Try to construct a QUICK_GROUP_MIN_MAX_SELECT. diff --git a/sql/sql_priv.h b/sql/sql_priv.h index 2f37e1106f0..ec7b2ccbdac 100644 --- a/sql/sql_priv.h +++ b/sql/sql_priv.h @@ -222,6 +222,7 @@ #define OPTIMIZER_SWITCH_ORDERBY_EQ_PROP (1ULL << 29) #define OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED (1ULL << 30) #define OPTIMIZER_SWITCH_SPLIT_MATERIALIZED (1ULL << 31) +#define OPTIMIZER_SWITCH_NOT_NULL_RANGE_SCAN (1ULL << 32) #define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0fbc4bac7d8..dae2cd76308 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -295,6 +295,14 @@ void set_postjoin_aggr_write_func(JOIN_TAB *tab); static Item **get_sargable_cond(JOIN *join, TABLE *table); +static +bool build_notnull_conds_for_range_scans(JOIN *join, COND *cond, + table_map allowed); +static +void build_notnull_conds_for_inner_nest_of_outer_join(JOIN *join, + TABLE_LIST *nest_tbl); + + #ifndef DBUG_OFF /* @@ -4907,6 +4915,9 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, } } + join->join_tab= stat; + join->make_notnull_conds_for_range_scans(); + /* Calc how many (possible) matched records in each table */ for (s=stat ; s < stat_end ; s++) @@ -27572,6 +27583,280 @@ Item *remove_pushed_top_conjuncts(THD *thd, Item *cond) return cond; } + +/** + @brief + Construct not null conditions for provingly not nullable fields + + @details + For each non-constant joined table the function creates a conjunction + of IS NOT NULL predicates containing a predicate for each field used + in the WHERE clause or an OR expression such that + - is declared as nullable + - for which it can proved be that it is null-rejected + - is a part of some index. + This conjunction could be anded with either the WHERE condition or with + an ON expression and the modified join query would produce the same + result set as the original one. + If a conjunction of IS NOT NULL predicates is constructed for an inner + table of an outer join OJ that is not an inner table of embedded outer + joins then it is to be anded with the ON expression of OJ. + The constructed conjunctions of IS NOT NULL predicates are attached + to the corresponding tables. They used for range analysis complementary + to other sargable range conditions. + + @note + Let f be a field of the joined table t. In the context of the upper + paragraph field f is called null-rejected if any the following holds: + + - t is a table of a top inner join and a conjunctive formula that rejects + rows with null values for f can be extracted from the WHERE condition + + - t is an outer table of a top outer join operation and a conjunctive + formula over the outer tables of the outer join that rejects rows with + null values for can be extracted from the WHERE condition + + - t is an outer table of a non-top outer join operation and a conjunctive + formula over the outer tables of the outer join that rejects rows with + null values for f can be extracted from the ON expression of the + embedding outer join + + - the joined table is an inner table of a outer join operation and + a conjunctive formula over inner tables of the outer join that rejects + rows with null values for f can be extracted from the ON expression of + the outer join operation. + + It is assumed above that all inner join nests have been eliminated and + that all possible conversions of outer joins into inner joins have been + already done. +*/ + +void JOIN::make_notnull_conds_for_range_scans() +{ + DBUG_ENTER("JOIN::make_notnull_conds_for_range_scans"); + + + if (impossible_where || + !optimizer_flag(thd, OPTIMIZER_SWITCH_NOT_NULL_RANGE_SCAN)) + { + /* Complementary range analysis is not needed */ + DBUG_VOID_RETURN; + } + + if (conds && build_notnull_conds_for_range_scans(this, conds, + conds->used_tables())) + { + Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1); + if (false_cond) + { + /* + Found a IS NULL conjunctive predicate for a null-rejected field + in the WHERE clause + */ + conds= false_cond; + cond_equal= 0; + impossible_where= true; + } + DBUG_VOID_RETURN; + } + + List_iterator<TABLE_LIST> li(*join_list); + TABLE_LIST *tbl; + while ((tbl= li++)) + { + if (tbl->on_expr) + { + if (tbl->nested_join) + { + build_notnull_conds_for_inner_nest_of_outer_join(this, tbl); + } + else if (build_notnull_conds_for_range_scans(this, tbl->on_expr, + tbl->table->map)) + { + /* + Found a IS NULL conjunctive predicate for a null-rejected field + of the inner table of an outer join with ON expression tbl->on_expr + */ + Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1); + if (false_cond) + tbl->on_expr= false_cond; + } + } + } + DBUG_VOID_RETURN; +} + + +/** + @brief + Build not null conditions for range scans of given join tables + + @param join the join for whose tables not null conditions are to be built + @param cond the condition from which not null predicates are to be inferred + @param allowed the bit map of join tables to be taken into account + + @details + For each join table t from the 'allowed' set of tables the function finds + all fields whose null-rejectedness can be inferred from null-rejectedness + of the condition cond. For each found field f from table t such that it + participates at least in one index on table t a NOT NULL predicate is + constructed and a conjunction of all such predicates is attached to t. + If when looking for null-rejecting fields of t it is discovered one of its + fields has to be null-rejected and there is IS NULL conjunctive top level + predicate for this field then the function immediately returns true. + The function uses the bitmap TABLE::tmp_set to mark found null-rejected + fields of table t. + + @note + Currently only top level conjuncts without disjunctive sub-formulas are + are taken into account when looking for null-rejected fields. + + @retval + true if a contradiction is inferred + false otherwise +*/ + +static +bool build_notnull_conds_for_range_scans(JOIN *join, Item *cond, + table_map allowed) +{ + THD *thd= join->thd; + + DBUG_ENTER("build_notnull_conds_for_range_scans"); + + for (JOIN_TAB *s= join->join_tab + join->const_tables ; + s < join->join_tab + join->table_count ; s++) + { + /* Clear all needed bitmaps to mark found fields */ + if (allowed & s->table->map) + bitmap_clear_all(&s->table->tmp_set); + } + + /* + Find all null-rejected fields assuming that cond is null-rejected and + only formulas over tables from 'allowed' are to be taken into account + */ + if (cond->find_not_null_fields(allowed)) + DBUG_RETURN(true); + + /* + For each table t from 'allowed' build a conjunction of NOT NULL predicates + constructed for all found fields if they are included in some indexes. + If the construction of the conjunction succeeds attach the formula to + t->table->notnull_cond. The condition will be used to look for complementary + range scans. + */ + for (JOIN_TAB *s= join->join_tab + join->const_tables ; + s < join->join_tab + join->table_count ; s++) + { + TABLE *tab= s->table; + List<Item> notnull_list; + Item *notnull_cond= 0; + + if (!(allowed & tab->map)) + continue; + + for (Field** field_ptr= tab->field; *field_ptr; field_ptr++) + { + Field *field= *field_ptr; + if (field->part_of_key.is_clear_all()) + continue; + if (!bitmap_is_set(&tab->tmp_set, field->field_index)) + continue; + Item_field *field_item= new (thd->mem_root) Item_field(thd, field); + if (!field_item) + continue; + Item *isnotnull_item= + new (thd->mem_root) Item_func_isnotnull(thd, field_item); + if (!isnotnull_item) + continue; + if (notnull_list.push_back(isnotnull_item, thd->mem_root)) + continue; + s->const_keys.merge(field->part_of_key); + } + + switch (notnull_list.elements) { + case 0: + break; + case 1: + notnull_cond= notnull_list.head(); + break; + default: + notnull_cond= + new (thd->mem_root) Item_cond_and(thd, notnull_list); + } + if (notnull_cond && !notnull_cond->fix_fields(thd, 0)) + { + tab->notnull_cond= notnull_cond; + } + } + DBUG_RETURN(false); +} + + +/** + @brief + Build not null conditions for inner nest tables of an outer join + + @param join the join for whose table nest not null conditions are to be built + @param nest_tbl the nest of the inner tables of an outer join + + @details + The function assumes that nest_tbl is the nest of the inner tables of an + outer join and so an ON expression for this outer join is attached to + nest_tbl. + The function selects the tables of the nest_tbl that are not inner tables of + embedded outer joins and then it calls build_notnull_conds_for_range_scans() + for nest_tbl->on_expr and the bitmap for the selected tables. This call + finds all fields belonging to the selected tables whose null-rejectedness + can be inferred from the null-rejectedness of nest_tbl->on_expr. After this + the function recursively finds all null_rejected fields for the remaining + tables from the nest of nest_tbl. +*/ + +static +void build_notnull_conds_for_inner_nest_of_outer_join(JOIN *join, + TABLE_LIST *nest_tbl) +{ + TABLE_LIST *tbl; + table_map used_tables= 0; + THD *thd= join->thd; + List_iterator<TABLE_LIST> li(nest_tbl->nested_join->join_list); + + while ((tbl= li++)) + { + if (!tbl->on_expr) + used_tables|= tbl->table->map; + } + if (used_tables && + build_notnull_conds_for_range_scans(join, nest_tbl->on_expr, used_tables)) + { + Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1); + if (false_cond) + nest_tbl->on_expr= false_cond; + } + + li.rewind(); + while ((tbl= li++)) + { + if (tbl->on_expr) + { + if (tbl->nested_join) + { + build_notnull_conds_for_inner_nest_of_outer_join(join, tbl); + } + else if (build_notnull_conds_for_range_scans(join, tbl->on_expr, + tbl->table->map)) + { + Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1); + if (false_cond) + tbl->on_expr= false_cond; + } + } + } +} + + /** @} (end of group Query_Optimizer) */ diff --git a/sql/sql_select.h b/sql/sql_select.h index dd823a7d7df..2b40a414d57 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1749,6 +1749,7 @@ public: void add_keyuses_for_splitting(); bool inject_best_splitting_cond(table_map remaining_tables); bool fix_all_splittings_in_plan(); + void make_notnull_conds_for_range_scans(); bool transform_in_predicates_into_in_subq(THD *thd); private: @@ -2343,7 +2344,7 @@ Item_equal *find_item_equal(COND_EQUAL *cond_equal, Field *field, extern bool test_if_ref(Item *, Item_field *left_item,Item *right_item); -inline bool optimizer_flag(THD *thd, uint flag) +inline bool optimizer_flag(THD *thd, ulonglong flag) { return (thd->variables.optimizer_switch & flag); } diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index b50f697b489..58b6da53e6c 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2544,6 +2544,7 @@ export const char *optimizer_switch_names[]= "orderby_uses_equalities", "condition_pushdown_for_derived", "split_materialized", + "not_null_range_scan", "default", NullS }; diff --git a/sql/table.cc b/sql/table.cc index 4805017972c..34fb6d5feac 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4658,6 +4658,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) (*f_ptr)->cond_selectivity= 1.0; } + notnull_cond= 0; + DBUG_ASSERT(!file->keyread_enabled()); restore_record(this, s->default_values); diff --git a/sql/table.h b/sql/table.h index fa6cb707efb..47096aacb67 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1344,6 +1344,13 @@ public: SplM_opt_info *spl_opt_info; key_map keys_usable_for_splitting; + /* + Conjunction of the predicates of the form IS NOT NULL(f) where f refers to + a column of this TABLE such that they can be inferred from the condition + of the WHERE clause or from some ON expression of the processed select + and can be useful for range optimizer. + */ + Item *notnull_cond; inline void reset() { bzero((void*)this, sizeof(*this)); } void init(THD *thd, TABLE_LIST *tl); diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result index a80e1664663..5d471d2228d 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result @@ -1,7 +1,7 @@ drop table if exists t; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off create table t (id int not null, x int not null, y int not null, primary key(id), key(x)) engine=innodb; insert into t values (0,0,0),(1,1,1),(2,2,2),(3,2,3),(4,2,4); explain select x,id from t force index (x) where x=0 and id=0; diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result index 96d681407fe..a3c518afa6a 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result @@ -1,7 +1,7 @@ drop table if exists t; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off create table t (id int not null, x int not null, y int not null, primary key(id), key(x)) engine=tokudb; insert into t values (0,0,0),(1,1,1),(2,2,2),(3,2,3),(4,2,4); explain select x,id from t force index (x) where x=0 and id=0; diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result index 43737c7753e..21dadb8b6c3 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result @@ -1,7 +1,7 @@ drop table if exists t; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off create table t (a int not null, b int not null, c int not null, d int not null, primary key(a,b), key(c,a)) engine=innodb; insert into t values (0,0,0,0),(0,1,0,1); explain select c,a,b from t where c=0 and a=0 and b=1; diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result index 1dcb1ee1b8b..41c3f2bbc11 100644 --- a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result +++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result @@ -1,7 +1,7 @@ drop table if exists t; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off create table t (a int not null, b int not null, c int not null, d int not null, primary key(a,b), key(c,a)) engine=tokudb; insert into t values (0,0,0,0),(0,1,0,1); explain select c,a,b from t where c=0 and a=0 and b=1; |