3 files changed, 81 insertions, 2 deletions

diff --git a/mysql-test/main/derived_opt.result b/mysql-test/main/derived_opt.result
index 6e4ea1b5d36..48ac7e62653 100644
--- a/mysql-test/main/derived_opt.result
+++ b/mysql-test/main/derived_opt.result
@@ -499,9 +499,45 @@ where
 D1.a= t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
-1	PRIMARY	<derived2>	hash_ALL	key0	#hash#key0	5	test.t1.a	100	Using join buffer (flat, BNLH join)
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	10	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	100	Using filesort
 set join_cache_level=@tmp_jcl;
 set optimizer_switch=@tmp_os;
 drop table t1, t2;
+#
+# Bug mdev-17382: equi-join of derived table with join_cache_level=4
+#
+CREATE TABLE t1 (
+id int NOT NULL,
+amount decimal DEFAULT NULL,
+PRIMARY KEY (id)
+);
+CREATE TABLE t2 (
+id int NOT NULL,
+name varchar(50) DEFAULT NULL,
+PRIMARY KEY (id)
+);
+INSERT INTO t1 VALUES
+(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),
+(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000);
+INSERT INTO t2 VALUES
+(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL),
+(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL);
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch='split_materialized=off';
+set join_cache_level=4;
+EXPLAIN
+SELECT t2.id,t2.name,t.total_amt
+FROM  t2
+LEFT JOIN
+(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
+ON t2.id=t.id
+WHERE t2.id < 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	range	PRIMARY	PRIMARY	4	NULL	3	Using index condition
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.id	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
+set join_cache_level=default;
+set optimizer_switch= @save_optimizer_switch;
+DROP TABLE t1,t2;
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/main/derived_opt.test b/mysql-test/main/derived_opt.test
index 7f19553e4e5..eccf4c13020 100644
--- a/mysql-test/main/derived_opt.test
+++ b/mysql-test/main/derived_opt.test
@@ -363,5 +363,48 @@ set join_cache_level=@tmp_jcl;
 set optimizer_switch=@tmp_os;
 drop table t1, t2;
 
+--echo #
+--echo # Bug mdev-17382: equi-join of derived table with join_cache_level=4
+--echo #
+
+CREATE TABLE t1 (
+  id int NOT NULL,
+  amount decimal DEFAULT NULL,
+PRIMARY KEY (id)
+);
+
+CREATE TABLE t2 (
+  id int NOT NULL,
+  name varchar(50) DEFAULT NULL,
+PRIMARY KEY (id)
+);
+
+INSERT INTO t1 VALUES
+(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),
+(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000);
+
+INSERT INTO t2 VALUES
+(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL),
+(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL);
+
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch='split_materialized=off';
+
+set join_cache_level=4;
+
+EXPLAIN
+SELECT t2.id,t2.name,t.total_amt
+  FROM  t2
+        LEFT JOIN
+        (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
+        ON t2.id=t.id
+  WHERE t2.id < 3;
+
+set join_cache_level=default;
+
+set optimizer_switch= @save_optimizer_switch;
+
+DROP TABLE t1,t2;
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/main/innodb_mrr_cpk.result b/mysql-test/main/innodb_mrr_cpk.result
index 28d7dd51df8..a2e43d7d127 100644
--- a/mysql-test/main/innodb_mrr_cpk.result
+++ b/mysql-test/main/innodb_mrr_cpk.result
@@ -226,7 +226,7 @@ set join_cache_level=3;
 explain SELECT 1 FROM (SELECT url, id FROM t2 LIMIT 1 OFFSET 20) derived RIGHT JOIN t1 ON t1.id = derived.id;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	#	
-1	PRIMARY	<derived2>	hash_ALL	key0	#hash#key0	25	test.t1.id	#	Using join buffer (flat, BNLH join)
+1	PRIMARY	<derived2>	ref	key0	key0	25	test.t1.id	#	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	#	
 set join_cache_level= @tmp_mdev5037;
 drop table t0,t1,t2;