Wrong result with multipart keys, in_to_exists=on, NOT IN in mwl#89
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Timour Katchaounov |
Bug Description
Not repeatable in maria-5.3, maria-5.2. The following query
SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10);;
returns rows when executed with in-to-exists even though the subquery returns (6),(6) which means that the NOT IN predicate should be FALSE, making the entire WHERE condition FALSE.
The following things seem to be required:
- multipart index
- 1 row in the other table in the subquery
Innodb is required for this particular test case, but the bug was just observed with MyISAM.
EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4
2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index
test case:
--source include/
CREATE TABLE t1 ( f4 int);
INSERT IGNORE INTO t1 VALUES (2),(2),(2),(2);
CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3) ) ENGINE=InnoDB;
INSERT IGNORE INTO t2 VALUES (6, 1), (6, 1);
CREATE TABLE t3 ( f10 int );
INSERT IGNORE INTO t3 VALUES (1);
SET SESSION optimizer_
SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10);
bzr version-info:
revision-id: <email address hidden>
date: 2011-05-05 01:35:03 +0300
build-date: 2011-05-05 08:59:05 +0300
revno: 2981
branch-nick: maria-5.3-mwl89
Related branches
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Timour Katchaounov (timour) |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → In Progress |
summary: |
- Wrong result with multipart keys, in_to_exists=on, NOT IN in - maria-5.3-mwl89 + Wrong result with multipart keys, in_to_exists=on, NOT IN in mwl#89 |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
Bug was just observed with MyISAM. I have corrected the title and the description.