From: Roy Lyseng Date: June 20 2011 3:48pm Subject: bzr commit into mysql-trunk branch (roy.lyseng:3385) Bug#12640083 List-Archive: http://lists.mysql.com/commits/139499 X-Bug: 12640083 Message-Id: <20110620154815.45B121F5@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============5993501638338663998==" --===============5993501638338663998== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #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 --===============5993501638338663998== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/roy.lyseng@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: roy.lyseng@stripped # target_branch: file:///home/rl136806/mysql/repo/mysql-review/ # testament_sha1: 7df11fc917b93208aff8536461f01d6306278f42 # timestamp: 2011-06-20 17:48:15 +0200 # base_revision_id: jorgen.loland@stripped\ # yhbq58b26glco915 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWRbKa6IAJIpfgHqwXPf//3// /+C////wYBs+Hl9saYgNLWC605aBQJCgBQAABtIAAAoxCARCSkqIoAClACk1LCiiGqnqYnqZNABo Gg09QAaaAGQAAADQcZMmjQGjTEZGhiGBNGmIMRoMIADDjJk0aA0aYjI0MQwJo0xBiNBhAAYJNUTI QKeoaIz1NNEbKep7SQ9QYBAaGRoaGmQKVIQTTQ0p6YJiZAI1NHoTSMmyhtJibSNpqYIFShAJoACE 0ATU9TUyNGjRoA9TQHpADTaXV8aP8KYL9liv96MUO6juDXH40eFGqlqoxVWvAX+FGCbAP322mzFY rNMBiMRkYYa4rVRutqmlHFRnR++jbfzo3Ufwo3UalAz+Xu9mgYctuPkFa0OkVr08NlWnOZppDSgI hT+vXqyd9J/GjVU147Of12qn77c+07ZTl7GK5baVrX/e2vOTFxXWHZRro7FngwwuWj9xNpV84jH7 ZY1+34pwHzGSU+KVZ0S+JWF1lSlOe54idDIU63CmtrEq9dayhn8N9sgPBICyA9tGVHP2qMUYqNM6 NsV0qN0Vw2RW+K0UfXyGmFjD5MGonao49SVvqMUeajNU4qOSjFGQu3PL0cyN3DrS/p2ouMa2tSlu dPfX20w2SFNmZ6IefNI6JPbu1/JoW7zVH0VHVUaVJrl3ExT1z8P283GCZdfRkoyoyo/qDHvEeqSB 9MIckIboQRYixioiRIiKiKfB7A6/as9l6VdN2lk1vC40OG1BpQ4fHISXzSSY4TTcwJoEjYoZYyBb DsaM8VL75CT2KlblK5KV8Sd71+BPbJwxRijBO/0rQmI9NR1yecnFildwmTTtE0Ce/qpWp6KP5E1R +uTjL9hPvJgTJQ6Se+T40ripW2lfdStlK3UrVStVKxStKV9Oz3/P+dTpB1r5mFYxjFYU6dmNmvo6 yc1RlkTtk2SbuyTGzepuhjBl6NSmvTGmvjleKjLERgvy24ApWtkuSz7RFc7iKRGX6csInK2qZ8Zk 37tam25pOPBP40rppX3kzEypW4mRNKjImraMJuJmiP4rbOHC23G7gRa/yIxEaYSBd8PT6/f9vjT7 YWo25q78csMHqbuK+fMrTVTHVTVpoHf2yAk50/tCb/1RGPDZxr43W33Z50v3765XZXffINaRJHx/ RIUooxijDFHXSZX1k5CcRPAmZMyZkzJpVK9UnRtJ7BMCd5ME1AyExE8/cTMnfDsxV19JOsnET65N Cbfd/wVZUZUaZ6tMqMqMlG+o1PL6rJCyoyiu7YTyJgj4EUI2EUI2kcM9+YMiMiPtHzgJBGY2ZX8I axDjinuSlKqI4WRISiBGAsARAYNgwdD5JQqcGj4pBrrSnINeiyPmvfhHRVx57vO+XnJ7O/RyHL46 /mWZND2qnK91HNU0hypGWeLpvNZEm1QikJKSQ4yQeTlJGUki6CRpIk7u7FmrxurtrXnWula4Vrsy sWcK0pHBxcsrGVw44QoEM9WiELTKnUlYxjGMVVVURNfVSZkILCAkYGg2PHC/yoR9kkY4zFZcgwTG RGKtqIOjJkofIy00ZK0VMmJ0MyceIqwblV3ZoqlJIuWVZXwlNLJmyZMBLAokjJMnm4MjA1NqrKki KMbZtrwyNmeDakypDMQLPWq9RtUfl/TVvbVHBVRi4MmajRixatWOmTeovcXq4v1ufvD5gf0SFZyY wr3pSFISZbXqRdKrWRBw58cofeReReZyQhnknlRPLEjNBrR5YK0J/apXmanM4qmFDdeDs2SBznRw fLM2Nghv1nm9jzbd3oxvdNA9j+WRF78m2/GuzXLdfW3CX9tOeF2OmGXokeOuj3h1m9vf5WdGx75I wRfEUYUV37m6r/3vc8MsG6SMW/k2JIxarQm3n0wY5pPSXOTY3Kv58EMGRyqp00X91N7g3rwxnvUT Snp43M4j6RAZtOrN5Ojczd7OjNd1XB5RGjFmvd2LU9ck6PJwdLu9RYk9NHO0ZO5MRW3o9RMsUrU9 RMidOulY0W3Al1Iufs7hh7dG/e7MUPVb1i9Wqa0zUlnhgbVJZo0VlGGCkx9hGdcLUvU5UtJEnsaK SFtaJs2yIwXLu6p5tJIqG5oxUss3rMWVcV7NY9tYTVepuUqxerFwbWxe3KPV9JCzg4L2quKvZRzK dlHLq7GbiYitdK4umla92xjVzEeRHFk4rk2JGyRJTSzk3YMXNvlW2/isqhpWYMyNWw7NxGTBZXFi 7+7vg0w1dNGa6d2mmxw4eFnFm2tHNay/JyVe5lEXdiOLkufueA3O9+XTJ0VcH9Tr1yaMWZwUqzc3 JabuCi91kOL2pExvwdYjbXVTW1/Hl2iPIji0DPIkaSRRhRxXs7s8G9tUlV7RpZl1XqKrM6KqRGQX rWblJi5Mi2TJZVtYK4sLm1ZJF1y5mxki3JflmubFS9bSnokYyRJqzbl5Zi2rbl7JVudetWTdi2Mp CzVc0YLPvcVZD93J5Pc92CeKbubhN1rK2z49mvUqYYYqa06WDNsZFzVMGrqbsa7s9bsXjNTlVVld OiiSOq8yiRVs2+G4vSmag2vpJG9quYRipfe40ctO0kYSFmxhUzb2Wxgss+0hvcXNVwZ5KtFWcJ+V c4trbNZD975Pe6OfJxopv8b+9/XvLSkot0w792oYsXRzs9iitPXcz2LpIldu9ZCaKstxkxb8WeBI v1VdllVnkvmyiyqpfp8812q5nvWxeGKirPO1zMwjzqMHDNo5udMJIokbm5Rk3lWWSi9eoxVXOSiy 9WSLlA8O7hk7ceHOvXS/dh6OQb2dzJmKXUSY3WZ9HNwXvlzYtHy1yX7ZDa4KWNFWDVJFV04uhepc wMMPh54PHhezdHtWVdG4vWc5R5/ZuZO/sY9OskcuV7u7N7RwdnZq6NyyjayXMFExwXt6lnJ4YNVz zpsUwIz1vw2bXZtZqSRmq7r7tzVk4KBpx588nBzZX1bJkvfnDVhndtzSNM9i0RydWCzg3PDD3r3F PNt4M3PNh3bmvDbe1ZNXRtZujBjZVg3XceskSWTVrJG5Vubd7FVm4LmD1SRjVx0kOeW9a5Kq05dL vYvYThwb1lGxVZlnnyd++skcWxicGe/fGraylAxXKt691/LZk3t+/gz9dKqUclCrcorvcWNuLYsq 2t/do7Pqkjwx0auvo2XqMW1m1c+eji2uzicmb1D4ZMKRHe/tVJSjpR0pGnXpXCkkKJIY2rJBxypc kLATHJt0a+OnQ4SAyBhR6qMlGrFGXpq6EWFhYtMsYxhjKSgcUhJzEg13beW9GQGRQm+EMIErBjGM YxVVVRE8QQ3eqEOjlM7yQ5pKfoCfaTpJ9FD+5OdSv6E+yqrKFOYq/ATKpz9IxiWCYwx/MJ+gH9y2 fNsE1lX2k0QthMlK0ExHJGonAq4VK4BNRMirfmhfsExSvuQtCf9RuJqjnJiOInyVK2E4AwTXvo/s EyQ5aOAmn8cAsqVpwJ+JP8VK4VVdGqoxR/BSspPuJ/Um4mcn8CYpX4fjpFZxMUYxR2JE5AY7CfaE 4IX4fjsiuuFOCGKMYKGCbuCF/aqraT2bM6VopWKqNalaoyJgm4GwTMmcrBOupLOE7if3UrqQuTji uXo6M4rUTbSugTiJzE5I/yTkJNe2jGKMrmLUTXSuBMqldoTzVVetC8SaE1UMTFWLEYwsWFV6siZQ puJx0YJ4YUcxMycmFjFjFL1YWQ31HOTfSuUnnxgmKMUbY1UreTIm0JvKtCZk85N/kX+S4iahI2FW 1Q5KDBObbBZScZOUFgTAmBMX7CqmwIfsST5uKEOrCCglT0b/Re17YUta0uYXte92rLPTV9MX6cVR hKxJWCrAmuk+yQPro3Cbqn37uI/Vok1sGFWDAmN+BfbUrUVU5qOij8BP3LV9ym5+xtkizarEXYOS 9Z+DFzfgr0Vfg/uZKKNpgyZd25Vi3as25ZZuf4NzR4b3+nOQ7ZdBFVc1hGSgi4j8e0kG51dnR2aO /n2i/k4yvTR5IUxRhSsUYUr+IfqXyyeMmvgtbFcWXHU8GvvzOXIiXIRLok2fJlKY41bg0k1v581Q Tkupvft12Kn/GI4sFli0kDPro5w+q7iRT1rpREe5xexezUvw8/2fo/zfJzaSRZi+b6L1Gqza4tq3 +LJ9WbJjRZVwaP2MTiv+bFzMVGBH7drFqvbhFznVZuVEVaNJkoxUarnM9eT8eTfrIeshTw7JI9ZI vSSNXKo2JEflfI6/ZJFa/kkNPG/yEXvLws9d3hnnnh2eqjLKzzbHk1fi9jR2UWZMGTYqzbVWL2Ve 9vOOlzmnNU+slk8TY0ZMHtOT9qSNwi5i4LKqOKij08Xv3ln19KJ8OxCJW1zPzvmdSp6tPIjKodTc 9VTppWfpo/7JkT51P6WqqNVGKjQTKjBNRM6h685NpXt+LnUvAPncKM0mEiUUFG6nERRMIiko5vDk +5x+HvWUevtVZNjJZ83zcWs472az69uckZnGSPubmNz4uTH5Or6L6OLk4aKcZEZyRUxdWT6rlzc1 avuI4Llze4Nz9Lf9zBr5PLkSHm3ujV+dJ4ybM6OZ8P5N73OG90/QXmdnKo9NHwU/IsD2sZyZcTF8 ZMaZ5E6/L2apnsqHaYiK+16Ku75/PPTHxg8174vjEfJRxyjfQPk0kO74PguUZti9cqvVaPRZgXKO ajcqydi6krEnZJ5ePz5SZaeeToGKPkF0vTS5sbVF7Q4Oj4RiD86RviNvrt8uvFc4ckwo8221jipe 3/d9Sbe/spbBI/bZ5zquOeX019tLPNvf20e7rB0cZWWOMPf56PRU9usLXSeRXxsqniDieHoyqm3e Q1SfNRpVpaBfdJEmcRrowJu1zApdAX0ej5MotfLC7GKXOpxUndJS3dCnFJ6p7X1+vxer3ve9WT4v mzz14vivbWKj2PgxbWr7lFGLRiuVb2bw6kaqSRJi0YsF7K9ezqzWVUfVZsLkii9WInWgOjRy5c2j ndqnoBiTAPHz/1k8JMpMex8Cz7XRRjHuzqceHL3MfRedrqHwxIc1J5gyy2MqpxPZr30YFip4t8m6 p2Ycm07qYe6mp9HyeTyXRvJHLk33EIlGk/NJH0qn3YJw13v/JGmEr5WXzvg1rU5Xe6KOdqeKXF7c M3X4POHeHCoJvoy+GrdR5vKk15ejfzfCk4ntcypo11V3bHX3YQwykDL4uT8NRXkL2KMlHPz1Dhig 6NIn0+/x2ex8XBxC/56Kt3pxp0iyenxzcUngWuTB5CyTEn00eGaR1M3YR62vg+L6W96W7U4IZ46q na6Hytf5YU9WBMsVRuZ/kZk6ueLxoxQpijFGbAu59NG0Oh+VVuOpTtY+Y6RYaBpgPVRmszUe0qp4 4qe47P78JA4mt5VN3lRzSclQwkyoyZg2UZ+vVwiiZYN/vqay+DmaJOBOfKTe5aPDwbqp1de5Q58a +9cplgsl8LrVpzpNhXj0bretdn8K73ZXRGSRcGdLW7kfAjwvkMVvbJMAyxkyZffhyNNVTUy0kDjZ KZmfLJr67NphVj7EMhZVMFGs9uBnlXqvuoSCkkjBtzmpHMjqHr7ejTw45MhMSWOPKp5M730dvfPJ 1anoqeCpwJyfmczrk2OojaVU8qnpZSp79cmtLcWest3jjhU/zUOUXuDPuctTKz0vJ6C3mlHTnzOl U30e59uVU6+2lryk9mUmKmSn1KOPc7PfCmzpoxij4iZ0rmcQbfhR5depq5M6ObBbGnoebzVO7297 jZPyZUZOqqdzj8nc6pOOTGP82BGj1g8x51MmWWBPdR9NHtDVC2NRVT00bKqZ4DLT0tUVLJMad5ml b9Od3qZntKxqk1SmupmxJ7oitMfbWyRm6vPOQtJFA46e9a6X/q93HLxxo6X+qmJOH5/NyA9D5Q51 Z8OdmmcmeFMYxhksRH1Ue6jXopjSTL9POpld0+4GEukgRqw7c5C+JqswmfXqewU7SHjgE66E1qnn QMYjY1kOP4JBtpIAbAJyYwhpERkkDpkh3HI3QhuVXZtiPW665cRi692yQqSK0I4qytCchOCoyQ0a FVVVVVVVVVVVVVVVVVVVVVVXQFKLhJJLazgCbvShFEL9lhHWQupOd1tqVUFrSFyjRauwyNCcskOu jFK+XBONq59e2TGZYo+tJxRkxRi6rdRxbCtP1q2pt88mWsMnPoqaVaKel1E4EzSY2Uhz5yu3H59v P5upnIG3G4+twLRUwJxm+QrVuiM6yR/A3+O/WQ7SH6GjBrqtjCi7/ocr4Ue2pXrUn2SfR63JPy8d VYua52fZRyl4+bw9MngyJxVKzzVOPtfmO91vM8FTjcYnGVUzbbzyan0UYwwJ6M3k5Be8nRr+t7li aXSBwU5yFpC76OFHgzpeLCna7mVJvVP9KOJxfmYaqpyjEowxSjTKk78ZiruyaVTHHvZRqowI+hy1 NqjSj7t1U+EI/W5+anvDJ5fmesQyc9e/tkPY0fV/+LuSKcKEgLZTXRA= --===============5993501638338663998==--