Fixed LP bug #899696.

If has been decided that the first match strategy is to be used to join table T
from a semi-join nest while no buffer can be employed to join this table
then no join buffer can be used to join any table in the join sequence between
the first one belonging to the semi-join nest and table T.

 

1 files changed, 27 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 46b62ce843c..32d8d245477 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -928,6 +928,33 @@ x	x
 set optimizer_switch=@tmp_optimizer_switch;
 set join_cache_level=default;
 DROP TABLE t1,t2,t3,t4;
+#
+# Bug #899696: potential incremental join cache for semijoin
+#
+CREATE TABLE t1 (pk int PRIMARY KEY, a int);
+INSERT INTO t1 VALUES (1, 6), (2, 8);
+CREATE TABLE t2 (b int) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (8);
+CREATE TABLE t3 (pk int PRIMARY KEY, a int);
+INSERT INTO t3 VALUES (1, 6), (2, 8);
+CREATE TABLE t4 (b int) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (2);
+set @tmp_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch = 'semijoin_with_cache=on';
+SET join_cache_level = 2;
+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	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using where; FirstMatch(t4)
+SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
+pk	a	b
+1	6	8
+set optimizer_switch=@tmp_optimizer_switch;
+set join_cache_level=default;
+DROP TABLE t1,t2,t3,t4;
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value