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

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2
    • Fix Version/s: 5.5.57
    • Component/s: Optimizer
    • Labels:
      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

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

              Dates

              • Created:
                Updated:
                Resolved: