Added the test case for bug mdev-5200.

The bug was fixed by the patch applied to the 5.3 tree in the revision 3727.

1 files changed, 30 insertions, 0 deletions

diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test
index 47f62365816..2b44d5157df 100644
--- a/mysql-test/t/selectivity.test
+++ b/mysql-test/t/selectivity.test
@@ -708,4 +708,34 @@ drop table t1;
 set use_stat_tables=@save_use_stat_tables;
 
 
+--echo #
+--echo # Bug mdev-5200: impossible where with a semijoin subquery   
+--echo #                when optimizer_use_condition_selectivity=2
+--echo #
+
+set use_stat_tables = 'preferably';
+set optimizer_use_condition_selectivity = 2;
+
+CREATE TABLE t1 (i1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0), (1);
+
+CREATE TABLE t2 (pk2 int, i2 int, c2 char(1), PRIMARY KEY(pk2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,8,'m'), (2,9,'b');
+
+CREATE TABLE t3 (c3 char(1), INDEX(c3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('v'), ('c');
+
+ANALYZE TABLE t1,t2,t3;
+
+SELECT * FROM t1
+  WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+EXPLAIN EXTENDED
+SELECT * FROM t1
+  WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+
+DROP TABLE t1,t2,t3;
+
 set use_stat_tables=@save_use_stat_tables;
+