MDEV-27366 SIGSEGV in handler_index_cond_check on SELECT in connection with rowid_filter settingbb-10.5-MDEV-27366

Cause: when optimizer picks a full table scan it does not reset previously
chosen rowid filter.
Solution: reset rowid filter for full table scan in best_access_path()

11 files changed, 255 insertions, 41 deletions

diff --git a/mysql-test/include/rowid_filter_debug_kill.inc b/mysql-test/include/rowid_filter_debug_kill.inc
index 6a8c5d3f70d..8ff1ba13b86 100644
--- a/mysql-test/include/rowid_filter_debug_kill.inc
+++ b/mysql-test/include/rowid_filter_debug_kill.inc
@@ -40,7 +40,7 @@ analyze table t2,t3;
 explain
 select * from t2, t3
 where 
-  t3.key1=t2.a and t3.key2 in (2,3);
+  t3.key1=t2.a and t3.key2 in (2);
 
 let $target_id= `select connection_id()`;
 
@@ -48,7 +48,7 @@ set debug_sync='handler_rowid_filter_check SIGNAL at_rowid_filter_check WAIT_FOR
 send
 select * from t2, t3
 where 
-  t3.key1=t2.a and t3.key2 in (2,3);
+ t3.key1=t2.a and t3.key2 in (2);
 
 connect (con1, localhost, root,,);
 connection con1;
diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index 79c5b7923bd..89591f6ea92 100644
--- a/mysql-test/main/join_cache.result
+++ b/mysql-test/main/join_cache.result
@@ -853,7 +853,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (flat, BNLH join); Using rowid filter
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1053,7 +1053,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (incremental, BNLH join); Using rowid filter
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (incremental, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1312,7 +1312,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1509,7 +1509,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1706,7 +1706,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1903,7 +1903,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2104,7 +2104,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (flat, BNLH join); Using rowid filter
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (flat, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2208,7 +2208,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	hash_ALL	PRIMARY	#hash#PRIMARY	3	world.City.Country	239	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	CountryLanguage	hash_ALL|filter	PRIMARY,Percentage	#hash#PRIMARY|Percentage	3|4	world.City.Country	984 (19%)	Using where; Using join buffer (incremental, BNLH join); Using rowid filter
+1	SIMPLE	CountryLanguage	hash_ALL	PRIMARY,Percentage	#hash#PRIMARY	3	world.City.Country	984	Using where; Using join buffer (incremental, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2312,7 +2312,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2416,7 +2416,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2520,7 +2520,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2624,7 +2624,7 @@ LENGTH(Language) < LENGTH(City.Name) - 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	City	ALL	Country	NULL	NULL	NULL	4079	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	1	Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan
-1	SIMPLE	CountryLanguage	ref|filter	PRIMARY,Percentage	PRIMARY|Percentage	3|4	world.City.Country	4 (19%)	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter
+1	SIMPLE	CountryLanguage	ref	PRIMARY,Percentage	PRIMARY	3	world.City.Country	4	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -6228,6 +6228,67 @@ EXPLAIN
   }
 }
 drop table t1,t2,t3;
+#
+# MDEV-27366 SIGSEGV in handler_index_cond_check on SELECT in connection with rowid_filter setting
+#
+SET @@optimizer_switch=@save_optimizer_switch;
+SET join_buffer_size=@save_join_buffer_size;
+SET join_buffer_space_limit=@save_join_buffer_space_limit;
+SET join_cache_level=3;
+CREATE TABLE t (c BIGINT, d INT, KEY c(c), KEY d(d)) ENGINE=InnoDB;
+INSERT INTO t VALUES (0,0),(1,2),(1,3),(2,0),(3,0),(4,6),(5,0);
+ANALYZE TABLE t;
+Table	Op	Msg_type	Msg_text
+test.t	analyze	status	Engine-independent statistics collected
+test.t	analyze	status	OK
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ref	c,d	c	9	const	1	Using where
+1	SIMPLE	b	hash_ALL	c,d	#hash#c	9	test.t.d	7	Using where; Using join buffer (flat, BNLH join)
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+c	d	c	d
+0	0	0	0
+SET join_cache_level=4;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ref	c,d	c	9	const	1	Using where
+1	SIMPLE	b	hash_ALL	c,d	#hash#c	9	test.t.d	7	Using where; Using join buffer (flat, BNLH join)
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+c	d	c	d
+0	0	0	0
+SET join_cache_level=5;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ref	c,d	c	9	const	1	Using where
+1	SIMPLE	b	ref	c,d	c	9	test.t.d	1	Using index condition; Using where
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+c	d	c	d
+0	0	0	0
+SET join_cache_level=6;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ref	c,d	c	9	const	1	Using where
+1	SIMPLE	b	ref	c,d	c	9	test.t.d	1	Using index condition; Using where
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+c	d	c	d
+0	0	0	0
+SET join_cache_level=7;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ref	c,d	c	9	const	1	Using where
+1	SIMPLE	b	ref	c,d	c	9	test.t.d	1	Using index condition; Using where
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+c	d	c	d
+0	0	0	0
+SET join_cache_level=8;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t	ref	c,d	c	9	const	1	Using where
+1	SIMPLE	b	ref	c,d	c	9	test.t.d	1	Using index condition; Using where
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+c	d	c	d
+0	0	0	0
+DROP TABLE t;
 # End of 10.3 tests
 set @@optimizer_switch=@save_optimizer_switch;
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test
index b4271f648e3..ad8022ce558 100644
--- a/mysql-test/main/join_cache.test
+++ b/mysql-test/main/join_cache.test
@@ -4168,6 +4168,38 @@ where
 
 drop table t1,t2,t3;
 
