Merge branch '10.4' into 10.5

1 files changed, 42 insertions, 0 deletions

diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index d3507f6bb59..15e77f5689e 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -55,6 +55,17 @@ orders	CREATE TABLE `orders` (
   KEY `i_o_totalprice` (`o_totalprice`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 set optimizer_use_condition_selectivity=2;
+select
+100 *
+(select count(*) from lineitem
+WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45
+)
+/
+(select count(*) from lineitem
+where l_shipdate BETWEEN '1997-01-01' AND '1997-06-30')
+as correct_r_filtered_when_using_l_shipdate;
+correct_r_filtered_when_using_l_shipdate
+11.7647
 set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem
 WHERE  l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND
 l_quantity > 45;
@@ -2080,6 +2091,37 @@ EXPLAIN
 }
 DROP TABLE t1,t2;
 #
+# MDEV-21794: Optimizer flag rowid_filter leads to long query
+#
+create table t10(a int);
+insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t11(a int);
+insert into t11 select A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C;
+CREATE TABLE t1 (
+el_id int(10) unsigned NOT NULL ,
+el_index blob NOT NULL,
+el_index_60 varbinary(60) NOT NULL,
+filler blob,
+PRIMARY KEY (el_id),
+KEY el_index (el_index(60)),
+KEY el_index_60 (el_index_60,el_id)
+);
+insert into t1
+select
+A.a+1000*B.a,
+A.a+1000*B.a + 10000,
+A.a+1000*B.a + 10000,
+'filler-data-filler-data'
+from
+t11 A, t10 B;
+# This must not use rowid_filter with key=el_index|el_index_60:
+explain
+select * from t1
+where el_index like '10%' and (el_index_60 like '10%' or el_index_60 like '20%');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	el_index,el_index_60	el_index	62	NULL	1000	Using where
+drop table t10, t11, t1;
+#
 # MDEV-22160: SIGSEGV in st_join_table::save_explain_data on SELECT
 #
 set @save_optimizer_switch= @@optimizer_switch;