diff options
author | Monty <monty@mariadb.org> | 2023-01-11 18:12:40 +0200 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2023-01-11 18:12:40 +0200 |
commit | 7d1df207c4ded0ac8aa61f0d35bcae7eda974c54 (patch) | |
tree | ffe460a400c5a89485a99305b3c2c9b2dea938c4 /mysql-test/main | |
parent | 17858e03a7bfe7f154a7f8097d2473dbd1cb20f2 (diff) | |
download | mariadb-git-7d1df207c4ded0ac8aa61f0d35bcae7eda974c54.tar.gz |
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
Diffstat (limited to 'mysql-test/main')
-rw-r--r-- | mysql-test/main/range.result | 11 | ||||
-rw-r--r-- | mysql-test/main/range.test | 9 | ||||
-rw-r--r-- | mysql-test/main/range_aria_dbt3.result | 16 | ||||
-rw-r--r-- | mysql-test/main/range_aria_dbt3.test | 11 | ||||
-rw-r--r-- | mysql-test/main/range_mrr_icp.result | 11 |
5 files changed, 55 insertions, 3 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; |