Fixed LP bug #898073.

The tables from the same semi-join or outer join nest cannot use
join buffers if in the join sequence of the query execution plan
they are separated by a table that is planned to be joined without
usage of a join buffer.

1 files changed, 29 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index bd330dd8143..46b62ce843c 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -899,6 +899,35 @@ DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
+#
+# Bug #898073: potential incremental join cache for semijoin
+#
+CREATE TABLE t1 (a int, b varchar(1), KEY (b,a));
+INSERT INTO t1 VALUES (0,'x'), (5,'r');
+CREATE TABLE t2 (a int) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (8);
+CREATE TABLE t3 (b varchar(1), c varchar(1)) ENGINE=InnoDB;
+INSERT INTO t3 VALUES ('x','x');
+CREATE TABLE t4 (a int NOT NULL, b varchar(1)) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (20,'r'), (10,'x');
+set @tmp_optimizer_switch=@@optimizer_switch;
+SET SESSION optimizer_switch='semijoin_with_cache=on';
+SET SESSION join_cache_level=2;
+EXPLAIN
+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	t1	ref	b	b	4	test.t3.b	1	Using index
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t2)
+SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
+WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
+b	c
+x	x
+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