[MDEV-3729] LP:609121 - RQG: wrong result on aggregate + NOT IN + HAVING and partial_match_table_scan=on Created: 2010-07-23  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug
Reporter: Philip Stoev (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug609121.xml    

 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 ;

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2010-10-25 ]

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

Comment by Timour Katchaounov (Inactive) [ 2010-10-25 ]

Re: RQG: wrong result on aggregate + NOT IN + HAVING and partial_match_table_scan=on
The bug is present also in mysql-6.0.

Comment by Rasmus Johansson (Inactive) [ 2010-10-27 ]

Launchpad bug id: 609121

Generated at Thu Feb 08 06:50:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.