main.join_outer_jcl6 w1 [ fail ]
|
Test ended at 2016-05-11 18:15:30
|
|
CURRENT_TEST: main.join_outer_jcl6
|
--- /usr/local/mariadb-10.1.15-linux-x86_64/mysql-test/r/join_outer_jcl6.result 2016-05-11 15:58:50.000000000 +0300
|
+++ /usr/local/mariadb-10.1.15-linux-x86_64/mysql-test/r/join_outer_jcl6.reject 2016-05-11 18:15:30.114995946 +0300
|
@@ -2284,6 +2284,69 @@
|
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;
|
set join_cache_level=default;
|
show variables like 'join_cache_level';
|
|
mysqltest: Result length mismatch
|