Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10072

main.join_outer_jcl6 fails in buildbot

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • N/A
    • 10.1.15
    • Tests
    • None

    Description

      The last push in 10.1 broke 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
      

      Please fix before it's merged to 10.2.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.