Merge

1 files changed, 54 insertions, 2 deletions

diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 9a49f74ec42..9c928fd81f6 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1892,7 +1892,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	Using where
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
@@ -1900,11 +1900,12 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materializ
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 a	b
+7	7
 EXPLAIN
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	Using where
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t1
@@ -1912,6 +1913,57 @@ WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
 a	b
 SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2;
+# 
+# BUG#946055: Crash with semijoin IN subquery when hash join is used
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (7);
+CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c));
+INSERT INTO t2 VALUES 
+(4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'),
+(7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j');
+SET @save_optimizer_switch=@@optimizer_switch;
+SET @save_join_cache_level=@@join_cache_level;
+SET join_cache_level=2;
+EXPLAIN
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	index	NULL	c	5	NULL	8	Using where; Using index
+2	MATERIALIZED	s2	ref	d	d	4	const	1	Using where; Using index
+2	MATERIALIZED	s1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	8	
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+a	c
+7	1
+7	1
+7	1
+SET optimizer_switch='join_cache_hashed=on';
+SET join_cache_level=4;
+EXPLAIN
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	index	NULL	c	5	NULL	8	Using where; Using index
+2	MATERIALIZED	s2	ref	d	d	4	const	1	Using where; Using index
+2	MATERIALIZED	s1	hash_ALL	NULL	#hash#$hj	5	test.s2.d	8	Using where; Using join buffer (flat, BNLH join)
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	8	
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+a	c
+7	1
+7	1
+7	1
+SET optimizer_switch=@save_optimizer_switch;
+SET join_cache_level=@save_join_cache_level;
+DROP TABLE t1,t2;
 #
 # BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization
 #