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

Different optimizer plan for "a BETWEEN 'string' AND ?" and "a BETWEEN ? AND 'string'"

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12, 5.5.39, 10.0.13
    • 10.0.14
    • None
    • None

    Description

      This script:

      set names utf8, collation_connection=utf8_swedish_ci;
      		 
      drop table if exists t1;
      		 
      create table t1 (a varchar(10) character set utf8,b int not null default 0, key(a));
      		 
      insert into t1 (a) values ('a'),('b'),('c'),('d'),('¢');
      		 
      set @arg='¢';
      		 
      prepare stmt from "explain select * from t1 where a between _utf8'¢' and ?";
      		 
      execute stmt using @arg;
      		 
      prepare stmt from "explain select * from t1 where a between ? and _utf8'¢'";
      		 
      execute stmt using @arg;

      returns

      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      		 
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      		 
      |    1 | SIMPLE      | t1    | range | a             | a    | 33      | NULL |    1 | Using index condition |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      		 
       
      		 
       
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
      		 
      |    1 | SIMPLE      | t1    | ref  | a             | a    | 33      | const |    1 | Using index condition |
      		 
      +------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

      Notice, the first query is using RANGE, while the second query is using REF.
      which means BETWEEN was changed to equality operator in the second
      query.

      If I run the same explains without a PS parameter:

      set names utf8, collation_connection=utf8_swedish_ci;
      		 
      explain select * from t1 where a between _utf8'¢' and '¢';
      		 
      explain select * from t1 where a between '¢' and _utf8'¢';

      It uses RANGE for both queries:

      MariaDB [test]> explain select * from t1 where a between _utf8'¢' and '¢';
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      		 
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      		 
      |    1 | SIMPLE      | t1    | range | a             | a    | 33      | NULL |    1 | Using index condition |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [test]> explain select * from t1 where a between '¢' and _utf8'¢';
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      		 
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      		 
      |    1 | SIMPLE      | t1    | range | a             | a    | 33      | NULL |    1 | Using index condition |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
      		 
      1 row in set (0.00 sec)
      		 
       

      It should be fixed to use exactly the same plan for all 4 queries.

      Attachments

        Activity

          People

            bar Alexander Barkov
            bar Alexander Barkov
            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.