#At file:///home/rl136806/mysql/repo/mysql-review/ based on revid:jorgen.loland@stripped
3327 Roy Lyseng 2011-01-21
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 2011-01-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +0000
@@ -17078,6 +17078,10 @@ 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-20110121160232-amx5vweiebc065w7.bundle
