diff options
Diffstat (limited to 'mysql-test/main/range.result')
| -rw-r--r-- | mysql-test/main/range.result | 293 |
1 files changed, 293 insertions, 0 deletions
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index bb111f25a07..7f03a7c596c 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3113,6 +3113,299 @@ drop table t1,ten,t2; # # 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 Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +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 3 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 3 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 3 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 3 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 3 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 3 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 3 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 Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +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 Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +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 3 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 Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +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 74 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.5 tests +# set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; |