[MDEV-3323] LP:609128 - RQG: Wrong result with JOIN + NOT IN + 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 Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug609128.xml    

 Description   

The following query:

SELECT table1 .`col_varchar_key`
FROM C table1 JOIN C table2 ON table1 .`col_int_nokey`
WHERE ( table1 .`col_varchar_key` , table2 .`col_varchar_nokey` ) NOT IN (
SELECT `col_varchar_key` , `col_varchar_nokey`
FROM BB )

returns no rows when evaluated using the partial match table scan strategy, whereas there are rows that match the NOT IN condition.

Test case:

CREATE TABLE `C` (
`col_int_nokey` int(11) DEFAULT NULL,
`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 `C` VALUES (NULL,2,'w','w');
INSERT INTO `C` VALUES (7,9,'m','m');
INSERT INTO `C` VALUES (9,3,'m','m');
INSERT INTO `C` VALUES (7,9,'k','k');
INSERT INTO `C` VALUES (4,NULL,'r','r');
INSERT INTO `C` VALUES (2,9,'t','t');
INSERT INTO `C` VALUES (6,3,'j','j');
INSERT INTO `C` VALUES (8,8,'u','u');
INSERT INTO `C` VALUES (NULL,8,'h','h');
INSERT INTO `C` VALUES (5,53,'o','o');
INSERT INTO `C` VALUES (NULL,0,NULL,NULL);
INSERT INTO `C` VALUES (6,5,'k','k');
INSERT INTO `C` VALUES (188,166,'e','e');
INSERT INTO `C` VALUES (2,3,'n','n');
INSERT INTO `C` VALUES (1,0,'t','t');
INSERT INTO `C` VALUES (1,1,'c','c');
INSERT INTO `C` VALUES (0,9,'m','m');
INSERT INTO `C` VALUES (9,5,'y','y');
INSERT INTO `C` VALUES (NULL,6,'f','f');
INSERT INTO `C` VALUES (4,2,'d','d');

CREATE TABLE `BB` (
`col_int_nokey` int(11) DEFAULT NULL,
`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,8,NULL,NULL);

SET @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on,subquery_cache=off,semijoin=off';

SELECT table1 .`col_varchar_key`
FROM C table1 JOIN C table2 ON table1 .`col_int_nokey`
WHERE ( table1 .`col_varchar_key` , table2 .`col_varchar_nokey` ) NOT IN (
SELECT `col_varchar_key` , `col_varchar_nokey`
FROM BB ) ;

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

Re: RQG: Wrong result with JOIN + NOT IN + partial_match_table_scan=ON
Confirmed both in 5.3 and 5.3-mwl89.
Reproducible with either partial match strategy.

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

Re: RQG: Wrong result with JOIN + NOT IN + partial_match_table_scan=ON
Reduced test case:

drop table t1, t2;

CREATE TABLE t1 (
c1 varchar(1) DEFAULT NULL,
c2 varchar(1) DEFAULT NULL
);

insert into t1 values ('m', NULL), ('k', NULL);

create table t2 (
c1 varchar(1) DEFAULT NULL,
c2 varchar(1) DEFAULT NULL
);

INSERT INTO t2 VALUES (NULL,NULL);
INSERT INTO t2 VALUES (NULL,NULL);

– coorect
SET @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,semijoin=off';
explain
select * from t1 where (c1, c2) NOT IN (SELECT c1, c2 FROM t2);
select * from t1 where (c1, c2) NOT IN (SELECT c1, c2 FROM t2);

– wrong result
SET @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on,subquery_cache=off,semijoin=off';

explain
select * from t1 where (c1, c2) NOT IN (SELECT c1, c2 FROM t2);
select * from t1 where (c1, c2) NOT IN (SELECT c1, c2 FROM t2);

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

Re: RQG: Wrong result with JOIN + NOT IN + partial_match_table_scan=ON
In fact materialization with partial matching returns the correct result.
The result is 'no rows' because the result of NOT IN is UNKNOWN, which
in this case is mapped to FALSE. The same is true for IN - its result is
also UNKNOWN, which is also mapped to FALSE. Thus both IN and
NOT IN produce 0 rows.

Therefore the bug is in the old IN-TO-EXISTS code. This bug has
been fixed in MySQL:
http://bugs.mysql.com/bug.php?id=51070
and will eventually get merged into MariaDB 5.3.

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

Launchpad bug id: 609128

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