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

Wrong query plan for query with no PK

    XMLWordPrintable

Details

    Description

      Found this while working on MDEV-14433:
      Found an interesting mismatch between query plans that depends on the index number:

      Upstream, MySQL 5.6

      CREATE TABLE t3(
        pk int primary key,
        a varchar(10) NOT NULL,   
        e int(11) DEFAULT 0,   
        KEY (a) 
      ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
      

      insert into t3 values (1,1,1),(2,2,2);
      explain select a from t3 where a <'zzz';
      +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      |  1 | SIMPLE      | t3    | range | a             | a    | 32      | NULL |    2 | Using where |
      +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      

      CREATE TABLE t4(
        pk int,
        a varchar(10) NOT NULL,
        e int(11) DEFAULT 0,   
        KEY (a)
      ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
      insert into t4 values (1,1,1),(2,2,2);
      

      explain select a from t4 where a <'zzz';
      +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      |  1 | SIMPLE      | t4    | range | a             | a    | 32      | NULL |    1 | Using where |
      +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      

      MariaDB:

      CREATE TABLE t3(
        pk int primary key,
        a varchar(10) NOT NULL,   
        e int(11) DEFAULT 0,   
        KEY (a) 
      ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
      insert into t3 values (1,1,1),(2,2,2);
      

      explain select a from t3 where a <'zzz';
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t3    | range | a             | a    | 32      | NULL |    1 | Using where |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      

      CREATE TABLE t4(
        pk int,
        a varchar(10) NOT NULL,
        e int(11) DEFAULT 0,   
        KEY (a)
      ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8;
      insert into t4 values (1,1,1),(2,2,2);
      

      explain select a from t4 where a <'zzz';
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | t4    | index | a             | a    | 32      | NULL |    0 | Using where; Using index |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              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.