Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12675

Two equivalent inner join queries are computed by different executions plans.

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 5.5.57
    • Optimizer
    • None

    Description

      These two join queries use just two different forms for expressing the join operation and they are are equivalent:

      select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
      select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
      

      However their execution plans are different:

      MariaDB [test]> explain
          -> select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref       | rows | Extra                                                        |
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+
      |    1 | PRIMARY     | t1    | ALL   | NULL          | NULL | NULL    | NULL      |    7 | Using where                                                  |
      |    1 | PRIMARY     | t2    | ref   | idx           | idx  | 5       | test.t1.a |  256 | Using index; FirstMatch(t1)                                  |
      |    1 | PRIMARY     | t2    | range | idx           | idx  | 5       | NULL      |    2 | Using where; Using index; Using join buffer (flat, BNL join) |
      +------+-------------+-------+-------+---------------+------+---------+-----------+------+--------------------------------------------------------------+
       
      MariaDB [test]> explain 
          -> select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
      +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------------------------------------------+
      | id   | select_type        | table | type           | possible_keys | key  | key_len | ref  | rows | Extra                                           |
      +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------------------------------------------+
      |    1 | PRIMARY            | t2    | range          | idx           | idx  | 5       | NULL |    2 | Using where; Using index                        |
      |    1 | PRIMARY            | t1    | ALL            | NULL          | NULL | NULL    | NULL |    7 | Using where; Using join buffer (flat, BNL join) |
      |    2 | DEPENDENT SUBQUERY | t2    | index_subquery | idx           | idx  | 5       | func |  256 | Using index                                     |
      +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------------------------------------------+
      

      The problem can be reproduced with the following test case.

      create table t1 (a int) engine=myisam;
      insert into t1  values (5),(3),(2),(7),(2),(5),(1); 
      create table t2 (b int, index idx(b)) engine=myisam;
      insert into t2 values (2),(3),(2),(1),(3),(4);
      insert into t2 select b+10 from t2;
      insert into t2 select b+10 from t2;
      insert into t2 select b+10 from t2;
      insert into t2 select b+10 from t2;
      insert into t2 select b+10 from t2;
      insert into t2 select b+10 from t2;
      insert into t2 select b+10 from t2;
      insert into t2 select b+10 from t2;
      insert into t2 select b+10 from t2;
      insert into t2 select b+10 from t2;
      insert into t2 select b+10 from t2;
      analyze table t1,t2;
      explain select a from t1, t2 where b between 1 and 2 and a in (select b from t2);
      explain select a from t1 join t2 on b between 1 and 2 and a in (select b from t2);
      

      Attachments

        Activity

          People

            igor Igor Babaev
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.