Different plan is chosen with extended_keys on a query which only uses MyISAM tables

[Elena Stepanova]

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`.`alias3`.`b` = `test`.`alias1`.`b`) and (`test`.`alias2`.`c` = `test`.`alias1`.`a`))
SET optimizer_switch='extended_keys=on';

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`.`alias3`.`b` = `test`.`alias1`.`b`) and (`test`.`alias2`.`c` = `test`.`alias1`.`a`))

The rest of optimizer_switch (apart from extended_keys which is variable here):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

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.

[Elena Stepanova (elenst)]

Also filed as JIRA issue MDEV-115

[Igor Babaev (igorb-seattle)]

After a merge with the latest build of 5.3 the wrong result for the reported query with optimizer_switch='extended_keys=off'
has disappeared, but the output of EXPLAIN still depends on the setting for the extended_keys flag.
This is a clear indication of some bug in the code mwl#247.

[Elena Stepanova (elenst)]

Fix released with 5.5.21.