Different plan is chosen with extended_keys on a query which only uses MyISAM tables
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug Description
The following query
SELECT a FROM
t1 AS alias1, t2 AS alias2
WHERE c = a
AND b IN (
SELECT alias3.b
FROM t1 AS alias3, t2 AS alias4
WHERE alias3.b = alias1.b
);
produces different plans (and with the given test data, different results) depending on the value of extended_keys switch, even although it only uses MyISAM tables.
bzr version-info
revision-id: <email address hidden>
date: 2012-01-18 19:38:03 -0800
build-date: 2012-01-24 23:43:28 +0400
revno: 3381
EXPLAIN without extended_keys:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY alias1 index a,b b 7 NULL 10 100.00 Using index
1 PRIMARY alias3 ref b b 3 test.alias1.b 2 100.00 Using index
1 PRIMARY alias2 eq_ref PRIMARY PRIMARY 4 test.alias1.a 1 100.00 Using index
1 PRIMARY alias4 index NULL PRIMARY 4 NULL 11 100.00 Using index; FirstMatch(alias3)
Warnings:
Note 1276 Field or reference 'test.alias1.b' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`alias1`.`a` AS `a` from `test`.`t1` `alias1` semi join (`test`.`t1` `alias3` join `test`.`t2` `alias4`) join `test`.`t2` `alias2` where ((`test`
SET optimizer_
EXPLAIN with extended_keys:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY alias1 index a,b b 7 NULL 10 100.00 Using index
1 PRIMARY alias3 ref b b 3 test.alias1.b 2 100.00 Using index
1 PRIMARY alias4 index NULL PRIMARY 4 NULL 11 100.00 Using index; FirstMatch(alias1)
1 PRIMARY alias2 ref PRIMARY PRIMARY 4 test.alias1.a 1 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.alias1.b' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`alias1`.`a` AS `a` from `test`.`t1` `alias1` semi join (`test`.`t1` `alias3` join `test`.`t2` `alias4`) join `test`.`t2` `alias2` where ((`test`
The rest of optimizer_switch (apart from extended_keys which is variable here):
index_merge=
Test case:
CREATE TABLE t1 ( a INT NOT NULL, b VARCHAR(1) NOT NULL, KEY(a), KEY(b,a) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES (0,'j');
INSERT INTO t1 VALUES (8,'v');
INSERT INTO t1 VALUES (1,'c');
INSERT INTO t1 VALUES (8,'m');
INSERT INTO t1 VALUES (9,'d');
INSERT INTO t1 VALUES (24,'d');
INSERT INTO t1 VALUES (6,'y');
INSERT INTO t1 VALUES (1,'t');
INSERT INTO t1 VALUES (6,'d');
INSERT INTO t1 VALUES (2,'s');
CREATE TABLE t2 ( c INT NOT NULL PRIMARY KEY ) ENGINE=MyISAM;
INSERT INTO t2 VALUES (10);
INSERT INTO t2 VALUES (11);
INSERT INTO t2 VALUES (12);
INSERT INTO t2 VALUES (13);
INSERT INTO t2 VALUES (14);
INSERT INTO t2 VALUES (15);
INSERT INTO t2 VALUES (16);
INSERT INTO t2 VALUES (17);
INSERT INTO t2 VALUES (18);
INSERT INTO t2 VALUES (19);
INSERT INTO t2 VALUES (24);
SELECT a FROM
t1 AS alias1, t2 AS alias2
WHERE c = a
AND b IN (
SELECT alias3.b
FROM t1 AS alias3, t2 AS alias4
WHERE alias3.b = alias1.b
);
# End of test case
# There is a variation of the test case without the second key (b,a) on t1. The plan changes, but it's still different with and without extended_keys.
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → Critical |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Also filed as JIRA issue MDEV-115