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

"WHERE varchar_field LIKE temporal_const" does not use range optimizer

    Details

      Description

      This script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, KEY(a)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('00:00:00');
      INSERT INTO t1 VALUES ('00:00:01');
      INSERT INTO t1 VALUES ('00:00:02');
      INSERT INTO t1 VALUES ('00:00:03');
      INSERT INTO t1 VALUES ('00:00:04');
      INSERT INTO t1 VALUES ('00:00:05');
      INSERT INTO t1 VALUES ('00:00:06');
      INSERT INTO t1 VALUES ('00:00:07');
      EXPLAIN SELECT * FROM t1 WHERE a LIKE '00:00:00';

      returns

      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | t1    | range | a             | a    | 13      | NULL |    1 | Using where; Using index |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

      The index is used to optimize the query.

      Now if I change the EXPLAIN SELECT query to:

      EXPLAIN SELECT * FROM t1 WHERE a LIKE TIME'00:00:00';

      it does not use the index any longer:

      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | t1    | index | a             | a    | 13      | NULL |    8 | Using where; Using index |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

      It happens because of this wrong condition in get_mm_leaf:

        if (value->cmp_type() == TIME_RESULT && field->cmp_type() != TIME_RESULT)
          goto end;

      This condition is valid for the comparison operators (<,>,=, etc), but should not be checked for LIKE.

        Attachments

          Activity

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: