Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.39, 10.0.13
-
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.