From: Roy Lyseng
Date: October 26 2010 2:15pm
Subject: bzr commit into mysql-next-mr-bugfixing branch (roy.lyseng:3270) Bug#52068
List-Archive: http://lists.mysql.com/commits/121934
X-Bug: 52068
Message-Id: <20101026141600.DDDEF1EC@tyr67.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============2592823880971518008=="

--===============2592823880971518008==
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-work5/ based on revid:roy.lyseng@stripped

 3270 Roy Lyseng	2010-10-26
      Bug#52068: Optimizer generates invalid semijoin materialization plan
      
      When MaterializeScan semijoin strategy was used and there were one
      or more outer dependent tables before the semijoin tables, the scan
      over the materialized table was not properly reset for each row of
      the prefix outer tables.
      
      Example: suppose we have a join order:
      
        ot1 SJ-Mat-Scan(it2 it3)  ot4
      
      Notice that this is called a MaterializeScan, even though there is an
      outer table ahead of the materialized tables. Usually a MaterializeScan
      has the outer tables after the materialized table, but this is
      a special (but legal) case with outer dependent tables both before and
      after the materialized table.
      
      For each qualifying row from ot1, a new scan over the materialized
      table must be set up. The code failed to do that, so all scans after
      the first one returned zero rows from the materialized table.
      
      mysql-test/include/subquery_sj.inc
        Bug#52068: Optimizer generates invalid semijoin materialization plan
        Added new test.
      
      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
        Bug#52068: Optimizer generates invalid semijoin materialization plan
        Earlier wrong semijoin materialization test results are corrected.
       
      sql/sql_select.cc
        Bug#52068: Optimizer generates invalid semijoin materialization plan
      
        In sub_select_sjm(), moved code that initializes the scan over the
        materialized table so that it is now performed for each scan of
        table, instead of only for the first scan.  

    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	2010-10-26 10:43:50 +0000
+++ b/mysql-test/include/subquery_sj.inc	2010-10-26 14:15:32 +0000
@@ -3433,3 +3433,28 @@ eval explain $query;
 DROP TABLE t1,t2,t3;
 
 --echo # End of the test for bug#50019.
+
+--echo #
+--echo # Bug#52068: Optimizer generates invalid semijoin materialization plan
+--echo #
+
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+
+let $query=
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+                        FROM it2,it3);
+
+eval $query;
+eval explain $query;
+
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+
+--echo # End of the test for bug#52068.

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2010-10-26 14:15:32 +0000
@@ -4926,6 +4926,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4934,6 +4935,7 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4953,6 +4955,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4962,6 +4965,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4971,6 +4975,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4990,6 +4995,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4999,6 +5005,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
+2
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5265,4 +5272,39 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Start materialize; Scan
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	End materialize
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2010-10-26 14:15:32 +0000
@@ -4930,6 +4930,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4938,6 +4939,7 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4957,6 +4959,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4966,6 +4969,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4975,6 +4979,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4994,6 +4999,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -5003,6 +5009,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
+2
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5269,6 +5276,41 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Start materialize; Scan
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	End materialize
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2010-10-26 14:15:32 +0000
@@ -4930,6 +4930,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4938,6 +4939,7 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4957,6 +4959,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4966,6 +4969,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4975,6 +4979,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4994,6 +4999,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -5003,6 +5009,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
+2
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5269,6 +5276,41 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Start materialize; Scan
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	End materialize
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2010-10-26 14:15:32 +0000
@@ -5271,4 +5271,39 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, regular buffers)
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (BNL, regular buffers)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-10-26 14:15:32 +0000
@@ -5275,6 +5275,41 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-10-26 14:15:32 +0000
@@ -5275,6 +5275,41 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, regular buffers)
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (BNL, regular buffers)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2010-10-26 14:15:32 +0000
@@ -5273,6 +5273,41 @@ id	select_type	table	type	possible_keys	
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
 #
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (BNL, regular buffers)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
+#
 # 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	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-10-26 14:15:32 +0000
@@ -5277,6 +5277,41 @@ id	select_type	table	type	possible_keys	
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
 #
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
+#
 # 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	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-10-26 14:15:32 +0000
@@ -5277,6 +5277,41 @@ id	select_type	table	type	possible_keys	
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
 #
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (BNL, regular buffers)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
+#
 # 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	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2010-10-26 14:15:32 +0000
@@ -5276,4 +5276,39 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, regular buffers)
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (BNL, regular buffers)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-10-26 14:15:32 +0000
@@ -5280,6 +5280,41 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-10-26 14:15:32 +0000
@@ -5280,6 +5280,41 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (BNL, regular buffers)
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (BNL, regular buffers)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2010-10-26 14:15:32 +0000
@@ -2651,6 +2651,8 @@ select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
@@ -2663,6 +2665,8 @@ select left(a1,7), left(a2,7)
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2775,6 +2779,8 @@ select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
@@ -2787,6 +2793,8 @@ select left(a1,7), left(a2,7)
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2864,6 +2872,8 @@ select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select b1 from t2_513 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
@@ -2876,6 +2886,8 @@ select left(a1,7), left(a2,7)
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2940,6 +2952,8 @@ select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
@@ -2952,6 +2966,8 @@ select left(a1,7), left(a2,7)
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3029,6 +3045,8 @@ select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
@@ -3041,6 +3059,8 @@ select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4906,6 +4926,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4914,6 +4935,7 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4933,6 +4955,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4942,6 +4965,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4951,6 +4975,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4970,6 +4995,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4979,6 +5005,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
+2
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5245,4 +5272,39 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Start materialize; Scan
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	End materialize
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2010-10-26 14:15:32 +0000
@@ -2655,6 +2655,8 @@ select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
@@ -2667,6 +2669,8 @@ select left(a1,7), left(a2,7)
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2779,6 +2783,8 @@ select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
@@ -2791,6 +2797,8 @@ select left(a1,7), left(a2,7)
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2868,6 +2876,8 @@ select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select b1 from t2_513 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
@@ -2880,6 +2890,8 @@ select left(a1,7), left(a2,7)
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2944,6 +2956,8 @@ select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
@@ -2956,6 +2970,8 @@ select left(a1,7), left(a2,7)
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3033,6 +3049,8 @@ select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
@@ -3045,6 +3063,8 @@ select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4910,6 +4930,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4918,6 +4939,7 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4937,6 +4959,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4946,6 +4969,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4955,6 +4979,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4974,6 +4999,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4983,6 +5009,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
+2
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5249,6 +5276,41 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Start materialize; Scan
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	End materialize
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2010-10-26 14:15:32 +0000
@@ -2655,6 +2655,8 @@ select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
@@ -2667,6 +2669,8 @@ select left(a1,7), left(a2,7)
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2779,6 +2783,8 @@ select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
@@ -2791,6 +2797,8 @@ select left(a1,7), left(a2,7)
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2868,6 +2876,8 @@ select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select b1 from t2_513 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
@@ -2880,6 +2890,8 @@ select left(a1,7), left(a2,7)
 from t1_513
 where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_513
 where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2944,6 +2956,8 @@ select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
@@ -2956,6 +2970,8 @@ select left(a1,7), left(a2,7)
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3033,6 +3049,8 @@ select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
@@ -3045,6 +3063,8 @@ select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
 left(a1,7)	left(a2,7)
+1 - 01x	2 - 01x
+1 - 02x	2 - 02x
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4910,6 +4930,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4918,6 +4939,7 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
 SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
 pk
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4937,6 +4959,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4946,6 +4969,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4955,6 +4979,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Start temporary
@@ -4974,6 +4999,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
 pk
 1
+2
 EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
