From 5c68bc2c3264fa2c4832c468bad32701dd3d4ed0 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Fri, 6 May 2016 12:30:01 +0300 Subject: MDEV-10006: optimizer doesn't convert outer join to inner on views with WHERE clause When simplify_joins() converts an outer join to an inner, it should reset the value of TABLE::dep_tables. This is needed, because the function may have already set TABLE::dep_tables according to the outer join dependency. --- mysql-test/r/join_outer.result | 63 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 63 insertions(+) (limited to 'mysql-test/r/join_outer.result') diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 820d66b9264..266c8332b36 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2273,4 +2273,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; -- cgit v1.2.1