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.

3 files changed, 88 insertions, 0 deletions

diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 4a2ebef8504..e213bc30778 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -1128,4 +1128,33 @@ Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a`
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-5200: impossible where with a semijoin subquery   
+#                when optimizer_use_condition_selectivity=2
+#
+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;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+SELECT * FROM t1
+WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+i1
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` left join `test`.`t3` on((`test`.`t3`.`c3` = 'b'))) where 0
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1,t2,t3;
 set use_stat_tables=@save_use_stat_tables;
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 9607bb98765..1dbd9b56079 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -1136,6 +1136,35 @@ Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a`
 set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
 drop table t1;
 set use_stat_tables=@save_use_stat_tables;
+#
+# Bug mdev-5200: impossible where with a semijoin subquery   
+#                when optimizer_use_condition_selectivity=2
+#
+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;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+SELECT * FROM t1
+WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+i1
+EXPLAIN EXTENDED
+SELECT * FROM t1
+WHERE 2 IN ( SELECT pk2 FROM t2 LEFT JOIN t3 ON (c3 = c2 ) WHERE i2 = 3 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` left join `test`.`t3` on((`test`.`t3`.`c3` = 'b'))) where 0
+set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+DROP TABLE t1,t2,t3;
 set use_stat_tables=@save_use_stat_tables;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 SET SESSION STORAGE_ENGINE=DEFAULT;
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;
+