RQG: wrong result on aggregate + NOT IN + HAVING and partial_match_table_scan=on
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Undecided
|
Timour Katchaounov |
Bug 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_
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_
);
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_
);
INSERT INTO `B` VALUES (7,'f','f');
SET @@optimizer_
SELECT SUM( `col_varchar_key` ) field1
FROM B
WHERE `col_varchar_nokey` NOT IN (
SELECT `col_varchar_key`
FROM BB )
HAVING field1 ;
Changed in maria: | |
assignee: | nobody → Timour Katchaounov (timour) |
Changed in maria: | |
milestone: | none → 5.3 |
Changed in maria: | |
status: | New → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
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'; switch= 'materializatio n=on,partial_ match_rowid_ merge=off, partial_ match_table_ scan=off' ;
SET @@optimizer_
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