Merge 5.2->5.3 in preparation for the release of mariadb-5.3.4-rc.

33 files changed, 1340 insertions, 715 deletions

diff --git a/mysql-test/include/wait_until_connected_again.inc b/mysql-test/include/wait_until_connected_again.inc
index aff92141a8b..96240e36db7 100644
--- a/mysql-test/include/wait_until_connected_again.inc
+++ b/mysql-test/include/wait_until_connected_again.inc
@@ -14,7 +14,7 @@ while ($mysql_errno)
   # Strangely enough, the server might return "Too many connections"
   # while being shutdown, thus 1040 is an "allowed" error
   # See BUG#36228
-  --error 0,1040,1053,2002,2003,2006,2013
+  --error 0,1040,1053,2002,2003,2005,2006,2013
   show status;
 
   dec $counter;
diff --git a/mysql-test/include/wait_until_disconnected.inc b/mysql-test/include/wait_until_disconnected.inc
index c274fbbe089..71361682442 100644
--- a/mysql-test/include/wait_until_disconnected.inc
+++ b/mysql-test/include/wait_until_disconnected.inc
@@ -12,7 +12,7 @@ while (!$mysql_errno)
   # Strangely enough, the server might return "Too many connections"
   # while being shutdown, thus 1040 is an "allowed" error.
   # See BUG#36228.
-  --error 0,1040,1053,2002,2003,2006,2013
+  --error 0,1040,1053,2002,2003,2005,2006,2013
   show status;
 
   dec $counter;
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index e853f5ac526..3f51c73b248 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1089,7 +1089,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t1` `t` join `test`.`t2` left join `test`.`t3` on((0 <> 0)) where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
 SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 
@@ -1103,7 +1103,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
-3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((0 <> 0)) where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
 SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 
@@ -1117,7 +1117,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1003	select 6 AS `a`,5 AS `b` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((0 <> 0)) where (not(<expr_cache><6,5>(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))))
 DROP VIEW v1;
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index d5ea7311cc4..9660640baaa 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1825,6 +1825,70 @@ drop table t1;
 #
 End of 5.1 tests
 #
+# Bug #904345: MIN/MAX optimization  with constant FALSE condition
+#
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	range	a	a	4	NULL	4	100.00	Using where; Using index
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (<expr_cache><1,2>(<in_optimizer>((1,2),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))) and (`test`.`t1`.`a` < 10))
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT 3,4) AND a<10;
+MAX(a)
+NULL
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	100.00	
+1	PRIMARY	t1	range	a	a	4	NULL	4	100.00	Using where; Using index; Using join buffer (flat, BNL join)
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = 2) and (`test`.`t2`.`a` = 1) and (`test`.`t1`.`a` < 10))
+SELECT MAX(a) FROM t1 WHERE (1,2) IN (SELECT a,b FROM t2 WHERE b<5) and a<10;
+MAX(a)
+NULL
+EXPLAIN EXTENDED
+SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	a	a	4	NULL	4	100.00	Using where; Using index
+Warnings:
+Note	1003	select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where (((rand() * 0) <> 0) and (`test`.`t1`.`a` < 10))
+SELECT MAX(a) FROM t1 WHERE RAND()*0<>0 AND a<10;
+MAX(a)
+NULL
+DROP TABLE t1,t2;
+#
+# Bug #879860: MIN/MAX for subquery returning empty set 
+#
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1276	Field or reference 'test.t3.b' of SELECT #2 was resolved in SELECT #1
+Note	1003	select <expr_cache><`test`.`t3`.`b`>((select min(1) from `test`.`t1` join `test`.`t2` where (10 = `test`.`t3`.`b`))) AS `(SELECT MIN(t1.a) FROM t1,t2 WHERE t2.a = t3.b)` from `test`.`t3`
+SELECT (SELECT MIN(t1.a) FROM t1,t2 WHERE t2.a = t3.b) FROM t3;
+(SELECT MIN(t1.a) FROM t1,t2 WHERE t2.a = t3.b)
+NULL
+NULL
+DROP TABLE t1,t2,t3;
+#
+End of 5.2 tests
+#
 # BUG#46680 - Assertion failed in file item_subselect.cc, 
 #             line 305 crashing on HAVING subquery
 #
@@ -1977,4 +2041,3 @@ set @@optimizer_switch=@save_optimizer_switch;
 # Cleanup for BUG#46680
 #
 DROP TABLE IF EXISTS t1,t2,t3,empty1;
-End of 6.0 tests
diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result
index 56e557ec881..7ea314f5c87 100644
--- a/mysql-test/r/join_outer_innodb.result
+++ b/mysql-test/r/join_outer_innodb.result
@@ -17,3 +17,11 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
 DROP TABLE t1,t2;
+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;
+a
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index e73ff62366c..c0a54817b4c 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1729,3 +1729,57 @@ select 1 order by max(1) + min(1);
 1
 1
 End of 5.1 tests
+#
+# Fix of LP BUG#793589 Wrong result with double ORDER BY
+#
+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;
+field1
+1
+7
+SELECT t2.b, t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b, t2.b;
+b	b
+1	8
+7	9
+SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
+b	b
+1	8
+7	9
+SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
+b
+1
+7
+# 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary; Using filesort
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	100.00	
+Warnings:
+Note	1003	select `test`.`t2`.`b` AS `field1` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`b`) group by `test`.`t2`.`b` order by `test`.`t1`.`b`
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary; Using filesort
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	100.00	
+Warnings:
+Note	1003	select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`b`) group by `test`.`t2`.`b` order by `test`.`t1`.`b`
+explain extended
+SELECT t2.b,t1.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary; Using filesort
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	100.00	
+Warnings:
+Note	1003	select `test`.`t2`.`b` AS `b`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`b`) group by `test`.`t2`.`b` order by `test`.`t1`.`b`
+explain extended
+SELECT t2.b FROM t1, t2 WHERE t1.b = t2.a GROUP BY t2.b ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary; Using filesort
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	100.00	
+Warnings:
+Note	1003	select `test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`a` = `test`.`t1`.`b`) group by `test`.`t2`.`b` order by `test`.`t1`.`b`
+drop table t1,t2;
+End of 5.2 tests
diff --git a/mysql-test/r/ps_11bugs.result b/mysql-test/r/ps_11bugs.result
index f9f0525646d..56894302505 100644
--- a/mysql-test/r/ps_11bugs.result
+++ b/mysql-test/r/ps_11bugs.result
@@ -121,8 +121,8 @@ insert into t1 values (1);
 explain select * from t1 where 3 in (select (1+1) union select 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 select * from t1 where 3 in (select (1+1) union select 1);
 a
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 9648860619b..9e06f2d3841 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2782,10 +2782,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	index	key1	key1	5	NULL	4	Using where; Using index
 explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	range	key1	key1	5	NULL	3	Using where; Using index
 select max(key1) from t1 where key1 <= 0.6158;
 max(key1)
 0.615800023078918
@@ -2804,10 +2804,10 @@ max(key1)	min(key2)
 0.615800023078918	1.37619996070862
 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 max(key1)
-0.615800023078918
+0.384499996900558
 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 min(key1)
-0.376199990510941
+0.384499996900558
 DROP TABLE t1,t2;
 CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
 INSERT INTO t1 VALUES (10);
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 88411e3482f..5b36791024f 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -2793,10 +2793,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	index	key1	key1	5	NULL	4	Using where; Using index
 explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	range	key1	key1	5	NULL	3	Using where; Using index
 select max(key1) from t1 where key1 <= 0.6158;
 max(key1)
 0.615800023078918
@@ -2815,10 +2815,10 @@ max(key1)	min(key2)
 0.615800023078918	1.37619996070862
 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 max(key1)
-0.615800023078918
+0.384499996900558
 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 min(key1)
-0.376199990510941
+0.384499996900558
 DROP TABLE t1,t2;
 CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
 INSERT INTO t1 VALUES (10);
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 9648860619b..9e06f2d3841 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -2782,10 +2782,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	index	key1	key1	5	NULL	4	Using where; Using index
 explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	range	key1	key1	5	NULL	3	Using where; Using index
 select max(key1) from t1 where key1 <= 0.6158;
 max(key1)
 0.615800023078918
@@ -2804,10 +2804,10 @@ max(key1)	min(key2)
 0.615800023078918	1.37619996070862
 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 max(key1)
-0.615800023078918
+0.384499996900558
 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 min(key1)
-0.376199990510941
+0.384499996900558
 DROP TABLE t1,t2;
 CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
 INSERT INTO t1 VALUES (10);
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 7dfcad074fe..47dcafc663b 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4567,6 +4567,13 @@ CREATE TABLE t1(a1 int);
 INSERT INTO t1 VALUES (1),(2);
 SELECT @@session.sql_mode INTO @old_sql_mode;
 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+EXPLAIN EXTENDED
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1)))
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5005,7 +5012,6 @@ EXPLAIN EXTENDED SELECT DISTINCT 1 FROM t1,
 WHERE t1.a = d1.a;
 ERROR 42S22: Unknown column 'd1.a' in 'where clause'
 DROP TABLE t1;
-End of 5.1 tests.
 Set up test tables.
 CREATE TABLE t1 (
 t1_id INT UNSIGNED,
@@ -5448,10 +5454,161 @@ NULL
 NULL
 5
 DROP TABLE t1, t2, t3;
+#
+# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+#
+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';
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+1
+1
+1
+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 )
+;
+pk
+SET SESSION sql_mode=@old_sql_mode;
+drop table t2, t1;
+drop view v1;
+#
+# BUG#50257: Missing info in REF column of the EXPLAIN 
+#            lines for subselects
+#
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	5	const	1	
+EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
+DROP TABLE t1;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+# (duplicate of LP bug #888456)
+#
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t2	index	f1_key	f1_key	4	NULL	10	Using where; Using index
+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);
+f1	f1_key
+v	j
+s	j
+v	v
+s	v
+v	c
+s	c
+v	m
+s	m
+v	d
+s	d
+v	d
+s	d
+v	y
+s	y
+v	t
+s	t
+v	d
+s	d
+v	s
+s	s
+DROP TABLE t1,t2;
+#
+# LP bug 919427: EXPLAIN for a query over a single-row table 
+#                with IN subquery in WHERE condition
+# 
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+2	MATERIALIZED	it1	ref	idx_cvk_cik	idx_cvk_cik	9	const,const	1	Using where; Using index
+SELECT col_int_nokey FROM ot 
+WHERE col_varchar_nokey IN 
+(SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+col_int_nokey
+1
+EXPLAIN
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	
+2	MATERIALIZED	it2	ref	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	8	const,const	1	Using where; Using index
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+col_int_nokey
+1
+DROP TABLE ot,it1,it2;
 End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
-# maria-5.3
 #
 CREATE TABLE t1 ( f1 int );
 INSERT INTO t1 VALUES (19), (20);
@@ -5498,32 +5655,6 @@ b	c
 9	NULL
 SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
-End of 5.3 tests
-#
-# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
-#
-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';
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
-1
-1
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
-1
-1
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
-1
-1
-1
-SET SESSION sql_mode=@old_sql_mode;
-DROP TABLE t1, t2;
 #
 # Bug#11764086: Null left operand to NOT IN in WHERE clause
 # behaves differently than real NULL
@@ -5573,27 +5704,6 @@ id	parent_id
 DROP TABLE parent, child;
 # End of test for bug#11764086.
 #
-# BUG#50257: Missing info in REF column of the EXPLAIN 
-#            lines for subselects
-#
-CREATE TABLE t1 (a INT, b INT, INDEX (a));
-INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
-
-set @tmp_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='derived_merge=off,derived_with_keys=off';
-EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
-2	DERIVED	t1	ref	a	a	5	const	1	
-set optimizer_switch=@tmp_optimizer_switch;
-
-EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
-
-DROP TABLE t1;
-#
 # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
 #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
 #
@@ -5619,54 +5729,6 @@ GROUP BY b
 1
 DROP TABLE t1, t2;
 #
-# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
-#
-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');
-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 );
-f1	f1_key
-v	j
-s	j
-v	v
-s	v
-v	c
-s	c
-v	m
-s	m
-v	d
-s	d
-v	d
-s	d
-v	y
-s	y
-v	t
-s	t
-v	d
-s	d
-v	s
-s	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 );
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
-2	DEPENDENT SUBQUERY	t2	index	f1_key	f1_key	4	NULL	10	Using where; Using index
-DROP TABLE t1,t2;
-#
 # LP bug #826279: assertion failure with GROUP BY a result of subquery
 #
 CREATE TABLE t1 (a int);
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 6c942bca31a..4e3247e9da8 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -831,7 +831,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
 f1	f2
 SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
@@ -922,7 +922,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2);
 f1	f2
 INSERT INTO t1 VALUES (1, 2);
@@ -1017,7 +1017,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 SET @@optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
@@ -1108,7 +1108,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No matching min/max row
 SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10);
 f1	f2
 set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index bb30bfaf7f1..009510276b5 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1793,7 +1793,7 @@ SELECT * FROM t1
 WHERE a IN ( SELECT MIN(a) FROM t1 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select 8 AS `a` from `test`.`t1` where <expr_cache><8>(<in_optimizer>(8,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` having (<cache>(8) = <ref_null_helper>(min(`test`.`t1`.`a`))))))
 DROP TABLE t1;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index c7d9fd96e5d..ea832b7f7ce 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -4569,6 +4569,13 @@ CREATE TABLE t1(a1 int);
 INSERT INTO t1 VALUES (1),(2);
 SELECT @@session.sql_mode INTO @old_sql_mode;
 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+EXPLAIN EXTENDED
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1)))
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5007,7 +5014,6 @@ EXPLAIN EXTENDED SELECT DISTINCT 1 FROM t1,
 WHERE t1.a = d1.a;
 ERROR 42S22: Unknown column 'd1.a' in 'where clause'
 DROP TABLE t1;
-End of 5.1 tests.
 Set up test tables.
 CREATE TABLE t1 (
 t1_id INT UNSIGNED,
@@ -5449,10 +5455,159 @@ NULL
 NULL
 5
 DROP TABLE t1, t2, t3;
+#
+# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+#
+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';
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+1
+1
+1
+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 )
+;
+pk
+SET SESSION sql_mode=@old_sql_mode;
+drop table t2, t1;
+drop view v1;
+#
+# BUG#50257: Missing info in REF column of the EXPLAIN 
+#            lines for subselects
+#
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	5	const	1	
+EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
+DROP TABLE t1;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+# (duplicate of LP bug #888456)
+#
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t2	index	f1_key	f1_key	4	NULL	10	Using where; Using index
+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);
+f1	f1_key
+v	j
+s	j
+v	v
+s	v
+v	c
+s	c
+v	m
+s	m
+v	d
+s	d
+v	d
+s	d
+v	y
+s	y
+v	t
+s	t
+v	d
+s	d
+v	s
+s	s
+DROP TABLE t1,t2;
+#
+# LP bug 919427: EXPLAIN for a query over a single-row table 
+#                with IN subquery in WHERE condition
+# 
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it1	ref	idx_cvk_cik	idx_cvk_cik	9	const,const	1	Using where; Using index; FirstMatch(ot)
+SELECT col_int_nokey FROM ot 
+WHERE col_varchar_nokey IN 
+(SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+col_int_nokey
+1
+EXPLAIN
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ref	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	8	const,const	1	Using where; Using index; FirstMatch(ot)
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+col_int_nokey
+1
+DROP TABLE ot,it1,it2;
 End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
-# maria-5.3
 #
 CREATE TABLE t1 ( f1 int );
 INSERT INTO t1 VALUES (19), (20);
@@ -5499,32 +5654,6 @@ b	c
 9	NULL
 SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
-End of 5.3 tests
-#
-# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
-#
-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';
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
-1
-1
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
-1
-1
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
-1
-1
-1
-SET SESSION sql_mode=@old_sql_mode;
-DROP TABLE t1, t2;
 #
 # Bug#11764086: Null left operand to NOT IN in WHERE clause
 # behaves differently than real NULL
@@ -5574,27 +5703,6 @@ id	parent_id
 DROP TABLE parent, child;
 # End of test for bug#11764086.
 #
-# BUG#50257: Missing info in REF column of the EXPLAIN 
-#            lines for subselects
-#
-CREATE TABLE t1 (a INT, b INT, INDEX (a));
-INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
-
-set @tmp_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='derived_merge=off,derived_with_keys=off';
-EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
-2	DERIVED	t1	ref	a	a	5	const	1	
-set optimizer_switch=@tmp_optimizer_switch;
-
-EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
-
-DROP TABLE t1;
-#
 # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
 #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
 #
@@ -5620,54 +5728,6 @@ GROUP BY b
 1
 DROP TABLE t1, t2;
 #
-# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
-#
-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');
-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 );
-f1	f1_key
-v	j
-s	j
-v	v
-s	v
-v	c
-s	c
-v	m
-s	m
-v	d
-s	d
-v	d
-s	d
-v	y
-s	y
-v	t
-s	t
-v	d
-s	d
-v	s
-s	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 );
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
-2	DEPENDENT SUBQUERY	t2	index	f1_key	f1_key	4	NULL	10	Using where; Using index
-DROP TABLE t1,t2;
-#
 # LP bug #826279: assertion failure with GROUP BY a result of subquery
 #
 CREATE TABLE t1 (a int);
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 3d6efa28e19..f86955eadcd 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -4565,6 +4565,13 @@ CREATE TABLE t1(a1 int);
 INSERT INTO t1 VALUES (1),(2);
 SELECT @@session.sql_mode INTO @old_sql_mode;
 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+EXPLAIN EXTENDED
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1)))
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5003,7 +5010,6 @@ EXPLAIN EXTENDED SELECT DISTINCT 1 FROM t1,
 WHERE t1.a = d1.a;
 ERROR 42S22: Unknown column 'd1.a' in 'where clause'
 DROP TABLE t1;
-End of 5.1 tests.
 Set up test tables.
 CREATE TABLE t1 (
 t1_id INT UNSIGNED,
@@ -5445,10 +5451,159 @@ NULL
 NULL
 5
 DROP TABLE t1, t2, t3;
+#
+# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+#
+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';
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+1
+1
+1
+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 )
+;
+pk
+SET SESSION sql_mode=@old_sql_mode;
+drop table t2, t1;
+drop view v1;
+#
+# BUG#50257: Missing info in REF column of the EXPLAIN 
+#            lines for subselects
+#
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	5	const	1	
+EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
+DROP TABLE t1;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+# (duplicate of LP bug #888456)
+#
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t2	index	f1_key	f1_key	4	NULL	10	Using where; Using index
+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);
+f1	f1_key
+v	j
+s	j
+v	v
+s	v
+v	c
+s	c
+v	m
+s	m
+v	d
+s	d
+v	d
+s	d
+v	y
+s	y
+v	t
+s	t
+v	d
+s	d
+v	s
+s	s
+DROP TABLE t1,t2;
+#
+# LP bug 919427: EXPLAIN for a query over a single-row table 
+#                with IN subquery in WHERE condition
+# 
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	it1	index_subquery	idx_cvk_cik	idx_cvk_cik	9	func,const	2	Using index; Using where
+SELECT col_int_nokey FROM ot 
+WHERE col_varchar_nokey IN 
+(SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+col_int_nokey
+1
+EXPLAIN
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	it2	index_subquery	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	8	func,const	1	Using index; Using where
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+col_int_nokey
+1
+DROP TABLE ot,it1,it2;
 End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
-# maria-5.3
 #
 CREATE TABLE t1 ( f1 int );
 INSERT INTO t1 VALUES (19), (20);
@@ -5495,32 +5650,6 @@ b	c
 9	NULL
 SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
-End of 5.3 tests
-#
-# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
-#
-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';
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
-1
-1
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
-1
-1
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
-1
-1
-1
-SET SESSION sql_mode=@old_sql_mode;
-DROP TABLE t1, t2;
 #
 # Bug#11764086: Null left operand to NOT IN in WHERE clause
 # behaves differently than real NULL
@@ -5570,27 +5699,6 @@ id	parent_id
 DROP TABLE parent, child;
 # End of test for bug#11764086.
 #
-# BUG#50257: Missing info in REF column of the EXPLAIN 
-#            lines for subselects
-#
-CREATE TABLE t1 (a INT, b INT, INDEX (a));
-INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
-
-set @tmp_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='derived_merge=off,derived_with_keys=off';
-EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
-2	DERIVED	t1	ref	a	a	5	const	1	
-set optimizer_switch=@tmp_optimizer_switch;
-
-EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
-
-DROP TABLE t1;
-#
 # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
 #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
 #
@@ -5616,54 +5724,6 @@ GROUP BY b
 1
 DROP TABLE t1, t2;
 #
-# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
-#
-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');
-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 );
-f1	f1_key
-v	j
-s	j
-v	v
-s	v
-v	c
-s	c
-v	m
-s	m
-v	d
-s	d
-v	d
-s	d
-v	y
-s	y
-v	t
-s	t
-v	d
-s	d
-v	s
-s	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 );
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
-2	DEPENDENT SUBQUERY	t2	index	f1_key	f1_key	4	NULL	10	Using where; Using index
-DROP TABLE t1,t2;
-#
 # LP bug #826279: assertion failure with GROUP BY a result of subquery
 #
 CREATE TABLE t1 (a int);
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 602905dd5b8..fa8342da223 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -4573,6 +4573,13 @@ CREATE TABLE t1(a1 int);
 INSERT INTO t1 VALUES (1),(2);
 SELECT @@session.sql_mode INTO @old_sql_mode;
 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+EXPLAIN EXTENDED
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1)))
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5011,7 +5018,6 @@ EXPLAIN EXTENDED SELECT DISTINCT 1 FROM t1,
 WHERE t1.a = d1.a;
 ERROR 42S22: Unknown column 'd1.a' in 'where clause'
 DROP TABLE t1;
-End of 5.1 tests.
 Set up test tables.
 CREATE TABLE t1 (
 t1_id INT UNSIGNED,
@@ -5454,10 +5460,161 @@ NULL
 NULL
 5
 DROP TABLE t1, t2, t3;
+#
+# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+#
+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';
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+1
+1
+1
+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 )
+;
+pk
+SET SESSION sql_mode=@old_sql_mode;
+drop table t2, t1;
+drop view v1;
+#
+# BUG#50257: Missing info in REF column of the EXPLAIN 
+#            lines for subselects
+#
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	5	const	1	
+EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
+DROP TABLE t1;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+# (duplicate of LP bug #888456)
+#
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t2	index	f1_key	f1_key	4	NULL	10	Using where; Using index
+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);
+f1	f1_key
+v	j
+s	j
+v	v
+s	v
+v	c
+s	c
+v	m
+s	m
+v	d
+s	d
+v	d
+s	d
+v	y
+s	y
+v	t
+s	t
+v	d
+s	d
+v	s
+s	s
+DROP TABLE t1,t2;
+#
+# LP bug 919427: EXPLAIN for a query over a single-row table 
+#                with IN subquery in WHERE condition
+# 
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	5	func	1	
+2	MATERIALIZED	it1	ref	idx_cvk_cik	idx_cvk_cik	9	const,const	1	Using where; Using index
+SELECT col_int_nokey FROM ot 
+WHERE col_varchar_nokey IN 
+(SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+col_int_nokey
+1
+EXPLAIN
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	10	func,func	1	
+2	MATERIALIZED	it2	ref	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	8	const,const	1	Using where; Using index
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+col_int_nokey
+1
+DROP TABLE ot,it1,it2;
 End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
-# maria-5.3
 #
 CREATE TABLE t1 ( f1 int );
 INSERT INTO t1 VALUES (19), (20);
@@ -5504,32 +5661,6 @@ b	c
 9	NULL
 SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
-End of 5.3 tests
-#
-# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
-#
-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';
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
-1
-1
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
-1
-1
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
-1
-1
-1
-SET SESSION sql_mode=@old_sql_mode;
-DROP TABLE t1, t2;
 #
 # Bug#11764086: Null left operand to NOT IN in WHERE clause
 # behaves differently than real NULL
@@ -5579,27 +5710,6 @@ id	parent_id
 DROP TABLE parent, child;
 # End of test for bug#11764086.
 #
-# BUG#50257: Missing info in REF column of the EXPLAIN 
-#            lines for subselects
-#
-CREATE TABLE t1 (a INT, b INT, INDEX (a));
-INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
-
-set @tmp_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='derived_merge=off,derived_with_keys=off';
-EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
-2	DERIVED	t1	ref	a	a	5	const	1	
-set optimizer_switch=@tmp_optimizer_switch;
-
-EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
-
-DROP TABLE t1;
-#
 # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
 #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
 #
@@ -5625,54 +5735,6 @@ GROUP BY b
 1
 DROP TABLE t1, t2;
 #
-# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
-#
-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');
-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 );
-f1	f1_key
-v	j
-s	j
-v	v
-s	v
-v	c
-s	c
-v	m
-s	m
-v	d
-s	d
-v	d
-s	d
-v	y
-s	y
-v	t
-s	t
-v	d
-s	d
-v	s
-s	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 );
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
-2	DEPENDENT SUBQUERY	t2	index	f1_key	f1_key	4	NULL	10	Using where; Using index
-DROP TABLE t1,t2;
-#
 # LP bug #826279: assertion failure with GROUP BY a result of subquery
 #
 CREATE TABLE t1 (a int);
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 9b2370efd61..985f840f7c2 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -4565,6 +4565,13 @@ CREATE TABLE t1(a1 int);
 INSERT INTO t1 VALUES (1),(2);
 SELECT @@session.sql_mode INTO @old_sql_mode;
 SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
+EXPLAIN EXTENDED
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	select 1 AS `1` from `test`.`t1` where <nop>(<in_optimizer>(1,((select max(`test`.`t1`.`a1`) from `test`.`t1`) > 1)))
 SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t1);
 1
 1
@@ -5003,7 +5010,6 @@ EXPLAIN EXTENDED SELECT DISTINCT 1 FROM t1,
 WHERE t1.a = d1.a;
 ERROR 42S22: Unknown column 'd1.a' in 'where clause'
 DROP TABLE t1;
-End of 5.1 tests.
 Set up test tables.
 CREATE TABLE t1 (
 t1_id INT UNSIGNED,
@@ -5445,10 +5451,159 @@ NULL
 NULL
 5
 DROP TABLE t1, t2, t3;
+#
+# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
+#
+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';
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
+1
+1
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
+1
+SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
+1
+1
+1
+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 )
+;
+pk
+SET SESSION sql_mode=@old_sql_mode;
+drop table t2, t1;
+drop view v1;
+#
+# BUG#50257: Missing info in REF column of the EXPLAIN 
+#            lines for subselects
+#
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	5	const	1	
+EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
+DROP TABLE t1;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+# (duplicate of LP bug #888456)
+#
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t2	index	f1_key	f1_key	4	NULL	10	Using where; Using index
+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);
+f1	f1_key
+v	j
+s	j
+v	v
+s	v
+v	c
+s	c
+v	m
+s	m
+v	d
+s	d
+v	d
+s	d
+v	y
+s	y
+v	t
+s	t
+v	d
+s	d
+v	s
+s	s
+DROP TABLE t1,t2;
+#
+# LP bug 919427: EXPLAIN for a query over a single-row table 
+#                with IN subquery in WHERE condition
+# 
+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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	it1	index_subquery	idx_cvk_cik	idx_cvk_cik	9	func,const	2	Using index; Using where
+SELECT col_int_nokey FROM ot 
+WHERE col_varchar_nokey IN 
+(SELECT col_varchar_key FROM it1 WHERE col_int_key IS NULL);
+col_int_nokey
+1
+EXPLAIN
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	it2	index_subquery	idx_cvk_cvk2_cik,idx_cvk_cik	idx_cvk_cvk2_cik	8	func,const	1	Using index; Using where
+SELECT col_int_nokey FROM ot
+WHERE (col_varchar_nokey, 'x') IN
+(SELECT col_varchar_key, col_varchar_key2 FROM it2);
+col_int_nokey
+1
+DROP TABLE ot,it1,it2;
 End of 5.2 tests
 #
 # BUG#779885: Crash in eliminate_item_equal with materialization=on in
-# maria-5.3
 #
 CREATE TABLE t1 ( f1 int );
 INSERT INTO t1 VALUES (19), (20);
@@ -5495,32 +5650,6 @@ b	c
 9	NULL
 SET optimizer_switch=@save_optimizer_switch;
 drop table t1, t2, t3;
-End of 5.3 tests
-#
-# Bug#12763207 - ASSERT IN SUBSELECT::SINGLE_VALUE_TRANSFORMER
-#
-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';
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2 FROM t2);
-1
-1
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 2.0 FROM t2);
-1
-1
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT 'a' FROM t2);
-1
-SELECT 1 FROM t1 WHERE 1 < SOME (SELECT a1 FROM t2);
-1
-1
-1
-SET SESSION sql_mode=@old_sql_mode;
-DROP TABLE t1, t2;
 #
 # Bug#11764086: Null left operand to NOT IN in WHERE clause
 # behaves differently than real NULL
@@ -5570,27 +5699,6 @@ id	parent_id
 DROP TABLE parent, child;
 # End of test for bug#11764086.
 #
-# BUG#50257: Missing info in REF column of the EXPLAIN 
-#            lines for subselects
-#
-CREATE TABLE t1 (a INT, b INT, INDEX (a));
-INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
-
-set @tmp_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='derived_merge=off,derived_with_keys=off';
-EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
-2	DERIVED	t1	ref	a	a	5	const	1	
-set optimizer_switch=@tmp_optimizer_switch;
-
-EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
-
-DROP TABLE t1;
-#
 # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
 #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
 #
@@ -5616,54 +5724,6 @@ GROUP BY b
 1
 DROP TABLE t1, t2;
 #
-# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
-#
-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');
-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 );
-f1	f1_key
-v	j
-s	j
-v	v
-s	v
-v	c
-s	c
-v	m
-s	m
-v	d
-s	d
-v	d
-s	d
-v	y
-s	y
-v	t
-s	t
-v	d
-s	d
-v	s
-s	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 );
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	table2	index	NULL	f1_key	4	NULL	10	Using where; Using index; Using join buffer (flat, BNL join)
-2	DEPENDENT SUBQUERY	t2	index	f1_key	f1_key	4	NULL	10	Using where; Using index
-DROP TABLE t1,t2;
-#
 # LP bug #826279: assertion failure with GROUP BY a result of subquery
 #
 CREATE TABLE t1 (a int);
diff --git a/mysql-test/r/sum_distinct.result b/mysql-test/r/sum_distinct.result
index c615817f52d..2746f5a09f4 100644
--- a/mysql-test/r/sum_distinct.result
+++ b/mysql-test/r/sum_distinct.result
@@ -95,3 +95,15 @@ SELECT SUM(DISTINCT id % 11) FROM t1;
 SUM(DISTINCT id % 11)
 55
 DROP TABLE t1;
+#
+# Bug #777654: empty subselect in FROM clause returning  
+#              SUM(DISTINCT) over non-nullable field 
+#
+CREATE TABLE t1 (a int NOT NULL) ;
+SELECT SUM(DISTINCT a) FROM t1;
+SUM(DISTINCT a)
+NULL
+SELECT * FROM (SELECT SUM(DISTINCT a) FROM t1) AS t;
+SUM(DISTINCT a)
+NULL
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result b/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result
index a19809366ae..6071587e888 100644
--- a/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result
+++ b/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result
@@ -48,9 +48,9 @@ ON orgs.org_id=sa_opportunities.org_id
 LEFT JOIN bug30243_2 contacts
 ON orgs.org_id=contacts.org_id ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	orgs	index	NULL	org_id	4	NULL	128	Using index
-1	SIMPLE	sa_opportunities	ref	org_id	org_id	5	test.orgs.org_id	1	Using index
-1	SIMPLE	contacts	ref	contacts$org_id	contacts$org_id	5	test.orgs.org_id	1	Using index
+1	SIMPLE	orgs	index	NULL	org_id	4	NULL	#	Using index
+1	SIMPLE	sa_opportunities	ref	org_id	org_id	5	test.orgs.org_id	#	Using index
+1	SIMPLE	contacts	ref	contacts$org_id	contacts$org_id	5	test.orgs.org_id	#	Using index
 select @@innodb_stats_method;
 @@innodb_stats_method
 nulls_ignored
diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test b/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test
index 458c2967e19..da490589400 100644
--- a/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test
+++ b/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test
@@ -140,6 +140,7 @@ analyze table bug30243_3;
 
 # Following query plan shows that we get the correct rows per
 # unique value (should be approximately 1 row per value)
+--replace_column 9 #
 explain SELECT COUNT(*), 0
 	FROM bug30243_1 orgs
 	LEFT JOIN bug30243_3 sa_opportunities
diff --git a/mysql-test/suite/pbxt/r/ps_11bugs.result b/mysql-test/suite/pbxt/r/ps_11bugs.result
index dd09e9d14f3..5a2743436e1 100644
--- a/mysql-test/suite/pbxt/r/ps_11bugs.result
+++ b/mysql-test/suite/pbxt/r/ps_11bugs.result
@@ -121,8 +121,8 @@ insert into t1 values (1);
 explain select * from t1 where 3 in (select (1+1) union select 1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	1	Using index
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 select * from t1 where 3 in (select (1+1) union select 1);
 a
diff --git a/mysql-test/suite/pbxt/r/select.result b/mysql-test/suite/pbxt/r/select.result
index 58a1a326f6b..a32f06da7df 100644
--- a/mysql-test/suite/pbxt/r/select.result
+++ b/mysql-test/suite/pbxt/r/select.result
@@ -2788,10 +2788,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 explain select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	index	key1	key1	5	NULL	4	Using where; Using index
 explain select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	t1	index	key1	key1	5	NULL	4	Using where; Using index
 select max(key1) from t1 where key1 <= 0.6158;
 max(key1)
 0.615800023078918
@@ -2810,10 +2810,10 @@ max(key1)	min(key2)
 0.615800023078918	1.37619996070862
 select max(key1) from t1 where key1 <= 0.6158 and rand() + 0.5 >= 0.5;
 max(key1)
-0.615800023078918
+0.384499996900558
 select min(key1) from t1 where key1 >= 0.3762 and rand() + 0.5 >= 0.5;
 min(key1)
-0.376199990510941
+0.384499996900558
 DROP TABLE t1,t2;
 CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);
 INSERT INTO t1 VALUES (10);
diff --git a/mysql-test/suite/plugins/r/unix_socket.result b/mysql-test/suite/plugins/r/unix_socket.result
new file mode 100644
index 00000000000..45bf608cc93
--- /dev/null
+++ b/mysql-test/suite/plugins/r/unix_socket.result
@@ -0,0 +1,30 @@
+install plugin unix_socket soname 'auth_socket.so';
+#
+# with named user
+#
+create user USER identified via unix_socket;
+#
+# name match = ok
+#
+select user(), current_user(), database();
+user()	current_user()	database()
+USER@localhost	USER@%	test
+#
+# name does not match = failure
+#
+drop user USER;
+#
+# and now with anonymous user
+#
+grant SELECT ON test.* TO '' identified via unix_socket;
+#
+# name match = ok
+#
+select user(), current_user(), database();
+user()	current_user()	database()
+USER@localhost	@%	test
+#
+# name does not match = failure
+#
+delete from mysql.user where user='';
+uninstall plugin unix_socket;
diff --git a/mysql-test/suite/plugins/t/unix_socket.test b/mysql-test/suite/plugins/t/unix_socket.test
new file mode 100644
index 00000000000..fc2e6c5b3c6
--- /dev/null
+++ b/mysql-test/suite/plugins/t/unix_socket.test
@@ -0,0 +1,56 @@
+--source include/not_embedded.inc
+
+if (!$AUTH_SOCKET_SO) {
+  skip No auth_socket plugin;
+}
+
+let $plugindir=`SELECT @@global.plugin_dir`;
+
+eval install plugin unix_socket soname '$AUTH_SOCKET_SO';
+
+--echo #
+--echo # with named user
+--echo #
+
+--replace_result $USER USER
+eval create user $USER identified via unix_socket;
+
+--write_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt
+--replace_result $USER USER
+select user(), current_user(), database();
+EOF
+
+--echo #
+--echo # name match = ok
+--echo #
+--exec $MYSQL_TEST -u $USER --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/peercred_test.txt
+
+--echo #
+--echo # name does not match = failure
+--echo #
+--error 1
+--exec $MYSQL_TEST -u foobar --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/peercred_test.txt
+
+--replace_result $USER USER
+eval drop user $USER;
+
+--echo #
+--echo # and now with anonymous user
+--echo #
+grant SELECT ON test.* TO '' identified via unix_socket;
+--echo #
+--echo # name match = ok
+--echo #
+--exec $MYSQL_TEST -u $USER --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/peercred_test.txt
+
+--echo #
+--echo # name does not match = failure
+--echo #
+--error 1
+--exec $MYSQL_TEST -u foobar --plugin-dir=$plugindir < $MYSQLTEST_VARDIR/tmp/peercred_test.txt
+
+# restoring mysql.user to the original state.
+delete from mysql.user where user='';
+uninstall plugin unix_socket;
+--remove_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt
+
diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result
index 3ad15009c76..ea3cbd3bc11 100644
--- a/mysql-test/suite/vcol/r/vcol_select_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result
@@ -265,3 +265,33 @@ NULL
 explain select sum(c) from t1 group by b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+#
+# Bug #806057: join with USING over a virtual column 
+#
+CREATE TABLE t1 (b int);
+INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154);
+CREATE TABLE t2 (a int, b int AS (a) VIRTUAL);
+INSERT INTO t2 VALUES (187,187), (9,9), (187,187);
+Warnings:
+Warning	1906	The value specified for computed column 'b' in table 't2' ignored
+Warning	1906	The value specified for computed column 'b' in table 't2' ignored
+Warning	1906	The value specified for computed column 'b' in table 't2' ignored
+EXPLAIN EXTENDED
+SELECT * FROM t1 JOIN t2 USING (b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b` = `test`.`t2`.`b`)
+SELECT * FROM t1 JOIN t2 USING (b);
+b	a
+EXPLAIN EXTENDED
+SELECT * FROM t1 NATURAL JOIN t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`b` = `test`.`t2`.`b`)
+SELECT * FROM t1 NATURAL JOIN t2;
+b	a
+DROP TABLE t1,t2;
diff --git a/mysql-test/suite/vcol/t/vcol_select_myisam.test b/mysql-test/suite/vcol/t/vcol_select_myisam.test
index 855e02ac113..c14faba576d 100644
--- a/mysql-test/suite/vcol/t/vcol_select_myisam.test
+++ b/mysql-test/suite/vcol/t/vcol_select_myisam.test
@@ -48,3 +48,23 @@ eval SET @@session.storage_engine = 'MyISAM';
 #------------------------------------------------------------------------------#
 # Cleanup
 --source suite/vcol/inc/vcol_cleanup.inc
+
+--echo #
+--echo # Bug #806057: join with USING over a virtual column 
+--echo #
+
+CREATE TABLE t1 (b int);
+INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154);
+
+CREATE TABLE t2 (a int, b int AS (a) VIRTUAL);
+INSERT INTO t2 VALUES (187,187), (9,9), (187,187);
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 JOIN t2 USING (b);
+SELECT * FROM t1 JOIN t2 USING (b);
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 NATURAL JOIN t2;
+SELECT * FROM t1 NATURAL JOIN t2;
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index de0eac10927..f6091883168 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 #
@@ -1292,5 +1339,3 @@ set @@optimizer_switch=@save_optimizer_switch;
 --echo #
 DROP TABLE IF EXISTS t1,t2,t3,empty1;
 
-###
---echo End of 6.0 tests
diff --git a/mysql-test/t/join_outer_innodb.test b/mysql-test/t/join_outer_innodb.test
index 40add7f488f..565bb72b152 100644
--- a/mysql-test/t/join_outer_innodb.test
+++ b/mysql-test/t/join_outer_innodb.test
@@ -24,3 +24,18 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
   WHERE t1.name LIKE 'A%' OR FALSE;
 
 DROP TABLE t1,t2;
+
+#
+# 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/order_by.test b/mysql-test/t/order_by.test
index c7a958b293a..425bef6c679 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -1560,3 +1560,32 @@ DROP TABLE t1;
 select 1 order by max(1) + min(1);
 
 --echo End of 5.1 tests
+
+--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/subselect.test b/mysql-test/t/subselect.test
index b92fb6f961c..b4e79416c40 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -3521,6 +3521,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.
@@ -4207,8 +4209,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
 #
@@ -4568,11 +4568,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 );
@@ -4625,32 +4755,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#11764086: Null left operand to NOT IN in WHERE clause
 --echo # behaves differently than real NULL
@@ -4701,25 +4805,6 @@ DROP TABLE parent, child;
 --echo # End of test for bug#11764086.
 
 --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);
-
---echo
-set @tmp_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='derived_merge=off,derived_with_keys=off';
-EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
-set optimizer_switch=@tmp_optimizer_switch;
---echo
-EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
-
---echo
-DROP TABLE t1;
-
---echo #
 --echo # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
 --echo #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
 --echo #
@@ -4749,30 +4834,6 @@ SELECT 1 FROM t1 WHERE a =
 DROP TABLE t1, t2;
 
 --echo #
---echo # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
---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');
-
-let $query=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 );
-
-eval $query;
-eval explain $query;
-
-DROP TABLE t1,t2;
-
---echo #
 --echo # LP bug #826279: assertion failure with GROUP BY a result of subquery
 --echo #
 
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/view.test b/mysql-test/t/view.test
index 821bbe055e6..d4d5f2acc0b 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3980,7 +3980,6 @@ drop table t1,t2;
 --echo # -----------------------------------------------------------------
 --echo # -- End of 5.1 tests.
 --echo # -----------------------------------------------------------------
-
 --echo #
 --echo # Bug #59696 Optimizer does not use equalities for conditions over view 
 --echo #
diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp
index 871bd6e55b8..0c75b4facab 100644
--- a/mysql-test/valgrind.supp
+++ b/mysql-test/valgrind.supp
@@ -395,133 +395,19 @@
    fun:__libc_start_main
 }
 
-{
-   dlclose memory loss from udf_free
-   Memcheck:Leak
-   fun:calloc
-   fun:_dlerror_run
-   fun:dlclose
-   fun:_Z8udf_freev
-}
-
-{
-   dlsym memory loss from udf_free on SuSE 11.1 x64 variant 2
-   Memcheck:Leak
-   fun:calloc
-   obj:/lib*/ld-*.so
-   fun:dlclose
-   fun:udf_free
-}
-
-{
-   dlclose memory loss from plugin variant 1
-   Memcheck:Leak
-   fun:calloc
-   fun:_dlerror_run
-   fun:dlclose
-   fun:plugin_dl_del(st_mysql_lex_string const*)
-}
-
-{
-   dlclose memory loss from plugin variant 2
-   Memcheck:Leak
-   fun:malloc
-   fun:_dl_close_worker
-   fun:_dl_close
-   fun:_dl_catch_error
-   fun:_dlerror_run
-   fun:dlclose
-   fun:_Z15free_plugin_memP12st_plugin_dl
-   fun:_Z13plugin_dl_delPK19st_mysql_lex_string
-}
-
-{
-   dlclose memory loss from plugin variant 3
-   Memcheck:Leak
-   fun:malloc
-   fun:_dl_scope_free
-   fun:_dl_close_worker
-   fun:_dl_close
-   fun:_dl_catch_error
-   fun:_dlerror_run
-   fun:dlclose
-   fun:_Z15free_plugin_memP12st_plugin_dl
-   fun:_Z13plugin_dl_delPK19st_mysql_lex_string
-}
-
-{
-   dlclose memory loss from plugin variant 4
-   Memcheck:Leak
-   fun:malloc
-   obj:/lib*/ld-*.so
-   obj:/lib*/ld-*.so
-   obj:/lib*/ld-*.so
-   obj:/lib*/libdl-*.so
-   fun:dlclose
-   fun:_ZL15free_plugin_memP12st_plugin_dl
-   fun:_ZL13plugin_dl_delPK19st_mysql_lex_string
-}
-
-{
-   dlclose memory loss from plugin variant 5
-   Memcheck:Leak
-   fun:malloc
-   obj:/lib*/ld-*.so
-   obj:/lib*/ld-*.so
-   obj:/lib*/ld-*.so
-   obj:/lib*/ld-*.so
-   obj:/lib*/libdl-*.so
-   fun:dlclose
-}
-
-{
-   dlclose memory loss from plugin variant 6, seen on Ubuntu Jaunty i686
-   Memcheck:Leak
-   fun:malloc
-   fun:_dl_scope_free
-   fun:_dl_close_worker
-   fun:_dl_close
-   fun:dlclose_doit
-   fun:_dl_catch_error
-   fun:_dlerror_run
-   fun:dlclose
-   fun:_ZL15free_plugin_memP12st_plugin_dl
-   fun:_ZL13plugin_dl_delPK19st_mysql_lex_string
-}
-
-{
-   dlclose memory loss from plugin variant 7, seen on Ubuntu Jaunty i686
-   Memcheck:Leak
-   fun:malloc
-   fun:_dl_close_worker
-   fun:_dl_close
-   fun:dlclose_doit
-   fun:_dl_catch_error
-   fun:_dlerror_run
-   fun:dlclose
-   fun:_ZL15free_plugin_memP12st_plugin_dl
-   fun:_ZL13plugin_dl_delPK19st_mysql_lex_string
-}
 
+#
+#  dlclose can allocate memory for error message, the memory will be
+#  freed by dlerror or other dl* function.
+#
 {
-   dlclose memory loss from plugin variant 8
+   memory "loss" from dlclose error messages
    Memcheck:Leak
-   fun:calloc
-   fun:_dlerror_run
+   fun:*alloc
+   ...
    fun:dlclose
-   fun:_Z15free_plugin_memP12st_plugin_dl
-   fun:_Z13plugin_dl_delPK19st_mysql_lex_string
 }
 
-{
-   dlclose memory loss from plugin variant 9
-   Memcheck:Leak
-   fun:calloc
-   fun:_dlerror_run
-   fun:dlclose
-   fun:_ZL15free_plugin_memP12st_plugin_dl
-   fun:_ZL13plugin_dl_delPK19st_mysql_lex_string
-}
 
 {
    dlclose memory loss from plugin variant 10