List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:January 24 2011 11:57am
Subject:bzr commit into mysql-trunk branch (roy.lyseng:3327) Bug#57431
View as plain text  
#At file:///home/rl136806/mysql/repo/mysql-review/ based on revid:jorgen.loland@stripped

 3327 Roy Lyseng	2011-01-24
      Bug#57431: subquery returns wrong result (semijoin=on) with pred AND
      
      The test case contains an outer query with a single table and an
      IN subquery with two outerjoined tables. The MaterializeScan
      semijoin strategy is selected, meaning that an outerjoin operation
      is first performed over join_tabs 0 and 1. After the outerjoin,
      sub_select_sjm() is called to perform a semijoin between the
      result of the outerjoin in join_tab 1 and the outer table in
      join_tab 2. However, join_tab 1 still contains some reminiscent data
      from the outerjoin operation, hence this operation also behaves like
      an outerjoin.
      
      Fixed by clearing the outerjoin information from the join_tab that
      represents the result of the outerjoin operation.
      
      mysql-test/include/subquery_sj_innodb.inc
        Test case for Bug#57431. The problem can only be reproduced with
        1 row in the inner table, hence Innodb is needed to prevent
        'const table' optimization.
      
      mysql-test/r/subquery_sj_innodb_all.result mysql-test/r/subquery_sj_innodb_all_jcl6.result
      mysql-test/r/subquery_sj_innodb_all_jcl7.result
      mysql-test/r/subquery_sj_innodb_none.result
      mysql-test/r/subquery_sj_innodb_none_jcl6.result
      mysql-test/r/subquery_sj_innodb_none_jcl7.result
        Updated test results for Bug#57431.
      
      sql/sql_select.cc
        In sub_select_sjm(), clear the last_inner field of the first
        join_tab to take part in the semijoin operation.

    modified:
      mysql-test/include/subquery_sj_innodb.inc
      mysql-test/r/subquery_sj_innodb_all.result
      mysql-test/r/subquery_sj_innodb_all_jcl6.result
      mysql-test/r/subquery_sj_innodb_all_jcl7.result
      mysql-test/r/subquery_sj_innodb_none.result
      mysql-test/r/subquery_sj_innodb_none_jcl6.result
      mysql-test/r/subquery_sj_innodb_none_jcl7.result
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj_innodb.inc'
--- a/mysql-test/include/subquery_sj_innodb.inc	2010-07-13 08:14:01 +0000
+++ b/mysql-test/include/subquery_sj_innodb.inc	2011-01-24 11:56:54 +0000
@@ -114,3 +114,24 @@ explain select 1 from t2 where 
   c2 in (select 1 from t3, t2) and
   c1 in (select convert(c6,char(1)) from t2);
 drop table t2, t3;
+--echo # 
+--echo # BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
+--echo # 
+CREATE TABLE t1 (
+   i INT
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (2),(4);
+
+CREATE TABLE t2 (
+   i INT,
+   vc VARCHAR(1)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (8,NULL);
+
+SELECT i
+FROM t1
+WHERE i IN (SELECT innr.i
+             FROM t2 LEFT JOIN t2 innr ON innr.vc)
+   AND i = 2;
+
+DROP TABLE t1, t2;

=== modified file 'mysql-test/r/subquery_sj_innodb_all.result'
--- a/mysql-test/r/subquery_sj_innodb_all.result	2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/subquery_sj_innodb_all.result	2011-01-24 11:56:54 +0000
@@ -141,4 +141,23 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
 drop table t2, t3;
+# 
+# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
+# 
+CREATE TABLE t1 (
+i INT
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (2),(4);
+CREATE TABLE t2 (
+i INT,
+vc VARCHAR(1)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (8,NULL);
+SELECT i
+FROM t1
+WHERE i IN (SELECT innr.i
+FROM t2 LEFT JOIN t2 innr ON innr.vc)
+AND i = 2;
+i
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_innodb_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_innodb_all_jcl6.result	2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/subquery_sj_innodb_all_jcl6.result	2011-01-24 11:56:54 +0000
@@ -145,5 +145,24 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
 drop table t2, t3;
+# 
+# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
+# 
+CREATE TABLE t1 (
+i INT
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (2),(4);
+CREATE TABLE t2 (
+i INT,
+vc VARCHAR(1)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (8,NULL);
+SELECT i
+FROM t1
+WHERE i IN (SELECT innr.i
+FROM t2 LEFT JOIN t2 innr ON innr.vc)
+AND i = 2;
+i
+DROP TABLE t1, t2;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_innodb_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_innodb_all_jcl7.result	2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/subquery_sj_innodb_all_jcl7.result	2011-01-24 11:56:54 +0000
@@ -145,5 +145,24 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
 drop table t2, t3;
+# 
+# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
+# 
+CREATE TABLE t1 (
+i INT
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (2),(4);
+CREATE TABLE t2 (
+i INT,
+vc VARCHAR(1)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (8,NULL);
+SELECT i
+FROM t1
+WHERE i IN (SELECT innr.i
+FROM t2 LEFT JOIN t2 innr ON innr.vc)
+AND i = 2;
+i
+DROP TABLE t1, t2;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_innodb_none.result'
--- a/mysql-test/r/subquery_sj_innodb_none.result	2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none.result	2011-01-24 11:56:54 +0000
@@ -141,4 +141,23 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
 2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 drop table t2, t3;
+# 
+# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
+# 
+CREATE TABLE t1 (
+i INT
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (2),(4);
+CREATE TABLE t2 (
+i INT,
+vc VARCHAR(1)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (8,NULL);
+SELECT i
+FROM t1
+WHERE i IN (SELECT innr.i
+FROM t2 LEFT JOIN t2 innr ON innr.vc)
+AND i = 2;
+i
+DROP TABLE t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_innodb_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_innodb_none_jcl6.result	2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none_jcl6.result	2011-01-24 11:56:54 +0000
@@ -145,5 +145,24 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
 2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
 drop table t2, t3;
+# 
+# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
+# 
+CREATE TABLE t1 (
+i INT
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (2),(4);
+CREATE TABLE t2 (
+i INT,
+vc VARCHAR(1)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (8,NULL);
+SELECT i
+FROM t1
+WHERE i IN (SELECT innr.i
+FROM t2 LEFT JOIN t2 innr ON innr.vc)
+AND i = 2;
+i
+DROP TABLE t1, t2;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_innodb_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_innodb_none_jcl7.result	2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none_jcl7.result	2011-01-24 11:56:54 +0000
@@ -145,5 +145,24 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
 2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
 drop table t2, t3;
+# 
+# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
+# 
+CREATE TABLE t1 (
+i INT
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (2),(4);
+CREATE TABLE t2 (
+i INT,
+vc VARCHAR(1)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (8,NULL);
+SELECT i
+FROM t1
+WHERE i IN (SELECT innr.i
+FROM t2 LEFT JOIN t2 innr ON innr.vc)
+AND i = 2;
+i
+DROP TABLE t1, t2;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-01-13 14:47:29 +0000
+++ b/sql/sql_select.cc	2011-01-24 11:56:54 +0000
@@ -17078,6 +17078,9 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
       last_tab->read_record.copy_field_end= sjm->copy_field +
                                             sjm->table_cols.elements;
       last_tab->read_record.read_record= rr_sequential_and_unpack;
+
+      // Clear possible outer join information from earlier use of this join tab
+      last_tab->last_inner= NULL;
     }
   }
   else


Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20110124115654-di2zbjgmv7im7oxk.bundle
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3327) Bug#57431Roy Lyseng24 Jan