Small semi-join optimization improvement:

- if we're considering FirstMatch access with one inner table, and 
  @@optimizer_switch has semijoin_with_cache flag, calculate costs
  as if we used join cache (because we will be able to do so)

9 files changed, 36 insertions, 43 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 0a4a16962ff..a31a6b6c3cf 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -5205,8 +5205,8 @@ INSERT INTO t2 VALUES (15,4);
 EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
-1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	3	Using index; Start temporary; Using join buffer (flat, BNL join)
-1	PRIMARY	it	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	Using index; End temporary
+1	PRIMARY	it	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	Using index
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	3	Using index; FirstMatch(it); Using join buffer (flat, BNL join)
 SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
 pk	i
 11	0
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index 898eed164b7..7d13bce1f85 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -105,7 +105,7 @@ oref	a
 1	1
 show status like '%Handler_read_rnd_next';
 Variable_name	Value
-Handler_read_rnd_next	14
+Handler_read_rnd_next	5
 delete from t2;
 insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
 set optimizer_switch='subquery_cache=off';
@@ -1308,7 +1308,7 @@ insert into t1 select * from t0;
 explain select * from t0 where a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(t0); Using join buffer (flat, BNL join)
 select * from t0 where a in (select a from t1);
 a
 10.24
@@ -1321,7 +1321,7 @@ insert into t1 select * from t0;
 explain select * from t0 where a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(t0); Using join buffer (flat, BNL join)
 select * from t0 where a in (select a from t1);
 a
 2008-01-01
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index 59750f2cfa0..5ad5878623d 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -114,7 +114,7 @@ oref	a
 1	1
 show status like '%Handler_read_rnd_next';
 Variable_name	Value
-Handler_read_rnd_next	14
+Handler_read_rnd_next	5
 delete from t2;
 insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
 set optimizer_switch='subquery_cache=off';
@@ -1316,8 +1316,8 @@ create table t1 as select * from t0;
 insert into t1 select * from t0;
 explain select * from t0 where a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	3	test.t0.a	4	Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(t0); Using join buffer (flat, BNL join)
 select * from t0 where a in (select a from t1);
 a
 10.24
@@ -1329,8 +1329,8 @@ create table t1 as select * from t0;
 insert into t1 select * from t0;
 explain select * from t0 where a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t1	hash_ALL	NULL	#hash#$hj	4	test.t0.a	4	Using where; Start temporary; End temporary; Using join buffer (flat, BNLH join)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(t0); Using join buffer (flat, BNL join)
 select * from t0 where a in (select a from t1);
 a
 2008-01-01
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 76496d73ef9..f830be38321 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -1230,16 +1230,16 @@ EXPLAIN
 SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2); Using join buffer (flat, BNL join)
 SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
 c1	c1
 EXPLAIN
 SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2); Using join buffer (flat, BNL join)
 SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
 c1	c1
 EXPLAIN
diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result
index 23c4849b120..9246128391e 100644
--- a/mysql-test/r/subselect_extra.result
+++ b/mysql-test/r/subselect_extra.result
@@ -16,7 +16,7 @@ flush tables;
 EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	OUTR	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	INNR	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	INNR	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(OUTR); Using join buffer (flat, BNL join)
 flush tables;
 SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL );
 dt
@@ -24,7 +24,7 @@ flush tables;
 EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	OUTR	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	INNR	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	INNR	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(OUTR); Using join buffer (flat, BNL join)
 flush tables;
 SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' );
 dt
@@ -68,7 +68,7 @@ select * from t1
 where id in (select id from t1 as x1 where (t1.cur_date is null));
 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	Using where
-1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 Warnings:
 Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where ((`test`.`x1`.`id` = `test`.`t1`.`id`) and (`test`.`t1`.`cur_date` = 0))
@@ -80,7 +80,7 @@ select * from t2
 where id in (select id from t2 as x1 where (t2.cur_date is null));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
-1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; FirstMatch(t2); Using join buffer (flat, BNL join)
 Warnings:
 Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
 Note	1003	select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where ((`test`.`x1`.`id` = `test`.`t2`.`id`) and (`test`.`t2`.`cur_date` = 0))
