MDEV-27366 SIGSEGV in handler_index_cond_check with rowid_filterbb-10.5-MDEV-27366-2

Cause:
After the optimizer has generated a query plan using "ref access"
plus "rowid filter", the execution reaches "join_cache_level" check.
As the result the server may decide to employ full table scan and
hash join but does not discard the rowid filter. This leads to an
inconsistency in the table access.

Solution:
Discard "rowid filter" when full table scan | hash join are employed

3 files changed, 68 insertions, 7 deletions

diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result
index 79c5b7923bd..d2e58d6b5fc 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
@@ -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
@@ -6228,7 +6228,33 @@ EXPLAIN
   }
 }
 drop table t1,t2,t3;
+#
+# MDEV-27366: SIGSEGV in handler_index_cond_check on SELECT 
+#             in connection with rowid_filter setting
+#
+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);
+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	#	Using where
+1	SIMPLE	b	hash_ALL	c,d	#hash#c	9	test.t.d	#	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
+PREPARE stmt FROM 'SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d';
+EXECUTE stmt;
+c	d	c	d
+0	0	0	0
+EXECUTE stmt;
+c	d	c	d
+0	0	0	0
+EXECUTE stmt;
+c	d	c	d
+0	0	0	0
+DROP TABLE t;
 # End of 10.3 tests
+set join_cache_level=@save_join_cache_level;
 set @@optimizer_switch=@save_optimizer_switch;
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
 set global innodb_stats_persistent_sample_pages=
diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test
index b4271f648e3..2b46b3658b0 100644
--- a/mysql-test/main/join_cache.test
+++ b/mysql-test/main/join_cache.test
@@ -4168,9 +4168,30 @@ where
 
 drop table t1,t2,t3;
 
+--echo #
+--echo # MDEV-27366: SIGSEGV in handler_index_cond_check on SELECT 
+--echo #             in connection with rowid_filter setting
+--echo #
+
+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);
+
+--replace_column 9 #
+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;
+
+PREPARE stmt FROM 'SELECT * FROM t,t AS b WHERE t.c=0 AND t.d=b.c AND t.c=b.d';
+EXECUTE stmt;
+EXECUTE stmt;
+EXECUTE stmt;
+ 
+DROP TABLE t;
+
 --echo # End of 10.3 tests
 
 # The following command must be the last one in the file
+set join_cache_level=@save_join_cache_level;
 set @@optimizer_switch=@save_optimizer_switch;
 
 set global innodb_stats_persistent= @innodb_stats_persistent_save;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 192c3285ba6..1619448ba82 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -13112,6 +13112,23 @@ restart:
     if (!tab->bush_children)
       idx++;
   }
+
+  for (tab= first_linear_tab(join, WITH_BUSH_ROOTS, WITHOUT_CONST_TABLES);
+       tab;
+       tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS))
+  {
+    if (tab->cache && tab->cache->get_join_alg() == JOIN_CACHE::BNLH_JOIN_ALG)
+    {
+      tab->type= JT_HASH;
+
+      /*
+        Reset Rowid Filter that may be possibly set
+      */
+      tab->range_rowid_filter_info= NULL;
+      delete tab->rowid_filter;
+      tab->rowid_filter= NULL;
+    }
+  }
 }
 
 /**
@@ -13308,9 +13325,6 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
     if (jcl)
        tab[-1].next_select=sub_select_cache;
 
-    if (tab->cache && tab->cache->get_join_alg() == JOIN_CACHE::BNLH_JOIN_ALG)
-      tab->type= JT_HASH;
-      
     switch (tab->type) {
     case JT_SYSTEM:				// Only happens with left join 
     case JT_CONST:				// Only happens with left join