#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
