MySQL Bugs: #46407: EXPLAIN says that FirstMatch is used while optimizer

Bug #46407	EXPLAIN says that FirstMatch is used while optimizer_switch.firstmatch is off
Submitted:	27 Jul 2009 14:50	Modified:	3 Nov 2009 14:46
Reporter:	Elena Stepanova	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Guilhem Bichot	CPU Architecture:	Any
Tags:	firstmatch, optimizer_switch, subquery

Description:
There seems to be inconsistency between EXPLAIN output and optimizer_switch value -- the flag is switched off but EXPLAIN still shows FirstMatch in Extra field, -- although I'm not sure which of them works wrongly (i.e. EXPLAIN shows incorrect info, or the flag does not switch off FirstMatch optimization).

With the scenario below, EXPLAIN output both times is exactly the same:

#+----+-------------+-------+-------+---------------+------+---------+------------+------+----------+-----------------------------+
#| id | select_type | table | type  | possible_keys | key  | key_len | ref        | rows | filtered | Extra                       |
#+----+-------------+-------+-------+---------------+------+---------+------------+------+----------+-----------------------------+
#|  1 | PRIMARY     | t2    | index | f1            | f1   | 4       | NULL       |    2 |   100.00 | Using index; LooseScan      | 
#|  1 | PRIMARY     | t3    | ref   | f1            | f1   | 4       | test.t2.f1 |    2 |   100.00 | Using index; FirstMatch(t2) | 
#|  1 | PRIMARY     | t1    | ref   | f1            | f1   | 4       | test.t2.f1 |    2 |   100.00 | Using index                 | 
#+----+-------------+-------+-------+---------------+------+---------+------------+------+----------+--------------------------

How to repeat:
use test;
drop table if exists t1, t2, t3;
create table t1 ( i1 int not null auto_increment, primary key (i1), f1 int not null, index f1 (f1) ) engine = MyISAM;
create table t2 ( i1 int not null auto_increment, primary key (i1), f1 int not null, index f1 (f1) ) engine = MyISAM;
create table t3 ( i1 int not null auto_increment, primary key (i1), f1 int not null, index f1 (f1) ) engine = MyISAM;
insert into t1 values ( null, 1 ), (null, 1);
insert into t2 values ( null, 1 ), (null, 1);
insert into t3 values ( null, 1 ), (null, 1);

set optimizer_switch='default,firstmatch=on';
explain select count(f1) from t1 where f1 = any ( select f1 from t2 where f1 in ( select f1 from t3 ) );

set optimizer_switch='default,firstmatch=off';
explain select count(f1) from t1 where f1 = any ( select f1 from t2 where f1 in ( select f1 from t3 ) );

Thank you for the report.

Verified as described.

quick debugging says that:
- FirstMatch in EXPLAIN comes from this line sql_select.cc:1228:
int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, 
                                    uint no_jbuf_after)
{ ...
      case SJ_OPT_LOOSE_SCAN:
      { ...
        if (pos->n_sj_tables > 1) 
        {
          tab[pos->n_sj_tables - 1].do_firstmatch= tab;
This setting of do_firstmatch provokes FirstMatch in EXPLAIN (see
        else if (tab->do_firstmatch)
in select_describe()).
So it's LooseScan which causes FirstMatch-in-EXPLAIN. And using loosescan=off makes FirstMatch go away.
I still don't know if it's correct that LooseScan uses do_firstmatch.

discussion at http://lists.mysql.com/internals/37322

So it's not a bug (see discussion), LooseScan uses FirstMatch on the second table; FirstMatch here is a sub-strategy of LooseScan, and optimizer_switch='firstmatch=off' doesn't have the intention to disable that, it should apply only when FirstMatch is the top strategy for the semi-join.