The following query:

SELECT SUM( `col_varchar_key` ) field1
FROM B
WHERE `col_varchar_nokey` NOT IN (
SELECT `col_varchar_key`
FROM BB )
HAVING field1 ;

returns NULL when partial_match_table_scan is ON (and some other optimizations are disabled) even though the HAVING predicate explicitly excludes NULL as the correct answer.

Test case:

CREATE TABLE `BB` (
`col_int_key` int(11) 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_varchar_key` (`col_varchar_key`,`col_int_key`)
);

INSERT INTO `BB` VALUES (8,NULL,NULL);

CREATE TABLE `B` (
`col_int_key` int(11) 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_varchar_key` (`col_varchar_key`,`col_int_key`)
);

INSERT INTO `B` VALUES (7,'f','f');

SET @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on,subquery_cache=off,semijoin=off';
SELECT SUM( `col_varchar_key` ) field1
FROM B
WHERE `col_varchar_nokey` NOT IN (
SELECT `col_varchar_key`
FROM BB )
HAVING field1 ;