5.3 merge

1 files changed, 53 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index e181fd20abc..f06fe52393d 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -1951,6 +1951,59 @@ 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	c	c	5	NULL	8	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	s2	ref	d	d	4	const	1	Using where; Using index
+2	MATERIALIZED	s1	ALL	c	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	c	c	5	NULL	8	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	s2	ref	d	d	4	const	1	Using where; Using index
+2	MATERIALIZED	s1	hash_ALL	c	#hash#$hj	10	const,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;
 # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;