#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