+             
+--echo #
+--echo # MDEV-27366 SIGSEGV in handler_index_cond_check on SELECT in connection with rowid_filter setting
+--echo #
+SET @@optimizer_switch=@save_optimizer_switch;
+SET join_buffer_size=@save_join_buffer_size;
+SET join_buffer_space_limit=@save_join_buffer_space_limit;
+SET join_cache_level=3;
+CREATE TABLE t (c BIGINT, d INT, KEY c(c), KEY d(d)) ENGINE=InnoDB;
+INSERT INTO t VALUES (0,0),(1,2),(1,3),(2,0),(3,0),(4,6),(5,0);
+ANALYZE TABLE t;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+# Check higher values of join_cache_level as well:
+SET join_cache_level=4;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+SET join_cache_level=5;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+SET join_cache_level=6;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+SET join_cache_level=7;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+SET join_cache_level=8;
+EXPLAIN SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+SELECT * FROM t, t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d;
+
+DROP TABLE t;
+
 --echo # End of 10.3 tests
 
 # The following command must be the last one in the file
@@ -4176,3 +4208,5 @@ set @@optimizer_switch=@save_optimizer_switch;
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
 set global innodb_stats_persistent_sample_pages=
              @innodb_stats_persistent_sample_pages_save;
+
+
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index 376cb63944a..53c0da23f5e 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -2083,7 +2083,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
 WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	101	100.00	Using where
-1	PRIMARY	t1	eq_ref|filter	PRIMARY,b1	PRIMARY|b1	4|4	test.t2.a2	1 (87%)	87.00	Using where; Using rowid filter
+1	PRIMARY	t1	eq_ref	PRIMARY,b1	PRIMARY	4	test.t2.a2	1	87.00	Using where
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	100.00	Using index condition
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2`
@@ -2108,14 +2108,6 @@ EXPLAIN
       "key_length": "4",
       "used_key_parts": ["pk1"],
       "ref": ["test.t2.a2"],
-      "rowid_filter": {
-        "range": {
-          "key": "b1",
-          "used_key_parts": ["b1"]
-        },
-        "rows": 87,
-        "selectivity_pct": 87
-      },
       "rows": 1,
       "filtered": 87,
       "attached_condition": "t1.b1 <= (subquery#2)"
@@ -2140,6 +2132,66 @@ EXPLAIN
     ]
   }
 }
+DELETE FROM t2 WHERE pk2 >= 50;
+SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+WHERE b1 <= ( SELECT MIN(b2) FROM t2 );
+pk1	a1	b1	pk2	a2	b2
+EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+WHERE b1 <= ( SELECT MIN(b2) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	41	100.00	Using where
+1	PRIMARY	t1	eq_ref|filter	PRIMARY,b1	PRIMARY|b1	4|4	test.t2.a2	1 (4%)	4.00	Using where; Using rowid filter
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	41	100.00	
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select min(`test`.`t2`.`b2`) from `test`.`t2`) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2`
+EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+WHERE b1 <= ( SELECT MIN(b2) FROM t2 );
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 41,
+      "filtered": 100,
+      "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null"
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "eq_ref",
+      "possible_keys": ["PRIMARY", "b1"],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["pk1"],
+      "ref": ["test.t2.a2"],
+      "rowid_filter": {
+        "range": {
+          "key": "b1",
+          "used_key_parts": ["b1"]
+        },
+        "rows": 4,
+        "selectivity_pct": 4
+      },
+      "rows": 1,
+      "filtered": 4,
+      "attached_condition": "t1.b1 <= (subquery#2)"
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "rows": 41,
+            "filtered": 100
+          }
+        }
+      }
+    ]
+  }
+}
 DROP TABLE t1,t2;
 #
 # MDEV-21794: Optimizer flag rowid_filter leads to long query
diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test
index 163b71b6153..f49c6ab5e75 100644
--- a/mysql-test/main/rowid_filter.test
+++ b/mysql-test/main/rowid_filter.test
@@ -351,6 +351,20 @@ eval $q;
 eval EXPLAIN EXTENDED $q;
 eval EXPLAIN FORMAT=JSON $q;
 
+# After fix for MDEV-27366 the explain plan above has stopped displaying
+# rowid_filter as it did before. The reason is selectivity of condition
+# on "b1" is not worth applying rowid filter using index "b1", so the optimizer
+# skips it. To force usage of rowid filter the following tweaks are made
+# for the data and the query:
+
+DELETE FROM t2 WHERE pk2 >= 50;
+let $q=
+SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+  WHERE b1 <= ( SELECT MIN(b2) FROM t2 );
+eval $q;
+eval EXPLAIN EXTENDED $q;
+eval EXPLAIN FORMAT=JSON $q;
+
 DROP TABLE t1,t2;
 
 --echo #
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 6c8b5eda8e8..5e5cc145d72 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -2032,7 +2032,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
 WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	101	100.00	Using where
-1	PRIMARY	t1	eq_ref|filter	PRIMARY,b1	PRIMARY|b1	4|4	test.t2.a2	1 (87%)	87.00	Using where; Using rowid filter
+1	PRIMARY	t1	eq_ref	PRIMARY,b1	PRIMARY	4	test.t2.a2	1	87.00	Using where
 2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	1	100.00	Using index condition
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2`
@@ -2057,14 +2057,6 @@ EXPLAIN
       "key_length": "4",
       "used_key_parts": ["pk1"],
       "ref": ["test.t2.a2"],
-      "rowid_filter": {
-        "range": {
-          "key": "b1",
-          "used_key_parts": ["b1"]
-        },
-        "rows": 87,
-        "selectivity_pct": 87
-      },
       "rows": 1,
       "filtered": 87,
       "attached_condition": "t1.b1 <= (subquery#2)"
@@ -2089,6 +2081,66 @@ EXPLAIN
     ]
   }
 }
+DELETE FROM t2 WHERE pk2 >= 50;
+SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+WHERE b1 <= ( SELECT MIN(b2) FROM t2 );
+pk1	a1	b1	pk2	a2	b2
+EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+WHERE b1 <= ( SELECT MIN(b2) FROM t2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	41	100.00	Using where
+1	PRIMARY	t1	eq_ref|filter	PRIMARY,b1	PRIMARY|b1	4|4	test.t2.a2	1 (4%)	4.00	Using where; Using rowid filter
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	41	100.00	
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select min(`test`.`t2`.`b2`) from `test`.`t2`) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2`
+EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 )
+WHERE b1 <= ( SELECT MIN(b2) FROM t2 );
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 41,
+      "filtered": 100,
+      "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null"
+    },
+    "table": {
+      "table_name": "t1",
+      "access_type": "eq_ref",
+      "possible_keys": ["PRIMARY", "b1"],
+      "key": "PRIMARY",
+      "key_length": "4",
+      "used_key_parts": ["pk1"],
+      "ref": ["test.t2.a2"],
+      "rowid_filter": {
+        "range": {
+          "key": "b1",
+          "used_key_parts": ["b1"]
+        },
+        "rows": 4,
+        "selectivity_pct": 4
+      },
+      "rows": 1,
+      "filtered": 4,
+      "attached_condition": "t1.b1 <= (subquery#2)"
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "table_name": "t2",
+            "access_type": "ALL",
+            "rows": 41,
+            "filtered": 100
+          }
+        }
+      }
+    ]
+  }
+}
 DROP TABLE t1,t2;
 #
 # MDEV-21794: Optimizer flag rowid_filter leads to long query
@@ -3003,7 +3055,7 @@ WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1
 WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	15	100.00	Using where
-2	DEPENDENT SUBQUERY	t2	ref|filter	c1,i1	c1|i1	3|5	func	6 (33%)	33.33	Using where; Full scan on NULL key; Using rowid filter
+2	DEPENDENT SUBQUERY	t2	ref	c1,i1	c1	3	func	6	33.33	Using where; Full scan on NULL key
 2	DEPENDENT SUBQUERY	a1	ALL	NULL	NULL	NULL	NULL	15	100.00	Using join buffer (flat, BNL join)
 Warnings:
 Note	1276	Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
diff --git a/mysql-test/main/rowid_filter_innodb_debug.result b/mysql-test/main/rowid_filter_innodb_debug.result
index 6fd75294bdb..d57c7230657 100644
--- a/mysql-test/main/rowid_filter_innodb_debug.result
+++ b/mysql-test/main/rowid_filter_innodb_debug.result
@@ -35,14 +35,14 @@ test.t3	analyze	status	OK
 explain
 select * from t2, t3
 where 
