1 files changed, 93 insertions, 0 deletions
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index e20291cfa67..b3c603728ee 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1857,6 +1857,99 @@ execute stmt;
 
 drop table t1;
 
+--echo #
+--echo # MDEV-12429: IN subquery used in WHERE of EXISTS subquery
+--echo #
+
+CREATE TABLE t1 (
+  pk INT, f1 INT NOT NULL, f2 VARCHAR(3), f3 INT NULL, PRIMARY KEY(pk)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1,'foo',8), (2,5,'bar',7);
+
+SELECT sq1.f2    FROM t1 AS sq1
+  WHERE EXISTS ( SELECT * FROM t1 AS sq2
+                   WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+
+set @save_optimizer_switch= @@optimizer_switch;
+
+set optimizer_switch='exists_to_in=off';
+EXPLAIN
+SELECT sq1.f2    FROM t1 AS sq1
+  WHERE EXISTS ( SELECT * FROM t1 AS sq2
+                   WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+
+--echo # this checks the result set above
+set optimizer_switch= 'materialization=off,semijoin=off';
+SELECT sq1.f2    FROM t1 AS sq1
+  WHERE EXISTS ( SELECT * FROM t1 AS sq2
+                   WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
+
+set optimizer_switch= @save_optimizer_switch;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-12145: IN subquery used in WHERE of EXISTS subquery
+--echo #
+
+CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4),(6);
+
+CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8),(7),(1);
+
+CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5);
+
+set @save_optimizer_switch= @@optimizer_switch;
+
+set optimizer_switch='exists_to_in=off';
+SELECT * FROM t1
+  WHERE EXISTS ( SELECT * FROM t2, t3
+                   WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
+EXPLAIN EXTENDED
+SELECT * FROM t1
+  WHERE EXISTS ( SELECT * FROM t2, t3
+                   WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
+
+--echo # this checks the result set above
+set optimizer_switch= 'materialization=off,semijoin=off';
+SELECT * FROM t1
+  WHERE EXISTS ( SELECT * FROM t2, t3
+                   WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM t3 ) );
+
+set optimizer_switch= @save_optimizer_switch;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-9686: IN subquery used in WHERE of a subquery from select list
+--echo #
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT);
+INSERT INTO t1 VALUES (1, 4),(2, 3),(3, 3),(4, 6),(5, 3);
+
+CREATE TABLE t2 (f2 INT);
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
+
+--echo # t1.pk is always IN ( SELECT f2 FROM t2 ),
+--echo # so the IN condition should be true for every row,
+--echo # and thus COUNT(*) should always return 5
+
+SELECT pk, f1, ( SELECT COUNT(*) FROM t2
+                   WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
+EXPLAIN EXTENDED
+SELECT pk, f1, ( SELECT COUNT(*) FROM t2
+                   WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
+
+--echo # this checks the result set above
+set @save_optimizer_switch= @@optimizer_switch;
+set optimizer_switch= 'materialization=off,semijoin=off';
+SELECT pk, f1, ( SELECT COUNT(*) FROM t2
+                   WHERE t1.pk IN ( SELECT f2 FROM t2 ) ) AS sq FROM t1;
+set optimizer_switch= @save_optimizer_switch;
+
+DROP TABLE t1,t2;
+
 --echo # End of 5.5 tests
 --echo #
 --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT