Merge latest MariaDB 5.5 into MWL#192: Non-blocking client library.

25 files changed, 1371 insertions, 38 deletions

diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test
index 42f3ce296e1..c2f831036e1 100644
--- a/mysql-test/t/derived_opt.test
+++ b/mysql-test/t/derived_opt.test
@@ -202,5 +202,15 @@ RIGHT JOIN (
 
 drop table t1,t2,t3,t4;
 
+--echo #
+--echo # LP BUG#910123 MariaDB 5.3.3 causes 1093 error on Drupal
+--echo # Fix: force materialization in case of conflict
+--echo #
+SET optimizer_switch='derived_merge=on';
+CREATE TABLE t1 ( i INT );
+INSERT INTO t1 VALUES ( (SELECT 1 FROM ( SELECT * FROM t1 ) as a) );
+drop table t1;
+set optimizer_switch=@save_optimizer_switch;
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index b674350af0a..95412426aa0 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1192,7 +1192,7 @@ INSERT INTO t2 VALUES (7), (4);
 CREATE TABLE t1 (b int NOT NULL);
 INSERT INTO t1 VALUES (5), (7);
 CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1;
-
+SET @save_optimizer_switch=@@optimizer_switch;
 SET SESSION optimizer_switch='derived_merge=off';
 
 PREPARE st1 FROM
@@ -1201,10 +1201,12 @@ EXECUTE st1;
 EXECUTE st1;
 DEALLOCATE PREPARE st1;
 
+set SESSION optimizer_switch= @save_optimizer_switch;
+
 DROP VIEW v1;
 DROP TABLE t1,t2;
 
-SET SESSION optimizer_switch='derived_merge=on';
+
 
 --echo #
 --echo # LP bug #879939: assertion in ha_maria::enable_indexes 
@@ -1226,7 +1228,7 @@ INSERT INTO t1 VALUES
 
 CREATE TABLE t3 (a varchar(35));
 INSERT INTO t3 VALUES ('Miami');
-
+SET @save_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch = 'derived_with_keys=on';
 SET @@tmp_table_size=1024*4;
 explain SELECT * FROM (SELECT t1.* FROM t1, t2) AS t JOIN t3 ON t3.a = t.b;
@@ -1234,7 +1236,7 @@ SELECT * FROM (SELECT t1.* FROM t1, t2) AS t JOIN t3 ON t3.a = t.b;
 SET @@tmp_table_size=1024*1024*16;
 SELECT * FROM (SELECT t1.* FROM t1, t2) AS t JOIN t3 ON t3.a = t.b;
 SET @@tmp_table_size=default;
-
+set SESSION optimizer_switch= @save_optimizer_switch;
 drop table t1,t2,t3;
 
 --echo #
@@ -1277,6 +1279,40 @@ ORDER BY CONCAT(alias2.col_varchar_nokey);
 set max_heap_table_size= @tmp_882994;
 drop table t1,t2,t3;
 
+--echo #
+--echo # LP bug #917990: Bad estimate of #rows for derived table with LIMIT
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES
+  (8), (3), (4), (7), (9), (5), (1), (2);
+
+SELECT * FROM (SELECT * FROM t1 LIMIT 3) t;
+EXPLAIN
+SELECT * FROM (SELECT * FROM t1 LIMIT 3) t;
+
+DROP TABLE t1;
+
+--echo #
+--echo # LP BUG#921878 incorrect check of items during columns union types
+--echo # aggregation for merged derived tables
+--echo #
+SET @save_optimizer_switch=@@optimizer_switch;
+SET SESSION optimizer_switch='derived_merge=on'; 
+CREATE TABLE t1 ( a ENUM( 'x', 'y' ) );
+insert into t1 values ('x');
+CREATE TABLE t2 LIKE t1;
+insert into t1 values ('y');
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3
+  SELECT * FROM ( SELECT * FROM t1 ) AS A
+    UNION SELECT * FROM t2;
+select * from t3;
+
+drop table t1,t2,t3;
+
+set SESSION optimizer_switch= @save_optimizer_switch;
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 54535470d4c..7a098c44e5b 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -1154,6 +1154,53 @@ drop table t1;
 --echo End of 5.1 tests
 
 --echo #
+--echo # Bug #904345: MIN/MAX optimization  with constant FALSE condition
+--echo #
+
+CREATE TABLE t1 (a int NOT NULL, KEY(a));
+INSERT INTO t1 VALUES (10), (8), (11), (7), (15), (12), (9);
+
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES
+  (8,2), (6,9), (8,4), (5,3), (9,1);
+
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
+
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10; 
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10; 
+
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10;
+SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug #879860: MIN/MAX for subquery returning empty set 
+--echo #
+
+CREATE TABLE t1 (a int PRIMARY KEY);
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (a int NOT NULL);
+INSERT INTO t2 VALUES (10);
+
+CREATE TABLE t3 ( a int, b int);
+INSERT INTO t3 VALUES (19,1), (20,5);
+
+EXPLAIN EXTENDED
+SELECT (SELECT MIN(t1.a) FROM t1,t2 WHERE t2.a = t3.b) FROM t3;
+SELECT (SELECT MIN(t1.a) FROM t1,t2 WHERE t2.a = t3.b) FROM t3;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo End of 5.2 tests
+
+--echo #
 --echo # BUG#46680 - Assertion failed in file item_subselect.cc, 
 --echo #             line 305 crashing on HAVING subquery
 --echo #
diff --git a/mysql-test/t/innodb_icp.test b/mysql-test/t/innodb_icp.test
index 0fb42355f96..d6caa36a88e 100644
--- a/mysql-test/t/innodb_icp.test
+++ b/mysql-test/t/innodb_icp.test
@@ -12,6 +12,39 @@ set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
 
 --source include/icp_tests.inc
 
+--echo #
+--echo # BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 
+--echo #
+CREATE TABLE t1 ( a INT )
+  ENGINE=InnoDB;
+INSERT INTO t1 VALUES (7),(7);
+
+CREATE TABLE t2 ( b VARCHAR(1) );
+INSERT INTO t2 VALUES ('j'),('v');
+
+CREATE TABLE t3 (
+  c INT, d VARCHAR(1), e VARCHAR(1),
+  KEY (d,c)
+) ENGINE=InnoDB;
+INSERT INTO t3
+  VALUES (6,'w','w'),
+    (1,'v','v'),(7,'s','s'),(4,'l','l'),
+    (7,'y','y'),(0,'c','c'),(2,'i','i'),
+    (9,'h','h'),(4,'q','q'),(0,'a','a'),
+    (9,'v','v'),(1,'u','u'),(3,'s','s'),
+    (8,'z','z'),(1,'h','h'),(8,'p','p'),
+    (6,'e','e'),(3,'i','i'),(6,'y','y');
+
+SELECT *
+FROM t1 INNER JOIN t2 INNER JOIN t3
+  ON d = b
+WHERE
+  NOT EXISTS ( SELECT * FROM t3 )
+  OR a = c
+ORDER BY e;
+
+DROP TABLE t1,t2,t3;
+
 set optimizer_switch=@innodb_icp_tmp;
 set storage_engine= @save_storage_engine;
 
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 4600da9ee17..c6017337c0a 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -1526,12 +1526,14 @@ insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
 insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
                       (4,4, 'qwerty');
 
+flush status;
 set join_cache_level=5;
 select t2.f1, t2.f2, t2.f3 from t1,t2
 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
 
 explain select t2.f1, t2.f2, t2.f3 from t1,t2
 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+show status like "Handler_pushed%";
 
 set join_cache_level=6;
 select t2.f1, t2.f2, t2.f3 from t1,t2
@@ -1539,6 +1541,7 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
 
 explain select t2.f1, t2.f2, t2.f3 from t1,t2
 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+show status like "Handler_pushed%";
 
 set join_cache_level=7;
 select t2.f1, t2.f2, t2.f3 from t1,t2
@@ -1546,6 +1549,7 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
 
 explain select t2.f1, t2.f2, t2.f3 from t1,t2
 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+show status like "Handler_pushed%";
 
 set join_cache_level=8;
 select t2.f1, t2.f2, t2.f3 from t1,t2
@@ -1553,6 +1557,7 @@ where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
 
 explain select t2.f1, t2.f2, t2.f3 from t1,t2
 where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+show status like "Handler_pushed%";
 
 drop table t1,t2;
 set join_cache_level=default;
@@ -3423,5 +3428,164 @@ SET optimizer_switch=@tmp_optimizer_switch;
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # Bug#53305 Duplicate weedout + join buffer (join cache --level=7,8)
+--echo #
+
+create table t1 (uid int, fid int, index(uid));
+insert into t1 values
+  (1,1), (1,2), (1,3), (1,4),
+  (2,5), (2,6), (2,7), (2,8),
+  (3,1), (3,2), (3,9);
+
+create table t2 (uid int primary key, name varchar(128), index(name));
+insert into t2 values 
+  (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"),
+  (6, "F"), (7, "G"), (8, "H"), (9, "I");
+
+create table t3 (uid int, fid int, index(uid));
+insert into t3 values
+  (1,1), (1,2), (1,3),(1,4),
+  (2,5), (2,6), (2,7), (2,8),
+  (3,1), (3,2), (3,9);
+
+set @tmp_optimizer_switch=@@optimizer_switch;
+set  @@optimizer_switch='semijoin=on';
+set optimizer_switch='materialization=off';
+set optimizer_switch='loosescan=off,firstmatch=off';
+set optimizer_switch='mrr_sort_keys=off';
+set join_cache_level=7;
+
+create table t4 (uid int primary key, name varchar(128), index(name));
+insert into t4 values 
+  (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"),
+  (6, "F"), (7, "G"), (8, "H"), (9, "I");
+
+explain select name from t2, t1 
+  where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
+        and t2.uid=t1.fid;
+
+--sorted_result
+select name from t2, t1 
+  where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
+        and t2.uid=t1.fid;
+
+set join_cache_level = default;
+set optimizer_switch=@tmp_optimizer_switch;
+
+drop table t1,t2,t3,t4;
+
+--echo #
+--echo # Bug#50358 - semijoin execution of subquery with outerjoin
+--echo #             emplying join buffer
+--echo #
+
+CREATE TABLE t1 (i int);
+CREATE TABLE t2 (i int);
+CREATE TABLE t3 (i int);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (6);
+INSERT INTO t3 VALUES (1), (2);
+
+set @tmp_optimizer_switch=@@optimizer_switch;
+set  @@optimizer_switch='semijoin=on';
+set optimizer_switch='materialization=on';
+
+set join_cache_level=0;
+EXPLAIN
+SELECT * FROM t1 WHERE t1.i IN 
+                       (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
+SELECT * FROM t1 WHERE t1.i IN 
+                       (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
+
+set join_cache_level=2;
+EXPLAIN
+SELECT * FROM t1 WHERE t1.i IN 
+                       (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
+SELECT * FROM t1 WHERE t1.i IN 
+                       (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
+
+set join_cache_level = default;
+set optimizer_switch=@tmp_optimizer_switch;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug #12546542: missing row with semijoin=off + join cache
+--echo # (LP bug #922971)
+--echo #
+
+CREATE TABLE t1 (a varchar(1024));
+INSERT INTO t1 VALUES ('v'), ('we');
+CREATE TABLE t2 (
+  a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int
+);
+INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6);
+CREATE TABLE t3 (b int, c int);
+INSERT INTO t3 VALUES (4,4);
+
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=off';
+set optimizer_switch='materialization=off';
+
+set join_cache_level=0;
+EXPLAIN
+SELECT * FROM t1
+  WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 
+                WHERE t2.c < 10 OR t3.c > 1);
+
+SELECT * FROM t1
+  WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b 
+                WHERE t2.c < 10 OR t3.c > 1);
+
+set join_cache_level=2;
+EXPLAIN
+SELECT * FROM t1
+  WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
+                WHERE t2.c < 10 OR t3.c > 1);
+SELECT * FROM t1
+  WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b
+                WHERE t2.c < 10 OR t3.c > 1);
+
+set join_cache_level = default;
+set optimizer_switch=@tmp_optimizer_switch;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug #925985: LEFT JOIN with optimize_join_buffer_size=off +
+--echo #              join_buffer_size > join_buffer_space_limit
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (5), (3);
+
+CREATE TABLE t2 (a int, b int);
+INSERT INTO t2 VALUES
+  (3,30), (1,10), (7,70), (2,20),
+  (3,31), (1,11), (7,71), (2,21),
+  (3,32), (1,12), (7,72), (2,22);
+
+CREATE TABLE t3 (b int, c int);
+INSERT INTO t3 VALUES (32, 302), (42,400), (30,300);
+
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='optimize_join_buffer_size=off';
+set join_buffer_space_limit=4096;
+set join_buffer_size=4096*2;
+set join_cache_level=2;
+set optimizer_switch='outer_join_with_cache=on';
+
+EXPLAIN
+SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a;
+SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a;
+  
+set join_buffer_space_limit=default;
+set join_buffer_size=default;
+set join_cache_level=default;
+set optimizer_switch=@tmp_optimizer_switch;
+
+DROP TABLE t1,t2,t3;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/join_outer_innodb.test b/mysql-test/t/join_outer_innodb.test
index 0d47c4ea57b..c5cf66c816b 100644
--- a/mysql-test/t/join_outer_innodb.test
+++ b/mysql-test/t/join_outer_innodb.test
@@ -61,3 +61,18 @@ ORDER BY pk;
 DROP TABLE t1,t2;
 
 --echo # End BUG#58456
+
+#
+# Bug #848652: crash with RIGHT JOIN and GROUP BY
+# 
+
+CREATE TABLE t1(a int, b int, KEY (a), PRIMARY KEY (b))  ENGINE=InnoDB;
+
+CREATE TABLE t2 (b int, PRIMARY KEY (b));
+INSERT INTO t2 VALUES (4),(9);
+
+SELECT STRAIGHT_JOIN t1.a FROM t1 RIGHT JOIN t2 ON t1.b = t2.b
+  WHERE (t1.b NOT BETWEEN 1 AND 7 OR t1.a IS NULL AND t1.b = t2.b) AND t2.b = 4
+GROUP BY 1;
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test
index 13367cbdcaa..9098ca466b7 100644
--- a/mysql-test/t/key_cache.test
+++ b/mysql-test/t/key_cache.test
@@ -254,6 +254,22 @@ DROP TABLE t1;
 
 # End of 4.1 tests
 
+--echo #
+--echo # Bug#12361113: crash when load index into cache
+--echo #
+
+--echo # Note that this creates an empty disabled key cache!
+SET GLOBAL key_cache_none.key_cache_block_size = 1024;
+CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) ) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1, 1);
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 in key_cache_none;
+--echo # The bug crashed the server at LOAD INDEX below. Now it will succeed 
+--echo # since the default cache is used due to CACHE INDEX failed for
+--echo # key_cache_none.
+LOAD INDEX INTO CACHE t1;
+DROP TABLE t1;
+
 # End of 5.1 tests
 
 #
diff --git a/mysql-test/t/myisam_icp.test b/mysql-test/t/myisam_icp.test
index 0e306a850c5..39a22d00a1a 100644
--- a/mysql-test/t/myisam_icp.test
+++ b/mysql-test/t/myisam_icp.test
@@ -262,4 +262,133 @@ SET optimizer_switch=@save_optimizer_switch;
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # BUG#933412: Server crashes in _mi_put_key_in_record on KILL QUERY with ICP, STRAIGHT_JOIN 
+--echo #
+CREATE TABLE t1 (
+  b INT,
+  c VARCHAR(1) NOT NULL,
+  d DATETIME,
+  KEY (c, b)
+) ENGINE=MyISAM;
+--echo # INSERT some data
+--disable_query_log
+INSERT INTO t1 ( b, d, c ) VALUES
+(4,'2005-01-08 00:00:00','f'),
+(1,'2004-05-20 10:45:51','c'),(2,'2006-08-11 21:33:49','e'),
+(5,'2003-05-19 00:20:40','a'),(3,'2005-01-03 06:18:39','a'),
+(7,'2008-11-25 18:18:14','b'),(6,'2008-11-09 15:53:46','b'),
+(9,'2003-03-01 03:40:36','c'),(8,'2003-09-25 23:14:09','d'),
+(0,'2007-01-17 09:18:31','f'),(9,'2008-09-08 09:52:24','c'),
+(2,'2008-03-10 00:00:00','a'),(0,'2003-03-14 09:31:07','c'),
+(4,'2005-04-25 00:00:00','h'),(6,'2001-08-01 05:55:55','e'),
+(3,'2005-04-09 01:22:48','f'),(7,'2009-11-12 13:27:22','r'),
+(0,'2009-03-28 05:05:28','h'),(15,'2005-05-16 04:35:41','f'),
+(7,'2006-03-26 05:19:58','c'),(9,'2002-10-06 02:17:00','g'),
+(4,'2007-01-28 03:28:20','b'),(1,'2009-04-22 10:16:40','c'),
+(2,'2003-01-01 19:39:00','f'),(0,'2008-05-03 19:16:29','t'),
+(2,'2005-01-28 00:00:00','j'),(8,'2004-01-10 00:00:00','w'),
+(8,'2000-06-13 21:56:37','a'),(5,'2001-03-21 19:24:49','o'),
+(99,'2003-12-20 21:29:06','f'),(0,'1900-01-01 00:00:00','w'),
+(7,'2000-12-19 00:00:00','c'),(0,'2000-03-03 06:10:19','l'),
+(3,'2000-08-11 00:00:00','q'),(0,'2007-05-25 03:46:41','e'),
+(241,'2005-05-17 00:00:00','j'),(4,'2005-11-02 00:44:06','r'),
+(43,'2001-07-11 00:00:00','a'),(1,'2008-12-01 18:30:27','z'),
+(4,'2004-10-25 00:00:00','i'),(5,'2000-04-08 12:12:01','c'),
+(0,'1900-01-01 00:00:00','f'),(9,'2002-05-13 22:47:02','p'),
+(1,'2008-10-09 15:39:40','d'),(3,'2004-06-24 00:00:00','d'),
+(0,'2008-03-06 00:00:00','r'),(9,'2007-04-16 18:40:03','i'),
+(3,'2008-03-16 19:49:37','t'),(7,'2003-07-15 08:11:21','d'),
+(8,'2005-02-11 00:04:53','r'),(0,'2002-09-21 00:00:00','y'),
+(3,'2004-11-03 00:37:21','z'),(6,'2007-10-18 00:00:00','e'),
+(6,'2007-01-21 10:42:56','o'),(5,'2000-03-26 21:21:04','b'),
+(9,'2001-03-15 08:08:21','e'),(1,'2001-10-16 12:56:59','a'),
+(6,'2004-05-01 23:45:55','o'),(4,'2000-03-04 00:00:00','f'),
+(9,'2002-12-03 16:48:28','e'),(8,'2003-01-09 00:36:07','m'),
+(1,'2006-06-22 04:32:41','s'),(8,'2008-09-20 05:01:48','q'),
+(4,'2006-06-02 22:15:31','g'),(2,'2002-05-14 07:07:42','e'),
+(7,'2005-06-05 01:30:42','r'),(127,'2004-05-11 01:56:48','a'),
+(210,'2003-11-05 00:41:34','z'),(5,'1900-01-01 00:00:00','h'),
+(1,'2006-04-16 00:00:00','f'),(7,'2000-12-17 00:00:00','x'),
+(8,'2009-05-09 20:43:07','b'),(175,'2008-11-26 16:33:09','p'),
+(0,'2002-05-09 21:18:44','v'),(8,'2002-06-01 11:32:25','k'),
+(1,'2008-11-09 23:56:00','a'),(0,'2008-01-08 10:18:46','c'),
+(2,'2005-04-16 00:00:00','o'),(5,'2002-08-25 00:00:00','b'),
+(64,'2005-12-05 21:51:52','b'),(4,'2005-08-10 00:00:00','i'),
+(6,'2006-03-23 00:00:00','d'),(9,'2007-01-27 00:00:00','i'),
+(8,'2008-08-16 00:00:00','a'),(7,'2003-01-16 12:13:18','k'),
+(0,'2003-06-22 00:00:00','v'),(5,'2008-06-20 05:43:56','u'),
+(8,'2004-09-23 18:57:17','e'),(1,'2000-12-26 00:00:00','y'),
+(4,'2009-06-01 13:00:28','e'),(1,'2009-11-18 06:28:48','m'),
+(0,'2004-06-12 10:01:10','e'),(2,'2005-10-16 01:48:55','e'),
+(5,'2001-12-23 09:50:21','l'),(6,'1900-01-01 00:00:00','a'),
+(1,'2001-10-28 00:00:00','d'),(1,'2008-07-12 23:30:19','s'),
+(0,'2002-10-11 16:51:16','r'),(4,'2007-09-18 06:27:10','x'),
+(1,'2007-02-21 12:28:14','e'),(6,'2001-09-16 00:00:00','f'),
+(0,'2007-09-20 02:25:45','c'),(0,'2006-08-07 03:25:56','j'),
+(8,'2006-12-04 20:20:32','t'),(7,'2007-09-05 10:13:10','i'),
+(9,'2006-04-12 17:59:57','t'),(2,'2009-04-28 00:06:09','b'),
+(8,'2000-01-07 00:00:00','b'),(7,'2000-03-25 10:04:41','k'),
+(4,'2000-07-10 00:44:55','w'),(9,'2007-09-22 14:26:26','j'),
+(9,'2003-09-11 22:41:17','a'),(0,'2004-06-07 13:52:32','c'),
+(8,'2008-10-09 00:00:00','p'),(1,'2007-04-01 00:00:00','c'),
+(9,'2000-12-05 00:00:00','i'),(3,'1900-01-01 00:00:00','a'),
+(3,'2005-12-24 21:50:54','e'),(8,'2009-07-21 19:34:55','n'),
+(9,'2005-11-13 17:57:56','d'),(7,'2004-10-07 06:41:39','l'),
+(1,'2004-11-20 08:05:08','u'),(3,'2005-05-25 00:00:00','r'),
+(1,'2006-09-02 14:16:41','u'),(8,'2006-01-07 00:00:00','a'),
+(9,'2003-04-05 00:54:20','w'),(2,'2003-12-22 00:00:00','a'),
+(9,'2006-04-16 17:31:40','e'),(6,'2005-02-10 14:22:46','e'),
+(7,'2004-04-27 05:54:52','p'),(1,'2005-12-07 00:00:00','t'),
+(5,'2004-04-03 20:56:28','d'),(4,'2000-09-07 05:17:16','h'),
+(2,'2004-08-04 16:10:42','i'),(1,'2007-03-04 00:00:00','b'),
+(9,'1900-01-01 00:00:00','d'),(1,'2000-05-12 23:02:50','m'),
+(2,'1900-01-01 00:00:00','l'),(1,'1900-01-01 00:00:00','k'),
+(4,'2000-07-14 01:25:18','d'),(5,'2009-08-21 00:00:00','w'),
+(6,'2009-05-25 13:33:54','f'),(7,'2006-06-13 00:00:00','e'),
+(8,'1900-01-01 00:00:00','a'),(6,'2004-02-24 00:00:00','j'),
+(0,'2003-05-21 07:03:46','k'),(9,'1900-01-01 00:00:00','e'),
+(2,'1900-01-01 00:00:00','y'),(2,'2000-12-22 00:00:00','e'),
+(3,'2003-09-26 00:00:00','f'),(2,'2001-01-13 08:20:19','h'),
+(9,'2008-09-23 20:03:28','n'),(5,'2007-03-20 02:41:38','s'),
+(1,'2009-02-14 10:27:18','a'),(0,'2001-08-10 17:44:05','s'),
+(3,'2008-01-20 12:49:54','v'),(1,'2001-05-05 09:09:59','r');
+--enable_query_log
+CREATE TABLE t2 ( a INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+  (7),(3),(7),(3);
+
+# 'con2' will be the connection that will run all the KILLable statements
+--connect (con2,127.0.0.1,root,,test)
+
+--let $run = 300
+--let $con_id = `SELECT CONNECTION_ID()`
+
+--echo # Now run a number of ICP queries while trying to kill them
+--disable_query_log
+--disable_result_log
+while ($run)
+{
+  --send
+    SELECT * FROM t1 AS alias1 STRAIGHT_JOIN t1 AS alias2
+        ON alias2.c = alias1.c
+        WHERE alias2.b >= 9;
+
+  --connect (con1,127.0.0.1,root,,test)
+  --eval KILL QUERY $con_id
+  --disconnect con1
+
+  --dec $run
+
+  --connection con2
+  --error 0,ER_QUERY_INTERRUPTED
+  --reap
+}
+--enable_result_log
+--enable_query_log
+--disconnect con2
+--connection default
+DROP TABLE t1,t2;
+
 set optimizer_switch=@myisam_icp_tmp;
+
diff --git a/mysql-test/t/myisam_mrr.test b/mysql-test/t/myisam_mrr.test
index 1e070ec9a34..28385c61b0f 100644
--- a/mysql-test/t/myisam_mrr.test
+++ b/mysql-test/t/myisam_mrr.test
@@ -268,5 +268,58 @@ set @@join_cache_level= @tmp_730133_jcl;
 set @@optimizer_switch= @tmp_730133_os;
 drop table t1;
 
+--echo #
+--echo # Test of MRR handler counters
+--echo #
+flush status;
+show status like 'Handler_mrr%';
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, filler char(200), key(a));
+insert into t1 
+select A.a+10*B.a+100*C.a+1000*D.a, 123,'filler' from t0 A, t0 B, t0 C, t0 D;
+
+explain select sum(b) from t1 where a < 10;
+--echo # This should show one MRR scan and no re-fills:
+flush status;
+select sum(b) from t1 where a < 10;
+show status like 'handler_mrr%';
+
+set @mrr_buffer_size_save= @@mrr_buffer_size;
+--disable_warnings
+set mrr_buffer_size=128;
+--enable_warnings
+
+explain select sum(b) from t1 where a < 1600;
+--echo # This should show one MRR scan and one extra rowid sort:
+flush status;
+select sum(b) from t1 where a < 1600;
+show status like 'handler_mrr%';
+set @@mrr_buffer_size= @mrr_buffer_size_save;
+
+--echo #Now, let's check BKA: 
+set @join_cache_level_save= @@join_cache_level;
+set @join_buffer_size_save= @@join_buffer_size;
+set join_cache_level=6;
+
+explain select sum(t1.b) from t0,t1 where t0.a=t1.a;
+flush status;
+select sum(t1.b) from t0,t1 where t0.a=t1.a;
+show status like 'handler_mrr%';
+
+--disable_warnings
+set join_buffer_size=10;
+--enable_warnings
+explain select sum(t1.b) from t0,t1 where t0.a=t1.a;
+flush status;
+select sum(t1.b) from t0,t1 where t0.a=t1.a;
+--replace_result 1 1or2 2 1or2
+show status like 'handler_mrr%';
+
+set join_cache_level= @join_cache_level_save;
+set join_buffer_size= @join_buffer_size_save;
+
+drop table t0, t1;
+
 ## This must be last line in the file:
 set optimizer_switch= @myisam_mrr_tmp;
diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test
index ef88b89e8d8..c0025f9e742 100644
--- a/mysql-test/t/mysqlcheck.test
+++ b/mysql-test/t/mysqlcheck.test
@@ -147,6 +147,7 @@ SET NAMES DEFAULT;
 call mtr.add_suppression("@003f.frm' \\(errno: 22\\)");
 --echo mysqlcheck --default-character-set="latin1" --databases test
 # Error returned depends on platform, replace it with "Table doesn't exist"
+call mtr.add_suppression("Can't find file: '..test.@003f.frm'");
 --replace_result "Can't find file: './test/@003f.frm' (errno: 22)" "Table doesn't exist" "Table 'test.?' doesn't exist" "Table doesn't exist"
 --exec $MYSQL_CHECK --default-character-set="latin1" --databases test
 --echo mysqlcheck --default-character-set="utf8" --databases test
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 302d79b6b13..424dd35a819 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -2249,8 +2249,12 @@ INSERT INTO b12809202_db.t2 VALUES (1), (2), (3);
 --echo #        commit starting 5.5.
 --echo
 --echo #### Dump starts here ####
+# We only need to check the --verbose output to verify that "start transaction"
+# happens after "logs flushed". We redirect normal output, as otherwise the
+# mixing of normal (stdout) and --verbose (stderr) output will happen in random
+# order depending on stdio internal buffer size.
 --replace_regex /-- Server version.*// /-- MySQL dump .*// /-- Dump completed on .*/-- Dump completed/
