RQG: wrong result on aggregate + NOT IN + HAVING and partial_match_table_scan=on

Bug #609121 reported by Philip Stoev
12
This bug affects 1 person
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_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 ;

Changed in maria:
assignee: nobody → Timour Katchaounov (timour)
Changed in maria:
milestone: none → 5.3
Changed in maria:
status: New → In Progress
Revision history for this message
Timour Katchaounov (timour) wrote :

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

Revision history for this message
Timour Katchaounov (timour) wrote :

The bug is present also in mysql-6.0.

Changed in maria:
status: In Progress → Fix Committed
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.