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

Different EXPLAIN SELECT DISTINCT on just created table and after deleted records

    XMLWordPrintable

Details

    Description

      Reproduce

      create or replace table t1 ( id int not null, name varchar(20) not null, dept varchar(20) not null, age tinyint(3) unsigned not null, primary key (id), index (name,dept) ) engine=innodb;
      explain select distinct t1.name, t1.dept from t1 where t1.name='rs5';
      insert into t1(id, dept, age, name) values (3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'), (3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'), (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'), (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
      delete from t1;
      explain select distinct t1.name, t1.dept from t1 where t1.name='rs5';
      

      Result

      Results from first and second explain do not match:

      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
      |    1 | SIMPLE      | t1    | ref  | name          | name | 22      | const | 1    | Using where; Using index |
      +------+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                 |
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
      |    1 | SIMPLE      | t1    | range | name          | name | 44      | NULL | 1    | Using where; Using index for group-by |
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
      

      Expected

      Results from first and second explain should be equal.

      Side-effect

      Test innodb.innodb_mysql is unstable, with --repeat=100 it fails:

      --- /home/midenok/src/mariadb/10.4/src/mysql-test/suite/innodb/r/innodb_mysql.result    2019-05-31 09:23:14.003022993 +0300
      +++ /home/midenok/src/mariadb/10.4/src/mysql-test/suite/innodb/r/innodb_mysql.reject    2019-06-26 15:33:23.132090751 +0300
      @@ -394,7 +394,7 @@
       # Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746).
       EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
       id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      -1      SIMPLE  t1      range   name    name    44      NULL    #       Using where; Using index for group-by
      +1      SIMPLE  t1      ref     name    name    22      const   #       Using where; Using index
       SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
       name   dept
       DROP TABLE t1;
      

      Info

      The original fix was https://lists.mysql.com/commits/19658 but now result is overridden by 0700cde7f1071fb676d21794aaf97bf0a74acc61.

      Attachments

        Issue Links

          Activity

            People

              vlad.lesin Vladislav Lesin
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.