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

Index is not used when value is retrieved from subquery

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.0.1, 5.5.29, 5.5.30, 5.3.12
    • Fix Version/s: 10.0.2, 5.5.31, 5.3.13
    • Component/s: None
    • Labels:

      Description

      Noticed only on MariaDB 5.5.29 and 5.5.30
      Query is fine and using index on MySQL 5.0,5.1,5.5.

      The index is not used when the value for the condition is retrieved from subquery (select max(id)...). The index is used when contant value is supplied.

      How to reproduce.

      MariaDB [test]> create table t1 (id int unsigned not null primary key, createdon timestamp default current_timestamp, name varchar(10)) engine=innodb;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [test]> insert into t1 (id) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
      Query OK, 9 rows affected (0.00 sec)
      Records: 9  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> update t1 set name=id;
      Query OK, 9 rows affected (0.00 sec)
      Rows matched: 9  Changed: 9  Warnings: 0
       
      MariaDB [test]> explain select * from t1 where id>(select max(id) from t1)-1;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      |    1 | PRIMARY     | t1    | ALL  | PRIMARY       | NULL | NULL    | NULL |    9 | Using where                  |
      |    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> select max(id) from t1;
      +---------+
      | max(id) |
      +---------+
      |       9 |
      +---------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> explain select * from t1 where id>9-1;
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
      1 row in set (0.00 sec)

        Attachments

          Activity

            People

            Assignee:
            timour Timour Katchaounov (Inactive)
            Reporter:
            seletit Sergiy Tytarenko
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: