Merge.

3 files changed, 144 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 97463447e1d..35b46ab975f 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1913,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;
 # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index db5a27a5d84..d13482e9b2d 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;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 252fbb525eb..ba260b7d8c3 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1602,6 +1602,46 @@ SET optimizer_switch=@save_optimizer_switch;
 
 DROP TABLE t1,t2;
 
+--echo # 
+--echo # BUG#946055: Crash with semijoin IN subquery when hash join is used
+--echo #
+
+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));
+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));
+
+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));
+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));
+
+SET optimizer_switch=@save_optimizer_switch;
+SET join_cache_level=@save_join_cache_level;
+
+DROP TABLE t1,t2;
+
 --echo # This must be at the end:
 set optimizer_switch=@subselect_sj_mat_tmp;
 set join_cache_level=@save_join_cache_level;