summaryrefslogtreecommitdiff
path: root/mysql-test/r/derived_opt.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/derived_opt.result')
-rw-r--r--mysql-test/r/derived_opt.result35
1 files changed, 34 insertions, 1 deletions
diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result
index dc9b5910af1..bbca7f2ac90 100644
--- a/mysql-test/r/derived_opt.result
+++ b/mysql-test/r/derived_opt.result
@@ -499,9 +499,42 @@ 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 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;
+DROP TABLE t1,t2;
set optimizer_switch=@exit_optimizer_switch;