Merge branch '10.1' into 10.2

1 files changed, 63 insertions, 0 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 24dcdeacf60..2602181f234 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2271,4 +2271,67 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	
 1	SIMPLE	t2	ref	c	c	5	const	393	Using where
 drop table t1,t2;
+#
+# MDEV-10006: optimizer doesn't convert outer join to inner on views with WHERE clause
+#
+CREATE TABLE t1(i1 int primary key, v1 int, key(v1));
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (3, 3);
+INSERT INTO t1 VALUES (4, 4);
+INSERT INTO t1 VALUES (5, 3);
+INSERT INTO t1 VALUES (6, 6);
+INSERT INTO t1 VALUES (7, 7);
+INSERT INTO t1 VALUES (8, 8);
+INSERT INTO t1 VALUES (9, 9);
+CREATE TABLE t2(i2 int primary key, v2 int, key(v2));
+INSERT INTO t2 VALUES (1, 1);
+INSERT INTO t2 VALUES (2, 2);
+INSERT INTO t2 VALUES (3, 3);
+INSERT INTO t2 VALUES (4, 4);
+INSERT INTO t2 VALUES (5, 3);
+INSERT INTO t2 VALUES (6, 6);
+INSERT INTO t2 VALUES (7, 7);
+INSERT INTO t2 VALUES (8, 8);
+INSERT INTO t2 VALUES (9, 9);
+CREATE TABLE t3(i3 int primary key, v3 int, key(v3));
+INSERT INTO t3 VALUES (2, 2);
+INSERT INTO t3 VALUES (4, 4);
+INSERT INTO t3 VALUES (6, 6);
+INSERT INTO t3 VALUES (8, 8);
+# This should have a join order of t3,t1,t2 (or t3,t2,t1, the idea is that t3 is the first one)
+EXPLAIN EXTENDED 
+SELECT * FROM 
+(SELECT t1.i1 as i1, t1.v1 as v1,
+t2.i2 as i2, t2.v2 as v2,
+t3.i3 as i3, t3.v3 as v3
+FROM t1 JOIN t2 on t1.i1 = t2.i2
+LEFT JOIN t3 on t2.i2 = t3.i3
+) as w1 
+WHERE v3 = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	ref	PRIMARY,v3	v3	5	const	1	100.00	
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`v3` = 4) and (`test`.`t1`.`i1` = `test`.`t3`.`i3`) and (`test`.`t2`.`i2` = `test`.`t3`.`i3`))
+# This should have the same join order like the query above:
+EXPLAIN EXTENDED 
+SELECT * FROM 
+(SELECT t1.i1 as i1, t1.v1 as v1,
+t2.i2 as i2, t2.v2 as v2,
+t3.i3 as i3, t3.v3 as v3
+FROM t1 JOIN t2 on t1.i1 = t2.i2
+LEFT JOIN t3 on t2.i2 = t3.i3
+WHERE t1.i1 = t2.i2
+AND 1 = 1
+) as w2 
+WHERE v3 = 4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t3	ref	PRIMARY,v3	v3	5	const	1	100.00	
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.i3	1	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`v3` = 4) and (`test`.`t1`.`i1` = `test`.`t3`.`i3`) and (`test`.`t2`.`i2` = `test`.`t3`.`i3`))
+drop table t1,t2,t3;
 SET optimizer_switch=@save_optimizer_switch;