---exec $MYSQL_DUMP --verbose --single-transaction --flush-log b12809202_db 2>&1
+--exec $MYSQL_DUMP --verbose --single-transaction --flush-log b12809202_db 2>&1 > $MYSQLTEST_VARDIR/tmp/bug61854.sql
 --echo
 --echo #### Dump ends here ####
 
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 82116558717..a6f50107cbe 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -1562,7 +1562,6 @@ select 1 order by max(1) + min(1);
 
 --echo End of 5.1 tests
 
-
 --echo #
 --echo # Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY
 --echo #             when it should use index
@@ -1585,3 +1584,32 @@ SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2
   ORDER BY t1.i1 LIMIT 5;
 
 DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # Fix of LP BUG#793589 Wrong result with double ORDER BY
+--echo #
+CREATE TABLE t1 ( b int) ;
+INSERT INTO t1 VALUES (8),(9);
+
+CREATE TABLE t2 ( a int, b int, PRIMARY KEY (a)) ;
+INSERT INTO t2 VALUES (6,7),(7,7),(8,1),(9,7),(10,1),(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5);
+
+SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1;
+SELECT t2.b, t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b, t2.b;
+SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
+SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
+
+--echo # field1 removed from ORDER BY
+explain extended
+SELECT t2.b AS field1 FROM t1, t2 WHERE t1.b = t2.a GROUP BY field1 ORDER BY t1.b, field1;
+explain extended
+SELECT t2.b, t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b, t2.b;
+explain extended
+SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
+explain extended
+SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
+
+
+drop table t1,t2;
+
+--echo End of 5.2 tests
diff --git a/mysql-test/t/partition_key_cache.test b/mysql-test/t/partition_key_cache.test
index 4beb9506b05..067eb7fae59 100644
--- a/mysql-test/t/partition_key_cache.test
+++ b/mysql-test/t/partition_key_cache.test
@@ -239,6 +239,34 @@ CACHE INDEX t2 INDEX (`inx_b`) IN hot_cache;
 CACHE INDEX t1 PARTITION (p0) KEY (`inx_b`) IN hot_cache;
 CACHE INDEX t1 INDEX (`inx_b`) IN hot_cache;
 DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug#12361113: crash when load index into cache
+--echo #
+--echo # Note that this creates an empty disabled key cache!
+SET GLOBAL key_cache_none.key_cache_block_size = 1024;
+CREATE TABLE t1 (a INT, b INTEGER NOT NULL, KEY (b) )
+ENGINE = MYISAM
+PARTITION BY HASH(a) PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 1);
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 IN key_cache_none;
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 PARTITION (p0) IN key_cache_none;
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 PARTITION (p1) IN key_cache_none;
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 PARTITION (p0) KEY (`b`) IN key_cache_none;
+--error ER_UNKNOWN_KEY_CACHE
+CACHE INDEX t1 PARTITION (p1) KEY (`b`) IN key_cache_none;
+--echo # The bug crashed the server at LOAD INDEX below. Now it will succeed
+--echo # since the default cache is used due to CACHE INDEX failed for
+--echo # key_cache_none.
+LOAD INDEX INTO CACHE t1;
+DROP TABLE t1;
+
+
+--echo # Clean up
 SET GLOBAL hot_cache.key_buffer_size = 0;
 SET GLOBAL warm_cache.key_buffer_size = 0;
 SET @@global.cold_cache.key_buffer_size = 0;
diff --git a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
index da41b8e9428..1c8a4d254a8 100644
--- a/mysql-test/t/partition_pruning.test
+++ b/mysql-test/t/partition_pruning.test
@@ -1106,12 +1106,14 @@ show status like 'Handler_read_key';
 flush status;
 delete from t2 where b > 5;
 show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_rnd_deleted';
 show status like 'Handler_read_key';
 show status like 'Handler_read_prev';
 show status like 'Handler_read_next';
 flush status;
 delete from t2 where b < 5 or b > 3;
 show status like 'Handler_read_rnd_next';
+show status like 'Handler_read_rnd_deleted';
 show status like 'Handler_read_key';
 show status like 'Handler_read_prev';
 show status like 'Handler_read_next';
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 746d6bad896..e4e26da7031 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1406,6 +1406,20 @@ DROP TABLE t1;
 
 --echo End of 5.1 tests
 
+--echo #
+--echo # LP Bug #533117: Wrong use_count in SEL_ARG trees
+--echo #   (Bug #58731)
+--echo #
+
+create table t1 (a int, b int, c int, key idx (a,b,c));
+insert into t1 values (0,0,0), (2,2,0), (1,1,1), (2,2,1);
+
+explain
+select * from t1 force index (idx) where a >=1 and c <= 1 and a=b and b > 1;
+select * from t1 force index (idx) where a >=1 and c <= 1 and a=b and b > 1;
+
+drop table t1;
+
 #
 # lp:750117 Bogus warning with aggregate and datetime column
 #
