Wrong result (missing rows) with semijoin=on, firstmatch=on, IN subquery, constant InnoDB table
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Sergey Petrunia |
Bug Description
The following query
SELECT * FROM t1, t2
WHERE c IN ( SELECT c FROM t1, t2 WHERE a = b );
on test data returns 1 row instead of expected 4 rows.
t2 is an InnoDB table with 1 row.
bzr version-info
revision-id: <email address hidden>
date: 2012-01-02 20:06:36 -0800
build-date: 2012-01-06 02:26:41 +0400
revno: 3376
branch-nick: maria-5.3
Reproducible on 5.5, but there the scenario additionally requires join_cache_level=0, as otherwise it hits a plan with flat BNL. On 5.3 revno 3376 it is reproducible with the default join_cache_level=2.
EXPLAIN with InnoDB table (wrong result):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00 Using where
1 PRIMARY t1 index NULL a 4 NULL 4 100.00 Using index
1 PRIMARY t1 eq_ref a a 4 test.t2.b 1 100.00 Using index; FirstMatch(t2)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where ((`test`.`t2`.`c` = `test`.`t2`.`c`) and (`test`.`t1`.`a` = `test`.`t2`.`b`))
EXPLAIN with MyISAM table (correct result):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t1 const a a 4 const 1 100.00 Using index
1 PRIMARY t1 index NULL a 4 NULL 4 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,1 AS `b`,1 AS `c` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where 1
Minimal optimizer_switch: semijoin=
Full optimizer_switch: index_merge=
Test case:
CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY(a) );
INSERT INTO t1 VALUES (1),(2),(3),(4);
# t2 needs to be InnoDB
CREATE TABLE t2 ( b INT, c INT ) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,1);
SELECT * FROM t1, t2
WHERE c IN ( SELECT c FROM t1, t2 WHERE a = b );
End of test case
Expected result:
a b c
1 1 1
2 1 1
3 1 1
4 1 1
Actual result:
a b c
1 1 1
Changed in maria: | |
importance: | Undecided → Critical |
status: | New → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Confirmed |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
This bug has also been filed in JIRA as MDEV-72