Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 11.4
-
None
Description
This script:
DROP TABLE IF EXISTS t1; |
SET NAMES utf8, collation_connection=utf8_general_ci; |
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'),('¢'); |
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN _utf8'¢' and '¢'; |
returns
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
| 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 |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|
It's using "ref" access. Looks good so far.
Now if I change the session collation to utf8_swedish_ci:
DROP TABLE IF EXISTS t1; |
SET NAMES utf8, collation_connection=utf8_swedish_ci; |
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'),('¢'); |
EXPLAIN SELECT * FROM t1 WHERE a BETWEEN _utf8'¢' and '¢'; |
It 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 |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|
Notice, it's now using "range" instead of "ref" access. This looks wrong. The value of @@collation_connection should not be important here, because the comparison operation should be done according to the collation of the field "a", which is utf8_general_ci.