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

Execution plans of the the first and the second executions of a prepared statement are not the same.

    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.55
    • Component/s: Optimizer
    • Labels:
      None

      Description

      If the where condition of a query contains a non-correlated, non-expensive subquery then
      then different executions of the prepared statement made for the query may use
      different execution plans.

      The following test case demonstrates this.

       create table t1 (id int, c varchar(3), key idx(c))engine=myisam;
      insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy');
       
      prepare stmt1 from
      "explain extended
       select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'";
      execute stmt1;
      execute stmt1;
      deallocate prepare stmt1;
       
      prepare stmt2 from
      "explain extended
       select * from t1 where (1, 2) in ( select 3, 4 )";
      execute stmt2;
      execute stmt2;
      deallocate prepare stmt2;
       
      drop table t1;
      

      Here for the first and the second executions of the prepared statement stmt1 we have:

      MariaDB [test]> execute stmt1;
      +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
      +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+
      |    1 | PRIMARY     | t1    | ref  | idx           | idx  | 6       | const |    1 |   100.00 | Using index condition |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL  | NULL |     NULL | No tables used        |
      +------+-------------+-------+------+---------------+------+---------+-------+------+----------+-----------------------+
       
      MariaDB [test]> execute stmt1;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
      |    1 | PRIMARY     | t1    | ALL  | idx           | NULL | NULL    | NULL |    4 |   100.00 | Using where    |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
      

      While for the first and the second executions of the prepared statement stmt2 the plans are
      as follows:

      ariaDB [test]> execute stmt2;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
      |    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used   |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
       
      MariaDB [test]> execute stmt2;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
      |    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 |                |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
      

        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: