1 files changed, 129 insertions, 0 deletions
diff --git a/mysql-test/main/subselect_sj2_mat.test b/mysql-test/main/subselect_sj2_mat.test
index 0234f0cb7b6..bba436078e3 100644
--- a/mysql-test/main/subselect_sj2_mat.test
+++ b/mysql-test/main/subselect_sj2_mat.test
@@ -303,3 +303,132 @@ eval $q;
 eval explain $q;
 
 DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-16225: wrong resultset from query with semijoin=on
+--echo #
+
+CREATE TABLE t1 (
+  `id` int(10)  NOT NULL AUTO_INCREMENT,
+  `local_name` varchar(64) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
+
+insert  into t1(`id`,`local_name`) values
+(1,'Cash Advance'),
+(2,'Cash Advance'),
+(3,'Rollover'),
+(4,'AL Installment'),
+(5,'AL Installment'),
+(6,'AL Installment'),
+(7,'AL Installment'),
+(8,'AL Installment'),
+(9,'AL Installment'),
+(10,'Internet Payday'),
+(11,'Rollover - Internet Payday'),
+(12,'AL Monthly Installment'),
+(13,'AL Semi-Monthly Installment');
+
+explain
+SELECT SQL_NO_CACHE t.id
+FROM t1 t
+WHERE (
+        t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11)))
+        OR
+        (t.id IN (0,4,12,13,1,10,3,11))
+);
+SELECT SQL_NO_CACHE t.id
+FROM t1 t
+WHERE (
+        t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11)))
+        OR
+        (t.id IN (0,4,12,13,1,10,3,11))
+);
+drop table t1;
+
+--echo #
+--echo # MDEV-15247: Crash when SET NAMES 'utf8' is set
+--echo #
+
+CREATE TABLE t1 (
+  id_category int unsigned,
+  id_product int unsigned,
+  PRIMARY KEY (id_category,id_product)
+) ENGINE=MyISAM;
+
+INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102);
+ 
+CREATE TABLE t2 (
+  id_product int,
+  id_t2 int,
+  KEY id_t2 (id_t2),
+  KEY id_product (id_product)
+) ENGINE=MyISAM;
+ 
+INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32),
+(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26),
+(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32),
+(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19),
+(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19),
+(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20),
+(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32),
+(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19),
+(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16),
+(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31),
+(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24),
+(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19),
+(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31),
+(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32),
+(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26),
+(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22),
+(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19),
+(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32),
+(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30),
+(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23),
+(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19),
+(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15),
+(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33),
+(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18),
+(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15),
+(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19);
+
+CREATE TABLE t3 (
+  id_product int unsigned,
+  PRIMARY KEY (id_product)
+) ENGINE=MyISAM;
+ 
+INSERT INTO t3 VALUES 
+(102),(103),(104),(105),(106),(107),(108),(109),(110),
+(315371),(315373),(315374),(315375),(315376),(315377),
+(315378),(315379),(315380);
+
+CREATE TABLE t4 (
+  id_product int  not null,
+  id_shop int,
+  PRIMARY KEY (id_product,id_shop)
+) ENGINE=MyISAM ;
+ 
+INSERT INTO t4 VALUES 
+(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1),
+(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1),
+(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1),
+(177,1),(176,1),(126,1),(315380,1);
+
+CREATE TABLE t5 (id_product int) ENGINE=MyISAM;
+INSERT INTO `t5` VALUES 
+(652),(668),(669),(670),(671),(673),(674),(675),(676),
+(677),(679),(680),(681),(682),(683),(684),(685),(686);
+ 
+explain
+SELECT * FROM t3 
+  JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1)
+  JOIN t1 ON (t1.id_product = t3.id_product)
+LEFT JOIN t5 ON (t5.id_product = t3.id_product)
+WHERE 1=1
+AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32) 
+AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15)  
+AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19)
+AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23) 
+AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26);
+ 
+drop table t1,t2,t3,t4,t5;