Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Description
This bug is known for a long time since adding UCA collations.
This script with no index:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci);
|
INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
|
SELECT * FROM t1 WHERE a<'\0b';
|
and this script with KEY(a):
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci, KEY(a));
|
INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
|
SELECT * FROM t1 WHERE a<'\0b';
|
correctly return one row:
+------+
|
| a |
|
+------+
|
| a |
|
+------+
|
1 row in set (0.00 sec)
|
This script with a prefix index KEY(a(1))
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci, KEY(a(1)));
|
INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
|
SELECT * FROM t1 WHERE a<'\0b';
|
erroneously returns empty set.