@@ -307,8 +307,7 @@ EXPLAIN SELECT 1 FROM t1 WHERE a IN
 (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	PRIMARY,i2	PRIMARY	4	NULL	144	Using index
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	144	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	144	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 CREATE TABLE t2 (a INT, b INT, KEY(a));
 INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
 EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
@@ -432,7 +431,7 @@ WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch(t3); Using join buffer (flat, BNL join)
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	
 SELECT * FROM t3 
 WHERE t3.b IN (SELECT v1.b FROM  v1, t2 
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 01b83916c4b..3a184cb0a54 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -5204,8 +5204,8 @@ INSERT INTO t2 VALUES (15,4);
 EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
-1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	3	Using index; Start temporary; Using join buffer (flat, BNL join)
-1	PRIMARY	it	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	Using index; End temporary
+1	PRIMARY	it	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	Using index
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	3	Using index; FirstMatch(it); Using join buffer (flat, BNL join)
 SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
 pk	i
 11	0
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 606a19fa500..31d1c61a014 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -5209,8 +5209,8 @@ INSERT INTO t2 VALUES (15,4);
 EXPLAIN SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	3	
-1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	3	Using index; Start temporary; Using join buffer (flat, BNL join)
-1	PRIMARY	it	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	Using index; End temporary
+1	PRIMARY	it	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	Using index
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	3	Using index; FirstMatch(it); Using join buffer (flat, BNL join)
 SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON 1);
 pk	i
 11	0
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 8fd9122465c..02d2fb01d93 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -76,8 +76,7 @@ insert into t3 select a,a, a+100,a+100,a+100 from t0;
 explain select * from t3 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	20	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch(t3); Using join buffer (flat, BNL join)
 select * from t3 where b in (select a from t1);
 a	b	pk1	pk2	pk3
 1	1	1	1	1
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 03940e0e5eb..2f0db469bd9 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -979,10 +979,9 @@ FROM t1
 WHERE `varchar_nokey`  < 'n' XOR `pk`  )   ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	18	100.00	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	100.00	
-2	MATERIALIZED	t1	ALL	varchar_key	NULL	NULL	NULL	15	100.00	Using where
+1	PRIMARY	t1	ref	varchar_key	varchar_key	3	test.t2.varchar_nokey	2	100.00	Using where; FirstMatch(t2); Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 Warnings:
-Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_key` < 'n') xor `test`.`t1`.`pk`))
+Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_key` = `test`.`t2`.`varchar_nokey`) and (`test`.`t1`.`varchar_nokey` = `test`.`t2`.`varchar_nokey`) and ((`test`.`t2`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`))
 SELECT varchar_nokey
 FROM t2  
 WHERE ( `varchar_nokey`  , `varchar_nokey`  )  IN (  
@@ -1061,10 +1060,8 @@ AND t1.val IN (SELECT t3.val FROM t3
 WHERE  t3.val LIKE 'a%' OR t3.val LIKE 'e%');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	14	func	1	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	14	func	1	
-3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(t3); Using join buffer (incremental, BNL join)
 SELECT *
 FROM t1
 WHERE t1.val IN (SELECT t2.val FROM t2
@@ -1254,7 +1251,7 @@ A.t1field IN (SELECT C.t2field FROM t2 C
 WHERE C.t2field IN (SELECT D.t2field FROM t2 D));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	index	PRIMARY	PRIMARY	4	NULL	3	Using index
-1	PRIMARY	B	index	NULL	PRIMARY	4	NULL	3	Using index; Start temporary; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	B	index	NULL	PRIMARY	4	NULL	3	Using index; FirstMatch(A); Using join buffer (flat, BNL join)
 1	PRIMARY	C	eq_ref	PRIMARY	PRIMARY	4	test.A.t1field	1	Using index
 1	PRIMARY	D	eq_ref	PRIMARY	PRIMARY	4	test.A.t1field	1	Using index
 SELECT * FROM t1 A 
@@ -1283,11 +1280,9 @@ select * from t1 A, t1 B
 where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	5	func	1	
-2	MATERIALIZED	C	ALL	NULL	NULL	NULL	NULL	3	
-3	MATERIALIZED	D	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch(B); Using join buffer (incremental, BNL join)
+1	PRIMARY	D	ALL	NULL	NULL	NULL	NULL	3	Using where; FirstMatch(C); Using join buffer (incremental, BNL join)
 drop table t1, t2;
 # 
 # BUG#784441: Abort on semijoin with a view as the inner table
@@ -2160,8 +2155,8 @@ SELECT * FROM t1, t2
 WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	d	NULL	NULL	NULL	5	Range checked for each record (index map: 0x2); Start temporary
-1	PRIMARY	t2	hash_ALL	PRIMARY	#hash#PRIMARY	4	test.t3.b	4	End temporary; Using join buffer (flat, BNLH join)
+1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	d	NULL	NULL	NULL	5	Range checked for each record (index map: 0x2); FirstMatch(t2)
 SELECT * FROM t1, t2
 WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
 a	a	b
@@ -2175,8 +2170,8 @@ SELECT * FROM t1, t2
 WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	d	NULL	NULL	NULL	5	Range checked for each record (index map: 0x2); Start temporary
-1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.b	1	End temporary; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	4	Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	d	NULL	NULL	NULL	5	Range checked for each record (index map: 0x2); FirstMatch(t2)
 SELECT * FROM t1, t2
 WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
 a	a	b