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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 5.5.55
    • Optimizer
    • 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

            igor Igor Babaev (Inactive)
            igor Igor Babaev (Inactive)
            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.