Merge 10.4 to 10.5

1 files changed, 78 insertions, 4 deletions

diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index cfbb66b06ad..37a6f2de2fd 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
@@ -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	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
 #