|
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.
|