Fixed LP bug #899777.

KEYUSE elements for a possible hash join key are not sorted by field
numbers of the second table T of the hash join operation. Besides
some of these KEYUSE elements cannot be used to build any key as their
key expressions depend on the tables that are planned to be accessed
after the table T. 
The code before the patch did not take this into account and, as a result,
execition of a query the employing block-based hash join algorithm could
cause a crash or return a wrong result set. 

1 files changed, 47 insertions, 0 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index be579c9240f..8372f68fde0 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5179,4 +5179,51 @@ a	b
 SET SESSION join_cache_level = DEFAULT;
 SET optimizer_switch=@tmp887479_optimizer_switch;
 DROP TABLE t1,t2;
+#
+# Bug #899777: join_cache_level=4 + semijoin=on 
+#
+CREATE TABLE t1 (a int, b int, c int, UNIQUE INDEX idx (a));
+INSERT INTO t1 VALUES (1,8,6), (2,2,8);
+CREATE TABLE t2 (a int, b int, c int, UNIQUE INDEX idx (a));
+INSERT INTO t2 VALUES (1,8,6), (2,2,8);
+CREATE TABLE t3 (a int, b int, c int, UNIQUE INDEX idx (a));
+INSERT INTO t3 VALUES (1,8,6), (2,2,8);
+CREATE TABLE t4 (a int, b int, c int, UNIQUE INDEX idx (a));
+INSERT INTO t4 VALUES (1,8,6), (2,2,8);
+SET @tmp_optimizer_switch=@@optimizer_switch;
+SET SESSION optimizer_switch='semijoin=on';
+SET SESSION optimizer_switch='semijoin_with_cache=on';
+SET SESSION join_cache_level=1;
+EXPLAIN 
+SELECT t1.* FROM t1,t2
+WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
+AND t1.a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	const	idx	idx	5	const	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (flat, BNL join)
+SELECT t1.* FROM t1,t2
+WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
+AND t1.a = 1;
+a	b	c
+1	8	6
+SET SESSION join_cache_level=4;
+EXPLAIN 
+SELECT t1.* FROM t1,t2
+WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
+AND t1.a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	const	idx	idx	5	const	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	hash_ALL	NULL	#hash#$hj	5	const	2	Using where; Start temporary; Using join buffer (flat, BNLH join)
+1	PRIMARY	t4	hash_ALL	NULL	#hash#$hj	10	const,test.t2.b	2	Using where; End temporary; Using join buffer (incremental, BNLH join)
+SELECT t1.* FROM t1,t2
+WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b)
+AND t1.a = 1;
+a	b	c
+1	8	6
+SET SESSION join_cache_level = DEFAULT;
+SET optimizer_switch=@tmp_optimizer_switch;
+DROP TABLE t1,t2,t3,t4;
 set @@optimizer_switch=@save_optimizer_switch;