Fixed LP bug #901312.

The function setup_sj_materialization_part1() forgot to set the value
of TABLE::map for any materialized IN subquery. 
This could lead to wrong results for queries with subqueries that were
converted to queries with semijoins.

3 files changed, 70 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 9f37dbf4e04..b0ded6e04e6 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2094,4 +2094,28 @@ a
 5
 set optimizer_switch= @tmp_otimizer_switch;
 DROP TABLE t1,t2,t3;
+#
+# Bug #901312: materialized semijoin + right join
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (4), (1);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (4), (1);
+CREATE TABLE t3 (c int);
+INSERT INTO t3 VALUES (4), (1);
+set @tmp_otimizer_switch= @@optimizer_switch;
+SET SESSION optimizer_switch='semijoin=on,materialization=on';
+EXPLAIN
+SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3);
+a	b
+4	4
+1	1
+set optimizer_switch= @tmp_otimizer_switch;
+DROP TABLE t1,t2,t3;
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 2f0db469bd9..29d5ef73608 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2100,6 +2100,30 @@ a
 5
 set optimizer_switch= @tmp_otimizer_switch;
 DROP TABLE t1,t2,t3;
+#
+# Bug #901312: materialized semijoin + right join
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (4), (1);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (4), (1);
+CREATE TABLE t3 (c int);
+INSERT INTO t3 VALUES (4), (1);
+set @tmp_otimizer_switch= @@optimizer_switch;
+SET SESSION optimizer_switch='semijoin=on,materialization=on';
+EXPLAIN
+SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3);
+a	b
+4	4
+1	1
+set optimizer_switch= @tmp_otimizer_switch;
+DROP TABLE t1,t2,t3;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index a2f46c76fce..130a5251846 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -1915,5 +1915,27 @@ set optimizer_switch= @tmp_otimizer_switch;
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # Bug #901312: materialized semijoin + right join
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (4), (1);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (4), (1);
+CREATE TABLE t3 (c int);
+INSERT INTO t3 VALUES (4), (1);
+
+set @tmp_otimizer_switch= @@optimizer_switch;
+SET SESSION optimizer_switch='semijoin=on,materialization=on';
+
+EXPLAIN
+SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3);
+SELECT * FROM t1 RIGHT JOIN t2 ON b = a WHERE t2.b IN (SELECT c FROM t3);
+
+set optimizer_switch= @tmp_otimizer_switch;
+
+DROP TABLE t1,t2,t3;
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;