Fixed LP bug #899509.

The optimizer must ignore any possible hash join key when looking for the 
query execution plan with join_cache_level set to 0.  

1 files changed, 38 insertions, 0 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 8372f68fde0..ef5c0a66130 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5226,4 +5226,42 @@ a	b	c
 SET SESSION join_cache_level = DEFAULT;
 SET optimizer_switch=@tmp_optimizer_switch;
 DROP TABLE t1,t2,t3,t4;
+#
+# Bug #899509: an attempt to use hash join with join_cache_level=0 
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (8), (7);
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (8), (7);
+CREATE TABLE t3 (a int);
+INSERT INTO t3 VALUES (8), (7);
+SET @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch=default;
+set @@optimizer_switch='semijoin_with_cache=off';
+set @@optimizer_switch='outer_join_with_cache=off';
+SET optimizer_switch='derived_merge=off,derived_with_keys=off';
+SET join_cache_level=0;
+EXPLAIN
+SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	Using where
+3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3);
+a
+8
+8
+7
+7
+SELECT * FROM ( SELECT ta.* FROM t1 AS ta, t1 ) tb WHERE a IN ( SELECT * FROM t1 );
+a
+8
+8
+7
+7
+SET SESSION join_cache_level = DEFAULT;
+SET optimizer_switch=@tmp_optimizer_switch;
+DROP TABLE t1,t2,t3;
 set @@optimizer_switch=@save_optimizer_switch;