MDEV-389: Wrong result (missing row) with semijoin, join_cache_level>4 ...

- Added testcase

1 files changed, 27 insertions, 0 deletions

diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index e2abb8040b3..15ca4ce884e 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -2540,5 +2540,32 @@ WHERE (field1) IN ($query);
 
 DROP TABLE t1, t2;
 
+--echo #
+--echo # MDEV-389: Wrong result (missing row) with semijoin, join_cache_level>4, LEFT JOIN...
+--echo #  (testcase only)
+--echo #
+
+# Use another connection to get rid of this test's settings
+connect (con1,localhost,root,,);
+connection con1;
+
+SET join_cache_level = 5;
+SET optimizer_switch = 'semijoin=on';
+
+CREATE TABLE t1 (a INT NOT NULL, b CHAR(1), KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4,'p'),(1,'q'),(8,'e');
+
+CREATE TABLE t2 (c INT, d CHAR(1), KEY(c), KEY(d)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'f'),(2,'i'),(5,'h'),(3,'q'),(1,'g');
+
+SELECT a, COUNT(*) AS cnt 
+FROM t1 LEFT JOIN t2 ON (d = b) 
+WHERE a IN ( SELECT c FROM t2 WHERE b > 'k' ) 
+GROUP BY a ORDER BY a, cnt LIMIT 2;
+
+drop table t1, t2;
+connection default;
+disconnect con1;
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;