@@ -1459,4 +1473,3 @@ SELECT * FROM t1 ignore index(d) WHERE d = 'q' OR d >= 'q' OR (d IN ( 'j' , 's'
 SELECT * FROM t1 force index(d)  WHERE d = 'q' OR d >= 'q' OR (d IN ( 'j' , 's' , 'i' ) AND ( b = 102 ));
 
 DROP TABLE t1;
-
diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test
index 76769e78d90..9395323d32b 100644
--- a/mysql-test/t/status.test
+++ b/mysql-test/t/status.test
@@ -369,6 +369,16 @@ show status like 'Handler%';
 show status like '%tmp%';
 drop table t1;
 
+#
+# Test of handler status counts
+#
+CREATE TABLE t1 (i int(11) DEFAULT NULL, KEY i (i) ) ENGINE=MyISAM;
+insert into t1 values (1),(2),(3),(4),(5);
+flush status;
+select * from t1 where i=5 union select * from t1 where i=5;
+show status like "handler%";
+drop table t1;
+
 # End of 5.3 tests
 
 # Restore global concurrent_insert value. Keep in the end of the test file.
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index cb43eb9f646..40ecfead133 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -3519,6 +3519,8 @@ SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
 
 ## First a simpler query, illustrating the transformation
 ## '1 < some (...)' => '1 < max(...)'
+EXPLAIN EXTENDED
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 
 ## The query which made the server crash.
@@ -4431,8 +4433,6 @@ WHERE t1.a = d1.a;
 DROP TABLE t1;
 
 
---echo End of 5.1 tests.
-
 #
 # Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index
 #
@@ -4879,11 +4879,141 @@ INSERT INTO t3 VALUES (0),(0);
 SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ;
 DROP TABLE t1, t2, t3;
 
+--echo #
+--echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+--echo #
+
+CREATE TABLE t1(a1 int);
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2(a1 int);
+INSERT INTO t2 VALUES (3);
+
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+
+## All these are subject to the transformation
+## '1 < some (...)' => '1 < max(...)'
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+
+SET SESSION sql_mode=@old_sql_mode;
+
+DROP TABLE t1, t2;
+
+create table t2(i int);
+insert into t2 values(0);
+
+SELECT @@session.sql_mode INTO @old_sql_mode;
+SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+
+CREATE VIEW v1 AS  
+SELECT 'f' FROM t2 UNION SELECT 'x' FROM t2
+;
+
+CREATE TABLE t1 (
+  pk int NOT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY col_varchar_key (col_varchar_key)
+);
+
+SELECT t1.pk
+FROM t1
+WHERE t1.col_varchar_key < ALL ( SELECT * FROM v1 )
+;
+
+SET SESSION sql_mode=@old_sql_mode;
+
+drop table t2, t1;
+drop view v1;
+
+--echo #
+--echo # BUG#50257: Missing info in REF column of the EXPLAIN 
+--echo #            lines for subselects
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, INDEX (a));
+INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
+
+EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
+EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
+
+DROP TABLE t1;
+
+--echo #
+--echo # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+--echo # (duplicate of LP bug #888456)
+--echo #
+
+CREATE TABLE t1 (f1 varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+
+CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
+INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),
+('d'),('y'),('t'),('d'),('s');
+
+EXPLAIN
+SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
+  WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 
+                  WHERE f1_key != table2.f1_key AND f1_key >= table1.f1);
+SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
+  WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 
+                  WHERE f1_key != table2.f1_key AND f1_key >= table1.f1);
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # LP bug 919427: EXPLAIN for a query over a single-row table 
+--echo #                with IN subquery in WHERE condition
+--echo # 
+
+CREATE TABLE ot (
+  col_int_nokey int(11), 
+  col_varchar_nokey varchar(1)
+) ;
+INSERT INTO ot VALUES (1,'x');
+
+CREATE TABLE it1(
+  col_int_key int(11), 
+  col_varchar_key varchar(1), 
+  KEY idx_cvk_cik (col_varchar_key,col_int_key)
+);
+INSERT INTO it1 VALUES (NULL,'x'), (NULL,'f');
+
+CREATE TABLE it2 (
+   col_int_key int(11),
+   col_varchar_key varchar(1),
+   col_varchar_key2 varchar(1),
+   KEY idx_cvk_cvk2_cik (col_varchar_key, col_varchar_key2, col_int_key),
+   KEY idx_cvk_cik (col_varchar_key, col_int_key)
+);
+INSERT INTO it2 VALUES (NULL,'x','x'), (NULL,'f','f');
+
+EXPLAIN
+SELECT col_int_nokey FROM ot 
+  WHERE col_varchar_nokey IN 
+        (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+SELECT col_int_nokey FROM ot 
+  WHERE col_varchar_nokey IN 
+        (SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+
+EXPLAIN
+SELECT col_int_nokey FROM ot
+  WHERE (col_varchar_nokey, 'x') IN
+        (SELECT col_varchar_key, col_varchar_key2 FROM it2);
+SELECT col_int_nokey FROM ot
+  WHERE (col_varchar_nokey, 'x') IN
+        (SELECT col_varchar_key, col_varchar_key2 FROM it2);
+
+DROP TABLE ot,it1,it2;
+
 --echo End of 5.2 tests
 
 --echo #
 --echo # BUG#779885: Crash in eliminate_item_equal with materialization=on in
---echo # maria-5.3
 --echo #
 
 CREATE TABLE t1 ( f1 int );
@@ -4936,32 +5066,6 @@ SET optimizer_switch=@save_optimizer_switch;
 
 drop table t1, t2, t3;
 
---echo End of 5.3 tests
-
---echo #
---echo # Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
---echo #
-
-CREATE TABLE t1(a1 int);
-INSERT INTO t1 VALUES (1),(2);
-
-CREATE TABLE t2(a1 int);
-INSERT INTO t2 VALUES (3);
-
-SELECT @@session.sql_mode INTO @old_sql_mode;
-SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
-
-## All these are subject to the transformation
-## '1 < some (...)' => '1 < max(...)'
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
-
-SET SESSION sql_mode=@old_sql_mode;
-
-DROP TABLE t1, t2;
-
 --echo #
 --echo # BUG#50257: Missing info in REF column of the EXPLAIN 
 --echo #            lines for subselects
@@ -4982,6 +5086,55 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 DROP TABLE t1;
 
 --echo #
+--echo # Bug#11764086: Null left operand to NOT IN in WHERE clause
+--echo # behaves differently than real NULL
+--echo #
+
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+
+--echo # Offending query (c.parent_id is NULL for null-complemented rows only)
+
+SELECT    p.id, c.parent_id
+FROM      parent p
+LEFT JOIN child  c
+ON        p.id = c.parent_id
+WHERE     c.parent_id NOT IN (
+              SELECT parent_id 
+              FROM   child
+              WHERE  parent_id = 3
+          );
+
+--echo # Some syntactic variations with IS FALSE and IS NOT TRUE
+
+SELECT    p.id, c.parent_id
+FROM      parent p
+LEFT JOIN child  c
+ON        p.id = c.parent_id
+WHERE     c.parent_id IN (
+              SELECT parent_id 
+              FROM   child
+              WHERE  parent_id = 3
+          ) IS NOT TRUE;
+
+SELECT    p.id, c.parent_id
+FROM      parent p
+LEFT JOIN child  c
+ON        p.id = c.parent_id
+WHERE     c.parent_id IN (
+              SELECT parent_id 
+              FROM   child
+              WHERE  parent_id = 3
+          ) IS FALSE;
+
+DROP TABLE parent, child;
+
+--echo # End of test for bug#11764086.
+
+--echo #
 --echo # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
 --echo #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
 --echo #
@@ -5279,6 +5432,17 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1;
 
 drop table t1,t2,t3;
 
+--echo #
+--echo # LP BUG#905353 Wrong non-empty result with a constant table,
+--echo # aggregate function in subquery, MyISAM or Aria
+--echo #
+
+CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+
+SELECT a FROM t1 WHERE ( SELECT MIN(a) = 100 );
+
+drop table t1;
 
 --echo # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index f34cf5ba338..6b8a757b9e8 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -1136,6 +1136,262 @@ WHERE (int_nokey, pk)
 
 DROP TABLE t1, t2, t3;
 
+--echo # 
+--echo # BUG#53060: LooseScan semijoin strategy does not return all rows
+--echo # 
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=on,materialization=off';
+set optimizer_switch='firstmatch=off,loosescan=on';
+
+CREATE TABLE t1 (i INTEGER);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE TABLE t2 (i INTEGER, j INTEGER, KEY k(i, j));
+INSERT INTO t2 VALUES (1, 0), (1, 1), (2, 0), (2, 1);
+
+EXPLAIN
+SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0);
+SELECT * FROM t1 WHERE (i) IN (SELECT i FROM t2 where j > 0);
+
+DROP TABLE t1, t2;
+
+set optimizer_switch=@save_optimizer_switch;
+
+--echo #
+--echo # BUG#49453: re-execution of prepared statement with view 
+--echo #            and semijoin crashes
+--echo #
+
+CREATE TABLE t1 (city VARCHAR(50), country_id INT);
+CREATE TABLE t2 (country_id INT, country VARCHAR(50));
+
+INSERT INTO t1 VALUES 
+  ('Batna',2),('Bchar',2),('Skikda',2),('Tafuna',3),('Algeria',2) ;
+INSERT INTO t2 VALUES (2,'Algeria'),(2,'AlgeriaDup'),(3,'XAmerican Samoa');
+
+CREATE VIEW v1 AS 
+  SELECT country_id as vf_country_id
+  FROM t2
+  WHERE LEFT(country,1) = "A"; 
+
+PREPARE stmt FROM "
+SELECT city, country_id
+FROM t1
+WHERE country_id IN (SELECT vf_country_id FROM v1);
+";
+
+--echo
+EXECUTE stmt;
+EXECUTE stmt;
+
+DROP TABLE t1,t2;
+DROP VIEW v1;
+
+--echo #
+--echo # Bug#54437 Extra rows with LEFT JOIN + semijoin 
+--echo #
+
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1),(1);
+insert into t2 values(1),(1),(1),(1);
+insert into t3 values(2),(2);
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+set optimizer_switch='materialization=off';
+
+set optimizer_switch='semijoin=off';
+explain
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+
+set optimizer_switch='semijoin=on';
+explain
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+
+set optimizer_switch=@save_optimizer_switch;
+
+drop table t1,t2,t3;
+
+--echo #
+--echo # Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+--echo #
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+set optimizer_switch='semijoin=off,materialization=off';
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+               FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+               FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+
+set optimizer_switch='semijoin=off,materialization=on';
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+               FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+               FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+
+set optimizer_switch='semijoin=on,materialization=off';
+EXPLAIN
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+               FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+               FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+
+set optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # BUG#52329 - Wrong result: subquery materialization, IN, 
+--echo #             non-null field followed by nullable
+--echo #
+
+CREATE TABLE t1 (a1 CHAR(8) NOT NULL, a2 char(8) NOT NULL);
+
+CREATE TABLE t2a (b1 char(8), b2 char(8));
+CREATE TABLE t2b (b1 CHAR(8), b2 char(8) NOT NULL);
+CREATE TABLE t2c (b1 CHAR(8) NOT NULL, b2 char(8));
+
+INSERT INTO t1 VALUES ('1 - 12', '2 - 22');
+
+INSERT INTO t2a VALUES ('1 - 11', '2 - 21'),
+                       ('1 - 11', '2 - 21'),
+                       ('1 - 12', '2 - 22'),
+                       ('1 - 12', '2 - 22'),
+                       ('1 - 13', '2 - 23');
+
+INSERT INTO t2b SELECT * FROM t2a;
+INSERT INTO t2c SELECT * FROM t2a;
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+set optimizer_switch='semijoin=off,materialization=on';
+
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+      SELECT b1, b2 FROM t2c WHERE b1 > '0' GROUP BY b1, b2);
+
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+      SELECT b1, b2 FROM t2a WHERE b1 > '0');
+
+
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+      SELECT b1, b2 FROM t2b WHERE b1 > '0');
+
+
+SELECT * FROM t1
+WHERE (a1, a2) IN (
+      SELECT b1, b2 FROM t2c WHERE b1 > '0');
+
+set optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE t1,t2a,t2b,t2c;
+
+--echo #
+--echo # Bug#57623: subquery within before insert trigger causes crash (sj=on)
+--echo #
+
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+
+let $query=
+SELECT *
+FROM   ot1
+     LEFT JOIN
+       (ot2 JOIN ot3 on ot2.a=ot3.a)
+     ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+set optimizer_switch='semijoin=on';
+
+set optimizer_switch='materialization=off';
+eval explain $query;
+eval $query;
+eval prepare s from '$query';
+execute s;
+execute s;
+deallocate prepare s;
+
+set optimizer_switch='materialization=on';
+eval explain $query;
+eval $query;
+eval prepare s from '$query';
+execute s;
+execute s;
+deallocate prepare s;
+
+set optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE ot1, ot2, ot3, it1;
+
+--echo #
+--echo # Bug#59919/11766739: Crash in tmp_table_param::init() with semijoin=on
+--echo #
+
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t3 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+INSERT INTO t3 VALUES 
+  (1,1), (2,1), (5,4), (7,3), (8,2), (8,1), (7,3),
+  (9,5), (4,3), (7,2), (7,7), (3,1), (5,8), (9,7);
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+set optimizer_switch='semijoin=off,materialization=on';
+EXPLAIN
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+             FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+             FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+
+set optimizer_switch='semijoin=on,materialization=on';
+EXPLAIN
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+             FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+             FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+
+set optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE t1, t2, t3 ;
+
+--echo #
 --echo #
 --echo # BUG#784723: Wrong result with semijoin + nested subqueries in maria-5.3  
 --echo #
