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

DELETE HISTORY ignores indices

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Not a Bug
    • 10.3.28, 10.6.4
    • N/A
    • Versioned Tables
    • None

    Description

      To reproduce:

      CREATE TABLE t2 (x INT,
      `start_time` timestamp(6) GENERATED ALWAYS AS ROW START,
      `end_time` timestamp(6) GENERATED ALWAYS AS ROW END,
         PERIOD FOR SYSTEM_TIME (`start_time`, `end_time`)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING
        PARTITION BY SYSTEM_TIME 
       (PARTITION `p_hist` HISTORY ENGINE = InnoDB,
        PARTITION `p_cur` CURRENT ENGINE = InnoDB);
       
       
       
      ALTER TABLE t2 ADD INDEX `startendtime` (`start_time`, `end_time`);
      
      

      Select use the index

      MariaDB [test]> explain SELECT * FROM t2 FOR SYSTEM_TIME FROM '2016-01-01 00:00:00' TO '2017-01-01 00:00:00';
      +------+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
      |    1 | SIMPLE      | t2    | range | startendtime  | startendtime | 7       | NULL | 2    | Using where |
      +------+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
      1 row in set (0.002 sec)
      

      Delete apparently not

      MariaDB [test]> explain DELETE HISTORY FROM t2 BEFORE SYSTEM_TIME '2016-10-09 08:07:06';
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL | 2    | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      1 row in set (0.001 sec)
      
      

      Attachments

        Activity

          People

            serg Sergei Golubchik
            Richard Richard Stracke
            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.