main.join_outer_jcl6 fails in buildbot

Change #109762

Category	None

Changed by	Sergei Petrunia <psergey@askmonty.org>

Changed at	Wed 11 May 2016 15:55:14

Repository	https://github.com/MariaDB/server

Branch	10.1

Revision	5c68bc2c3264fa2c4832c468bad32701dd3d4ed0

Comments

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.

Changed files

mysql-test/r/join_outer.result

mysql-test/t/join_outer.test

sql/sql_select.cc

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