Merge branch '10.0' into 10.1

1 files changed, 65 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index 13dc990b806..8e95b963971 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -3017,6 +3017,71 @@ explain
 select 1 from t1 where _cp932 "1" in (select '1' from t1);
 ERROR HY000: Illegal mix of collations (cp932_japanese_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '='
 drop table t1;
+#
+# MDEV-7823: Server crashes in next_depth_first_tab on nested IN clauses with SQ inside
+#
+set @tmp_mdev7823=@@optimizer_switch;
+set optimizer_switch=default;
+CREATE TABLE t1 (f1 INT);
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (f2 INT, KEY(f2));
+INSERT INTO t2 VALUES (8),(0);
+CREATE TABLE t3 (f3 INT);
+INSERT INTO t3 VALUES (1),(2);
+CREATE TABLE t4 (f4 INT);
+INSERT INTO t4 VALUES (0),(5);
+explain 
+SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ref	f2	f2	5	const	0	Using where; Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
+f1	f2	f3
+1	0	1
+1	0	2
+drop table t1,t2,t3,t4;
+set optimizer_switch= @tmp_mdev7823;
+#
+# MDEV-6859: scalar subqueries in a comparison produced unexpected result
+#
+set @tmp_mdev6859=@@optimizer_switch;
+set optimizer_switch=default;
+CREATE TABLE t1 (
+project_number varchar(50) NOT NULL,
+PRIMARY KEY (project_number)
+) ENGINE=MyISAM;
+INSERT INTO t1 (project_number) VALUES ('aaa'),('bbb');
+CREATE TABLE t2 (
+id int(10) unsigned NOT NULL AUTO_INCREMENT,
+project_number varchar(50) NOT NULL,
+history_date date NOT NULL,
+country varchar(50) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=MyISAM;
+INSERT INTO t2 (id, project_number, history_date, country) VALUES
+(1, 'aaa', '2014-08-09', 'france'),(2, 'aaa', '2014-09-09', 'singapore');
+CREATE TABLE t3 (
+region varchar(50) NOT NULL,
+country varchar(50) NOT NULL
+) ENGINE=MyISAM;
+INSERT INTO t3 (region, country) VALUES ('apac', 'singapore'),('eame', 'france');
+SELECT SQL_NO_CACHE a.project_number 
+FROM t1 a 
+WHERE ( SELECT z.country 
+FROM t2 z 
+WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' 
+ORDER BY z.id DESC LIMIT 1 
+) IN ( 
+SELECT r.country 
+FROM t3 r 
+WHERE r.region = 'eame'
+      );
+project_number
+aaa
+drop table t1, t2, t3;
+set optimizer_switch= @tmp_mdev6859;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off