@@ -4983,6 +5009,7 @@ Note	1003	select `test`.`t1`.`pk` AS `pk
 SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
 pk
 1
+2
 DROP TABLE t1, t2;
 # End of Bug#48213
 # 
@@ -5249,6 +5276,41 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; End materialize
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Start materialize; Scan
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	End materialize
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2010-10-26 14:15:32 +0000
@@ -5494,4 +5494,39 @@ id	select_type	table	type	possible_keys	
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (BNL, regular buffers)
+2	SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2010-10-26 14:15:32 +0000
@@ -5420,4 +5420,39 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (BNL, regular buffers)
+2	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2010-10-26 14:15:32 +0000
@@ -5424,6 +5424,41 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2010-10-26 14:15:32 +0000
@@ -5424,6 +5424,41 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, regular buffers)
 DROP TABLE t1,t2,t3;
 # End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a	a
+5	1
+8	1
+5	5
+8	5
+5	7
+8	7
+5	7
+8	7
+5	1
+8	1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (BNL, regular buffers)
+2	DEPENDENT SUBQUERY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	DEPENDENT SUBQUERY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-10-26 10:43:50 +0000
+++ b/sql/sql_select.cc	2010-10-26 14:15:32 +0000
@@ -16954,28 +16954,27 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
       Ok, materialization finished. Initialize the access to the temptable
     */
     sjm->materialized= TRUE;
+
     join_tab->read_record.read_record= join_no_more_records;
-    if (sjm->is_scan)
-    {
-      /* Initialize full scan */
-      JOIN_TAB *last_tab= join_tab + (sjm->table_count - 1);
-      init_read_record(&last_tab->read_record, join->thd,
-                       sjm->table, NULL, TRUE, TRUE, FALSE);
-
-      DBUG_ASSERT(last_tab->read_record.read_record == rr_sequential);
-      last_tab->read_first_record= join_read_record_no_init;
-      last_tab->read_record.copy_field= sjm->copy_field;
-      last_tab->read_record.copy_field_end= sjm->copy_field +
-                                            sjm->table_cols.elements;
-      last_tab->read_record.read_record= rr_sequential_and_unpack;
-    }
   }
 
   if (sjm->is_scan)
   {
-    /* Do full scan of the materialized table */
+    /*
+      For each prefix partial record (if any), initialize and perform
+      a full scan of the materialized table.
+    */
     JOIN_TAB *last_tab= join_tab + (sjm->table_count - 1);
 
+    init_read_record(&last_tab->read_record, join->thd,
+                     sjm->table, NULL, TRUE, TRUE, FALSE);
+
+    last_tab->read_first_record= join_read_record_no_init;
+    last_tab->read_record.read_record= rr_sequential_and_unpack;
+    last_tab->read_record.copy_field= sjm->copy_field;
+    last_tab->read_record.copy_field_end= sjm->copy_field +
+                                          sjm->table_cols.elements;
+
     Item *save_cond= last_tab->select_cond;
     last_tab->set_select_cond(sjm->join_cond, __LINE__);
     rc= sub_select(join, last_tab, end_of_records);


--===============2592823880971518008==
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-work5/
# testament_sha1: 77fa5bcd5fbb06b002e3f8523d867878614709c2
# timestamp: 2010-10-26 16:16:00 +0200
# base_revision_id: roy.lyseng@stripped\
#   eawnur5qjrsp0342
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWcVBZpgAItT/gFAwJABdf///
f/f/4L////BgG975e+ziVhla2anthJJ0BkUFAABQCgKGgbKzQA9ZO1hoKXZoFCqkBRdtIlKVtNko
kERqmnqPUMQaAZA0DT0gAAABoAAOMjTJiaDJkwmmQMhoDQGmTQwAmgMMpgpQaGQMnpNAaHqAYmg0
ZAAAABJqiGplJ+oZGqP1Q89U0AhoBkPUDQbQGo9RpmgikFMEaCngmU2poYqfiA0po9MRkjT1PSHq
D1MgKkkATQJoACCehJkNNUD9SbJM1PSaaDyhspkNkLAm7iocwoQoehQ/UFYUJKE2FpCyQskLSHjI
YyHlIZSFk80/pIYhIYkF7BKhgi0zhUqWsllkfFQ/EULVD9lCQofmofVQ+ah81AoJApUB6Ki2c9EF
OKeusqmxiyD0IHVVVZPtixsyQTkk3EIZycDESXf5vZimtOl9V2fo0150eLUbbNcQjXqo93+fR0ej
h/Y+0ruUdGLvAKqHEGUBBioTyUjsHyoUH1IYqEIUwIYDaK+IVozYp3IjGQ8pC0hUhvkLJCpCyQt2
17J8akWwYU4VdKXV5153y27y6CM+khYi18hW9IVcEV+dpDGSF1pD6e9wmtBXsiswE29GZzhuNcoI
IUckW5NOosQhhxYZMN1VlBOVZXTtyRZO7BQuleHEQ3Zi3HP9vDMJnUjzqJYlKHbXuaf02x+IEawj
/KSCuXP8FQ+ioTmK6xWBWBX2InSieNIIpFRhIpFRgQVURUREVEU3/wAknUSQt8fLppWvHlhZo32N
KFLHLgKXXFCuDQxSrhel2+KKKYkks6rFkLGBZWuKYDL8SkrYpMWGrUUBSpyXFSWxLYl2pGNhSjQp
oqSZKalTVWJUaK0KjNa5bWyrXX6FL2hYTHBdTLA0ZskrMoN2FcSlVK1MKOIpSpkY4iiiiinVuPmR
/0ItYj4w8vwkh4Ee0kPcI8gEt7Z80W+sBmobLBwH+oJgAJYdNFWNYjIjtI6Eb/b6afVb3tZf9bvh
+2zqhHvPqlSiLBRRRViwhlduyybzeQwtcBMoQyZfXH7Yc14DbZeLdGRBpEz6Am4EqCfRUOCoUBKA
muoJ0IJll6LtaDmGQ5uUDnst+ERplyac7rVEY699+7kRyI/P/wR1yQwwEqCZwS/hHsvNWpEnn0VA
clmiOa8F30reKduXj3YXaW3Z3bCZ7Ys21u698uWc6VLbmFrroVDIi/biiLuNVknb3O/hWNxHVu2b
Wtjj0p1IK3GqoCeig2LoG0kpxDOxBF6jEpnMUpReCc4v3wgFlYhAApAN8PpgDRYKeDfRi/xasNGi
Lbs+vThHQIFYFU8XfIVSQpUh4Ei0/wrPrsI+xFESKEUEURRCbyOBHEjYi+etSQ9UjwqQx65IfdmR
zI6CMCLEXCO8j0IvIojAReRgR7dX0yw2EM4J/cASahQUPboVD3xt9AACX+GPfdPbNGs8oJuBLaWA
mQF8m/IqG9UKqhDIbCSeZyd39m/9X09ur53XY432yyy+54M/NXUdp7HW/+v6uti8Eoscmr5pBttV
dQbda5GD6JBp+uW46QH3BtJmuzJxNQJ3c4qTM4dVe0dQJQMpLzKGbhjIAjNdKbj+uTTnyqhtVCSo
5SQ3GskG5n5OckHg8GGLJdzwtha3Xa3K1sbW0zuq1scbMKztalZ2Z5H6fT5xF5Gm20RnN6m2VjGM
YxVVVRE2GuhryCQ2nCUIl6oGDotIk7b7ZWr7yP1CX1ngsmqmKDQsozK17qlWESmAhkzZF2SkmDE+
LUklxe8VCFLiSaLm7xEpop3ub4OGzBsxs0EqXLLODBdkwWUDcY4KcCRoxTE522ZrMsF9LlzR+X7r
17RTBwayRhKZlyz4r3RkxYsF690c3Noz03HN7PzeUh9iDu68IWRH8ISUipDdCaNEqWArORhtxsK0
JTzlSVIMqJF2CGegJzAgwCtAhUMMN1oiO7AXN+lBQJEG8TZpKMJTPPuebts/PbZoltizi2lvLjXC
OHRdgvVv/Dje0lSibjubl/AKmu9ztlv2w8csfLXy8+nZjWfbWLDW2FmjFqs1j1C5k14Obcyb5IbB
TAVMCwW2+hy2SLzSZxUmWGkkVxpUjshydnNXBok4OTWtGz9NzKQpwZ4tC+b73TosvJetiFuDzXOw
wmUR2NzJm4MWbqYU7TJ4d/axDviMna2Wdy5Z1r+x45Ji0aNnxaEk7HL2R6SHsLQpEXvBJNHYhl1L
8iScy4wKWsxb+FzBnzYKU3m+I02Fl+Yu32KoUVSFQwymQbjCQCOUMAY7Y13WRoGTuX6MVvDK/Biu
8HYrBctpcsW76YN17gayHXe6W6Laxf53CGKnHKSVRhS5cyC9NyjClJRU3L+be2bMWRkvXSZ1Gi5a
WqF295WExb3JzbNmzFuZtXPy4tV+Aydr7pDRu3qkir66Ek6nLukO52dWW+l1zlWbdbDErcaEaNrR
HwEVJDNc3sUvkhk5O2dTBldDaM3eyZ331jSqhwYs+AarzHGSRizvNlGC/JyYzRc1E25tnddIbYNr
CbMVlze2bnd0bNTRm0XLOxx2cfRwhOhHU5OjjwcmjFm/s7QvYOLDivaP+LRzbKXubg4TfZTJfIcH
nykKQrsx17dMJIbpdz59l9zZzZEZ3Ydm7lfiR3kUR2NwWkhud20kclzouSOPBgpTbbmxCXU46r3b
ui+5qrFZLWYtBLt69dcJwXM4KbqU1c2zam2SmV7Vi4U3L9r2Obhes8vLV3NcOyEb1Q5qg3qkptM6
6kjKbt25s3sm7mszXqXNJDFk3L3PqwZavxZSH9ut2Os5XHQqpZw2KHEUNwmtno3zmyzc0RWWq6vE
CMhNM1E72rZTLtePjz4fwJGszqI8Vtlcr/NTaicEkcOp7icuXLx3ctW9UKu813Fc6nR6OZnhOjo4
qXL2drKiRzdGT0X7mON0de9ym9gXYsTp0yNnBqyZLn1kNzoyXsVnFvbbb2bgp9f1NjcpvkP2fi+y
SPqJ7vN8JDPt+HZd6b0wd9u3BVnky672LJV1mmXm2DFn2srnm0XL9yrCaeiySPJT0bMUnKkNyUYv
Fbm1zWZTLnq3t/FwaTWbmUxV9qKp6enNvXmsrrChoO6TQvJWQaMc14ZAUzk2ZRCek14rHBe2tc9l
OVze1dTmyyXnWb3Ww7GzLnmq2Lc5NWjmvDzeDDJuamnfXgSF6zAUs47ly/qWWXr77l1cV2eMRlhb
5Ek+LuC6I2YtMqXtWBdOS5JixrF3N/F0aR99zR3Oxv+/hq0tk8aZqpykMJ3r9W7Ng7GuC9yc8Zhg
yfJvTN0yWVbWrujokjB4umF032OTJaYKNnzWe3NbZJHB04SRm1c2UydGzFeva9Kc3a7CwsOpVTio
WXl/GelshpWM5BIirKu4xK1lFdy/Tl5CNXgwasyPBkudbVdsthc3qQpivL1mfW/IPbnrizarO91b
tlmT3Zrs54KMHFaa+a2KmzFxxZrWk5ZN6nJlhODZ4KdQm7fzub2by6aOKnBTHe3dSr3JxcFl65h2
NV4mBsGIKIxUYMZvZGo4KmGNTViXljDiy7RHoxbNLODg6NmmVNldnNSnC5o4rPJTI3LDYpVFnx4L
nBRzrgFllZ56MMnU25aaVxcLq4qvWJFy64yaLYKwbmy6NWdjc5ODRvZ213M2lJdMl1xWCrLMFoU1
uXZJIpvacFy92s2m85uTkZrtyyx4MF75ubRvc3qHJjvE6tdK30T8bWcqOlTfnWHSQpkrNUiWCDKo
pcA2Wx1+yzukeXZ5qqFBCQvZA4YQoKwiCJ3SQ0pYODU4LICApD5oQugSsGMYxjFVVVETx/cidR3w
B3KgfAEHtBQ+P/QBPxBA5wCGvMA2QFCJFekIE8MCH/gYYfSkjEIuSQ/imyXEfvkh+8jiEUEfw1Xh
G+9JDAIwSQ/in8yf0CP/ioUAEuAUxRMowi5ZKH2BJ/WgotJD/zbmEf7SQvI5kFSQvCKBH9SK3c4k
kjpCP7iE0SQ4BIdxIVUEZf6pIcSD0ulpIYplCLBFkoEcwkL9/aEcUEuE0IyTEgzFpIk/2CO4g9Ek
O8i8I1VwSpQlFJDxuIuCOrgRvkPKSHQItzI42lVDydKuknKSHQIyS0kMSE4BFwIs7CKoYP6jeEYQ
SGEzlRKSolUpBUEbxHAjIkP5J+/fYnv95VifX74jCIr/CxZeJovzPEkRndxAdneIZEZnAJGESBMA
S0gC4mN0Wp+3LM/v/uxRGiikkooRUJlwUPzqoaVCQocABNR1QZPlVEWB+89p8TApoVKkInyPwPu6
ZHvI+RYXmBM4e4+B7jDCRQXtbeDJc4r2K9Zo4tmTRi0WZuL0f4+MbG9vZs1y90YkOm9vVR/2eUkF
nFOxg3LwEdpqNxu0yAldugQ6P64YSlP0aATopIEt1jtp0olvzvAarZCuWTbvKTqC8Jb0U7L1aYkD
18zjyGUP4vBJ+7JCzCBSIFAI80E+UhhxDzE/TRrzjBS3oser1fyUYuT/NhTJe+5mueDBkwU+TR8H
zYtbnUu3LKZvipOp7ZvuXuKm9yYKfrSRq3vanJc0Xs1zVe3XJJ8pCk7WV4ho+thckJ+FfZy9/tBW
JdihG3TI3G8g2EilK6KlTUayh26bSn6HgOe9llg8WrTZZ6vJY+blvb3HqSuTjxxdv+6TKKlC286m
WXm9mym9+KqVOF71p6dWz7lzE8vvTPwO5+K2N9exCpIMnW/HlJB0/ms0hhjE+35L4HeH4t8hjf6/
S0XJXBaIwEuksinjjrVyaRFWlMnr7Pf208jF5vk9Xq1e7F8fDkNhfgeU5GBkNvUYmGFpuVMxabCR
gahU1qlG5lTqaGbB89Xa8O9rbnIis8zC0mGK97uDsYqOin/D7Pd1Mmrvd/is4PZv5dbB6OLZ2Obt
ekUsUN53ZvabzrOOJv6wcxlFDwqHl7R2O7sqrOk7EEKLLISqBBNI2GQAiNaGx0lXnYsBCAoaFRFv
Bi1eDyYNML/VZ44yRtcxd/mzEykNW5ksyWXO5Zc2cmjVvW3rcOUOiUUhE9KJzgFsPdDKjkFNYEKG
oBrcXvBwepu7uAr1Eweyx54Q3grjbx8+nZrjJj0604UweDtXHc3uvsYfd6Ex9K0zkPi+v1/0+L4O
TFkvdPFcSdOvHrqrOPB+F77jyB+GsTa6RNyFpD29L0ke/s+4S4Q6NEj4RPrnLXSRew3e8Ip8RKmU
pxxvut1icD0oHBvkMQm7dfpaEWwkQ5TETC29TIm13DEBElErUlyOnrJgOc4FKqFyic6C37lDRtUk
fJ1/JPdc9l9lMn0Xv1Pk72DJexWYr8mD6fZm0+rFosxfNexdzJZew0XMmRZc1cHZuYs2DNiavte+
jFuSPpUI7KhGLg6mqzR2JHqhHt9x/WI84jrki6Jk7XKQiOVVShSR2SzkzixTeY2ioYgppAItJrBr
PhrukKE3CZeTr2ZknlV/s2PpdafR8XOaiiaAUzG28M/kkfCBA1bf59jQhV6iR3nacC7BwebpIc2L
kQmUs7fJ4Bde8pJKpAi9QiVbFDsAdfJRI5wnZ39gpPtOo61HUWqrwuEY8MKHMqB5jH6VR8YDp1oJ
3wgx/1xnShPt8c/d9HKE1E5yTOfFV3QS36SFEbUkSWkKhH80kKI83zp35NXkkNYipF4lLI/3yHnZ
EO5c7SHu5vyfubPi0jByhH0hKpTUT5u1+tj71qth+tbp3pDnIfsUJ5v2yGgcX0g71anJIuV+o6CU
vC+g++QuTG8xNiR+wk9/61M3q0PUjfEVDaSC0RpdaIqQoAGE+aepQqPTnAd0AjnhnCSioPnPM0CO
oja3BxkNfNh8rvwkgrR3+Fkgoq70TgWolk6RIu5RFdEn27HG6RPS4S7X83tq10SK0Q1yw7iMfiR3
spDOBstUREiILQGJflE6UMSlJsZSoqSFJhTBEgkyR/MhFgNAJfRLyTN2FQJYwokIXKC0kGLa4v6o
MXh+Hru07Wm6EVQn3h8hNRr2qG/nCvC+WHQbbznxFTxFvrLTwINhukg+LLJQh9sIJi1Jdhd8iWmJ
zI/ip8a/tJB0E90MYaYdVeTwtGGOU+C3qTkWUI7VDBEH0qhJQl6VQgFYVDhZ8uepqg0wS1mEAnnP
lJUDX4gG2FG2ztkiQqRQU9goV6ShicsTvBKANqhsNahmMi/CbXyHCiZP8xPm2PvSRz+DZcqQp7id
Ukj1a+70cuz+08xDBciPsScSLKKAYlIJQncofwUO4Am7AG0lt8qhaqq+kLXiey5FpWtllfkVJJ/f
tRdbjQ17HpIdGBJ9Im6JGa6kRT0+dkje73nxSAY+MrfQkldfwpr4nZfpMxqUI0APMfgKQiS/Tsw5
b4R+0T5P2yTrC/DsXQtLloVCHKQ+0hj23267LYWEsxqrV5Qi0a72Tv65Co5xgYjMUIuv37Q5ARuU
LlH1CpqAQv7dSbY77yelIxiN7hIf6SIcRPrhEbiIySBkdfPxSQ73xF5uAmkIaKrXXxwh2r60KAlp
stMVDmmCTiIUSNaSyzB2AOahx0zzVVVVVVVVVVVVVVVVVVVVVVVcwpRb4EML+A+AJ8kgw27oi1u6
uPBMOVOS6F+GWchbFZaTG7yLiQJaqoXAqhAqheSpkRKyBhQnHnESxCSQob04qGfdB0wkQN6Nn5K4
E1u1uxQ+fOKdLxC+I91d5HQiyIrp1XHGuVQqiT/lpj29Wa67udomMJsfo6E3UBvdahKWeL4VPOZu
beocyh/EuKm/A66EoAj2+rgw/XIVIh9UhP8hP3eHu4EnwbQjl4u654yHIncJ8MNuDfG2L8jydjqe
DATzcNL75aWfqiKE8GbuZBg3spk6nJ/i9VrhhQM5FwDjK0E+Ir6gVgV5GBzqFihyPf4zicixVaKG
BbgQUUNCFJko4b50VFblE9DKQsIfm3eqQvkP5bpIPaEdHb6K+8M3f3Qn5KSDwrR2b/RJHZIU1dEk
fV//F3JFOFCQxUFmmA==


--===============2592823880971518008==--