List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:June 16 2011 10:56am
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-16
      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-16 10:55:05 +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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+
+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 < 7 OR alias1.pk AND alias3.pk; 
+
+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 < 7 OR alias1.pk AND alias3.pk); 
+
+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-16 10:55:05 +0000
@@ -5504,4 +5504,56 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5508,5 +5508,57 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5508,5 +5508,57 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5503,4 +5503,56 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5507,5 +5507,57 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5507,5 +5507,57 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5505,6 +5505,58 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5509,6 +5509,58 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5509,6 +5509,58 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5504,4 +5504,56 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5508,5 +5508,57 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5508,5 +5508,57 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5504,4 +5504,56 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5508,5 +5508,57 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5508,5 +5508,57 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5726,4 +5726,56 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5649,4 +5649,56 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5653,5 +5653,57 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +0000
@@ -5653,5 +5653,57 @@ 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'),
+(5, 'abcde', 'abcde'),
+(6, 'abcdef', 'abcdef'),
+(7, 'abcdefg', 'abcdefg'),
+(8, 'abcdefgh', 'abcdefgh'),
+(9, 'abcdefghi', 'abcdefghi'),
+(10, 'abcdefghij', 'abcdefghij');
+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 < 7 OR alias1.pk AND alias3.pk;
+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 < 7 OR alias1.pk AND alias3.pk);
+col_varchar_10_latin1_key
+a
+ab
+abc
+abcd
+abcde
+abcdef
+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-16 10:55:05 +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-20110616105505-4ub7twm2m99hcfzf.bundle
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3385) Bug#12640083Roy Lyseng16 Jun
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3385) Bug#12640083Øystein Grøvlen19 Jun
    • Re: bzr commit into mysql-trunk branch (roy.lyseng:3385) Bug#12640083Roy Lyseng20 Jun