@@ -2017,5 +2273,63 @@ SELECT a, COUNT(*) FROM t1
 
 DROP TABLE t1, t2, t3;
 
+--echo #
+--echo # BUG#920255: Wrong result (extra rows) with loosescan and IN subquery
+--echo #
+CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
+INSERT INTO t1 VALUES
+  (1,2),(2,1),(3,3),(4,2),(5,5),
+  (6,3),(7,1),(8,4),(9,3),(10,2);
+
+CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+INSERT INTO t2 VALUES
+  (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+  ( SELECT b, d FROM t1, t2 WHERE b = c );
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
+--echo #
+# t1 should be MyISAM or InnoDB
+CREATE TABLE t1 ( a VARCHAR(1) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('e'),('w'),('a'),('h'),('x'),('k'),('g');
+CREATE TABLE t2 ( b INT, c VARCHAR(1) );
+INSERT INTO t2 VALUES (0,'j'),(8,'v');
+
+#SET debug_optimizer_prefer_join_prefix= 'alias2,alias4,alias1,alias3';
+
+SELECT * FROM t1 alias1, t2 alias2
+WHERE alias2.c IN (
+  SELECT alias4.c FROM t1 alias3, t2 alias4
+);
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # BUG#923246: Loosescan reports different result than other semijoin methods
+--echo #
+set @tmp_923246= @@optimizer_switch;
+set optimizer_switch='mrr=on,materialization=off';
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2));
+insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C;
+insert into t1 select * from t1 where kp1 < 20;
+
+create table t3 (a int);
+insert into t3 select A.a + 10*B.a from t0 A, t0 B;
+
+select * from t3 where a in (select kp1 from t1 where kp1<20);
+explain select * from t3 where a in (select kp1 from t1 where kp1<20);
+
+drop table t0,t1,t3;
+set optimizer_switch= @tmp_923246;
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index a77a70bbe4e..ede631f32be 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -1079,5 +1079,19 @@ SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # BUG#912538: Wrong result (missing rows) with semijoin=on, firstmatch=on, ...
+--echo #
+CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) );
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+
+# t2 needs to be InnoDB
+CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1);
+
+SELECT * FROM t1, t2 WHERE c IN (SELECT c FROM t1, t2 WHERE a = b);
+
+DROP TABLE t1,t2;
+
 --echo # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test
index 4eeaa465b11..e86ed017951 100644
--- a/mysql-test/t/subselect_sj_jcl6.test
+++ b/mysql-test/t/subselect_sj_jcl6.test
@@ -88,6 +88,124 @@ set join_cache_level=default;
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # BUG#912513: Wrong result (missing rows) with join_cache_hashed+materialization+semijoin=on
+--echo #
+set @os_912513= @@optimizer_switch;
+set @jcl_912513= @@join_cache_level;
+SET optimizer_switch = 'semijoin=on,materialization=on,join_cache_hashed=on';
+SET join_cache_level = 3;
+
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES
+  (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
+
+CREATE TABLE t2 ( c INT );
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7);
+
+SELECT alias1.* FROM
+    t1 AS alias1 INNER JOIN t1 AS alias2
+      ON alias2.a = alias1.b
+WHERE alias1.b IN (
+        SELECT a FROM t1, t2
+    );
+
+DROP table t1, t2;
+set @@optimizer_switch= @os_912513;
+set @@join_cache_level= @jcl_912513;
+
+--echo # End
+
+--echo #
+--echo # BUG#934342: outer join + semijoin materialization 
+--echo #             + join_cache_level > 2
+--echo #
+
+CREATE TABLE t1 (a varchar(1), b varchar(1), INDEX idx_a(a) );
+INSERT INTO t1 VALUES ('v','v'), ('w','w'), ('t','t');
+
+CREATE TABLE t2 (c varchar(1), INDEX idx_c(c) );
+INSERT INTO t2 VALUES ('v'), ('v'), ('s'), ('j');
+
+CREATE TABLE t3 (c varchar(1), d varchar(1), INDEX idx_c(c) );
+INSERT INTO t3 VALUES ('v','v'), ('v','v'), ('s','s'), ('j','j');
+INSERT INTO t3 VALUES ('m','m'), ('d','d'), ('k','k'), ('m','m');
+
+set @tmp_otimizer_switch= @@optimizer_switch;
+set @tmp_join_cache_level=@@join_cache_level;
+set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on';
+
+set join_cache_level=0;
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
+  WHERE (a, b) IN (SELECT a, b FROM t1 t);
+SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
+  WHERE (a, b) IN (SELECT a, b FROM t1 t);
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
+  WHERE (a, b) IN (SELECT a, b FROM t1 t);
+SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
+  WHERE (a, b) IN (SELECT a, b FROM t1 t);
+
+set join_cache_level=6;
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
+  WHERE (a, b) IN (SELECT a, b FROM t1 t);
+SELECT * FROM t1 LEFT JOIN t2 ON (c = b)
+  WHERE (a, b) IN (SELECT a, b FROM t1 t);
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
+  WHERE (a, b) IN (SELECT a, b FROM t1 t);
+SELECT * FROM t1 LEFT JOIN t3 ON (c = b)
+  WHERE (a, b) IN (SELECT a, b FROM t1 t);
+
+set optimizer_switch=@tmp_optimizer_switch;
+set join_cache_level=@tmp_join_cache_level;
+
+DROP TABLE t1,t2,t3;
+
+--echo # End
+
+--echo #
+--echo # BUG#934348: GROUP BY with HAVING + semijoin materialization 
+--echo #             + join_cache_level > 2
+--echo #
+
+CREATE TABLE t1 (a varchar(1), INDEX idx_a(a));
+INSERT INTO t1 VALUES ('c'), ('v'), ('c');
+
+CREATE TABLE t2 (b varchar(1));
+INSERT INTO t2 VALUES ('v'), ('c');
+
+set @tmp_otimizer_switch= @@optimizer_switch;
+set @tmp_join_cache_level=@@join_cache_level;
+set optimizer_switch = 'materialization=on,semijoin=on,join_cache_hashed=on';
+
+set join_cache_level=0;
+
+EXPLAIN
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+  GROUP BY a HAVING a != 'z';
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+  GROUP BY a HAVING a != 'z';
+
+set join_cache_level=6;
+
+EXPLAIN
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+  GROUP BY a HAVING a != 'z';
+SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
+  GROUP BY a HAVING a != 'z';
+
+set optimizer_switch=@tmp_optimizer_switch;
+set join_cache_level=@tmp_join_cache_level;
+
+DROP TABLE t1,t2;
+
 --echo # End
 
 set join_cache_level=default;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 2a5b0f56877..0d04b3f984a 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1510,6 +1510,55 @@ SELECT * FROM t1 LEFT JOIN t2 ON ( a = b )
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # BUG#922254: Assertion `0' failed at item_cmpfunc.cc:5899: Item* Item_equal::get_first(JOIN_TAB*, Item*)
+--echo #
+CREATE TABLE t1 ( a VARCHAR(3) );
+CREATE TABLE t2 ( b VARCHAR(3), c VARCHAR(8), KEY(c) );
+INSERT INTO t2 VALUES ('USA','Abilene'),('USA','Akron');
+
+EXPLAIN
+SELECT * FROM
+  ( SELECT * FROM t1 ) AS alias1,
+  t2 AS alias2
+WHERE b = a AND a IN (
+  SELECT alias3.c
+  FROM t2 AS alias3, t2 AS alias4
+  WHERE alias4.c = alias3.b
+);
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # BUG#928048: Query containing IN subquery with OR in the where clause returns a wrong result
+--echo #
+create table t1 (a int, b int);
+insert into t1 values (7,5), (3,3), (5,4), (9,3);
+
+create table t2 (a int, b int, index i_a(a));
+
+insert into t2 values
+  (4,2), (7,9), (7,4), (3,1), (5,3), (3,1), (9,4), (8,1);
+
+explain select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1);
+select * from t1 where t1.a in (select a from t2 where t2.a=7 or t2.b<=1);
+
+drop table t1,t2;
+
+--echo #
+--echo # BUG#933407: Valgrind warnings in mark_as_null_row with materialization+semijoin, STRAIGHT_JOIN, impossible WHERE
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(8);
+
+SELECT STRAIGHT_JOIN MIN(a) FROM t1
+WHERE a IN (
+    SELECT a FROM t1
+    WHERE 'condition'='impossible'
+  );
+
+DROP TABLE t1;
+
 --echo # 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/sum_distinct.test b/mysql-test/t/sum_distinct.test
