BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ...

- setup_semijoin_dups_elimination() would incorrectly set join_tab->do_firstmatch 
  when the join order had outer tables interleaved with inner.

1 files changed, 19 insertions, 4 deletions

diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 222d34d9bf4..1b7d65275ab 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -903,6 +903,20 @@ a	b	b	a
 c	c	NULL	NULL
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
+#
+# BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ...
+#
+CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) );
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1);
+SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b);
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+DROP TABLE t1,t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 #
@@ -924,9 +938,9 @@ SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
 WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	FirstMatch(t3)
 1	PRIMARY	t1	ref	b	b	4	test.t3.b	1	Using index
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
 SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
 WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
 b	c
@@ -952,12 +966,13 @@ EXPLAIN
 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t2)
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using where; FirstMatch(t4)
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using where; FirstMatch(t1)
 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
 pk	a	b
 1	6	8
+2	8	8
 set optimizer_switch=@tmp_optimizer_switch;
 set join_cache_level=default;
 DROP TABLE t1,t2,t3,t4;