-t3.key1=t2.a and t3.key2 in (2,3);
+t3.key1=t2.a and t3.key2 in (2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t3	ref|filter	key1,key2	key1|key2	5|5	test.t2.a	1000 (20%)	Using where; Using rowid filter
+1	SIMPLE	t3	ref|filter	key1,key2	key1|key2	5|5	test.t2.a	1000 (10%)	Using where; Using rowid filter
 set debug_sync='handler_rowid_filter_check SIGNAL at_rowid_filter_check WAIT_FOR go';
 select * from t2, t3
 where 
-t3.key1=t2.a and t3.key2 in (2,3);
+t3.key1=t2.a and t3.key2 in (2);
 connect  con1, localhost, root,,;
 connection con1;
 set debug_sync='now WAIT_FOR at_rowid_filter_check';
diff --git a/mysql-test/main/rowid_filter_myisam_debug.result b/mysql-test/main/rowid_filter_myisam_debug.result
index 16fcb2a416e..f940564a97f 100644
--- a/mysql-test/main/rowid_filter_myisam_debug.result
+++ b/mysql-test/main/rowid_filter_myisam_debug.result
@@ -34,14 +34,14 @@ test.t3	analyze	status	Table is already up to date
 explain
 select * from t2, t3
 where 
-t3.key1=t2.a and t3.key2 in (2,3);
+t3.key1=t2.a and t3.key2 in (2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	SIMPLE	t3	ref|filter	key1,key2	key1|key2	5|5	test.t2.a	1000 (18%)	Using where; Using rowid filter
+1	SIMPLE	t3	ref|filter	key1,key2	key1|key2	5|5	test.t2.a	1000 (9%)	Using where; Using rowid filter
 set debug_sync='handler_rowid_filter_check SIGNAL at_rowid_filter_check WAIT_FOR go';
 select * from t2, t3
 where 
-t3.key1=t2.a and t3.key2 in (2,3);
+t3.key1=t2.a and t3.key2 in (2);
 connect  con1, localhost, root,,;
 connection con1;
 set debug_sync='now WAIT_FOR at_rowid_filter_check';
diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result
index a5089c609d2..93a6464c23d 100644
--- a/mysql-test/main/stat_tables.result
+++ b/mysql-test/main/stat_tables.result
@@ -216,7 +216,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	part	ALL	PRIMARY	NULL	NULL	NULL	200	Using where; Using join buffer (flat, BNL join)
 1	SIMPLE	lineitem	ref	PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity	i_l_partkey	5	dbt3_s001.part.p_partkey	30	Using where
 1	SIMPLE	supplier	eq_ref	PRIMARY,i_s_nationkey	PRIMARY	4	dbt3_s001.lineitem.l_suppkey	1	Using where
-1	SIMPLE	orders	eq_ref|filter	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY|i_o_orderdate	4|4	dbt3_s001.lineitem.l_orderkey	1 (27%)	Using where; Using rowid filter
+1	SIMPLE	orders	eq_ref	PRIMARY,i_o_orderdate,i_o_custkey	PRIMARY	4	dbt3_s001.lineitem.l_orderkey	1	Using where
 1	SIMPLE	n2	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	
 1	SIMPLE	customer	eq_ref	PRIMARY,i_c_nationkey	PRIMARY	4	dbt3_s001.orders.o_custkey	1	Using where
 1	SIMPLE	n1	eq_ref	PRIMARY,i_n_regionkey	PRIMARY	4	dbt3_s001.customer.c_nationkey	1	Using where
diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result
index e6363610301..aca6530b0f5 100644
--- a/mysql-test/main/subselect2.result
+++ b/mysql-test/main/subselect2.result
@@ -132,7 +132,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
 1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
 1	PRIMARY	t3	eq_ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	PRIMARY	34	test.t3.PARENTID	1	Using where
-1	PRIMARY	t3	ref|filter	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	FFOLDERID_IDX|CMFLDRPARNT_IDX	34|35	test.t3.PARENTID	1 (29%)	Using where; Using rowid filter
+1	PRIMARY	t3	ref	PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX	FFOLDERID_IDX	34	test.t3.PARENTID	1	Using where
 drop table t1, t2, t3, t4;
 CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
 INSERT INTO t1 VALUES (1),(2);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 192c3285ba6..7c05bd005c8 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8144,6 +8144,7 @@ best_access_path(JOIN      *join,
       {
         tmp= s->scan_time();
         type= JT_ALL;
+        best_filter= 0;
       }
 
       if ((s->table->map & join->outer_join) || disable_jbuf)     // Can't use join cache