Wrong result (missing rows) with exists_to_in=on, semijoin+firstmatch, MERGE views or derived_merge, MyISAM or Aria
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Invalid
|
High
|
Oleksandr "Sanja" Byelkin |
Bug Description
The following query
SELECT * FROM v1, v2
WHERE v1.a = v2.a
AND EXISTS (
SELECT c FROM t1, t2
WHERE b = d
AND d = v2.c
);
on the test data returns 2 rows if it's run with exists_to_in=ON, and 4 rows otherwise. The latter is correct.
A query with subselects instead of views return the same wrong result, but it also requires derived_merge=ON (current default in 5.3).
I could not convert this test into an 'IN' subquery, because it is important for the test that the column `c` is selected in subselect, while the outer column is matched to `d`. So possibly the problem is related to exists2in.
bzr version-info
revision-id: <email address hidden>
date: 2011-12-26 10:08:40 +0200
build-date: 2012-01-06 19:10:00 +0400
revno: 3370
branch-nick: lp-5.3-exists2in
EXPLAIN with exists_to_in=ON (wrong result):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY alias2 ALL a NULL NULL NULL 7 71.43 Using where
1 PRIMARY t2 ref d d 5 test.alias2.c 2 100.00 Using index
1 PRIMARY t1 ref a a 5 test.alias2.a 2 100.00
1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; FirstMatch(t2)
1 PRIMARY t2 index d d 5 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join)
1 PRIMARY alias1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join)
Warnings:
Note 1276 Field or reference 'v2.c' of SELECT #2 was resolved in SELECT #1
Note 1003 select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`
EXPLAIN with exists_to_in=OFF (correct result):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index d d 5 NULL 3 100.00 Using index
1 PRIMARY t1 ALL a NULL NULL NULL 7 71.43 Using where; Using join buffer (flat, BNL join)
1 PRIMARY alias2 ALL a NULL NULL NULL 7 71.43 Using where; Using join buffer (incremental, BNL join)
1 PRIMARY alias1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using join buffer (incremental, BNL join)
2 DEPENDENT SUBQUERY t2 ref d d 5 test.alias2.c 2 100.00 Using index
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 7100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1276 Field or reference 'v2.c' of SELECT #2 was resolved in SELECT #1
Note 1003 select `t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`
Minimal optimizer_switch: in_to_exists=
(in_to_exists or materialization are required, otherwise the query does not run)
Full optimizer_switch: index_merge=
Test case:
# in_to_exists or materialization are required,
# otherwise the query does not run
SET optimizer_switch = 'in_to_
# MyISAM or Aria, but not InnoDB
CREATE TABLE t1 ( a INT, b INT, c INT, KEY(a) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES (9,1,1);
INSERT INTO t1 VALUES (3,2,2);
INSERT INTO t1 VALUES (2,3,3);
INSERT INTO t1 VALUES (5,4,4);
INSERT INTO t1 VALUES (3,5,5);
INSERT INTO t1 VALUES (1,6,6);
INSERT INTO t1 VALUES (3,7,7);
CREATE TABLE t2 ( d INT, KEY(d) );
INSERT INTO t2 VALUES (9),(7),(2);
CREATE ALGORITHM=MERGE VIEW v1 AS
SELECT * FROM t1, t2
WHERE b = d;
CREATE ALGORITHM=MERGE VIEW v2 AS
SELECT alias2.* FROM t1 AS alias1, t1 AS alias2
WHERE alias1.b = alias2.b AND alias2.a < 6;
SELECT * FROM v1, v2
WHERE v1.a = v2.a
AND EXISTS (
SELECT c FROM t1, t2
WHERE b = d
AND d = v2.c
);
# End of test case
# Expected result:
a b c d a b c
3 2 2 2 3 2 2
3 7 7 7 3 2 2
3 2 2 2 3 7 7
3 7 7 7 3 7 7
# Actual result:
a b c d a b c
3 2 2 2 3 2 2
3 2 2 2 3 7 7
Changed in maria: | |
status: | In Progress → Invalid |
A link to this bug is added to JIRA as MDEV-75