diff options
Diffstat (limited to 'mysql-test/main/range.result')
| -rw-r--r-- | mysql-test/main/range.result | 82 |
1 files changed, 78 insertions, 4 deletions
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 837adf472f9..a9c3b9c6e2a 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1297,7 +1297,7 @@ SELECT * FROM t1 WHERE 25 <= a AND b = 23 OR 23 <= a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 2 Using where; Using index SELECT * FROM t1 WHERE 23 <= a AND a <= 25 OR 25 <= a AND b = 23 OR @@ -3121,6 +3121,82 @@ a b set eq_range_index_dive_limit=default; drop table t1; # +# MDEV-23811: Both disjunct of WHERE condition contain range conditions +# for the same index such that the second range condition +# fully covers the first one. Additionally one of the disjuncts +# contains a range condition for the other index. +# +create table t1 ( +pk int primary key auto_increment, a int, b int, +index idx1(a), index idx2(b) +); +insert into t1(a,b) values +(5,50), (1,10), (3,30), (7,70), (8,80), (4,40), (2,20), (6,60); +insert into t1(a,b) select a+10, b+100 from t1; +insert into t1(a,b) select a+20, b+200 from t1; +insert into t1(a,b) select a+30, b+300 from t1; +insert into t1(a,b) select a,b from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +explain select * from t1 where ((a between 3 and 4) and b < 100) or (a between 2 and 5); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1,idx2 idx1 5 NULL 11 Using index condition; Using where +select * from t1 where ((a between 3 and 4) and b < 100) or (a between 2 and 5); +pk a b +7 2 20 +71 2 20 +3 3 30 +67 3 30 +6 4 40 +70 4 40 +1 5 50 +65 5 50 +explain select * from t1 where (a between 2 and 5) or ((a between 3 and 4) and b < 100); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1,idx2 idx1 5 NULL 11 Using index condition; Using where +select * from t1 where (a between 2 and 5) or ((a between 3 and 4) and b < 100); +pk a b +7 2 20 +71 2 20 +3 3 30 +67 3 30 +6 4 40 +70 4 40 +1 5 50 +65 5 50 +explain select * from t1 where (a between 3 and 4) or ((a between 2 and 5) and b < 100); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1,idx2 idx1 5 NULL 11 Using index condition; Using where +select * from t1 where (a between 3 and 4) or ((a between 2 and 5) and b < 100); +pk a b +7 2 20 +71 2 20 +3 3 30 +67 3 30 +6 4 40 +70 4 40 +1 5 50 +65 5 50 +explain select * from t1 where ((a between 2 and 5) and b < 100) or (a between 3 and 4); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1,idx2 idx1 5 NULL 11 Using index condition; Using where +select * from t1 where ((a between 2 and 5) and b < 100) or (a between 3 and 4); +pk a b +7 2 20 +71 2 20 +3 3 30 +67 3 30 +6 4 40 +70 4 40 +1 5 50 +65 5 50 +drop table t1; +# +# End of 10.2 tests +# +# # MDEV-18551: New defaults for eq_range_index_dive_limit # create table ten(a int); @@ -3140,9 +3216,7 @@ analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17 id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE t1 range a a 5 NULL 201 1201.00 100.00 100.00 Using where; Using index drop table t1,ten,t2; -# -# End of 10.2 tests -# +# End of 10.4 tests # # MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer # |