merge

25 files changed, 349 insertions, 201 deletions

diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result
index b4da56cda6f..6b20add1bed 100644
--- a/mysql-test/r/csv.result
+++ b/mysql-test/r/csv.result
@@ -5415,6 +5415,19 @@ foo
 
 
 DROP TABLE t1;
+CREATE TABLE t1 ( b TEXT NOT NULL ) ENGINE=MYISAM;
+INSERT INTO t1 VALUES ('x'),('y');
+CREATE TABLE t2 ( a VARCHAR(1) NOT NULL ) ENGINE=CSV;
+INSERT INTO t2 VALUES ('r'),('t');
+SELECT * FROM t2 ORDER BY a;
+a
+r
+t
+SELECT * FROM t1 LEFT JOIN t2 ON ( b = a );
+b	a
+x	NULL
+y	NULL
+drop table t1,t2;
 #
 # Test for the following cases
 # 1) integers and strings enclosed in quotes
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 5a3d547d6b3..32081c13376 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2007,5 +2007,44 @@ a	b	gc
 SET SESSION optimizer_switch= @save_optimizer_switch;
 DROP VIEW v;
 DROP TABLE t1,t2;
+#
+# LP BUG#968720 crash due to converting to materialized and
+# natural join made only once
+#
+SET @save968720_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+CREATE TABLE t1 (a int, INDEX(a));
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (a int, INDEX(a));
+INSERT INTO t2 VALUES (1), (2);
+INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN
+t2 AS s2;
+SELECT * FROM t1;
+a
+1
+1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (1);
+PREPARE stmt FROM "
+INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN
+t2 AS s2;
+";
+EXECUTE stmt;
+SELECT * FROM t1;
+a
+1
+1
+EXECUTE stmt;
+SELECT * FROM t1;
+a
+1
+1
+1
+1
+drop table t1,t2;
+set optimizer_switch=@save968720_optimizer_switch;
+#
+# end of 5.3 tests
+#
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index d4a071d2d63..bfc6b72caa1 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -885,18 +885,12 @@ DROP TABLE t1;
 select last_day('2005-00-00');
 last_day('2005-00-00')
 NULL
-Warnings:
-Warning	1292	Incorrect datetime value: '2005-00-00'
 select last_day('2005-00-01');
 last_day('2005-00-01')
 NULL
-Warnings:
-Warning	1292	Incorrect datetime value: '2005-00-01'
 select last_day('2005-01-00');
 last_day('2005-01-00')
-NULL
-Warnings:
-Warning	1292	Incorrect datetime value: '2005-01-00'
+2005-01-31
 select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')),
 monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m'));
 monthname(str_to_date(null, '%m'))	monthname(str_to_date(null, '%m'))	monthname(str_to_date(1, '%m'))	monthname(str_to_date(0, '%m'))
@@ -1435,7 +1429,7 @@ NULL
 #
 DO WEEK((DATE_ADD((CAST(0 AS DATE)), INTERVAL 1 YEAR_MONTH)), 5);
 Warnings:
-Warning	1292	Truncated incorrect date value: '0000-00-00'
+Warning	1292	Incorrect datetime value: '0'
 #
 # BUG#13458237 INCONSISTENT HANDLING OF INVALIDE DATES WITH ZERO DAY
 # SIMILAR TO '2009-10-00' 
@@ -1469,9 +1463,9 @@ TO_DAYs(DATE'2009-10-00'),
 TO_DAYs(STR_TO_DATE('200910','%Y%m'));
 DATE('20091000')	2009-10-00
 STR_TO_DATE('200910','%Y%m')	2009-10-00
-LAST_DAY('2009-10-00')	NULL
-LAST_DAY(DATE('2009-10-00'))	NULL
-LAST_DAY(DATE'2009-10-00')	NULL
+LAST_DAY('2009-10-00')	2009-10-31
+LAST_DAY(DATE('2009-10-00'))	2009-10-31
+LAST_DAY(DATE'2009-10-00')	2009-10-31
 LAST_DAY(STR_TO_DATE('200910','%Y%m'))	2009-10-31
 WEEK('2009-10-00')	NULL
 WEEK(DATE('2009-10-00'))	NULL
@@ -1499,18 +1493,9 @@ Code	1292
 Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '2009-10-00'
-Level	Warning
-Code	1292
-Message	Incorrect datetime value: '2009-10-00'
-Level	Warning
-Code	1292
 Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '2009-10-00'
-Level	Warning
-Code	1292
 Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1411
@@ -1520,7 +1505,7 @@ Code	1292
 Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '2009-10-00'
+Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1292
 Message	Incorrect datetime value: '2009-10-00'
@@ -1532,7 +1517,7 @@ Code	1292
 Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '2009-10-00'
+Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1292
 Message	Incorrect datetime value: '2009-10-00'
@@ -1544,7 +1529,7 @@ Code	1292
 Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '2009-10-00'
+Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1292
 Message	Incorrect datetime value: '2009-10-00'
@@ -1556,7 +1541,7 @@ Code	1292
 Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '2009-10-00'
+Message	Incorrect datetime value: '2009-10-00'
 Level	Warning
 Code	1292
 Message	Incorrect datetime value: '2009-10-00'
@@ -1592,9 +1577,9 @@ TO_DAYs(DATE'0000-01-00'),
 TO_DAYs(STR_TO_DATE('000001','%Y%m'));
 DATE('00000100')	0000-01-00
 STR_TO_DATE('000001','%Y%m')	0000-01-00
-LAST_DAY('0000-01-00')	NULL
-LAST_DAY(DATE('0000-01-00'))	NULL
-LAST_DAY(DATE'0000-01-00')	NULL
+LAST_DAY('0000-01-00')	0000-01-31
+LAST_DAY(DATE('0000-01-00'))	0000-01-31
+LAST_DAY(DATE'0000-01-00')	0000-01-31
 LAST_DAY(STR_TO_DATE('000001','%Y%m'))	0000-01-31
 WEEK('0000-01-00')	NULL
 WEEK(DATE('0000-01-00'))	NULL
@@ -1622,18 +1607,9 @@ Code	1292
 Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '0000-01-00'
-Level	Warning
-Code	1292
-Message	Incorrect datetime value: '0000-01-00'
-Level	Warning
-Code	1292
 Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '0000-01-00'
-Level	Warning
-Code	1292
 Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1411
@@ -1643,7 +1619,7 @@ Code	1292
 Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '0000-01-00'
+Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1292
 Message	Incorrect datetime value: '0000-01-00'
@@ -1655,7 +1631,7 @@ Code	1292
 Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '0000-01-00'
+Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1292
 Message	Incorrect datetime value: '0000-01-00'
@@ -1667,7 +1643,7 @@ Code	1292
 Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '0000-01-00'
+Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1292
 Message	Incorrect datetime value: '0000-01-00'
@@ -1679,7 +1655,7 @@ Code	1292
 Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1292
-Message	Truncated incorrect date value: '0000-01-00'
+Message	Incorrect datetime value: '0000-01-00'
 Level	Warning
 Code	1292
 Message	Incorrect datetime value: '0000-01-00'
@@ -1912,7 +1888,7 @@ select timestampadd(week, 1, date("0000-00-00"));
 timestampadd(week, 1, date("0000-00-00"))
 NULL
 Warnings:
-Warning	1292	Truncated incorrect date value: '0000-00-00'
+Warning	1292	Incorrect datetime value: '0000-00-00'
 drop table t1;
 create table t1 (f2 time not null, f3 datetime, f4 int not null, f5 timestamp);
 insert ignore t1 values ('04:38:11','0000-00-00 00:00:00',0,'0000-00-00 00:00:00');
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index d926ebcfc2b..fa8471eb1cb 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5261,8 +5261,8 @@ SET join_cache_level=0;
 EXPLAIN
 SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	
 3	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
@@ -5480,8 +5480,8 @@ EXPLAIN
 SELECT * FROM t1 WHERE t1.i IN 
 (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 SELECT * FROM t1 WHERE t1.i IN 
@@ -5494,8 +5494,8 @@ EXPLAIN
 SELECT * FROM t1 WHERE t1.i IN 
 (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
 SELECT * FROM t1 WHERE t1.i IN 
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index da2f59f375e..fc7e229603c 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -148,13 +148,13 @@ insert INTO t1 VALUES (110);
 ERROR HY000: Table has no partition for value 110
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 90;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1		ALL	NULL	NULL	NULL	NULL	0	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 90;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1		ALL	NULL	NULL	NULL	NULL	0	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 90;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1		ALL	NULL	NULL	NULL	NULL	0	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 89;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p90	ALL	NULL	NULL	NULL	NULL	3	Using where
@@ -163,16 +163,16 @@ id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p90	ALL	NULL	NULL	NULL	NULL	3	Using where
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 89;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1		ALL	NULL	NULL	NULL	NULL	0	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 100;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1		ALL	NULL	NULL	NULL	NULL	0	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 100;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1		ALL	NULL	NULL	NULL	NULL	0	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 100;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1		ALL	NULL	NULL	NULL	NULL	0	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 DROP TABLE t1;
 #
 # Bug#50104: Partitioned table with just 1 partion works with fk
diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result
index b59ec858e98..b1e4cb669ed 100644
--- a/mysql-test/r/strict.result
+++ b/mysql-test/r/strict.result
@@ -260,11 +260,11 @@ INSERT INTO t1 (col2) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
 INSERT INTO t1 (col3) VALUES (CAST('2004-10-15 10:15' AS DATETIME));
 INSERT INTO t1 (col1) VALUES(CAST('0000-10-31' AS DATE));
 INSERT INTO t1 (col1) VALUES(CAST('2004-10-0' AS DATE));
-ERROR 22007: Truncated incorrect date value: '2004-10-00'
+ERROR 22007: Incorrect datetime value: '2004-10-0'
 INSERT INTO t1 (col1) VALUES(CAST('2004-0-10' AS DATE));
-ERROR 22007: Truncated incorrect date value: '2004-00-10'
+ERROR 22007: Incorrect datetime value: '2004-0-10'
 INSERT INTO t1 (col1) VALUES(CAST('0000-00-00' AS DATE));
-ERROR 22007: Truncated incorrect date value: '0000-00-00'
+ERROR 22007: Incorrect datetime value: '0000-00-00'
 INSERT INTO t1 (col2) VALUES(CAST('0000-10-31 15:30' AS DATETIME));
 INSERT INTO t1 (col2) VALUES(CAST('2004-10-0 15:30' AS DATETIME));
 ERROR 22007: Incorrect datetime value: '2004-10-0 15:30'
@@ -287,13 +287,13 @@ INSERT INTO t1 (col2) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
 INSERT INTO t1 (col3) VALUES (CONVERT('2004-10-15 10:15',DATETIME));
 INSERT INTO t1 (col1) VALUES(CONVERT('0000-10-31' , DATE));
 INSERT INTO t1 (col1) VALUES(CONVERT('2004-10-0' , DATE));
-ERROR 22007: Truncated incorrect date value: '2004-10-00'
+ERROR 22007: Incorrect datetime value: '2004-10-0'
 INSERT INTO t1 (col1) VALUES(CONVERT('2004-0-10' , DATE));
-ERROR 22007: Truncated incorrect date value: '2004-00-10'
+ERROR 22007: Incorrect datetime value: '2004-0-10'
 INSERT INTO t1 (col1) VALUES('2004-0-10');
 ERROR 22007: Incorrect date value: '2004-0-10' for column 'col1' at row 1
 INSERT INTO t1 (col1) VALUES(CONVERT('0000-00-00',DATE));
-ERROR 22007: Truncated incorrect date value: '0000-00-00'
+ERROR 22007: Incorrect datetime value: '0000-00-00'
 INSERT INTO t1 (col2) VALUES(CONVERT('0000-10-31 15:30',DATETIME));
 INSERT INTO t1 (col2) VALUES(CONVERT('2004-10-0 15:30',DATETIME));
 ERROR 22007: Incorrect datetime value: '2004-10-0 15:30'
@@ -1137,9 +1137,9 @@ ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
 insert into t1 values (0.0,0.0,0.0);
 ERROR 22007: Incorrect date value: '0' for column 'col1' at row 1
 insert into t1 (col1) values (convert('0000-00-00',date));
-ERROR 22007: Truncated incorrect date value: '0000-00-00'
+ERROR 22007: Incorrect datetime value: '0000-00-00'
 insert into t1 (col1) values (cast('0000-00-00' as date));
-ERROR 22007: Truncated incorrect date value: '0000-00-00'
+ERROR 22007: Incorrect datetime value: '0000-00-00'
 set sql_mode='no_zero_date';
 insert into t1 values (0,0,0);
 Warnings:
@@ -1164,7 +1164,7 @@ Warnings:
 Warning	1264	Out of range value for column 'col1' at row 1
 insert ignore into t1 (col1) values (cast('0000-00-00' as date));
 Warnings:
-Warning	1292	Truncated incorrect date value: '0000-00-00'
+Warning	1292	Incorrect datetime value: '0000-00-00'
 insert into t1 select * from t1;
 ERROR 22007: Incorrect date value: '0000-00-00' for column 'col1' at row 1
 alter table t1 modify col1 datetime;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 0c071f1adfc..26faf465749 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -5595,8 +5595,8 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
 FROM it2,it3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	24	Using where
-1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	it2	ALL	NULL	NULL	NULL	NULL	4	
 2	MATERIALIZED	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
 DROP TABLE IF EXISTS ot1, ot4, it2, it3;
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result
index d5d75b68628..014ff5b5810 100644
--- a/mysql-test/r/subselect3.result
+++ b/mysql-test/r/subselect3.result
@@ -1144,9 +1144,9 @@ insert into t4 select a from t3;
 explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
 and t4.pk=t1.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using index condition; Using where; LooseScan
-1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; FirstMatch(t1)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using where
+1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; FirstMatch(t3)
 drop table t1, t3, t4;
 set @@optimizer_switch=@save_optimizer_switch;
 create table t1 (a int) as select * from t0 where a < 5;
@@ -1155,11 +1155,11 @@ set @@optimizer_switch='firstmatch=off,materialization=off';
 set @@max_heap_table_size= 16384;
 explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Start temporary
-1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
-1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	D	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Start temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	D	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (flat, BNL join)
 flush status;
 select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
 count(*)
diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result
index d794d35cf1a..a62048db506 100644
--- a/mysql-test/r/subselect3_jcl6.result
+++ b/mysql-test/r/subselect3_jcl6.result
@@ -1154,9 +1154,9 @@ insert into t4 select a from t3;
 explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
 and t4.pk=t1.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using index condition; Using where; LooseScan
-1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; FirstMatch(t1)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using where
+1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; FirstMatch(t3)
 drop table t1, t3, t4;
 set @@optimizer_switch=@save_optimizer_switch;
 create table t1 (a int) as select * from t0 where a < 5;
@@ -1165,11 +1165,11 @@ set @@optimizer_switch='firstmatch=off,materialization=off';
 set @@max_heap_table_size= 16384;
 explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
-1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
-1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (incremental, BNL join)
-1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (incremental, BNL join)
-1	PRIMARY	D	hash_ALL	NULL	#hash#$hj	5	test.E.a	10	Using where; End temporary; Using join buffer (incremental, BNLH join)
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
+1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary; Using join buffer (incremental, BNL join)
+1	PRIMARY	D	hash_ALL	NULL	#hash#$hj	5	test.E.a	10	Using where; Using join buffer (incremental, BNLH join)
+1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer (incremental, BNL join)
 flush status;
 select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E where a+1 < 10000 + A.a + B.a +C.a+D.a);
 count(*)
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index ecafe57f47a..8a43852a159 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -5597,9 +5597,9 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
 FROM it2,it3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where; Start temporary; Using join buffer (flat, BNL join)
-1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
-1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using join buffer (flat, BNL join)
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(ot4)
 DROP TABLE IF EXISTS ot1, ot4, it2, it3;
 #
 # Bug#729039: NULL keys used to evaluate subquery
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index e85f2ccf1e1..d8169de9130 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -5601,8 +5601,8 @@ WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
 FROM it2,it3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	24	Using where
-1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	ot4	ALL	NULL	NULL	NULL	NULL	8	Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	it2	ALL	NULL	NULL	NULL	NULL	4	
 2	MATERIALIZED	it3	ALL	NULL	NULL	NULL	NULL	6	Using join buffer (flat, BNL join)
 DROP TABLE IF EXISTS ot1, ot4, it2, it3;
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index c767ee1663e..ca834b21df5 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -760,16 +760,16 @@ explain extended
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; FirstMatch(t1)
 3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
 show warnings;
 Level	Code	Message
 Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 a
@@ -2168,10 +2168,10 @@ INSERT INTO t5 VALUES (7,0),(9,0);
 explain
 SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t5	index	a	a	10	NULL	2	Using where; Using index; LooseScan
+1	PRIMARY	t5	index	a	a	10	NULL	2	Using index; Start temporary
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ref	b	b	5	test.t5.b	2	Using where; FirstMatch(t5)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	15	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	15	Using where; End temporary; Using join buffer (flat, BNL join)
 SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
 a
 0
@@ -2250,11 +2250,11 @@ alias1.c IN (SELECT SQ3_alias1.b
 FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2)
 LIMIT 100;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	
+1	PRIMARY	SQ3_alias1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	SQ3_alias2	index	NULL	PRIMARY	4	NULL	20	Using index; End temporary
 1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
-1	PRIMARY	SQ3_alias1	ALL	NULL	NULL	NULL	NULL	20	Using where; Start temporary
-1	PRIMARY	SQ3_alias2	index	NULL	PRIMARY	4	NULL	20	Using index; End temporary
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	20	
 create table t3 as
 SELECT 
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 835403bb5b1..97e36cd705c 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -49,9 +49,9 @@ a	b
 19	14
 explain select * from t2 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ref	b	b	5	test.t1.a	2	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t2 where b in (select a from t1);
 a	b
 1	1
@@ -69,9 +69,9 @@ insert into t3 select a,a, a,a,a from t0;
 insert into t3 select a,a, a+100,a+100,a+100 from t0;
 explain select * from t3 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t3	ref	b	b	5	test.t1.a	1	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t3 where b in (select a from t1);
 a	b	pk1	pk2	pk3
 1	1	1	1	1
@@ -95,13 +95,17 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
 from t0 A, t0 B where B.a <5;
 explain select * from t3 where b in (select a from t0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	10	
-1	PRIMARY	t3	ref	b	b	5	test.t0.a	1	
-2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	#	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	#	
+2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	#	
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 a	b	pk1	pk2
 0	0	0	0
 1	1	1	1
+10	10	10	10
+11	11	11	11
+12	12	12	12
+13	13	13	13
 2	2	2	2
 3	3	3	3
 4	4	4	4
@@ -110,10 +114,6 @@ a	b	pk1	pk2
 7	7	7	7
 8	8	8	8
 9	9	9	9
-10	10	10	10
-11	11	11	11
-12	12	12	12
-13	13	13	13
 set join_buffer_size= @save_join_buffer_size;
 set max_heap_table_size= @save_max_heap_table_size;
 explain select * from t1 where a in (select b from t2);
@@ -745,8 +745,8 @@ c2 in (select 1 from t3, t2) and
 c1 in (select convert(c6,char(1)) from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t2)
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
 drop table t2, t3;
 #
@@ -991,8 +991,8 @@ WHERE
 alias2.b = alias1.a AND 
 (alias1.b >= alias1.a OR alias2.b = 'z'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
-1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	
+1	PRIMARY	t2	ALL	a	NULL	NULL	NULL	38	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
 2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where
 SELECT * FROM t2 
diff --git a/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff b/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff
index 30df3c42a0d..c32f52fe0c2 100644
--- a/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff
+++ b/mysql-test/r/subselect_sj2_jcl6,innodb_plugin.rdiff
@@ -1,29 +1,8 @@
---- r/subselect_sj2_jcl6.result	2012-03-28 20:10:41.000000000 +0200
-+++ r/subselect_sj2_jcl6,innodb_plugin.reject	2012-03-28 20:21:14.000000000 +0200
-@@ -81,7 +81,7 @@
- explain select * from t3 where b in (select a from t1);
- id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
- 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
--1	PRIMARY	t3	ref	b	b	5	test.t1.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-+1	PRIMARY	t3	ref	b	b	5	test.t1.a	1	
- 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
- select * from t3 where b in (select a from t1);
- a	b	pk1	pk2	pk3
-@@ -107,7 +107,7 @@
- explain select * from t3 where b in (select a from t0);
- id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
- 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	10	
--1	PRIMARY	t3	ref	b	b	5	test.t0.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-+1	PRIMARY	t3	ref	b	b	5	test.t0.a	1	
- 2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
- select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
- a	b	pk1	pk2
-@@ -1006,9 +1006,9 @@
- (alias1.b >= alias1.a OR alias2.b = 'z'));
- id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
- 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
--1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-+1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	
+--- r/subselect_sj2_jcl6.result	2012-04-07 12:45:03.000000000 +0200
++++ r/subselect_sj2_jcl6,innodb_plugin.reject	2012-04-07 13:10:38.000000000 +0200
+@@ -1009,7 +1009,7 @@
+ 1	PRIMARY	t2	ALL	a	NULL	NULL	NULL	38	
+ 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
  2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
 -2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 +2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index c4c8a858b68..e4030b8138b 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -60,9 +60,9 @@ a	b
 19	14
 explain select * from t2 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ref	b	b	5	test.t1.a	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t2 where b in (select a from t1);
 a	b
 1	1
@@ -80,9 +80,9 @@ insert into t3 select a,a, a,a,a from t0;
 insert into t3 select a,a, a+100,a+100,a+100 from t0;
 explain select * from t3 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t3	ref	b	b	5	test.t1.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t3 where b in (select a from t1);
 a	b	pk1	pk2	pk3
 1	1	1	1	1
@@ -106,13 +106,17 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
 from t0 A, t0 B where B.a <5;
 explain select * from t3 where b in (select a from t0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	10	
-1	PRIMARY	t3	ref	b	b	5	test.t0.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	#	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	#	
+2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	#	
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 a	b	pk1	pk2
 0	0	0	0
 1	1	1	1
+10	10	10	10
+11	11	11	11
+12	12	12	12
+13	13	13	13
 2	2	2	2
 3	3	3	3
 4	4	4	4
@@ -121,10 +125,6 @@ a	b	pk1	pk2
 7	7	7	7
 8	8	8	8
 9	9	9	9
-10	10	10	10
-11	11	11	11
-12	12	12	12
-13	13	13	13
 set join_buffer_size= @save_join_buffer_size;
 set max_heap_table_size= @save_max_heap_table_size;
 explain select * from t1 where a in (select b from t2);
@@ -759,9 +759,10 @@ c2 in (select 1 from t3, t2) and
 c1 in (select convert(c6,char(1)) from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	Using where
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2); Using join buffer (incremental, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch((sj-nest)); Using join buffer (incremental, BNL join)
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	
 drop table t2, t3;
 #
 # BUG#761598: InnoDB: Error: row_search_for_mysql() is called without ha_innobase::external_lock() in maria-5.3
@@ -1005,8 +1006,8 @@ WHERE
 alias2.b = alias1.a AND 
 (alias1.b >= alias1.a OR alias2.b = 'z'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
-1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	PRIMARY	t2	ALL	a	NULL	NULL	NULL	38	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
 2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 SELECT * FROM t2 
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index b535f37ee90..f69befc8608 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -51,9 +51,9 @@ a	b
 19	14
 explain select * from t2 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ref	b	b	5	test.t1.a	2	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t2 where b in (select a from t1);
 a	b
 1	1
@@ -71,9 +71,9 @@ insert into t3 select a,a, a,a,a from t0;
 insert into t3 select a,a, a+100,a+100,a+100 from t0;
 explain select * from t3 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t3	ref	b	b	5	test.t1.a	1	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t3 where b in (select a from t1);
 a	b	pk1	pk2	pk3
 1	1	1	1	1
@@ -97,13 +97,17 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
 from t0 A, t0 B where B.a <5;
 explain select * from t3 where b in (select a from t0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	10	
-1	PRIMARY	t3	ref	b	b	5	test.t0.a	1	
-2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	#	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	#	
+2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	#	
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 a	b	pk1	pk2
 0	0	0	0
 1	1	1	1
+10	10	10	10
+11	11	11	11
+12	12	12	12
+13	13	13	13
 2	2	2	2
 3	3	3	3
 4	4	4	4
@@ -112,10 +116,6 @@ a	b	pk1	pk2
 7	7	7	7
 8	8	8	8
 9	9	9	9
-10	10	10	10
-11	11	11	11
-12	12	12	12
-13	13	13	13
 set join_buffer_size= @save_join_buffer_size;
 set max_heap_table_size= @save_max_heap_table_size;
 explain select * from t1 where a in (select b from t2);
@@ -747,8 +747,8 @@ c2 in (select 1 from t3, t2) and
 c1 in (select convert(c6,char(1)) from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t2)
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
 drop table t2, t3;
 #
@@ -993,8 +993,8 @@ WHERE
 alias2.b = alias1.a AND 
 (alias1.b >= alias1.a OR alias2.b = 'z'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
-1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	
+1	PRIMARY	t2	ALL	a	NULL	NULL	NULL	38	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
 2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where
 SELECT * FROM t2 
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 38c7f2ff554..19f3baac5f2 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -773,16 +773,16 @@ explain extended
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Start temporary
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
 show warnings;
 Level	Code	Message
 Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
 select a from t1
 where a in (select c from t2 where d >= some(select e from t3 where b=e));
 a
@@ -2182,10 +2182,10 @@ INSERT INTO t5 VALUES (7,0),(9,0);
 explain
 SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t5	index	a	a	10	NULL	2	Using where; Using index; LooseScan
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ref	b	b	5	test.t5.b	2	Using where; FirstMatch(t5)
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	15	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t5	index	a	a	10	NULL	2	Using index; Start temporary
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	10	Using where; Using join buffer (incremental, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	15	Using where; End temporary; Using join buffer (incremental, BNL join)
 SELECT * FROM t3 WHERE t3.a IN (SELECT t5.a FROM t2, t4, t5 WHERE t2.c = t5.a AND t2.b = t5.b);
 a
 0
@@ -2264,11 +2264,11 @@ alias1.c IN (SELECT SQ3_alias1.b
 FROM t2 AS SQ3_alias1 STRAIGHT_JOIN t2 AS SQ3_alias2)
 LIMIT 100;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	
-1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (incremental, BNL join)
-1	PRIMARY	SQ3_alias1	ALL	NULL	NULL	NULL	NULL	20	Using where; Start temporary; Using join buffer (incremental, BNL join)
+1	PRIMARY	SQ3_alias1	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)
 1	PRIMARY	SQ3_alias2	index	NULL	PRIMARY	4	NULL	20	Using index; End temporary; Using join buffer (incremental, BNL join)
+1	PRIMARY	alias2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (incremental, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (incremental, BNL join)
 2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	20	
 create table t3 as
 SELECT 
@@ -2892,8 +2892,8 @@ EXPLAIN
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
 GROUP BY a HAVING a != 'z';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	Using temporary; Using filesort
-1	PRIMARY	t	ref	idx_a	idx_a	4	test.t2.b	2	Using index
+1	PRIMARY	t	index	idx_a	idx_a	4	NULL	3	Using index; Using temporary; Using filesort
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	MATERIALIZED	t1	ref	idx_a	idx_a	4	test.t2.b	2	Using index
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
@@ -2906,8 +2906,8 @@ EXPLAIN
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
 GROUP BY a HAVING a != 'z';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	Using temporary; Using filesort
-1	PRIMARY	t	ref	idx_a	idx_a	4	test.t2.b	2	Using index
+1	PRIMARY	t	index	idx_a	idx_a	4	NULL	3	Using index; Using temporary; Using filesort
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	MATERIALIZED	t1	ref	idx_a	idx_a	4	test.t2.b	2	Using index
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 5729e86ce70..dc1d9b407fd 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -505,15 +505,15 @@ b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
 where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
 1	PRIMARY	t2i	ref	it2i1,it2i2,it2i3	it2i3	18	test.t1.a1,test.t1.a2	2	100.00	Using index; Start temporary
 1	PRIMARY	t3c	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary; End temporary; Using join buffer (flat, BNL join)
 4	MATERIALIZED	t3b	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 3	DEPENDENT SUBQUERY	t3a	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1
-Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))))
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2`) semi join (`test`.`t2i` join `test`.`t3` `t3c`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3c`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2`.`b2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3c`.`c1` = `test`.`t1`.`a1`) and (`test`.`t2`.`b1` = `test`.`t1`.`a1`) and (<expr_cache><`test`.`t2`.`b2`,`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t2`.`b2`,<exists>(select `test`.`t3a`.`c2` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`))))) or <expr_cache><`test`.`t2`.`b2`>(<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `<subquery4>`.`c2`))))))))
 explain extended
 select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -1087,11 +1087,11 @@ create index it1a on t1(a);
 explain extended
 select a from t1 where a in (select c from t2 where d >= 20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	6	100.00	
-1	PRIMARY	t1	ref	it1a	it1a	4	test.t2.c	2	100.00	Using index
+1	PRIMARY	t1	index	it1a	it1a	4	NULL	7	100.00	Using index
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and (`test`.`t2`.`d` >= 20))
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` >= 20))
 select a from t1 where a in (select c from t2 where d >= 20);
 a
 2
diff --git a/mysql-test/r/subselect_sj_nonmerged.result b/mysql-test/r/subselect_sj_nonmerged.result
index 2a3768c8c50..c7e04225ffe 100644
--- a/mysql-test/r/subselect_sj_nonmerged.result
+++ b/mysql-test/r/subselect_sj_nonmerged.result
@@ -77,8 +77,8 @@ explain select * from t4 where
 t4.a in (select max(t2.a) from t1, t2 group by t2.b) and 
 t4.b in (select max(t2.a) from t1, t2 group by t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	
-1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	5	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
 1	PRIMARY	t4	ref	a	a	10	<subquery2>.max(t2.a),<subquery3>.max(t2.a)	12	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 9b6ae60b3b5..8939baf1864 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4628,6 +4628,45 @@ NULL	NULL	1	0
 NULL	NULL	1	0
 DROP VIEW v2;
 DROP TABLE t1, t2, t3;
+#
+# BUG#915222: Valgrind complains or crashes with INSERT SELECT
+#              within a trigger that uses a view
+#
+CREATE TABLE t1 (a char(1));
+CREATE TABLE t2 (d int, e char(1));
+INSERT INTO t2 VALUES (13,'z');
+CREATE TRIGGER tr AFTER UPDATE ON t2
+FOR EACH ROW
+REPLACE INTO t3
+SELECT f, a AS alias FROM t3, v;
+CREATE TABLE t3 (f int, g char(8));
+CREATE VIEW v AS SELECT a, e FROM t2, t1;
+UPDATE t2 SET d=7;
+UPDATE t2 SET d=7;
+UPDATE t2 SET d=7;
+UPDATE t2 SET d=7;
+DROP TRIGGER tr;
+DROP VIEW v;
+DROP TABLE t1,t2,t3;
+#
+# BUG#972943: Assertion failure with INSERT SELECT within a trigger 
+#             that uses derived table and materialized view
+#
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,0), (2,8);
+CREATE ALGORITHM=TEMPTABLE VIEW v1
+AS SELECT * FROM t1;
+CREATE TABLE t2 (c int);
+CREATE TABLE t3 (d int, e int);
+CREATE TRIGGER tr BEFORE INSERT ON t2 FOR EACH ROW
+INSERT INTO t3
+SELECT t1.*
+FROM (SELECT * FROM t1 WHERE b IN (SELECT b FROM v1)) AS alias1, t1
+WHERE t1.a = 3 OR t1.a > 5;
+INSERT INTO t2 VALUES (1);
+DROP TRIGGER tr;
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------
diff --git a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
index 8b48c1723fd..70566da4cfa 100644
--- a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
+++ b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
@@ -141,7 +141,7 @@ perl;
   use strict;
   use Cwd;
   my $log_error= $ENV{'LOG_ERROR'} or die "LOG_ERROR not set";
-  open(FILE, "$log_error") or die("Unable to open '$log_error' from directory " . cwd() . "\n");
+  open(FILE, "$log_error") or die("Unable to open '$log_error' from directory " . cwd() . " :$! \n");
   my $binlog_counter= $ENV{'BINLOG_COUNTER1'} or die "BINLOG_COUNTER1 not set";
   my $count = () = grep(/$binlog_counter/g,<FILE>); 
   # Grep the timestamp value from the error file.
diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test
index 148964d8d96..768a21912a2 100644
--- a/mysql-test/t/csv.test
+++ b/mysql-test/t/csv.test
@@ -1820,6 +1820,21 @@ INSERT INTO t1 VALUES(-1);
 SELECT * FROM t1;
 DROP TABLE t1;
 
+#
+# Bug#970528 
+# Server crashes in my_strnncollsp_simple on LEFT JOIN with CSV table,
+# TEXT field
+#
+
+CREATE TABLE t1 ( b TEXT NOT NULL ) ENGINE=MYISAM;
+INSERT INTO t1 VALUES ('x'),('y');
+
+CREATE TABLE t2 ( a VARCHAR(1) NOT NULL ) ENGINE=CSV;
+INSERT INTO t2 VALUES ('r'),('t');
+
+SELECT * FROM t2 ORDER BY a;
+SELECT * FROM t1 LEFT JOIN t2 ON ( b = a );
+drop table t1,t2;
 
 #
 # Bug #40814 CSV engine does not parse \X characters when they occur in unquoted fields
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index d1ed2ff5ba6..03d308b6c45 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1380,6 +1380,43 @@ SET SESSION optimizer_switch= @save_optimizer_switch;
 DROP VIEW v;
 DROP TABLE t1,t2;
 
+--echo #
+--echo # LP BUG#968720 crash due to converting to materialized and
+--echo # natural join made only once
+--echo #
+
+SET @save968720_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+
+CREATE TABLE t1 (a int, INDEX(a));
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (a int, INDEX(a));
+INSERT INTO t2 VALUES (1), (2);
+
+INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN
+t2 AS s2;
+SELECT * FROM t1;
+
+DELETE FROM t1;
+INSERT INTO t1 VALUES (1);
+
+PREPARE stmt FROM "
+INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN
+t2 AS s2;
+";
+EXECUTE stmt;
+SELECT * FROM t1;
+EXECUTE stmt;
+SELECT * FROM t1;
+
+drop table t1,t2;
+set optimizer_switch=@save968720_optimizer_switch;
+
+--echo #
+--echo # end of 5.3 tests
+--echo #
+
 # 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/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
index 04d1f9e9d9c..92fc500cf55 100644
--- a/mysql-test/t/subselect_sj2.test
+++ b/mysql-test/t/subselect_sj2.test
@@ -78,13 +78,7 @@ insert into t3 select
   A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a 
 from t0 A, t0 B where B.a <5;
 
-#
-# InnoDB does not use join buffer here, XtraDB does
-# (despite the comment above which says "no join buffering",
-# because it does not hold when this file is included
-# into subselect_sj2_jcl6.test)
-#
-#--replace_regex /Using join buffer//
+--replace_column 9 #
 explain select * from t3 where b in (select a from t0);
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 79c733ed791..6208ae7a057 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4546,6 +4546,61 @@ SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM
 DROP VIEW v2;
 DROP TABLE t1, t2, t3;
 
+--echo #
+--echo # BUG#915222: Valgrind complains or crashes with INSERT SELECT
+--echo #              within a trigger that uses a view
+--echo #
+
+CREATE TABLE t1 (a char(1));
+
+CREATE TABLE t2 (d int, e char(1));
+
+INSERT INTO t2 VALUES (13,'z');
+
+CREATE TRIGGER tr AFTER UPDATE ON t2
+  FOR EACH ROW
+  REPLACE INTO t3
+  SELECT f, a AS alias FROM t3, v;
+
+CREATE TABLE t3 (f int, g char(8));
+
+CREATE VIEW v AS SELECT a, e FROM t2, t1;
+
+UPDATE t2 SET d=7;
+UPDATE t2 SET d=7;
+UPDATE t2 SET d=7;
+UPDATE t2 SET d=7;
+
+DROP TRIGGER tr;
+DROP VIEW v;
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # BUG#972943: Assertion failure with INSERT SELECT within a trigger 
+--echo #             that uses derived table and materialized view
+--echo #
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,0), (2,8);
+
+CREATE ALGORITHM=TEMPTABLE VIEW v1
+  AS SELECT * FROM t1;
+
+CREATE TABLE t2 (c int);
+CREATE TABLE t3 (d int, e int);
+
+CREATE TRIGGER tr BEFORE INSERT ON t2 FOR EACH ROW
+  INSERT INTO t3
+  SELECT t1.*
+    FROM (SELECT * FROM t1 WHERE b IN (SELECT b FROM v1)) AS alias1, t1
+    WHERE t1.a = 3 OR t1.a > 5;
+
+INSERT INTO t2 VALUES (1);
+
+DROP TRIGGER tr;
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
+
 --echo # -----------------------------------------------------------------
 --echo # -- End of 5.3 tests.
 --echo # -----------------------------------------------------------------