tidb: Executing the statement of `select ... col like 'String'` gets the wrong result when col is unique key.

Description

1.What did you do?

create table t (c varchar(30), unique key(c));
insert into t values("a");
SELECT * FROM t WHERE c LIKE 'A';

2.What did you expect to see? ±-----+ | c | ±-----+ | a | ±-----+

3.What did you see instead? Empty set (0.00 sec)

SIG slack channel

#sig-exec

Score

Mentor

@SunRunAway

About this issue

Original URL
State: closed
Created 8 years ago
Comments: 21 (19 by maintainers)

Commits related to this issue

tests/br/compatibility: fix failed for manifest unknown (#1161) — committed to 3pointer/tidb by Leavrth 3 years ago
[parser] parser: support builtin function SOUNDEX (#1161) * add soundex * add unit test — committed to xhebox/tidb by clark1013 3 years ago
[parser] parser: support builtin function SOUNDEX (#1161) * add soundex * add unit test — committed to xhebox/tidb by clark1013 3 years ago
[parser] parser: support builtin function SOUNDEX (#1161) * add soundex * add unit test — committed to xhebox/tidb by clark1013 3 years ago
[parser] parser: support builtin function SOUNDEX (#1161) * add soundex * add unit test — committed to pingcap/tidb by clark1013 3 years ago

Most upvoted comments

After a more careful investigation, this bug is already fixed.

As the document states, https://docs.pingcap.com/tidb/stable/character-set-and-collation#new-framework-for-collations, we need to enable

new_collations_enabled_on_first_bootstrap = true

on the first time of bootstrap, and then the collation will work as MySQL.

mysql> CREATE TABLE `t` (
    ->   `c` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ->   UNIQUE KEY `c` (`c`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values("a");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t WHERE c LIKE 'A';
+------+
| c    |
+------+
| a    |
+------+
1 row in set (0.01 sec)

mysql> SELECT VARIABLE_VALUE FROM mysql.tidb WHERE VARIABLE_NAME='new_collation_enabled';
+----------------+
| VARIABLE_VALUE |
+----------------+
| True           |
+----------------+
1 row in set (0.00 sec)

@lzmhhh123 Please help close this issue.

ichn-hu on Nov 10, 2020