Details
-
Bug
-
Status: Closed (View Workflow)
-
Resolution: Fixed
-
None
-
None
-
None
Description
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 ;
Attachments
Activity
Re: RQG: wrong result on aggregate + NOT IN + HAVING and partial_match_table_scan=on
The bug is present also in mysql-6.0.
Re: RQG: wrong result on aggregate + NOT IN + HAVING and partial_match_table_scan=on
Simplified test case:
create table t1 (c1 int);
create table t2 (c2 int);
insert into t1 values (1);
insert into t2 values (2);
SET @@optimizer_switch='subquery_cache=off,semijoin=off';
SET @@optimizer_switch='materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=off';
SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
– returns NULL, correct result
SELECT SUM(c1) c1_sum FROM t1 WHERE c1 IN (SELECT c2 FROM t2) HAVING c1_sum;
– returns NULL, incorrect result, should be empty result