Wrong result (missing rows) with semijoin+firstmatch, IN/ANY subquery
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Sergey Petrunia |
Bug Description
The following query
SELECT * FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
WHERE alias3.d IN (
SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
WHERE alias5.b = alias4.b
AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
)
on test data returns 2940 rows if it is executed with semijoin=on, firstmatch=on, optimizer_
In the test case SELECT * is replaced with SELECT COUNT(*) for convenience, it does not change the result.
bzr version-info
revision-id: <email address hidden>
date: 2012-03-05 22:33:46 -0800
build-date: 2012-03-10 05:45:32 +0400
revno: 3455
Also reproducible on MariaDB 5.5 (revno 3316). Not reproducible on MySQL 5.6 (revno 3706).
Could not reproduce on MyISAM or Aria.
EXPLAIN with semijoin=on, firstmatch=on, optimizer_
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL 19 100.00 Using where
1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d 1 100.00 Using index; FirstMatch(alias3)
1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 100.00
1 PRIMARY alias2 ALL NULL NULL NULL NULL 14 100.00
1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b 1 100.00 Using where; FirstMatch(alias2)
Warnings:
Note 1276 Field or reference 'test.alias3.b' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.alias3.c' of SELECT #2 was resolved in SELECT #1
Note 1003 select count(0) AS `COUNT(*)` from `test`.`t1` `alias1` semi join (`test`.`t2` `alias4` join `test`.`t2` `alias5`) join `test`.`t1` `alias2` join `test`.`t2` `alias3` where ((`test`
EXPLAIN with semijoin=on, firstmatch=on, optimizer_
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 100.00
1 PRIMARY alias2 ALL NULL NULL NULL NULL 14 100.00 Using join buffer (flat, BNL join)
1 PRIMARY alias3 ALL PRIMARY NULL NULL NULL 19 100.00 Using where; Using join buffer (incremental, BNL join)
1 PRIMARY alias4 ref PRIMARY,c c 4 test.alias3.d 1 100.00 Using index
1 PRIMARY alias5 eq_ref PRIMARY PRIMARY 4 test.alias4.b 1 100.00 Using where; FirstMatch(alias3)
Warnings:
Note 1276 Field or reference 'test.alias3.b' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'test.alias3.c' of SELECT #2 was resolved in SELECT #1
Note 1003 select count(0) AS `COUNT(*)` from `test`.`t1` `alias1` semi join (`test`.`t2` `alias4` join `test`.`t2` `alias5`) join `test`.`t1` `alias2` join `test`.`t2` `alias3` where ((`test`
Minimal optimizer_switch: firstmatch=
Full optimizer_switch (default): index_merge=
Test case:
--source include/
SET optimizer_
SET optimizer_
CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
INSERT INTO t1 VALUES
(10),
(17),
CREATE TABLE t2 (
b INT PRIMARY KEY,
c VARCHAR(1),
d VARCHAR(1),
KEY(c)
) ENGINE=InnoDB;
INSERT INTO t2 VALUES
(1,'j'
(5,'d'
(9,'d'
(13,'
(17,'
SELECT COUNT(*) FROM t1 AS alias1, t1 AS alias2, t2 AS alias3
WHERE alias3.d IN (
SELECT alias4.c FROM t2 AS alias4, t2 AS alias5
WHERE alias5.b = alias4.b
AND ( alias5.b >= alias3.b OR alias5.c != alias3.c )
);
# End of test case
# Expected result
# COUNT(*)
# 3724
# Actual result
# COUNT(*)
# 2940
Changed in maria: | |
assignee: | Alexey Botchkov (holyfoot) → Sergey Petrunia (sergefp) |
importance: | Undecided → Critical |
Changed in maria: | |
status: | New → Confirmed |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | Fix Committed → Fix Released |
While the provided test requires optimizer_ prune_level= 0 and InnoDB tables, later I had similar mismatches with MyISAM tables and default optimizer_ prune_level, so I am removing these conditions from the bug summary.