index c58155a8e25..633a72fddc8 100644
--- a/mysql-test/t/sum_distinct.test
+++ b/mysql-test/t/sum_distinct.test
@@ -93,3 +93,15 @@ SELECT SUM(DISTINCT id) FROM t1;
 SELECT SUM(DISTINCT id % 11) FROM t1;
 
 DROP TABLE t1;
+
+--echo #
+--echo # Bug #777654: empty subselect in FROM clause returning  
+--echo #              SUM(DISTINCT) over non-nullable field 
+--echo #
+
+CREATE TABLE t1 (a int NOT NULL) ;
+
+SELECT SUM(DISTINCT a) FROM t1;
+SELECT * FROM (SELECT SUM(DISTINCT a) FROM t1) AS t;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test
index dc32618eb8c..26b98c9023b 100644
--- a/mysql-test/t/table_elim.test
+++ b/mysql-test/t/table_elim.test
@@ -521,4 +521,22 @@ EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
 drop view v1;
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # BUG#919878: Assertion `!eliminated_tables...
+--echo #
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2
+  ( b INT, UNIQUE INDEX(b) );
+INSERT INTO t2 VALUES (1),(2);
+
+EXPLAIN EXTENDED
+  SELECT * FROM t2
+  WHERE b IN (
+    SELECT SUM(a) FROM t1 LEFT JOIN t2 ON b=a
+  );
+
+DROP TABLE t1,t2;
+
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/variables-big.test b/mysql-test/t/variables-big.test
index 60d83e92c0a..58098f4c879 100644
--- a/mysql-test/t/variables-big.test
+++ b/mysql-test/t/variables-big.test
@@ -74,5 +74,3 @@ SET SESSION transaction_prealloc_size=1024*1024*1024*5;
 SHOW PROCESSLIST;
 --enable_warnings
 
-SET @@session.transaction_prealloc_size= @def_var;
-
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 5fd8da745e4..79c733ed791 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4018,6 +4018,59 @@ drop table t1,t2;
 --echo # -----------------------------------------------------------------
 --echo # -- End of 5.1 tests.
 --echo # -----------------------------------------------------------------
+--echo #
+--echo # Bug #794005: crash in st_table::mark_virtual_columns_for_write
+--echo #
+
+CREATE TABLE t1 (a int);
+insert into t1 values (1);
+CREATE TABLE t2 (a int);
+insert into t2 values (1);
+
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v1 AS SELECT * FROM v2;
+CREATE VIEW v3 AS SELECT t2.a,v1.a as b FROM t2,v1 where t2.a=v1.a;
+CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v2 AS SELECT * FROM t1;
+
+--error ER_NON_UPDATABLE_TABLE
+UPDATE v1 SET a = 10;
+--error ER_NON_INSERTABLE_TABLE
+REPLACE v1 SET a = 10;
+--error ER_NON_INSERTABLE_TABLE
+INSERT into v1 values (20);
+--error ER_NON_UPDATABLE_TABLE
+DELETE from v1;
+--error ER_NON_UPDATABLE_TABLE
+UPDATE v3 SET b= 10;
+--error ER_NON_INSERTABLE_TABLE
+REPLACE v3 SET b= 10;
+--error ER_NON_INSERTABLE_TABLE
+INSERT into v3(b) values (20);
+--error ER_VIEW_DELETE_MERGE_VIEW
+DELETE from v3 where b=20;
+--error ER_VIEW_DELETE_MERGE_VIEW
+DELETE from v3 where a=20;
+--error ER_NON_UPDATABLE_TABLE
+DELETE v1 from v1,t1 where v1.a=t1.a;
+UPDATE v3 SET a = 10;
+REPLACE v3 SET a = 11;
+INSERT INTO v3(a) values (20);
+
+select * from t1;
+select * from t2;
+
+CREATE OR REPLACE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM t2;
+DELETE from v1 where a=11;
+DELETE v1 from v1,t1 where v1.a=t1.a;
+select * from t1;
+select * from t2;
+
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2;
+
+--echo # -----------------------------------------------------------------
+--echo # -- End of 5.2 tests.
+--echo # -----------------------------------------------------------------
 
 --echo #
 --echo # Bug #59696 Optimizer does not use equalities for conditions over view 
@@ -4493,4 +4546,8 @@ SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM
 DROP VIEW v2;
 DROP TABLE t1, t2, t3;
 
+--echo # -----------------------------------------------------------------
+--echo # -- End of 5.3 tests.
+--echo # -----------------------------------------------------------------
+
 SET optimizer_switch=@save_optimizer_switch;