5.3 merge

9 files changed, 923 insertions, 100 deletions

diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result
index 0cdfb4f57a7..d2fb29a023c 100644
--- a/mysql-test/r/innodb_ext_key.result
+++ b/mysql-test/r/innodb_ext_key.result
@@ -681,9 +681,9 @@ SELECT a FROM t1 AS t, t2
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	index	a,b	b	7	NULL	10	Using index
-1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index; FirstMatch(t)
+1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; FirstMatch(t)
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t.a	1	Using index
-1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; FirstMatch(t2)
 SELECT a FROM t1 AS t, t2 
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 a
@@ -694,9 +694,9 @@ SELECT a FROM t1 AS t, t2
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t	index	a,b	b	7	NULL	10	Using index
-1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index; FirstMatch(t)
+1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; FirstMatch(t)
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t.a	1	Using index
-1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; FirstMatch(t2)
 SELECT a FROM t1 AS t, t2 
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 a
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 0c41fef01ea..835403bb5b1 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -900,5 +900,199 @@ a	b	c
 3	1	1
 4	1	1
 DROP TABLE t1,t2;
+# 
+# BUG#962667: Assertion `0' failed in QUICK_INDEX_SORT_SELECT::need_sorted_output() 
+#   with index_merge+index_merge_sort_union+loosescan+semijoin
+#
+CREATE TABLE t1 (
+a INT, b VARCHAR(1), c INT,
+KEY(a), KEY(b)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5),
+(6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9),
+(11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
+(16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
+CREATE TABLE t2 (
+pk INT, d VARCHAR(1), e INT,
+PRIMARY KEY(pk), KEY(d,e)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
+(6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
+(11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
+(15,'g',6),(16,'x',7),(17,'f',8);
+explain
+SELECT * FROM t1 WHERE b IN (
+SELECT d FROM t2, t1
+WHERE a = d AND ( pk < 2 OR d = 'z' )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	b	NULL	NULL	NULL	19	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	index	PRIMARY,d	d	9	NULL	17	Using where; Using index
+2	MATERIALIZED	t1	ref	a	a	5	test.t2.d	1	Using where; Using index
+SELECT * FROM t1 WHERE b IN (
+SELECT d FROM t2, t1
+WHERE a = d AND ( pk < 2 OR d = 'z' )
+);
+a	b	c
+DROP TABLE t1, t2;
+#
+# BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view
+#
+CREATE TABLE t1 (
+a VARCHAR(1),
+b VARCHAR(1) NOT NULL,
+KEY(a)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('j','j'),('v','v'),('c','c'),('m','m'),('d','d'),
+('y','y'),('t','t'),('d','d'),('s','s'),('r','r'),
+('m','m'),('b','b'),('x','x'),('g','g'),('p','p'),
+('q','q'),('w','w'),('d','d'),('e','e');
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+# This query returned 6 rows instead of 19
+SELECT * FROM v1
+WHERE ( a, a ) IN (
+SELECT alias2.b, alias2.a
+FROM t1 AS alias1, t1 AS alias2
+WHERE alias2.b = alias1.a
+AND ( alias1.b >= alias1.a OR alias2.b = 'z' )
+);
+a	b
+b	b
+c	c
+d	d
+d	d
+d	d
+e	e
+g	g
+j	j
+m	m
+m	m
+p	p
+q	q
+r	r
+s	s
+t	t
+v	v
+w	w
+x	x
+y	y
+# Another testcase, without the VIEW:
+CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t2 SELECT * FROM t1;
+EXPLAIN 
+SELECT * FROM t2 
+WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+WHERE 
+alias2.b = alias1.a AND 
+(alias1.b >= alias1.a OR alias2.b = 'z'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
+1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	
+2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
+2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where
+SELECT * FROM t2 
+WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+WHERE 
+alias2.b = alias1.a AND 
+(alias1.b >= alias1.a OR alias2.b = 'z'));
+a	b
+b	b
+b	b
+c	c
+c	c
+d	d
+d	d
+d	d
+d	d
+d	d
+d	d
+e	e
+e	e
+g	g
+g	g
+j	j
+j	j
+m	m
+m	m
+m	m
+m	m
+p	p
+p	p
+q	q
+q	q
+r	r
+r	r
+s	s
+s	s
+t	t
+t	t
+v	v
+v	v
+w	w
+w	w
+x	x
+x	x
+y	y
+y	y
+DROP VIEW v1;
+DROP TABLE t1, t2;
+# 
+# BUG#965872: Server crashes in embedding_sjm on a simple 1-table select with AND and OR  
+#  (this is a regression caused by the fix for BUG#951937)
+CREATE TABLE t1 ( a INT, b INT, c INT, d INT );
+INSERT INTO t1 VALUES (4,2,8,9),(4,2,7,8);
+SELECT * FROM t1
+WHERE a = d AND ( b = 50 AND b = d OR a = c );
+a	b	c	d
+DROP TABLE t1;
+#
+# BUG#951283: Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery
+#
+set @tmp_951283=@@optimizer_prune_level;
+SET optimizer_prune_level=0;
+CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10),(11),(12),(13),(14),(15),(16),
+(17),(18),(19),(20),(21),(22),(23);
+CREATE TABLE t2 (
+b INT PRIMARY KEY,
+c VARCHAR(1),
+d VARCHAR(1),
+KEY(c)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,'j','j'),(2,'v','v'),(3,'c','c'),(4,'m','m'),
+(5,'d','d'),(6,'d','d'),(7,'y','y'),(8,'t','t'),
+(9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'),
+(13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'),
+(17,'q','q'),(18,'w','w'),(19,'d','d');
+EXPLAIN
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	19	Using where
+1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	1	Using index
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	1	Using where; FirstMatch(alias3)
+1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	14	Using join buffer (flat, BNL join)
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	14	Using join buffer (flat, BNL join)
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
+);
+COUNT(*)
+3724
+set optimizer_prune_level=@tmp_951283;
+DROP TABLE t1,t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
diff --git a/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff b/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff
index 528d3ac08ea..30df3c42a0d 100644
--- a/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff
+++ b/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff
@@ -1,5 +1,5 @@
---- r/subselect_sj2_jcl6.result	2012-01-11 18:05:14.000000000 +0100
-+++ r/subselect_sj2_jcl6.reject	2012-02-07 12:52:32.000000000 +0100
+--- r/subselect_sj2_jcl6.result	2012-03-28 20:10:41.000000000 +0200
++++ r/subselect_sj2_jcl6,innodb_plugin.reject	2012-03-28 20:21:14.000000000 +0200
 @@ -81,7 +81,7 @@
  explain select * from t3 where b in (select a from t1);
  id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -18,3 +18,15 @@
  2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
  select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
  a	b	pk1	pk2
+@@ -1006,9 +1006,9 @@
+ (alias1.b >= alias1.a OR alias2.b = 'z'));
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+ 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
+-1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
++1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	
+ 2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
+-2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
++2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where
+ SELECT * FROM t2 
+ WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+ WHERE 
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index 073f1619b91..c4c8a858b68 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -914,6 +914,200 @@ a	b	c
 3	1	1
 4	1	1
 DROP TABLE t1,t2;
+# 
+# BUG#962667: Assertion `0' failed in QUICK_INDEX_SORT_SELECT::need_sorted_output() 
+#   with index_merge+index_merge_sort_union+loosescan+semijoin
+#
+CREATE TABLE t1 (
+a INT, b VARCHAR(1), c INT,
+KEY(a), KEY(b)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5),
+(6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9),
+(11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
+(16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
+CREATE TABLE t2 (
+pk INT, d VARCHAR(1), e INT,
+PRIMARY KEY(pk), KEY(d,e)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
+(6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
+(11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
+(15,'g',6),(16,'x',7),(17,'f',8);
+explain
+SELECT * FROM t1 WHERE b IN (
+SELECT d FROM t2, t1
+WHERE a = d AND ( pk < 2 OR d = 'z' )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	b	NULL	NULL	NULL	19	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	index	PRIMARY,d	d	9	NULL	17	Using where; Using index
+2	MATERIALIZED	t1	ref	a	a	5	test.t2.d	1	Using where; Using index
+SELECT * FROM t1 WHERE b IN (
+SELECT d FROM t2, t1
+WHERE a = d AND ( pk < 2 OR d = 'z' )
+);
+a	b	c
+DROP TABLE t1, t2;
+#
+# BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view
+#
+CREATE TABLE t1 (
+a VARCHAR(1),
+b VARCHAR(1) NOT NULL,
+KEY(a)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('j','j'),('v','v'),('c','c'),('m','m'),('d','d'),
+('y','y'),('t','t'),('d','d'),('s','s'),('r','r'),
+('m','m'),('b','b'),('x','x'),('g','g'),('p','p'),
+('q','q'),('w','w'),('d','d'),('e','e');
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+# This query returned 6 rows instead of 19
+SELECT * FROM v1
+WHERE ( a, a ) IN (
+SELECT alias2.b, alias2.a
+FROM t1 AS alias1, t1 AS alias2
+WHERE alias2.b = alias1.a
+AND ( alias1.b >= alias1.a OR alias2.b = 'z' )
+);
+a	b
+b	b
+c	c
+d	d
+d	d
+d	d
+e	e
+g	g
+j	j
+m	m
+m	m
+p	p
+q	q
+r	r
+s	s
+t	t
+v	v
+w	w
+x	x
+y	y
+# Another testcase, without the VIEW:
+CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t2 SELECT * FROM t1;
+EXPLAIN 
+SELECT * FROM t2 
+WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+WHERE 
+alias2.b = alias1.a AND 
+(alias1.b >= alias1.a OR alias2.b = 'z'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
+1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
+2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+SELECT * FROM t2 
+WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+WHERE 
+alias2.b = alias1.a AND 
+(alias1.b >= alias1.a OR alias2.b = 'z'));
+a	b
+b	b
+b	b
+c	c
+c	c
+d	d
+d	d
+d	d
+d	d
+d	d
+d	d
+e	e
+e	e
+g	g
+g	g
+j	j
+j	j
+m	m
+m	m
+m	m
+m	m
+p	p
+p	p
+q	q
+q	q
+r	r
+r	r
+s	s
+s	s
+t	t
+t	t
+v	v
+v	v
+w	w
+w	w
+x	x
+x	x
+y	y
+y	y
+DROP VIEW v1;
+DROP TABLE t1, t2;
+# 
+# BUG#965872: Server crashes in embedding_sjm on a simple 1-table select with AND and OR  
+#  (this is a regression caused by the fix for BUG#951937)
+CREATE TABLE t1 ( a INT, b INT, c INT, d INT );
+INSERT INTO t1 VALUES (4,2,8,9),(4,2,7,8);
+SELECT * FROM t1
+WHERE a = d AND ( b = 50 AND b = d OR a = c );
+a	b	c	d
+DROP TABLE t1;
+#
+# BUG#951283: Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery
+#
+set @tmp_951283=@@optimizer_prune_level;
+SET optimizer_prune_level=0;
+CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10),(11),(12),(13),(14),(15),(16),
+(17),(18),(19),(20),(21),(22),(23);
+CREATE TABLE t2 (
+b INT PRIMARY KEY,
+c VARCHAR(1),
+d VARCHAR(1),
+KEY(c)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,'j','j'),(2,'v','v'),(3,'c','c'),(4,'m','m'),
+(5,'d','d'),(6,'d','d'),(7,'y','y'),(8,'t','t'),
+(9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'),
+(13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'),
+(17,'q','q'),(18,'w','w'),(19,'d','d');
+EXPLAIN
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	19	Using where
+1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	1	Using index
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	1	Using where; FirstMatch(alias3)
+1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	14	Using join buffer (flat, BNL join)
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	14	Using join buffer (incremental, BNL join)
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
+);
+COUNT(*)
+3724
+set optimizer_prune_level=@tmp_951283;
+DROP TABLE t1,t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 #
@@ -935,9 +1129,9 @@ SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
 WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	1	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	FirstMatch(t3)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t3); Using join buffer (incremental, BNL join)
 1	PRIMARY	t1	ref	b	b	4	test.t3.b	1	Using index
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
 SELECT t3.* FROM t1 JOIN t3 ON t3.b = t1.b
 WHERE c IN (SELECT t4.b FROM t4 JOIN t2);
 b	c
@@ -963,9 +1157,9 @@ EXPLAIN
 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t2)
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using where; FirstMatch(t1)
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t4.b	1	Using where; FirstMatch(t2)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
 SELECT * FROM t1, t2 WHERE b IN (SELECT a FROM t3, t4 WHERE b = pk);
 pk	a	b
 1	6	8
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index f15492c7322..b535f37ee90 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -902,6 +902,200 @@ a	b	c
 3	1	1
 4	1	1
 DROP TABLE t1,t2;
+# 
+# BUG#962667: Assertion `0' failed in QUICK_INDEX_SORT_SELECT::need_sorted_output() 
+#   with index_merge+index_merge_sort_union+loosescan+semijoin
+#
+CREATE TABLE t1 (
+a INT, b VARCHAR(1), c INT,
+KEY(a), KEY(b)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5),
+(6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9),
+(11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
+(16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
+CREATE TABLE t2 (
+pk INT, d VARCHAR(1), e INT,
+PRIMARY KEY(pk), KEY(d,e)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
+(6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
+(11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
+(15,'g',6),(16,'x',7),(17,'f',8);
+explain
+SELECT * FROM t1 WHERE b IN (
+SELECT d FROM t2, t1
+WHERE a = d AND ( pk < 2 OR d = 'z' )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	b	NULL	NULL	NULL	19	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t2	index	PRIMARY,d	d	9	NULL	17	Using where; Using index
+2	MATERIALIZED	t1	ref	a	a	5	test.t2.d	1	Using where; Using index
+SELECT * FROM t1 WHERE b IN (
+SELECT d FROM t2, t1
+WHERE a = d AND ( pk < 2 OR d = 'z' )
+);
+a	b	c
+DROP TABLE t1, t2;
+#
+# BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view
+#
+CREATE TABLE t1 (
+a VARCHAR(1),
+b VARCHAR(1) NOT NULL,
+KEY(a)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('j','j'),('v','v'),('c','c'),('m','m'),('d','d'),
+('y','y'),('t','t'),('d','d'),('s','s'),('r','r'),
+('m','m'),('b','b'),('x','x'),('g','g'),('p','p'),
+('q','q'),('w','w'),('d','d'),('e','e');
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+# This query returned 6 rows instead of 19
+SELECT * FROM v1
+WHERE ( a, a ) IN (
+SELECT alias2.b, alias2.a
+FROM t1 AS alias1, t1 AS alias2
+WHERE alias2.b = alias1.a
+AND ( alias1.b >= alias1.a OR alias2.b = 'z' )
+);
+a	b
+b	b
+c	c
+d	d
+d	d
+d	d
+e	e
+g	g
+j	j
+m	m
+m	m
+p	p
+q	q
+r	r
+s	s
+t	t
+v	v
+w	w
+x	x
+y	y
+# Another testcase, without the VIEW:
+CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t2 SELECT * FROM t1;
+EXPLAIN 
+SELECT * FROM t2 
+WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+WHERE 
+alias2.b = alias1.a AND 
+(alias1.b >= alias1.a OR alias2.b = 'z'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
+1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	
+2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
+2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where
+SELECT * FROM t2 
+WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+WHERE 
+alias2.b = alias1.a AND 
+(alias1.b >= alias1.a OR alias2.b = 'z'));
+a	b
+b	b
+b	b
+c	c
+c	c
+d	d
+d	d
+d	d
+d	d
+d	d
+d	d
+e	e
+e	e
+g	g
+g	g
+j	j
+j	j
+m	m
+m	m
+m	m
+m	m
+p	p
+p	p
+q	q
+q	q
+r	r
+r	r
+s	s
+s	s
+t	t
+t	t
+v	v
+v	v
+w	w
+w	w
+x	x
+x	x
+y	y
+y	y
+DROP VIEW v1;
+DROP TABLE t1, t2;
+# 
+# BUG#965872: Server crashes in embedding_sjm on a simple 1-table select with AND and OR  
+#  (this is a regression caused by the fix for BUG#951937)
+CREATE TABLE t1 ( a INT, b INT, c INT, d INT );
+INSERT INTO t1 VALUES (4,2,8,9),(4,2,7,8);
+SELECT * FROM t1
+WHERE a = d AND ( b = 50 AND b = d OR a = c );
+a	b	c	d
+DROP TABLE t1;
+#
+# BUG#951283: Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery
+#
+set @tmp_951283=@@optimizer_prune_level;
+SET optimizer_prune_level=0;
+CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10),(11),(12),(13),(14),(15),(16),
+(17),(18),(19),(20),(21),(22),(23);
+CREATE TABLE t2 (
+b INT PRIMARY KEY,
+c VARCHAR(1),
+d VARCHAR(1),
+KEY(c)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,'j','j'),(2,'v','v'),(3,'c','c'),(4,'m','m'),
+(5,'d','d'),(6,'d','d'),(7,'y','y'),(8,'t','t'),
+(9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'),
+(13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'),
+(17,'q','q'),(18,'w','w'),(19,'d','d');
+EXPLAIN
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias3	ALL	PRIMARY	NULL	NULL	NULL	19	Using where
+1	PRIMARY	alias4	ref	PRIMARY,c	c	4	test.alias3.d	1	Using index
+1	PRIMARY	alias5	eq_ref	PRIMARY	PRIMARY	4	test.alias4.b	1	Using where; FirstMatch(alias3)
+1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	14	Using join buffer (flat, BNL join)
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	14	Using join buffer (flat, BNL join)
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+WHERE alias5.b = alias4.b
+AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
+);
+COUNT(*)
+3724
+set optimizer_prune_level=@tmp_951283;
+DROP TABLE t1,t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 set optimizer_switch=default;
diff --git a/mysql-test/suite/innodb/r/innodb-autoinc-61209.result b/mysql-test/suite/innodb/r/innodb-autoinc-61209.result
new file mode 100644
index 00000000000..daa9d54df4c
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-autoinc-61209.result
@@ -0,0 +1,30 @@
+DROP TABLE IF EXISTS bug_61209;
+CREATE TABLE bug_61209 (a INT auto_increment, PRIMARY KEY(a)) ENGINE=InnoDB;
+INSERT INTO bug_61209 VALUES (), ();
+
+# Connect con1
+
+# Connect con2
+
+# Connection con1
+SET SESSION auto_increment_increment=3;
+SET SESSION auto_increment_offset=2;
+SELECT GET_LOCK('a', 10);
+
+# Connection con2
+SET SESSION auto_increment_increment=3;
+SET SESSION auto_increment_offset=2;
+INSERT INTO bug_61209 (a) VALUES (NULL), (NULL), (NULL + GET_LOCK('a', 10));
+
+# Connection con1 reap
+GET_LOCK('a', 10)
+1
+INSERT INTO bug_61209 (a) VALUES (NULL), (NULL), (NULL);
+SELECT RELEASE_LOCK('a');
+
+# Connection con2 reap
+
+# Connection con1 reap
+RELEASE_LOCK('a')
+1
+DROP TABLE bug_61209;
diff --git a/mysql-test/suite/innodb/r/innodb-autoinc.result b/mysql-test/suite/innodb/r/innodb-autoinc.result
index 0c8d16f27fb..fa821372337 100644
--- a/mysql-test/suite/innodb/r/innodb-autoinc.result
+++ b/mysql-test/suite/innodb/r/innodb-autoinc.result
@@ -366,7 +366,7 @@ c1
 310
 400
 1000
-1010
+1110
 DROP TABLE t1;
 SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
 SET @@INSERT_ID=1;
@@ -648,7 +648,7 @@ t2	CREATE TABLE `t2` (
   `n` int(10) unsigned NOT NULL,
   `o` enum('FALSE','TRUE') DEFAULT NULL,
   PRIMARY KEY (`m`)
-) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -657,7 +657,7 @@ t1	CREATE TABLE `t1` (
   `b` int(10) unsigned NOT NULL,
   `c` enum('FALSE','TRUE') DEFAULT NULL,
   PRIMARY KEY (`a`)
-) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
 INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
 SELECT * FROM t1;
 a	b	c
@@ -671,16 +671,16 @@ a	b	c
 8	4	FALSE
 9	5	TRUE
 10	5	FALSE
-13	1	TRUE
-14	1	FALSE
-15	2	TRUE
-16	2	FALSE
-17	3	TRUE
-18	3	FALSE
-19	4	TRUE
-20	4	FALSE
-21	5	TRUE
-22	5	FALSE
+16	1	TRUE
+17	1	FALSE
+18	2	TRUE
+19	2	FALSE
+20	3	TRUE
+21	3	FALSE
+22	4	TRUE
+23	4	FALSE
+24	5	TRUE
+25	5	FALSE
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -688,7 +688,7 @@ t1	CREATE TABLE `t1` (
   `b` int(10) unsigned NOT NULL,
   `c` enum('FALSE','TRUE') DEFAULT NULL,
   PRIMARY KEY (`a`)
-) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1
 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
 SELECT * FROM t1;
 a	b	c
@@ -702,21 +702,21 @@ a	b	c
 8	4	FALSE
 9	5	TRUE
 10	5	FALSE
-13	1	TRUE
-14	1	FALSE
-15	2	TRUE
-16	2	FALSE
-17	3	TRUE
-18	3	FALSE
-19	4	TRUE
-20	4	FALSE
-21	5	TRUE
-22	5	FALSE
-23	1	FALSE
-24	2	FALSE
-25	3	FALSE
-26	4	FALSE
-27	5	FALSE
+16	1	TRUE
+17	1	FALSE
+18	2	TRUE
+19	2	FALSE
+20	3	TRUE
+21	3	FALSE
+22	4	TRUE
+23	4	FALSE
+24	5	TRUE
+25	5	FALSE
+31	1	FALSE
+32	2	FALSE
+33	3	FALSE
+34	4	FALSE
+35	5	FALSE
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -724,7 +724,7 @@ t1	CREATE TABLE `t1` (
   `b` int(10) unsigned NOT NULL,
   `c` enum('FALSE','TRUE') DEFAULT NULL,
   PRIMARY KEY (`a`)
-) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1
 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
 SELECT * FROM t1;
 a	b	c
@@ -738,26 +738,26 @@ a	b	c
 8	4	FALSE
 9	5	TRUE
 10	5	FALSE
-13	1	TRUE
-14	1	FALSE
-15	2	TRUE
-16	2	FALSE
-17	3	TRUE
-18	3	FALSE
-19	4	TRUE
-20	4	FALSE
-21	5	TRUE
-22	5	FALSE
-23	1	FALSE
-24	2	FALSE
-25	3	FALSE
-26	4	FALSE
-27	5	FALSE
-30	1	FALSE
-31	2	FALSE
-32	3	FALSE
-33	4	FALSE
-34	5	FALSE
+16	1	TRUE
+17	1	FALSE
+18	2	TRUE
+19	2	FALSE
+20	3	TRUE
+21	3	FALSE
+22	4	TRUE
+23	4	FALSE
+24	5	TRUE
+25	5	FALSE
+31	1	FALSE
+32	2	FALSE
+33	3	FALSE
+34	4	FALSE
+35	5	FALSE
+38	1	FALSE
+39	2	FALSE
+40	3	FALSE
+41	4	FALSE
+42	5	FALSE
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -765,7 +765,7 @@ t1	CREATE TABLE `t1` (
   `b` int(10) unsigned NOT NULL,
   `c` enum('FALSE','TRUE') DEFAULT NULL,
   PRIMARY KEY (`a`)
-) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=latin1
 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -774,7 +774,7 @@ t1	CREATE TABLE `t1` (
   `b` int(10) unsigned NOT NULL,
   `c` enum('FALSE','TRUE') DEFAULT NULL,
   PRIMARY KEY (`a`)
-) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=latin1
 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -783,7 +783,7 @@ t1	CREATE TABLE `t1` (
   `b` int(10) unsigned NOT NULL,
   `c` enum('FALSE','TRUE') DEFAULT NULL,
   PRIMARY KEY (`a`)
-) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1
 INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
 SELECT * FROM t1;
 a	b	c
@@ -797,41 +797,41 @@ a	b	c
 8	4	FALSE
 9	5	TRUE
 10	5	FALSE
-13	1	TRUE
-14	1	FALSE
-15	2	TRUE
-16	2	FALSE
-17	3	TRUE
-18	3	FALSE
-19	4	TRUE
-20	4	FALSE
-21	5	TRUE
-22	5	FALSE
-23	1	FALSE
-24	2	FALSE
-25	3	FALSE
-26	4	FALSE
-27	5	FALSE
-30	1	FALSE
-31	2	FALSE
-32	3	FALSE
-33	4	FALSE
-34	5	FALSE
-37	1	FALSE
-38	2	FALSE
-39	3	FALSE
-40	4	FALSE
-41	5	FALSE
-44	1	FALSE
-45	2	FALSE
-46	3	FALSE
-47	4	FALSE
-48	5	FALSE
-51	1	FALSE
-52	2	FALSE
-53	3	FALSE
-54	4	FALSE
-55	5	FALSE
+16	1	TRUE
+17	1	FALSE
+18	2	TRUE
+19	2	FALSE
+20	3	TRUE
+21	3	FALSE
+22	4	TRUE
+23	4	FALSE
+24	5	TRUE
+25	5	FALSE
+31	1	FALSE
+32	2	FALSE
+33	3	FALSE
+34	4	FALSE
+35	5	FALSE
+38	1	FALSE
+39	2	FALSE
+40	3	FALSE
+41	4	FALSE
+42	5	FALSE
+45	1	FALSE
+46	2	FALSE
+47	3	FALSE
+48	4	FALSE
+49	5	FALSE
+52	1	FALSE
+53	2	FALSE
+54	3	FALSE
+55	4	FALSE
+56	5	FALSE
+59	1	FALSE
+60	2	FALSE
+61	3	FALSE
+62	4	FALSE
+63	5	FALSE
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -839,7 +839,7 @@ t1	CREATE TABLE `t1` (
   `b` int(10) unsigned NOT NULL,
   `c` enum('FALSE','TRUE') DEFAULT NULL,
   PRIMARY KEY (`a`)
-) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=latin1
 DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE IF EXISTS t1;
diff --git a/mysql-test/suite/innodb/t/innodb-autoinc-61209.test b/mysql-test/suite/innodb/t/innodb-autoinc-61209.test
new file mode 100644
index 00000000000..12bdc236b90
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb-autoinc-61209.test
@@ -0,0 +1,61 @@
+# This is the test case for bug #61209 - duplicate key errors
+# when using auto_increment_increment > 1 and auto_increment_offset > 1
+#
+--source include/have_innodb.inc
+
+#
+# Precautionary clean up.
+#
+--disable_warnings
+DROP TABLE IF EXISTS bug_61209;
+--enable_warnings
+
+#
+# Create test data.
+#
+CREATE TABLE bug_61209 (a INT auto_increment, PRIMARY KEY(a)) ENGINE=InnoDB;
+
+INSERT INTO bug_61209 VALUES (), ();
+
+--echo
+--echo # Connect con1
+--connect (con1,localhost,root,,)
+--echo
+--echo # Connect con2
+--connect (con2,localhost,root,,)
+
+--echo
+--echo # Connection con1
+--connection con1
+SET SESSION auto_increment_increment=3;
+SET SESSION auto_increment_offset=2;
+send SELECT GET_LOCK('a', 10);
+
+--echo
+--echo # Connection con2
+--connection con2
+SET SESSION auto_increment_increment=3;
+SET SESSION auto_increment_offset=2;
+send INSERT INTO bug_61209 (a) VALUES (NULL), (NULL), (NULL + GET_LOCK('a', 10));
+
+--echo
+--echo # Connection con1 reap
+--connection con1
+reap;
+INSERT INTO bug_61209 (a) VALUES (NULL), (NULL), (NULL);
+send SELECT RELEASE_LOCK('a');
+
+--echo
+--echo # Connection con2 reap
+--connection con2
+reap;
+
+--echo
+--echo # Connection con1 reap
+--connection con1
+reap;
+
+#
+# Clean up
+#
+DROP TABLE bug_61209;
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index ede631f32be..04d1f9e9d9c 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -1093,5 +1093,143 @@ SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b);
 
 DROP TABLE t1,t2;
 
+--echo # 
+--echo # BUG#962667: Assertion `0' failed in QUICK_INDEX_SORT_SELECT::need_sorted_output() 
+--echo #   with index_merge+index_merge_sort_union+loosescan+semijoin
+--echo #
+CREATE TABLE t1 (
+  a INT, b VARCHAR(1), c INT,
+  KEY(a), KEY(b)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+(1,'v',9),(2,'v',8),(3,'c',7),(4,'m',6),(5,'x',5),
+(6,'i',4),(7,'e',3),(8,'p',2),(9,'s',1),(10,'j',9),
+(11,'z',8),(12,'c',7),(13,'a',6),(14,'q',5),(15,'y',4),
+(16,'n',3),(17,'r',2),(18,'v',1),(19,'p',0);
+
+CREATE TABLE t2 (
+  pk INT, d VARCHAR(1), e INT,
+  PRIMARY KEY(pk), KEY(d,e)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES
+(1,'x',1),(2,'d',2),(3,'r',3),(4,'f',4),(5,'y',5),
+(6,'u',6),(7,'m',7),(8,'k',8),(9,'o',9),(10,'w',1),
+(11,'m',2),(12,'q',3),(13,'m',4),(14,'d',5),
+(15,'g',6),(16,'x',7),(17,'f',8);
+
+explain
+SELECT * FROM t1 WHERE b IN (
+  SELECT d FROM t2, t1
+  WHERE a = d AND ( pk < 2 OR d = 'z' )
+);
+SELECT * FROM t1 WHERE b IN (
+  SELECT d FROM t2, t1
+  WHERE a = d AND ( pk < 2 OR d = 'z' )
+);
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # BUG#951937: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view
+--echo #
+CREATE TABLE t1 (
+  a VARCHAR(1),
+  b VARCHAR(1) NOT NULL,
+  KEY(a)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+('j','j'),('v','v'),('c','c'),('m','m'),('d','d'),
+('y','y'),('t','t'),('d','d'),('s','s'),('r','r'),
+('m','m'),('b','b'),('x','x'),('g','g'),('p','p'),
+('q','q'),('w','w'),('d','d'),('e','e');
+
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+
+--echo # This query returned 6 rows instead of 19
+--sorted_result
+SELECT * FROM v1
+WHERE ( a, a ) IN (
+  SELECT alias2.b, alias2.a
+  FROM t1 AS alias1, t1 AS alias2
+  WHERE alias2.b = alias1.a
+    AND ( alias1.b >= alias1.a OR alias2.b = 'z' )
+);
+
+--echo # Another testcase, without the VIEW:
+CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(1) NOT NULL, KEY(a)) ENGINE=InnoDB;
+INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t2 SELECT * FROM t1;
+EXPLAIN 
+SELECT * FROM t2 
+ WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+                  WHERE 
+                    alias2.b = alias1.a AND 
+                    (alias1.b >= alias1.a OR alias2.b = 'z'));
+
+--sorted_result
+SELECT * FROM t2 
+ WHERE (a, a) IN (SELECT alias2.b, alias2.a FROM t1 AS alias1, t1 AS alias2 
+                  WHERE 
+                    alias2.b = alias1.a AND 
+                    (alias1.b >= alias1.a OR alias2.b = 'z'));
+
+DROP VIEW v1;
+DROP TABLE t1, t2;
+
+--echo # 
+--echo # BUG#965872: Server crashes in embedding_sjm on a simple 1-table select with AND and OR  
+--echo #  (this is a regression caused by the fix for BUG#951937)
+CREATE TABLE t1 ( a INT, b INT, c INT, d INT );
+INSERT INTO t1 VALUES (4,2,8,9),(4,2,7,8);
+
+SELECT * FROM t1
+WHERE a = d AND ( b = 50 AND b = d OR a = c );
+DROP TABLE t1;
+
+--echo #
+--echo # BUG#951283: Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery
+--echo #
+set @tmp_951283=@@optimizer_prune_level;
+SET optimizer_prune_level=0;
+
+CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+  (10),(11),(12),(13),(14),(15),(16),
+  (17),(18),(19),(20),(21),(22),(23);
+
+CREATE TABLE t2 (
+  b INT PRIMARY KEY,
+  c VARCHAR(1),
+  d VARCHAR(1),
+  KEY(c)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES
+  (1,'j','j'),(2,'v','v'),(3,'c','c'),(4,'m','m'),
+  (5,'d','d'),(6,'d','d'),(7,'y','y'),(8,'t','t'),
+  (9,'d','d'),(10,'s','s'),(11,'r','r'),(12,'m','m'),
+  (13,'b','b'),(14,'x','x'),(15,'g','g'),(16,'p','p'),
+  (17,'q','q'),(18,'w','w'),(19,'d','d');
+
+EXPLAIN
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+  SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+  WHERE alias5.b = alias4.b
+    AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
+);
+
+SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
+WHERE alias3.d IN (
+  SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
+  WHERE alias5.b = alias4.b
+    AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
+);
+
+set optimizer_prune_level=@tmp_951283;
+DROP TABLE t1,t2;
+
 --echo # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;