diff options
Diffstat (limited to 'mysql-test/main/range_mrr_icp.result')
| -rw-r--r-- | mysql-test/main/range_mrr_icp.result | 84 |
1 files changed, 79 insertions, 5 deletions
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 04c3ad2780d..27daa76e976 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -1300,7 +1300,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 @@ -1430,7 +1430,7 @@ SELECT * FROM t3 WHERE a < 5 OR a < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index a a 10 NULL 23 Using where; Using index +1 SIMPLE t3 range a a 5 NULL 9 Using where; Using index DROP TABLE t1, t2, t3; # # Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN @@ -3110,6 +3110,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; Rowid-ordered scan +select * from t1 where ((a between 3 and 4) and b < 100) or (a between 2 and 5); +pk a b +1 5 50 +3 3 30 +6 4 40 +7 2 20 +65 5 50 +67 3 30 +70 4 40 +71 2 20 +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; Rowid-ordered scan +select * from t1 where (a between 2 and 5) or ((a between 3 and 4) and b < 100); +pk a b +1 5 50 +3 3 30 +6 4 40 +7 2 20 +65 5 50 +67 3 30 +70 4 40 +71 2 20 +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; Rowid-ordered scan +select * from t1 where (a between 3 and 4) or ((a between 2 and 5) and b < 100); +pk a b +1 5 50 +3 3 30 +6 4 40 +7 2 20 +65 5 50 +67 3 30 +70 4 40 +71 2 20 +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; Rowid-ordered scan +select * from t1 where ((a between 2 and 5) and b < 100) or (a between 3 and 4); +pk a b +1 5 50 +3 3 30 +6 4 40 +7 2 20 +65 5 50 +67 3 30 +70 4 40 +71 2 20 +drop table t1; +# +# End of 10.2 tests +# +# # MDEV-18551: New defaults for eq_range_index_dive_limit # create table ten(a int); @@ -3129,9 +3205,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 index a a 5 NULL 2000 2000.00 10.05 60.05 Using where; Using index drop table t1,ten,t2; -# -# End of 10.2 tests -# +# End of 10.4 tests set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; |