Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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 ) ;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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);
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.
Launchpad bug id: 609128
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.