1 files changed, 49 insertions, 0 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index ba01db4a66f..95ff464918c 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2115,6 +2115,55 @@ a
 4
 drop table t1,t2;
 #
+# MDEV-3873: Wrong result (extra rows) with NOT IN and
+# a subquery from a MERGE view
+#
+CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4),(7),(0);
+CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2);
+CREATE TABLE t3 (c INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (4),(6),(3);
+CREATE TABLE t4 (d INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (4),(5),(3);
+CREATE TABLE tv (e INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO tv VALUES (1),(3);
+CREATE ALGORITHM=TEMPTABLE VIEW v_temptable AS SELECT * FROM tv;
+CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM tv;
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_temptable ON (c = e) WHERE c <> b ) AND a < b;
+a	b
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_merge ON (c = e) WHERE c <> b ) AND a < b;
+a	b
+SELECT * FROM t1, t2 
+WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN (SELECT * FROM tv) as derived ON (c = e) WHERE c <> b ) AND a < b;
+a	b
+drop view v_temptable, v_merge;
+drop table t1,t2,t3,t4,tv;
+#
+# MDEV-3912: Wrong result (extra rows) with FROM subquery inside
+# ALL subquery, LEFT JOIN, derived_merge.
+# (duplicate of MDEV-3873 (above))
+#
+SET @save3912_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on,in_to_exists=on';
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4),(8);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (7),(0);
+CREATE TABLE t3 (c INT, d INT NOT NULL) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (0,4),(8,6);
+SELECT * FROM t1
+WHERE a >= ALL ( 
+SELECT d FROM t2 LEFT JOIN ( SELECT * FROM t3 ) AS alias ON ( c = b ) 
+WHERE b >= a 
+);
+a
+8
+set optimizer_switch=@save3912_optimizer_switch;
+drop table t1, t2, t3;
+#
 # end of 5.3 tests
 #
 set optimizer_switch=@exit_optimizer_switch;