Merge

19 files changed, 594 insertions, 9 deletions

diff --git a/mysql-test/r/ctype_recoding.result b/mysql-test/r/ctype_recoding.result
index ee95812c03e..f096620daf0 100644
--- a/mysql-test/r/ctype_recoding.result
+++ b/mysql-test/r/ctype_recoding.result
@@ -267,3 +267,19 @@ drop table t1;
 create table t1(a char character set latin1 default _cp1251 0xFF);
 ERROR 42000: Invalid default value for 'a'
 End of 4.1 tests
+SET CHARACTER SET DEFAULT;
+#
+# LP BUG#944504 Item_func_conv_charset tries to execute subquery constant
+# 
+SET optimizer_switch = 'in_to_exists=on';
+SET character_set_connection = utf8;
+CREATE TABLE t1 ( a VARCHAR(1) );
+INSERT INTO t1 VALUES ('m'),('n');
+CREATE VIEW v1 AS SELECT 'w' ;
+SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v1 );
+ERROR HY000: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<='
+drop view v1;
+drop table t1;
+SET character_set_connection = default;
+SET optimizer_switch= default;
+#End of 5.3 tests
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 2f0e0bd371b..49e1422c3d4 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -420,7 +420,7 @@ join
 (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) z
 on x.f1 = z.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	<derived3>	ALL	key0	NULL	NULL	NULL	11	100.00	Using where
+1	SIMPLE	<derived3>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
 1	SIMPLE	<derived5>	ref	key0	key0	5	tt.f1	2	100.00	
 5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
@@ -478,7 +478,7 @@ join
 (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) z
 on x.f1 = z.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived2>	ALL	key0	NULL	NULL	NULL	11	100.00	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where
 1	PRIMARY	<derived4>	ref	key0	key0	5	x.f1	2	100.00	
 4	DERIVED	<derived5>	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
 5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	11	100.00	Using where; Using temporary; Using filesort
@@ -1585,7 +1585,7 @@ a
 EXPLAIN
 SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	key0	NULL	NULL	NULL	3	Using where; Using filesort
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
 1	PRIMARY	<derived3>	ref	key0	key0	5	v1.b	2	
 3	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
@@ -1929,5 +1929,37 @@ x
 y
 drop table t1,t2,t3;
 set SESSION optimizer_switch= @save_optimizer_switch;
+#
+# LP BUG#944782: derived table from an information schema table
+#
+SET @save_optimizer_switch=@@optimizer_switch;
+SET SESSION optimizer_switch='derived_merge=on';
+SET SESSION optimizer_switch='derived_with_keys=on';
+CREATE TABLE t1 (c1 int PRIMARY KEY, c2 char(5));
+EXPLAIN
+SELECT COUNT(*) > 0
+FROM INFORMATION_SCHEMA.COLUMNS
+INNER JOIN
+(SELECT TABLE_SCHEMA,
+GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES
+FROM INFORMATION_SCHEMA.STATISTICS
+GROUP BY TABLE_SCHEMA) AS UNIQUES
+ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	COLUMNS	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+2	DERIVED	STATISTICS	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases; Using filesort
+SELECT COUNT(*) > 0
+FROM INFORMATION_SCHEMA.COLUMNS
+INNER JOIN
+(SELECT TABLE_SCHEMA,
+GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES
+FROM INFORMATION_SCHEMA.STATISTICS
+GROUP BY TABLE_SCHEMA) AS UNIQUES
+ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA);
+COUNT(*) > 0
+1
+DROP TABLE t1;
+set SESSION optimizer_switch= @save_optimizer_switch;
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 9918ad2380d..f8db2c8cf72 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5151,7 +5151,7 @@ EXPLAIN
 SELECT * FROM (SELECT DISTINCT * FROM t1) t
 WHERE t.a  IN (SELECT t2.a FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	key0	NULL	NULL	NULL	3	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
 SELECT * FROM (SELECT DISTINCT * FROM t1) t
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 60defea291b..5624d9467af 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1656,4 +1656,88 @@ b	b	a	b
 DEALLOCATE PREPARE stmt;
 SET SESSION join_cache_level=default;
 DROP TABLE t1,t2,t3;
+#
+# LP bug #943543: LEFT JOIN converted to JOIN with 
+#                 ORed IS NULL(primary key) in WHERE clause                
+#
+CREATE TABLE t1 (
+a int, b int NOT NULL, pk int NOT NULL,
+PRIMARY KEY (pk), INDEX idx(b)
+);
+INSERT INTO t1 VALUES
+(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4),
+(1,9,6), (8,5,7), (NULL,8,8), (8,1,5);
+CREATE TABLE t2 (pk int PRIMARY KEY);
+INSERT INTO t2 VALUES (3), (8), (5);
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY,idx	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+DROP TABLE t2;
+CREATE TABLE t2 (c int, d int, KEY (c));
+INSERT INTO t2 VALUES
+(3,30), (8,88), (5,50), (8,81),
+(4,40), (9,90), (7,70), (9,90),
+(13,130), (18,188), (15,150), (18,181),
+(14,140), (19,190), (17,170), (19,190);
+INSERT INTO t1 VALUES (8,5,9);
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	idx	idx	4	const	2	100.00	Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	PRIMARY,idx	idx	4	const	2	100.00	Using where; Using filesort
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+DROP TABLE t1,t2;
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 767d0544f1b..d80b838e4e7 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1667,6 +1667,90 @@ b	b	a	b
 DEALLOCATE PREPARE stmt;
 SET SESSION join_cache_level=default;
 DROP TABLE t1,t2,t3;
+#
+# LP bug #943543: LEFT JOIN converted to JOIN with 
+#                 ORed IS NULL(primary key) in WHERE clause                
+#
+CREATE TABLE t1 (
+a int, b int NOT NULL, pk int NOT NULL,
+PRIMARY KEY (pk), INDEX idx(b)
+);
+INSERT INTO t1 VALUES
+(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4),
+(1,9,6), (8,5,7), (NULL,8,8), (8,1,5);
+CREATE TABLE t2 (pk int PRIMARY KEY);
+INSERT INTO t2 VALUES (3), (8), (5);
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY,idx	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+DROP TABLE t2;
+CREATE TABLE t2 (c int, d int, KEY (c));
+INSERT INTO t2 VALUES
+(3,30), (8,88), (5,50), (8,81),
+(4,40), (9,90), (7,70), (9,90),
+(13,130), (18,188), (15,150), (18,181),
+(14,140), (19,190), (17,170), (19,190);
+INSERT INTO t1 VALUES (8,5,9);
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	idx	idx	4	const	2	100.00	Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	PRIMARY,idx	idx	4	const	2	100.00	Using where; Using filesort
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+DROP TABLE t1,t2;
 SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
 show variables like 'join_cache_level';
diff --git a/mysql-test/r/myisam-big.result b/mysql-test/r/myisam-big.result
new file mode 100644
index 00000000000..95a6e91d766
--- /dev/null
+++ b/mysql-test/r/myisam-big.result
@@ -0,0 +1,40 @@
+drop table if exists t1,t2;
+create table t1 (id int, sometext varchar(100)) engine=myisam;
+insert into t1 values (1, "hello"),(2, "hello2"),(4, "hello3"),(4, "hello4");
+create table t2 like t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+select count(*) from t1;
+count(*)
+131072
+alter table t1 add index (id), add index(sometext), add index(sometext,id);
+alter table t1 disable keys;
+alter table t1 enable keys;
+drop table t1,t2;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 327cb5b2563..4eec1729fe3 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -5994,6 +5994,7 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1;
 1
 NULL
 drop table t1,t2,t3;
+set optimizer_switch=@subselect_tmp;
 #
 # LP BUG#905353 Wrong non-empty result with a constant table,
 # aggregate function in subquery, MyISAM or Aria
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 9a49f74ec42..9c928fd81f6 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1892,7 +1892,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	Using where
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
@@ -1900,11 +1900,12 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materializ
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 a	b
+7	7
 EXPLAIN
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	Using where
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t1
@@ -1912,6 +1913,57 @@ WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
 a	b
 SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2;
+# 
+# BUG#946055: Crash with semijoin IN subquery when hash join is used
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (7);
+CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c));
+INSERT INTO t2 VALUES 
+(4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'),
+(7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j');
+SET @save_optimizer_switch=@@optimizer_switch;
+SET @save_join_cache_level=@@join_cache_level;
+SET join_cache_level=2;
+EXPLAIN
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	index	NULL	c	5	NULL	8	Using where; Using index
+2	MATERIALIZED	s2	ref	d	d	4	const	1	Using where; Using index
+2	MATERIALIZED	s1	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	8	
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+a	c
+7	1
+7	1
+7	1
+SET optimizer_switch='join_cache_hashed=on';
+SET join_cache_level=4;
+EXPLAIN
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	index	NULL	c	5	NULL	8	Using where; Using index
+2	MATERIALIZED	s2	ref	d	d	4	const	1	Using where; Using index
+2	MATERIALIZED	s1	hash_ALL	NULL	#hash#$hj	5	test.s2.d	8	Using where; Using join buffer (flat, BNLH join)
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	8	
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+a	c
+7	1
+7	1
+7	1
+SET optimizer_switch=@save_optimizer_switch;
+SET join_cache_level=@save_join_cache_level;
+DROP TABLE t1,t2;
 #
 # BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization
 #
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index bcd08b70517..0962894bc19 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -5993,6 +5993,7 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1;
 1
 NULL
 drop table t1,t2,t3;
+set optimizer_switch=@subselect_tmp;
 #
 # LP BUG#905353 Wrong non-empty result with a constant table,
 # aggregate function in subquery, MyISAM or Aria
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index a493722fabe..6bd5e0e4ddd 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -5989,6 +5989,7 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1;
 1
 NULL
 drop table t1,t2,t3;
+set optimizer_switch=@subselect_tmp;
 #
 # LP BUG#905353 Wrong non-empty result with a constant table,
 # aggregate function in subquery, MyISAM or Aria
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 1167e9483c9..d97b95ad314 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6000,6 +6000,7 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1;
 1
 NULL
 drop table t1,t2,t3;
+set optimizer_switch=@subselect_tmp;
 #
 # LP BUG#905353 Wrong non-empty result with a constant table,
 # aggregate function in subquery, MyISAM or Aria
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 782aba08d26..5c8ccfdbda0 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -5989,6 +5989,7 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1;
 1
 NULL
 drop table t1,t2,t3;
+set optimizer_switch=@subselect_tmp;
 #
 # LP BUG#905353 Wrong non-empty result with a constant table,
 # aggregate function in subquery, MyISAM or Aria
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index d737cbd71d1..e60851775c0 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -1930,7 +1930,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	Using where
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
@@ -1938,11 +1938,12 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materializ
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 a	b
+7	7
 EXPLAIN
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	Using where
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t1
@@ -1950,6 +1951,59 @@ WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
 a	b
 SET optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2;
+# 
+# BUG#946055: Crash with semijoin IN subquery when hash join is used
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (7);
+CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c));
+INSERT INTO t2 VALUES 
+(4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'),
+(7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j');
+SET @save_optimizer_switch=@@optimizer_switch;
+SET @save_join_cache_level=@@join_cache_level;
+SET join_cache_level=2;
+EXPLAIN
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	index	c	c	5	NULL	8	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	s2	ref	d	d	4	const	1	Using where; Using index
+2	MATERIALIZED	s1	ALL	c	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	8	
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+a	c
+7	1
+7	1
+7	1
+SET optimizer_switch='join_cache_hashed=on';
+SET join_cache_level=4;
+EXPLAIN
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	index	c	c	5	NULL	8	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
+2	MATERIALIZED	s2	ref	d	d	4	const	1	Using where; Using index
+2	MATERIALIZED	s1	hash_ALL	c	#hash#$hj	10	const,test.s2.d	8	Using where; Using join buffer (flat, BNLH join)
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	8	
+SELECT a, c FROM t1, t2
+WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+a	c
+7	1
+7	1
+7	1
+SET optimizer_switch=@save_optimizer_switch;
+SET join_cache_level=@save_join_cache_level;
+DROP TABLE t1,t2;
 #
 # BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization
 #
diff --git a/mysql-test/t/ctype_recoding.test b/mysql-test/t/ctype_recoding.test
index 1f55aea414a..ee07ef24def 100644
--- a/mysql-test/t/ctype_recoding.test
+++ b/mysql-test/t/ctype_recoding.test
@@ -210,3 +210,21 @@ drop table t1;
 create table t1(a char character set latin1 default _cp1251 0xFF);
 
 --echo End of 4.1 tests
+
+SET CHARACTER SET DEFAULT;
+--echo #
+--echo # LP BUG#944504 Item_func_conv_charset tries to execute subquery constant
+--echo # 
+SET optimizer_switch = 'in_to_exists=on';
+SET character_set_connection = utf8;
+CREATE TABLE t1 ( a VARCHAR(1) );
+INSERT INTO t1 VALUES ('m'),('n');
+CREATE VIEW v1 AS SELECT 'w' ;
+--error ER_CANT_AGGREGATE_2COLLATIONS
+SELECT * FROM t1 WHERE a < ALL ( SELECT * FROM v1 );
+drop view v1;
+drop table t1;
+SET character_set_connection = default;
+SET optimizer_switch= default;
+
+--echo #End of 5.3 tests
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 95412426aa0..9660dd1e5f5 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1313,6 +1313,40 @@ drop table t1,t2,t3;
 
 set SESSION optimizer_switch= @save_optimizer_switch;
 
+--echo #
+--echo # LP BUG#944782: derived table from an information schema table
+--echo #
+
+SET @save_optimizer_switch=@@optimizer_switch;
+SET SESSION optimizer_switch='derived_merge=on'; 
+SET SESSION optimizer_switch='derived_with_keys=on'; 
+
+CREATE TABLE t1 (c1 int PRIMARY KEY, c2 char(5));
+
+EXPLAIN
+SELECT COUNT(*) > 0
+  FROM INFORMATION_SCHEMA.COLUMNS
+       INNER JOIN
+       (SELECT TABLE_SCHEMA,
+               GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES
+          FROM INFORMATION_SCHEMA.STATISTICS
+        GROUP BY TABLE_SCHEMA) AS UNIQUES
+        ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA);
+
+#  this query crashed in mariadb-5.5.20
+SELECT COUNT(*) > 0
+  FROM INFORMATION_SCHEMA.COLUMNS
+       INNER JOIN
+       (SELECT TABLE_SCHEMA,
+               GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COL_NAMES
+          FROM INFORMATION_SCHEMA.STATISTICS
+        GROUP BY TABLE_SCHEMA) AS UNIQUES
+        ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA);
+
+DROP TABLE t1;
+
+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/join_outer.test b/mysql-test/t/join_outer.test
index 04816402205..3fae21ac738 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1221,4 +1221,65 @@ SET SESSION join_cache_level=default;
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # LP bug #943543: LEFT JOIN converted to JOIN with 
+--echo #                 ORed IS NULL(primary key) in WHERE clause                
+--echo #
+
+CREATE TABLE t1 (
+  a int, b int NOT NULL, pk int NOT NULL,
+  PRIMARY KEY (pk), INDEX idx(b)
+);
+INSERT INTO t1 VALUES
+  (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4),
+  (1,9,6), (8,5,7), (NULL,8,8), (8,1,5);
+
+CREATE TABLE t2 (pk int PRIMARY KEY);
+INSERT INTO t2 VALUES (3), (8), (5);
+
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+ WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+ WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+ WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+ WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+
+DROP TABLE t2;
+
+CREATE TABLE t2 (c int, d int, KEY (c));
+INSERT INTO t2 VALUES
+  (3,30), (8,88), (5,50), (8,81),
+  (4,40), (9,90), (7,70), (9,90),
+  (13,130), (18,188), (15,150), (18,181),
+  (14,140), (19,190), (17,170), (19,190);
+
+INSERT INTO t1 VALUES (8,5,9);
+
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+  WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+  WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+  WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+  WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+
+DROP TABLE t1,t2;
+
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/myisam-big.test b/mysql-test/t/myisam-big.test
new file mode 100644
index 00000000000..2fec2450ecd
--- /dev/null
+++ b/mysql-test/t/myisam-big.test
@@ -0,0 +1,64 @@
+#
+# Test bugs in the MyISAM code that require more space/time
+--source include/big_test.inc
+
+# Initialise
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+
+#
+# BUG#925377:
+# Querying myisam table metadata while 'alter table..enable keys' is
+# running may corrupt the table
+#
+create table t1 (id int, sometext varchar(100)) engine=myisam;
+insert into t1 values (1, "hello"),(2, "hello2"),(4, "hello3"),(4, "hello4");
+create table t2 like t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+insert into t2 select * from t1;
+insert into t1 select * from t1;
+select count(*) from t1;
+connect (con2,localhost,root,,);
+connection con2;
+alter table t1 add index (id), add index(sometext), add index(sometext,id);
+alter table t1 disable keys;
+send alter table t1 enable keys;
+connection default;
+--sleep 1
+--disable_query_log
+--disable_result_log
+show table status like 't1';
+--enable_query_log
+--enable_result_log
+connection con2;
+reap;
+disconnect con2;
+connection default;
+drop table t1,t2;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index e9735dae79f..6e2b2ef86ae 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5075,8 +5075,8 @@ SELECT ( SELECT b FROM t2 WHERE b = a OR b * 0) FROM t1;
 # example with "random"
 SELECT ( SELECT b FROM t2 WHERE b = a OR rand() * 0) FROM t1;
 
-
 drop table t1,t2,t3;
+set optimizer_switch=@subselect_tmp;
 
 --echo #
 --echo # LP BUG#905353 Wrong non-empty result with a constant table,
@@ -5090,5 +5090,6 @@ 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_mat.test b/mysql-test/t/subselect_sj_mat.test
index 78dad76738c..a077e9b5af5 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1602,6 +1602,46 @@ SET optimizer_switch=@save_optimizer_switch;
 
 DROP TABLE t1,t2;
 
+--echo # 
+--echo # BUG#946055: Crash with semijoin IN subquery when hash join is used
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (7);
+
+CREATE TABLE t2 (b int, c int, d varchar(1), e varchar(1), KEY (c), KEY (d, c));
+
+INSERT INTO t2 VALUES 
+  (4,2,'v','v'), (6,1,'v','v'), (0,5,'x','x'), (7,1,'x','x'),
+  (7,3,'i','i'), (7,1,'e','e'), (1,4,'p','p'), (1,2,'j','j');
+
+SET @save_optimizer_switch=@@optimizer_switch;
+SET @save_join_cache_level=@@join_cache_level;
+
+SET join_cache_level=2;
+EXPLAIN
+SELECT a, c FROM t1, t2
+  WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+                     WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+SELECT a, c FROM t1, t2
+  WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+                     WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+
+SET optimizer_switch='join_cache_hashed=on';
+SET join_cache_level=4;
+EXPLAIN
+SELECT a, c FROM t1, t2
+  WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+                     WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+SELECT a, c FROM t1, t2
+  WHERE (a, c) IN (SELECT s1.b, s1.c FROM t2 AS s1, t2 AS s2
+                     WHERE s2.d = s1.e AND s1.e = (SELECT MAX(e) FROM t2));
+
+SET optimizer_switch=@save_optimizer_switch;
+SET join_cache_level=@save_join_cache_level;
+
+DROP TABLE t1,t2;
+
 --echo #
 --echo # BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization
 --echo #