Re: RQG: Query returns extra rows when executed with semijoin=off
Test case:

SET SESSION optimizer_switch = 'semijoin=off';
CREATE TABLE `CC` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (8,NULL,'v','v');
INSERT INTO `CC` VALUES (9,'2006-06-14','r','r');
INSERT INTO `CC` VALUES (9,'2002-09-12','a','a');
INSERT INTO `CC` VALUES (186,'2005-02-15','m','m');
INSERT INTO `CC` VALUES (NULL,NULL,'y','y');
INSERT INTO `CC` VALUES (2,'2008-11-04','j','j');
INSERT INTO `CC` VALUES (3,'2004-09-04','d','d');
INSERT INTO `CC` VALUES (0,'2006-06-05','z','z');
INSERT INTO `CC` VALUES (133,'1900-01-01','e','e');
INSERT INTO `CC` VALUES (1,'1900-01-01','h','h');
INSERT INTO `CC` VALUES (8,'1900-01-01','b','b');
INSERT INTO `CC` VALUES (5,'2005-01-13','s','s');
INSERT INTO `CC` VALUES (5,'2006-05-21','e','e');
INSERT INTO `CC` VALUES (8,'2003-09-08','j','j');
INSERT INTO `CC` VALUES (6,'2006-12-23','e','e');
INSERT INTO `CC` VALUES (51,'2006-10-15','f','f');
INSERT INTO `CC` VALUES (4,'2005-04-06','v','v');
INSERT INTO `CC` VALUES (7,'2008-04-07','x','x');
INSERT INTO `CC` VALUES (6,'2006-10-10','m','m');
INSERT INTO `CC` VALUES (4,'1900-01-01','c','c');
CREATE TABLE `C` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (2,NULL,'w','w');
INSERT INTO `C` VALUES (9,'2001-09-19','m','m');
INSERT INTO `C` VALUES (3,'2004-09-12','m','m');
INSERT INTO `C` VALUES (9,NULL,'k','k');
INSERT INTO `C` VALUES (NULL,'2002-07-19','r','r');
INSERT INTO `C` VALUES (9,'2002-12-16','t','t');
INSERT INTO `C` VALUES (3,'2006-02-08','j','j');
INSERT INTO `C` VALUES (8,'2006-08-28','u','u');
INSERT INTO `C` VALUES (8,'2001-04-14','h','h');
INSERT INTO `C` VALUES (53,'2000-01-05','o','o');
INSERT INTO `C` VALUES (0,'2003-12-06',NULL,NULL);
INSERT INTO `C` VALUES (5,'1900-01-01','k','k');
INSERT INTO `C` VALUES (166,'2002-11-27','e','e');
INSERT INTO `C` VALUES (3,NULL,'n','n');
INSERT INTO `C` VALUES (0,'2003-05-27','t','t');
INSERT INTO `C` VALUES (1,'2005-05-03','c','c');
INSERT INTO `C` VALUES (9,'2001-04-18','m','m');
INSERT INTO `C` VALUES (5,'2005-12-27','y','y');
INSERT INTO `C` VALUES (6,'2004-08-20','f','f');
INSERT INTO `C` VALUES (2,'1900-01-01','d','d');
CREATE TABLE `BB` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (8,'2002-02-21',NULL,NULL);

SELECT table2 .`col_date_key`
FROM BB table1 JOIN ( C table2 JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
WHERE table1 .`col_varchar_nokey` IN (
SELECT `col_varchar_nokey`
FROM C ) ;

SELECT table2 .`col_date_key`
FROM BB table1 JOIN ( C table2 JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
WHERE table1 .`col_varchar_nokey` IN (
'w', 'm', 'm', 'k', 'r', 't', 'j', 'u', 'h', 'o', NULL, 'k', 'e', 'n', 't', 'c', 'm', 'y', 'f', 'd'
);

Re: RQG: Query returns extra rows when executed with semijoin=off
Here is another similar situation. Note that the second execution of the prepared statement returns no rows, whereas the first execution of the prepared statement and the stand-alone execution of the statement return rows:

SET SESSION optimizer_switch = 'semijoin=off';

--disable_warnings
DROP TABLE /*! IF EXISTS */ D;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ BB;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

CREATE TABLE `D` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,NULL,NULL,'r','r');
INSERT INTO `D` VALUES (2,0,'14:34:45','c','c');
INSERT INTO `D` VALUES (3,0,'11:49:48','o','o');
INSERT INTO `D` VALUES (4,7,'18:12:55','c','c');
INSERT INTO `D` VALUES (5,8,'18:30:05','d','d');
INSERT INTO `D` VALUES (6,4,'14:19:30','v','v');
INSERT INTO `D` VALUES (7,6,'05:20:04','m','m');
INSERT INTO `D` VALUES (8,5,'20:29:31','j','j');
INSERT INTO `D` VALUES (9,NULL,'07:08:09','f','f');
INSERT INTO `D` VALUES (10,NULL,'14:49:14','n','n');
INSERT INTO `D` VALUES (11,8,'00:00:00','z','z');
INSERT INTO `D` VALUES (12,8,'09:58:06','h','h');
INSERT INTO `D` VALUES (13,8,NULL,'q','q');
INSERT INTO `D` VALUES (14,1,'18:24:16','w','w');
INSERT INTO `D` VALUES (15,1,'17:39:57','z','z');
INSERT INTO `D` VALUES (16,5,'08:23:21','j','j');
INSERT INTO `D` VALUES (17,2,NULL,'a','a');
INSERT INTO `D` VALUES (18,7,'21:50:46','m','m');
INSERT INTO `D` VALUES (19,6,'12:33:17','n','n');
INSERT INTO `D` VALUES (20,4,'03:06:43','e','e');
INSERT INTO `D` VALUES (21,7,'03:46:14','u','u');
INSERT INTO `D` VALUES (22,0,'20:34:52','s','s');
INSERT INTO `D` VALUES (23,9,NULL,'u','u');
INSERT INTO `D` VALUES (24,3,'10:41:20','r','r');
INSERT INTO `D` VALUES (25,5,'08:43:11','g','g');
INSERT INTO `D` VALUES (26,1,NULL,'o','o');
INSERT INTO `D` VALUES (27,1,'10:17:51','w','w');
INSERT INTO `D` VALUES (28,5,'06:34:09','b','b');
INSERT INTO `D` VALUES (29,9,'21:22:47',NULL,NULL);
INSERT INTO `D` VALUES (30,2,'04:02:32','y','y');
INSERT INTO `D` VALUES (31,5,'02:33:14','y','y');
INSERT INTO `D` VALUES (32,248,'16:32:56','u','u');
INSERT INTO `D` VALUES (33,0,'21:32:42','p','p');
INSERT INTO `D` VALUES (34,8,'23:04:47','s','s');
INSERT INTO `D` VALUES (35,1,'22:05:43','e','e');
INSERT INTO `D` VALUES (36,255,'02:05:45','d','d');
INSERT INTO `D` VALUES (37,9,'00:00:00','d','d');
INSERT INTO `D` VALUES (38,9,'18:09:07','c','c');
INSERT INTO `D` VALUES (39,3,'10:54:06','b','b');
INSERT INTO `D` VALUES (40,9,'23:15:50','t','t');
INSERT INTO `D` VALUES (41,6,'10:17:40',NULL,NULL);
INSERT INTO `D` VALUES (42,4,'03:37:09','y','y');
INSERT INTO `D` VALUES (43,60,'22:26:06','c','c');
INSERT INTO `D` VALUES (44,7,'17:10:38','d','d');
INSERT INTO `D` VALUES (45,1,'00:00:00','x','x');
INSERT INTO `D` VALUES (46,6,'17:08:49','p','p');
INSERT INTO `D` VALUES (47,4,'19:04:40','e','e');
INSERT INTO `D` VALUES (48,NULL,'20:53:28','g','g');
INSERT INTO `D` VALUES (49,8,'11:46:03','x','x');
INSERT INTO `D` VALUES (50,0,NULL,'s','s');
INSERT INTO `D` VALUES (51,8,'10:58:07','e','e');
INSERT INTO `D` VALUES (52,151,'00:00:00','l','l');
INSERT INTO `D` VALUES (53,7,'09:43:15','p','p');
INSERT INTO `D` VALUES (54,6,'21:40:32','h','h');
INSERT INTO `D` VALUES (55,NULL,'00:17:44','m','m');
INSERT INTO `D` VALUES (56,23,'16:47:26','n','n');
INSERT INTO `D` VALUES (57,2,'00:00:00','v','v');
INSERT INTO `D` VALUES (58,4,'05:25:59','b','b');
INSERT INTO `D` VALUES (59,NULL,'00:00:00','x','x');
INSERT INTO `D` VALUES (60,NULL,'20:33:04','r','r');
INSERT INTO `D` VALUES (61,77,'00:46:12','t','t');
INSERT INTO `D` VALUES (62,NULL,'00:00:00','w','w');
INSERT INTO `D` VALUES (63,NULL,'02:13:59','w','w');
INSERT INTO `D` VALUES (64,7,'02:54:47','k','k');
INSERT INTO `D` VALUES (65,1,'18:13:59','a','a');
INSERT INTO `D` VALUES (66,9,'13:53:08','t','t');
INSERT INTO `D` VALUES (67,6,'22:21:30','z','z');
INSERT INTO `D` VALUES (68,2,'11:41:50','e','e');
INSERT INTO `D` VALUES (69,3,'15:20:02','q','q');
INSERT INTO `D` VALUES (70,0,NULL,'e','e');
INSERT INTO `D` VALUES (71,NULL,NULL,'v','v');
INSERT INTO `D` VALUES (72,6,'07:51:52','d','d');
INSERT INTO `D` VALUES (73,3,'00:00:00','u','u');
INSERT INTO `D` VALUES (74,195,NULL,'o','o');
INSERT INTO `D` VALUES (75,5,NULL,'b','b');
INSERT INTO `D` VALUES (76,2,'12:31:05','c','c');
INSERT INTO `D` VALUES (77,7,'00:00:00','q','q');
INSERT INTO `D` VALUES (78,25,'01:16:45',NULL,NULL);
INSERT INTO `D` VALUES (79,NULL,'20:38:54','h','h');
INSERT INTO `D` VALUES (80,0,'00:30:15','d','d');
INSERT INTO `D` VALUES (81,98,'23:46:36','w','w');
INSERT INTO `D` VALUES (82,6,'19:03:13','m','m');
INSERT INTO `D` VALUES (83,5,'10:54:27','i','i');
INSERT INTO `D` VALUES (84,0,'00:25:47','w','w');
INSERT INTO `D` VALUES (85,3,'08:44:27','f','f');
INSERT INTO `D` VALUES (86,1,'08:15:38','k','k');
INSERT INTO `D` VALUES (87,1,'19:56:21','v','v');
INSERT INTO `D` VALUES (88,147,'00:00:00','c','c');
INSERT INTO `D` VALUES (89,3,'20:50:52','y','y');
INSERT INTO `D` VALUES (90,3,'03:54:39','h','h');
INSERT INTO `D` VALUES (91,NULL,'23:58:17',NULL,NULL);
INSERT INTO `D` VALUES (92,2,'12:54:58','t','t');
INSERT INTO `D` VALUES (93,1,'04:02:43','l','l');
INSERT INTO `D` VALUES (94,8,'11:31:12','a','a');
INSERT INTO `D` VALUES (95,8,'20:20:04','r','r');
INSERT INTO `D` VALUES (96,8,'00:22:24','s','s');
INSERT INTO `D` VALUES (97,0,'10:09:31','z','z');
INSERT INTO `D` VALUES (98,1,NULL,'j','j');
INSERT INTO `D` VALUES (99,8,'18:45:35','c','c');
INSERT INTO `D` VALUES (100,5,'11:49:25','f','f');
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2,'11:28:45','w','w');
INSERT INTO `C` VALUES (2,9,'20:25:14','m','m');
INSERT INTO `C` VALUES (3,3,'13:47:24','m','m');
INSERT INTO `C` VALUES (4,9,'19:24:11','k','k');
INSERT INTO `C` VALUES (5,NULL,'15:59:13','r','r');
INSERT INTO `C` VALUES (6,9,'00:00:00','t','t');
INSERT INTO `C` VALUES (7,3,'15:15:04','j','j');
INSERT INTO `C` VALUES (8,8,'11:32:06','u','u');
INSERT INTO `C` VALUES (9,8,'18:32:33','h','h');
INSERT INTO `C` VALUES (10,53,'15:19:25','o','o');
INSERT INTO `C` VALUES (11,0,'19:03:19',NULL,NULL);
INSERT INTO `C` VALUES (12,5,'00:39:46','k','k');
INSERT INTO `C` VALUES (13,166,NULL,'e','e');
INSERT INTO `C` VALUES (14,3,'00:00:00','n','n');
INSERT INTO `C` VALUES (15,0,'13:12:11','t','t');
INSERT INTO `C` VALUES (16,1,'04:56:48','c','c');
INSERT INTO `C` VALUES (17,9,'19:56:05','m','m');
INSERT INTO `C` VALUES (18,5,'19:35:19','y','y');
INSERT INTO `C` VALUES (19,6,'05:03:03','f','f');
INSERT INTO `C` VALUES (20,2,'18:38:59','d','d');
CREATE TABLE `BB` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,'18:27:58',NULL,NULL);
CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,7,'01:13:38','f','f');

SELECT table1 .`col_time_key` FROM ( SELECT SUBQUERY1_t1 .* FROM BB SUBQUERY1_t1 JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`pk` ) table1 RIGHT JOIN ( SELECT * FROM B ) table2 JOIN D table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey` WHERE ( table2 .`col_varchar_key` , table1 .`col_varchar_key` ) IN ( SELECT SUBQUERY3_t1 .`col_varchar_key` , SUBQUERY3_t2 .`col_varchar_nokey` FROM C SUBQUERY3_t1 STRAIGHT_JOIN B SUBQUERY3_t2 ON SUBQUERY3_t1 .`col_varchar_key` );

PREPARE prep_stmt_7707 FROM " SELECT table1 .`col_time_key` FROM ( SELECT SUBQUERY1_t1 .* FROM BB SUBQUERY1_t1 JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_int_key` = SUBQUERY1_t1 .`pk` ) table1 RIGHT JOIN ( SELECT * FROM B ) table2 JOIN D table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey` WHERE ( table2 .`col_varchar_key` , table1 .`col_varchar_key` ) IN ( SELECT SUBQUERY3_t1 .`col_varchar_key` , SUBQUERY3_t2 .`col_varchar_nokey` FROM C SUBQUERY3_t1 STRAIGHT_JOIN B SUBQUERY3_t2 ON SUBQUERY3_t1 .`col_varchar_key` ) ";
EXECUTE prep_stmt_7707 ;
EXECUTE prep_stmt_7707 ;
DEALLOCATE PREPARE prep_stmt_7707;

Re: RQG: Query returns extra rows when executed with semijoin=off
The test case from the first comment is still repeatable in maria-5.3 with materialization=on . It is not repeatable in maria-5.3-mwl89, however the plan there is different.

Test case:

CREATE TABLE t1 ( f4 varchar(1)) ENGINE=MyISAM;
INSERT IGNORE INTO t1 VALUES (NULL);

CREATE TABLE t2 ( f2 date, f3 varchar(1), f4 varchar(1)) ;
INSERT IGNORE INTO t2 VALUES ('2005-05-03','c','c'),('1900-01-01','d','d');

CREATE TABLE t3 ( f3 varchar(1)) ;
INSERT IGNORE INTO t3 VALUES ('c');

SET SESSION optimizer_switch = 'materialization=on,semijoin=off';

SELECT t1.f4
FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;

Explain in maria-5.3:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t3 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2

Explain in maria-5.3-mwl89:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t3 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where

Re: RQG: Query returns extra rows when executed with materialization=on
This appears to be a bug in 5.3 materialization.
In 5.3-mwl89 all possible subquery execution plans produce the same empty result.

Re: RQG: Query returns extra rows when executed with materialization=on
The query plan in 5.3-mwl89 equivalent to the 5.3 plan above is:
---------------------------------------------------------------------+

---------------------------------------------------------------------+

---------------------------------------------------------------------+

Re: RQG: Query returns extra rows when executed with materialization=on
Test case pushed to 5.3. The bug itself was fixed by MWL#89.

Launchpad bug id: 611382