List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:November 24 2010 8:17am
Subject:bzr commit into mysql-trunk branch (roy.lyseng:3287) Bug#57431
View as plain text  
#At file:///home/rl136806/mysql/repo/mysql-review/ based on revid:tor.didriksen@stripped

 3287 Roy Lyseng	2010-11-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 temporarily deleting the outerjoin information from the
      join_tab representing 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	2010-11-24 08:16:05 +0000
@@ -114,3 +114,35 @@ 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 c (
+  col_int_nokey INT DEFAULT NULL,
+  col_int_key INT DEFAULT NULL,
+  col_varchar_key VARCHAR(1) DEFAULT NULL,
+  col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+  KEY col_int_key(col_int_key),
+  KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO c VALUES (NULL,2,'w','w');
+INSERT INTO c VALUES (2,9,'t','t');
+INSERT INTO c VALUES (2,3,'n','n');
+INSERT INTO c VALUES (4,2,'d','d');
+CREATE TABLE bb (
+  col_int_nokey INT DEFAULT NULL,
+  col_int_key INT DEFAULT NULL,
+  col_varchar_key VARCHAR(1) DEFAULT NULL,
+  col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+  KEY col_int_key(col_int_key),
+  KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO bb VALUES (8,8,NULL,NULL);
+
+SELECT col_varchar_key
+FROM c
+WHERE col_int_nokey IN (SELECT INNR.col_int_nokey
+                        FROM bb LEFT JOIN bb INNR ON INNR.col_varchar_nokey)
+  AND col_int_nokey = 2;
+
+DROP TABLE c, bb;

=== modified file 'mysql-test/r/subquery_sj_innodb_all.result'
--- a/mysql-test/r/subquery_sj_innodb_all.result	2010-07-13 08:14:01 +0000
+++ b/mysql-test/r/subquery_sj_innodb_all.result	2010-11-24 08:16:05 +0000
@@ -141,4 +141,35 @@ 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 c (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO c VALUES (NULL,2,'w','w');
+INSERT INTO c VALUES (2,9,'t','t');
+INSERT INTO c VALUES (2,3,'n','n');
+INSERT INTO c VALUES (4,2,'d','d');
+CREATE TABLE bb (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO bb VALUES (8,8,NULL,NULL);
+SELECT col_varchar_key
+FROM c
+WHERE col_int_nokey IN (SELECT INNR.col_int_nokey
+FROM bb LEFT JOIN bb INNR ON INNR.col_varchar_nokey)
+AND col_int_nokey = 2;
+col_varchar_key
+DROP TABLE c, bb;
 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-07-13 08:14:01 +0000
+++ b/mysql-test/r/subquery_sj_innodb_all_jcl6.result	2010-11-24 08:16:05 +0000
@@ -145,6 +145,37 @@ 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 c (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO c VALUES (NULL,2,'w','w');
+INSERT INTO c VALUES (2,9,'t','t');
+INSERT INTO c VALUES (2,3,'n','n');
+INSERT INTO c VALUES (4,2,'d','d');
+CREATE TABLE bb (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO bb VALUES (8,8,NULL,NULL);
+SELECT col_varchar_key
+FROM c
+WHERE col_int_nokey IN (SELECT INNR.col_int_nokey
+FROM bb LEFT JOIN bb INNR ON INNR.col_varchar_nokey)
+AND col_int_nokey = 2;
+col_varchar_key
+DROP TABLE c, bb;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_innodb_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_innodb_all_jcl7.result	2010-07-13 08:14:01 +0000
+++ b/mysql-test/r/subquery_sj_innodb_all_jcl7.result	2010-11-24 08:16:05 +0000
@@ -145,6 +145,37 @@ 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 c (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO c VALUES (NULL,2,'w','w');
+INSERT INTO c VALUES (2,9,'t','t');
+INSERT INTO c VALUES (2,3,'n','n');
+INSERT INTO c VALUES (4,2,'d','d');
+CREATE TABLE bb (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO bb VALUES (8,8,NULL,NULL);
+SELECT col_varchar_key
+FROM c
+WHERE col_int_nokey IN (SELECT INNR.col_int_nokey
+FROM bb LEFT JOIN bb INNR ON INNR.col_varchar_nokey)
+AND col_int_nokey = 2;
+col_varchar_key
+DROP TABLE c, bb;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_innodb_none.result'
--- a/mysql-test/r/subquery_sj_innodb_none.result	2010-07-13 08:14:01 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none.result	2010-11-24 08:16:05 +0000
@@ -141,4 +141,35 @@ 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 c (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO c VALUES (NULL,2,'w','w');
+INSERT INTO c VALUES (2,9,'t','t');
+INSERT INTO c VALUES (2,3,'n','n');
+INSERT INTO c VALUES (4,2,'d','d');
+CREATE TABLE bb (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO bb VALUES (8,8,NULL,NULL);
+SELECT col_varchar_key
+FROM c
+WHERE col_int_nokey IN (SELECT INNR.col_int_nokey
+FROM bb LEFT JOIN bb INNR ON INNR.col_varchar_nokey)
+AND col_int_nokey = 2;
+col_varchar_key
+DROP TABLE c, bb;
 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-07-13 08:14:01 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none_jcl6.result	2010-11-24 08:16:05 +0000
@@ -145,6 +145,37 @@ 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 c (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO c VALUES (NULL,2,'w','w');
+INSERT INTO c VALUES (2,9,'t','t');
+INSERT INTO c VALUES (2,3,'n','n');
+INSERT INTO c VALUES (4,2,'d','d');
+CREATE TABLE bb (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO bb VALUES (8,8,NULL,NULL);
+SELECT col_varchar_key
+FROM c
+WHERE col_int_nokey IN (SELECT INNR.col_int_nokey
+FROM bb LEFT JOIN bb INNR ON INNR.col_varchar_nokey)
+AND col_int_nokey = 2;
+col_varchar_key
+DROP TABLE c, bb;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_innodb_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_innodb_none_jcl7.result	2010-07-13 08:14:01 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none_jcl7.result	2010-11-24 08:16:05 +0000
@@ -145,6 +145,37 @@ 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 c (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO c VALUES (NULL,2,'w','w');
+INSERT INTO c VALUES (2,9,'t','t');
+INSERT INTO c VALUES (2,3,'n','n');
+INSERT INTO c VALUES (4,2,'d','d');
+CREATE TABLE bb (
+col_int_nokey INT DEFAULT NULL,
+col_int_key INT DEFAULT NULL,
+col_varchar_key VARCHAR(1) DEFAULT NULL,
+col_varchar_nokey VARCHAR(1) DEFAULT NULL,
+KEY col_int_key(col_int_key),
+KEY col_varchar_key(col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO bb VALUES (8,8,NULL,NULL);
+SELECT col_varchar_key
+FROM c
+WHERE col_int_nokey IN (SELECT INNR.col_int_nokey
+FROM bb LEFT JOIN bb INNR ON INNR.col_varchar_nokey)
+AND col_int_nokey = 2;
+col_varchar_key
+DROP TABLE c, bb;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-16 16:17:25 +0000
+++ b/sql/sql_select.cc	2010-11-24 08:16:05 +0000
@@ -17027,10 +17027,14 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
     /* Do full scan of the materialized table */
     JOIN_TAB *last_tab= join_tab + (sjm->table_count - 1);
 
+
     Item *save_cond= last_tab->select_cond;
+    st_join_table *save_last_inner= last_tab->last_inner;
     last_tab->set_select_cond(sjm->join_cond, __LINE__);
+    last_tab->last_inner= NULL;
     rc= sub_select(join, last_tab, end_of_records);
     last_tab->set_select_cond(save_cond, __LINE__);
+    last_tab->last_inner= save_last_inner;
     DBUG_RETURN(rc);
   }
   else


Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20101124081605-576ge01lcj5kwor8.bundle
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3287) Bug#57431Roy Lyseng24 Nov
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3287) Bug#57431Jorgen Loland20 Jan
    • Re: bzr commit into mysql-trunk branch (roy.lyseng:3287) Bug#57431Roy Lyseng21 Jan