1 files changed, 63 insertions, 1 deletions
diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test
index 737beb6cb35..7f19553e4e5 100644
--- a/mysql-test/t/derived_opt.test
+++ b/mysql-test/t/derived_opt.test
@@ -1,6 +1,7 @@
 # Initialize
 --disable_warnings
-drop table if exists t1,t2,t3;
+drop table if exists t0,t1,t2,t3;
+drop database if exists test1;
 --enable_warnings
 
 set @exit_optimizer_switch=@@optimizer_switch;
@@ -273,6 +274,67 @@ limit 10;
 drop table t1, t2, t3, t4;
 
 --echo #
+--echo # MDEV-6888: Query spends a long time in best_extension_by_limited_search with mrr enabled
+--echo #
+create database test1;
+use test1;
+
+set @tmp_jcl= @@join_cache_level;
+set @tmp_os= @@optimizer_switch;
+set join_cache_level=8;
+set optimizer_switch='mrr=on,mrr_sort_keys=on';
+
+CREATE TABLE t0 (
+  f1 bigint(20) DEFAULT NULL,
+  f2 char(50) DEFAULT NULL
+) ENGINE=MEMORY DEFAULT CHARSET=latin1;
+INSERT INTO t0 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(1,'one'),(2,'two');
+
+CREATE TABLE t1 (
+  f1 decimal(64,30) DEFAULT NULL,
+  f2 varchar(50) DEFAULT NULL
+) ENGINE=MEMORY DEFAULT CHARSET=latin1;
+
+INSERT INTO t1 VALUES 
+(NULL,'numeric column is NULL'),
+(0.000000000000000000000000000000,NULL),
+(5.000000000000000000000000000000,'five'),
+(1.000000000000000000000000000000,'one'),
+(3.000000000000000000000000000000,'three');
+
+CREATE TABLE t2 (
+  f1 double DEFAULT NULL,
+  f2 varbinary(50) DEFAULT NULL
+) ENGINE=MEMORY DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(2,'two'),(3,'three');
+
+create VIEW v0  AS select f1,f2 from t1 ;
+
+let $cnt= 27; 
+while ($cnt)
+{
+# i runs from 1 to 27
+  let $i= `select 28 - $cnt`;
+  let $prev=`select $i - 1`;
+
+# rem = i mod 3
+  let $rem= `select MOD($i, 3)`;
+# view uses $i, $prev and $rem:
+  eval create VIEW v$i AS select tab1_v$i.f1,tab1_v$i.f2 from t$rem tab1_v$i join v$prev tab2 on tab1_v$i.f1 = tab2.f1 and tab1_v$i.f2 = tab2.f2;
+  dec $cnt;
+}
+
+EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27;
+--echo # This used to hang forever:
+EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27;
+
+use test;
+drop database test1;
+set join_cache_level=@tmp_jcl;
+set optimizer_switch=@tmp_os;
+
+
+--echo #
 --echo # MDEV-6879: Dereference of NULL primary_file->table in DsMrr_impl::get_disk_sweep_mrr_cost()
 --echo #
 create table t1(a int);