MDEV-30373 Wrong result with range access

This issue was caused by the bug fix for
MDEV-30325 Wrong result upon range query using index condition

The bug could happen in the case of several overlapping key ranges
with OR

6 files changed, 56 insertions, 4 deletions

diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 145464896b3..31777773240 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -3655,6 +3655,17 @@ b
 SET @@optimizer_switch=@save_optimizer_switch;
 drop table t1,t2;
 #
+# MDEV-30373 Wrong result with range access
+#
+CREATE TABLE t1 (pk int, a int, PRIMARY KEY (pk), KEY(a)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,3),(2,6),(3,9);
+SELECT * FROM t1 WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 );
+pk	a
+1	3
+2	6
+3	9
+DROP TABLE t1;
+#
 # End of 10.5 tests
 #
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index 8f9af563dc3..ba6f2942a9b 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2521,6 +2521,15 @@ SET @@optimizer_switch=@save_optimizer_switch;
 drop table t1,t2;
 
 --echo #
+--echo # MDEV-30373 Wrong result with range access
+--echo #
+
+CREATE TABLE t1 (pk int, a int, PRIMARY KEY (pk), KEY(a)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,3),(2,6),(3,9);
+SELECT * FROM t1 WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 );
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.5 tests
 --echo #
 
diff --git a/mysql-test/main/range_aria_dbt3.result b/mysql-test/main/range_aria_dbt3.result
index ae5a2e1329f..f08a1b244f8 100644
--- a/mysql-test/main/range_aria_dbt3.result
+++ b/mysql-test/main/range_aria_dbt3.result
@@ -6,8 +6,20 @@ use dbt3_s001;
 #
 SELECT COUNT(*) FROM lineitem force index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );
 COUNT(*)
-5056
+5658
+SELECT COUNT(*) FROM lineitem ignore index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );
+COUNT(*)
+5658
 #
-# End of 10.5 tests
+# MDEV-30373 Wrong result with range access
 #
+explain SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	lineitem	range	PRIMARY,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	8	NULL	506	Using where; Using index
+SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
+COUNT(*)
+293
 DROP DATABASE dbt3_s001;
+#
+# End of 10.5 tests
+#
diff --git a/mysql-test/main/range_aria_dbt3.test b/mysql-test/main/range_aria_dbt3.test
index 89328280987..141bf43885b 100644
--- a/mysql-test/main/range_aria_dbt3.test
+++ b/mysql-test/main/range_aria_dbt3.test
@@ -16,9 +16,18 @@ use dbt3_s001;
 --echo #
 
 SELECT COUNT(*) FROM lineitem force index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );
+SELECT COUNT(*) FROM lineitem ignore index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 );
 
 --echo #
---echo # End of 10.5 tests
+--echo # MDEV-30373 Wrong result with range access
 --echo #
 
+explain SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
+
+SELECT COUNT(*) FROM lineitem WHERE l_orderkey BETWEEN 111 AND 262 OR ( l_orderkey BETWEEN 152 AND  672 AND l_linenumber BETWEEN 4 AND 9 );
+
 DROP DATABASE dbt3_s001;
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index e68af8545cf..6817edd30cd 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -3644,6 +3644,17 @@ b
 SET @@optimizer_switch=@save_optimizer_switch;
 drop table t1,t2;
 #
+# MDEV-30373 Wrong result with range access
+#
+CREATE TABLE t1 (pk int, a int, PRIMARY KEY (pk), KEY(a)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,3),(2,6),(3,9);
+SELECT * FROM t1 WHERE a < 8 OR ( pk BETWEEN 1 AND 5 AND a BETWEEN 7 AND 10 );
+pk	a
+1	3
+2	6
+3	9
+DROP TABLE t1;
+#
 # End of 10.5 tests
 #
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 3999d75eb20..2cc6c9ad37f 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -10621,7 +10621,7 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *key1,SEL_ARG *key2)
             tmp:     [---------]
           */
           key2->copy_max_to_min(tmp);
-          key2= key2_next;
+          key2->next= key2_next;                // In case of key2_shared
         }
         continue;
       }