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

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 5.3.12, 5.5.39, 10.0.13
        • Fix Version/s: 10.0.14
        • Component/s: None
        • Labels:
          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.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  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: