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

Index is not used when value is retrieved from subquery

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.0.1, 5.5.29, 5.5.30, 5.3.12
    • 10.0.2, 5.5.31, 5.3.13
    • None

    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

            timour Timour Katchaounov (Inactive)
            seletit Sergiy Tytarenko
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.