The following query:

SELECT table2 .`col_varchar_key`
FROM BB JOIN (
SELECT *
FROM C ) table2 ON table2 .`col_int_key`
WHERE ( 8 , 8 ) NOT IN (
SELECT SUBQUERY2_t1 .`col_int_key` , SUBQUERY2_t2 .`col_int_key`
FROM D SUBQUERY2_t1 JOIN C SUBQUERY2_t2 ON SUBQUERY2_t2 .`pk` ) ;

returns no rows under partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,semijoin=off .The EXPLAIN shows "Impossible WHERE noticed after reading const tables" which is not correct, since there are matching rows and they are properly returned under other switches and product versions.

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 DEPENDENT SUBQUERY SUBQUERY2_t2 ref_or_null col_int_key col_int_key 5 3 Using index condition; Using where
3 DEPENDENT SUBQUERY SUBQUERY2_t1 ref_or_null col_int_key col_int_key 5 23 Using where; Using index
2 DERIVED C ALL NULL NULL NULL NULL 20

test case:

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

CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_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,'w');
INSERT INTO `C` VALUES (2,9,'m');
INSERT INTO `C` VALUES (3,3,'m');
INSERT INTO `C` VALUES (4,9,'k');
INSERT INTO `C` VALUES (5,NULL,'r');
INSERT INTO `C` VALUES (6,9,'t');
INSERT INTO `C` VALUES (7,3,'j');
INSERT INTO `C` VALUES (8,8,'u');
INSERT INTO `C` VALUES (9,8,'h');
INSERT INTO `C` VALUES (10,53,'o');
INSERT INTO `C` VALUES (11,0,NULL);
INSERT INTO `C` VALUES (12,5,'k');
INSERT INTO `C` VALUES (13,166,'e');
INSERT INTO `C` VALUES (14,3,'n');
INSERT INTO `C` VALUES (15,0,'t');
INSERT INTO `C` VALUES (16,1,'c');
INSERT INTO `C` VALUES (17,9,'m');
INSERT INTO `C` VALUES (18,5,'y');
INSERT INTO `C` VALUES (19,6,'f');
INSERT INTO `C` VALUES (20,2,'d');

CREATE TABLE `BB` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_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,NULL);

SET @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,semijoin=off';

SELECT table2 .`col_varchar_key`
FROM BB JOIN (
SELECT *
FROM C ) table2 ON table2 .`col_int_key`
WHERE ( 8 , 8 ) NOT IN (
SELECT SUBQUERY2_t1 .`col_int_key` , SUBQUERY2_t2 .`col_int_key`
FROM D SUBQUERY2_t1 JOIN C SUBQUERY2_t2 ON SUBQUERY2_t2 .`pk` ) ;