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

Merge of derived tables / views is not transparent for semi-join optimizations

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.5.57
    • Component/s: Optimizer
    • Labels:
      None

      Description

      For any query Q for which a semi-join optimization is used the equivalent query Q1 with derived table

      SELECT * FROM ( Q ) t 
      

      is always executed without semi-join optimization.
      In a general case any merge of a derived table / view blocks usage of semi-join optimizations
      for it.
      Here' s a test case that demonstrate this problem.

      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;
      analyze table t1,t2;
      explain select a from t1 where a in (select b from t2); 
      explain select * from (select a from t1 where a in (select b from t2)) t; 
      

      For the EXPLAINs from the testcase the following is returned:

      +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
      | 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 |  140 | Using index; FirstMatch(t1) |
      +------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
       
      MariaDB [test]> explain select * from (select a from t1 where a in (select b from t2)) t; 
      +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
      | id   | select_type        | table | type           | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE             | t1    | ALL            | NULL          | NULL | NULL    | NULL |    7 | Using where |
      |    3 | DEPENDENT SUBQUERY | t2    | index_subquery | idx           | idx  | 5       | func |  140 | Using index |
      +------+--------------------+-------+----------------+---------------+------+---------+------+------+-------------+
      

      This is not expected as the merge transformation applied to t converts Q1 to Q.

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: