3 files changed, 160 insertions, 2 deletions
diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test
index 5431c6dba2b..614c6595d61 100644
--- a/mysql-test/t/index_merge_myisam.test
+++ b/mysql-test/t/index_merge_myisam.test
@@ -125,5 +125,29 @@ set optimizer_switch=default;
 
 drop table t0, t1;
 
+
+--echo #
+--echo # BUG#834514 Assertion `!table || (!table->read_set || bitmap_is_set(...' with aggregates
+--echo #
+CREATE TABLE t1 ( a int , b int, c int, KEY (b), PRIMARY KEY (a)) ;
+INSERT INTO t1 VALUES (1,4,0),(5,0,0),(6,7,0),(7,7,0),(8,1,0),(9,7,0),(10,1,0);
+
+CREATE TABLE t2 ( b int, c int, KEY (c,b)) ;
+INSERT INTO t2 VALUES (7,0),(1,0),(7,0),(1,0);
+
+CREATE TABLE t3 ( a int ) ;
+
+SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c)
+FROM t1, t2
+WHERE (t2.c = t1.c)
+AND (
+        t1.b IN ( 4 )
+        OR t1.a = 137
+        AND EXISTS ( SELECT a FROM t3 )
+)
+GROUP BY 2;
+
+DROP TABLE t1,t2,t3;
+
 set optimizer_switch= @optimizer_switch_save;
 
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index a175a2f7cf0..49ecfe86fbc 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2,7 +2,7 @@
 # Nested Loops semi-join subquery evaluation tests
 #
 --disable_warnings
-drop table if exists t0, t1, t2, t3, t4, t10, t11, t12;
+drop table if exists t0, t1, t2, t3, t4, t5, t10, t11, t12;
 drop view if exists v1, v2, v3, v4;
 drop procedure if exists p1;
 --enable_warnings
@@ -1594,5 +1594,53 @@ WHERE (t3.a) IN (
 );
 drop table t1, t2,t3; 
 
+--echo #
+--echo # BUG#834758: Wrong result with innner join, LooseScan, two-column IN() predicate
+--echo #
+
+set @tmp835758=@@optimizer_switch;
+set optimizer_switch='semijoin=on,loosescan=on,materialization=off,firstmatch=off';
+
+CREATE TABLE t1 (b int) ;
+INSERT INTO t1 VALUES (1),(5);
+
+CREATE TABLE t2 (a int, PRIMARY KEY (a)) ;
+INSERT INTO t2 VALUES (6),(10);
+
+CREATE TABLE t3 (a int, b int, KEY (b)) ;
+INSERT INTO t3 VALUES (6,5),(6,2),(8,0),(9,1),(6,5);
+
+--echo # This used to incorrectly pick a join order of (t1, LooseScan(t3), t2):
+explain 
+SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3);
+SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3);
+
+DROP TABLE t1, t2, t3;
+set @@optimizer_switch= @tmp835758;
+
+--echo #
+--echo # BUG#834739: Wrong result with 3-way inner join, LooseScan,multipart keys  
+--echo #
+set @tmp834739=@@optimizer_switch;
+set optimizer_switch='semijoin=on,loosescan=on,materialization=off,firstmatch=off';
+CREATE TABLE t2 ( b int, c int, KEY (b)) ;
+INSERT INTO t2 VALUES (1,0),(1,0),(9,0),(1,0),(5,0);
+
+CREATE TABLE t3 ( a int);
+INSERT INTO t3 VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
+
+CREATE TABLE t4 ( a int);
+INSERT INTO t4 VALUES (0),(0),(0);
+
+CREATE TABLE t5 ( b int, a int , KEY (a,b)) ;
+INSERT INTO t5 VALUES (7,0),(9,0);
+
+explain
+SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
+SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
+
+DROP TABLE t2, t3, t4, t5;
+set @@optimizer_switch=@tmp834739;
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 324b16123c2..95ef6a25df3 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -9,7 +9,7 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 set @optimizer_switch_local_default= @@optimizer_switch;
 
 --disable_warnings
-drop table if exists t1, t2, t3, t1i, t2i, t3i;
+drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i;
 drop table if exists columns;
 drop table if exists t1_16, t2_16, t3_16;
 drop view if exists v1, v2, v1m, v2m;
@@ -1263,5 +1263,91 @@ GROUP BY 1 , 2;
 
 drop table t1,t2,t3;
 
+--echo #
+--echo # BUG#836523: Crash in JOIN::get_partial_cost_and_fanout with semijoin+materialization 
+--echo #
+CREATE TABLE t1 (a varchar(1));
+INSERT INTO t1 VALUES ('a'),('a');
+
+CREATE TABLE t2 (a varchar(1));
+
+CREATE TABLE t3 (a int);
+INSERT INTO t3 VALUES (1),(2);
+
+CREATE TABLE t4 (a varchar(1));
+INSERT INTO t4 VALUES ('a'),('a');
+
+SELECT t1.a
+FROM t1
+WHERE t1.a IN (
+        SELECT t2.a
+        FROM t2, t3
+)
+HAVING a IN (
+        SELECT a
+        FROM t4
+);
+DROP TABLE t1, t2, t3, t4;
+
+--echo #
+--echo # BUG#836507: Crash in setup_sj_materialization_part1() with semijoin+materialization
+--echo #
+CREATE TABLE t1 (a int) ;
+INSERT IGNORE INTO t1 VALUES (1),(1);
+
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (1);
+
+CREATE TABLE t3 (a int);
+
+CREATE TABLE t4 (a int);
+INSERT INTO t4 VALUES (2),(2);
+
+CREATE TABLE t5 (a int);
+INSERT INTO t5 VALUES (1);
+
+SELECT * FROM t1
+WHERE (a) IN (
+        SELECT t5.a
+        FROM (
+                t2
+                LEFT JOIN ( t3 , t4 )
+                ON 1 = 1
+        )
+        JOIN t5
+);
+
+DROP TABLE t1,t2,t3,t4,t5;
+
+--echo #
+--echo # BUG#836532: Crash in Item_equal_fields_iterator::get_curr_field with semijoin+materialization
+--echo #
+
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES ('a'),('a');
+
+CREATE TABLE t4 (a varchar(1));
+INSERT INTO t4 VALUES ('m'),('o');
+
+CREATE TABLE t3 (a varchar(1) , b varchar(1) ) ;
+INSERT INTO t3 VALUES ('b','b');
+
+CREATE TABLE t5 (a varchar(1), KEY (a)) ;
+INSERT INTO t5 VALUES ('d'),('e');
+
+SELECT *
+FROM t2
+WHERE t2.a = ALL (
+        SELECT t4.a
+        FROM t4
+        WHERE t4.a IN (
+                SELECT t3.a
+                FROM t3 , t5
+                WHERE ( t5.a = t3.b )
+        )
+);
+
+DROP TABLE t2,t3,t4,t5;
+
 set optimizer_switch=@subselect_sj_mat_tmp;