1 files changed, 44 insertions, 0 deletions
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 7b09cdc5f1c..51bed53be17 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2724,6 +2724,50 @@ SELECT * FROM t1, t2, t3 WHERE f2 IN ( f1 IN ( SELECT f4 FROM t4 ) );
 drop table t1,t2,t3,t4;
 set optimizer_switch= @tmp_mdev7823;
 
+--echo #
+--echo # MDEV-6859: scalar subqueries in a comparison produced unexpected result
+--echo #
+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'
+      );
+
+drop table t1, t2, t3;
+set optimizer_switch= @tmp_mdev6859;
 
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;