List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:June 20 2011 3:48pm
Subject:bzr commit into mysql-trunk branch (roy.lyseng:3385) Bug#12640083
View as plain text  
#At file:///home/rl136806/mysql/repo/mysql-review/ based on revid:jorgen.loland@stripped

 3385 Roy Lyseng	2011-06-20
      Bug#12640083: Same query executed as WHERE subquery gives different
                    results on IN() compare
      
      The query that returns with an empty result is using the
      MaterializeScan semi-join strategy, and it has an outer join
      operation inside the materialized subquery. As has happened before,
      there are remnants of outer join execution in the join_tab that is
      reused for reading the materialized tables, in this case it was the
      first_unmatched field that needed clearing.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#12640083.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
        Added test case result for bug#12640083.
      
      sql/sql_select.cc
        In sub_select_sjm(), cleared the flag last_tab->first_unmatched.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2011-06-15 10:50:48 +0000
+++ b/mysql-test/include/subquery_sj.inc	2011-06-20 15:47:50 +0000
@@ -3617,3 +3617,56 @@ WHERE GRANDPARENT1.i2
 ORDER BY GRANDPARENT1.i2 ;
 
 DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug#12640083: Same query executed as WHERE subquery gives different
+--echo #              results on IN() compare
+--echo #
+
+CREATE TABLE t1 (
+  pk int NOT NULL,
+  col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+  col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+  KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+
+CREATE TABLE t2 (
+  pk int NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (pk)
+) ENGINE=Innodb;
+
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+     LEFT JOIN t1 AS alias2
+       JOIN t2 AS alias3
+       ON alias2.col_varchar_10_latin1_key
+     ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk; 
+
+let $query=
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+    SELECT alias1.col_varchar_10_latin1_key 
+    FROM t1 AS alias1
+         LEFT JOIN t1 AS alias2
+           JOIN t2 AS alias3
+           ON alias2.col_varchar_10_latin1_key
+         ON alias1.col_varchar_1024_utf8_key
+    WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2, t3;
+
+--echo # End of the test for bug#12640083.

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2011-06-20 15:47:50 +0000
@@ -5504,4 +5504,63 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start materialize; Scan
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2011-06-20 15:47:50 +0000
@@ -5508,5 +5508,64 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start materialize; Scan
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2011-06-20 15:47:50 +0000
@@ -5508,5 +5508,64 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start materialize; Scan
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2011-06-20 15:47:50 +0000
@@ -5503,4 +5503,63 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2011-06-20 15:47:50 +0000
@@ -5507,5 +5507,64 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2011-06-20 15:47:50 +0000
@@ -5507,5 +5507,64 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End temporary
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2011-06-20 15:47:50 +0000
@@ -5505,6 +5505,65 @@ i1
 1
 DROP TABLE t1,t2;
 #
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2011-06-20 15:47:50 +0000
@@ -5509,6 +5509,65 @@ i1
 1
 DROP TABLE t1,t2;
 #
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2011-06-20 15:47:50 +0000
@@ -5509,6 +5509,65 @@ i1
 1
 DROP TABLE t1,t2;
 #
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; FirstMatch(t3)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2011-06-20 15:47:50 +0000
@@ -5504,4 +5504,63 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2011-06-20 15:47:50 +0000
@@ -5508,5 +5508,64 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2011-06-20 15:47:50 +0000
@@ -5508,5 +5508,64 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End temporary
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2011-06-20 15:47:50 +0000
@@ -5504,4 +5504,63 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start materialize; Scan
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2011-06-20 15:47:50 +0000
@@ -5508,5 +5508,64 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start materialize; Scan
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2011-06-20 15:47:50 +0000
@@ -5508,5 +5508,64 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start materialize; Scan
+1	PRIMARY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+1	PRIMARY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; End materialize
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2011-06-20 15:47:50 +0000
@@ -5726,4 +5726,63 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	alias1	ALL	PRIMARY	NULL	NULL	NULL	4	Using where
+2	SUBQUERY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index; Using join buffer (BNL, incremental buffers)
+2	SUBQUERY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2011-06-20 15:47:50 +0000
@@ -5649,4 +5649,63 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	alias1	ref	PRIMARY,col_varchar_10_latin1_key	col_varchar_10_latin1_key	13	func	2	Using where
+2	DEPENDENT SUBQUERY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2011-06-20 15:47:50 +0000
@@ -5653,5 +5653,64 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	alias1	ref	PRIMARY,col_varchar_10_latin1_key	col_varchar_10_latin1_key	13	func	2	Using where
+2	DEPENDENT SUBQUERY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index; Using join buffer (BNL, incremental buffers)
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2011-06-15 10:50:48 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2011-06-20 15:47:50 +0000
@@ -5653,5 +5653,64 @@ ORDER BY GRANDPARENT1.i2 ;
 i1
 1
 DROP TABLE t1,t2;
+#
+# Bug#12640083: Same query executed as WHERE subquery gives different
+#              results on IN() compare
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
+col_varchar_10_latin1_key varchar(10) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_varchar_1024_utf8_key(col_varchar_1024_utf8_key(333)),
+KEY col_varchar_10_latin1_key(col_varchar_10_latin1_key)
+);
+INSERT INTO t1 VALUES
+(1, 'a', 'a'),
+(2, 'ab', 'ab'),
+(3, 'abc', 'abc'),
+(4, 'abcd', 'abcd');
+CREATE TABLE t2 (
+pk int NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk)
+) ENGINE=Innodb;
+CREATE TABLE t3
+SELECT alias1.col_varchar_10_latin1_key
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk;
+EXPLAIN SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	alias1	ref	PRIMARY,col_varchar_10_latin1_key	col_varchar_10_latin1_key	13	func	2	Using where
+2	DEPENDENT SUBQUERY	alias3	index	NULL	PRIMARY	4	NULL	1	Using where; Using index
+2	DEPENDENT SUBQUERY	alias2	index	NULL	col_varchar_10_latin1_key	13	NULL	4	Using where; Using index
+SELECT * 
+FROM t3 
+WHERE col_varchar_10_latin1_key IN (
+SELECT alias1.col_varchar_10_latin1_key 
+FROM t1 AS alias1
+LEFT JOIN t1 AS alias2
+JOIN t2 AS alias3
+ON alias2.col_varchar_10_latin1_key
+ON alias1.col_varchar_1024_utf8_key
+WHERE alias1.pk AND alias1.pk < 3 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+DROP TABLE t1, t2, t3;
+# End of the test for bug#12640083.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-06-11 13:38:32 +0000
+++ b/sql/sql_select.cc	2011-06-20 15:47:50 +0000
@@ -17420,6 +17420,7 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
 
       // Clear possible outer join information from earlier use of this join tab
       last_tab->last_inner= NULL;
+      last_tab->first_unmatched= NULL;
     }
   }
   else


Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20110620154750-v8xter9boutoccij.bundle
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3385) Bug#12640083Roy Lyseng20 Jun