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

[zimulala]

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

• 300

Mentor

• @SunRunAway

[dawxy]

@zimulala
I think this is because the default collation in tidb is utf8_bin, use utf8_general_ci to solve.

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

[zimulala]

@dawxy TiDB doesn't currently support case-insensitive. Setting utf8_general_ci doesn't work.

[dawxy]

@zimulala
I was mistaken, thanks.

[darren]

Support utf8_general_ci collation is in the roadmap:

https://github.com/pingcap/docs/blob/master/ROADMAP.md

@queenypingcap will that fixes this issue?

[shenli]

@darren Yes, we will fix this after we support utf8_general_ci collatio.

[darren]

Any update on this issue?
or is this bug already fixed in v2.0.0?

[zimulala]

@darren
We fixed it in #4683.

[darren]

@zimulala

it is neccessary to set sql_mode mysql self to get this behaviour?

I tried setting sql_mode but failed:

create table t (c varchar(30), unique key(c));
insert into t values('a');
SELECT * FROM t WHERE c LIKE 'A';
set @@session.sql_mode=',NO_ZERO_DATE,ANSI,ANSI_QUOTES';
SELECT * FROM t WHERE c LIKE 'A';

TiDB-Server [test]> drop table t;
Query OK, 0 rows affected (2.16 sec)

TiDB-Server [test]> create table t (c varchar(30), unique key(c));
Query OK, 0 rows affected (3.22 sec)

TiDB-Server [test]> insert into t values('a');
Query OK, 1 row affected (0.21 sec)

TiDB-Server [test]> SELECT * FROM t WHERE c LIKE 'A';
Empty set (0.00 sec)

TiDB-Server [test]> set @@session.sql_mode=',NO_ZERO_DATE,ANSI,ANSI_QUOTES';
Query OK, 0 rows affected (0.01 sec)

TiDB-Server [test]> SELECT * FROM t WHERE c LIKE 'A';
Empty set (0.02 sec)

TiDB-Server [test]> 

Release Version: v2.0.0
Git Commit Hash: 637e130e6a9ba2e54e158131c0466233db39a60e
Git Branch: heads/v2.0.0
UTC Build Time: 2018-05-07 03:12:01
GoVersion: go version go1.10.2 linux/amd64
TiKV Min Version: 2.0.0-rc.4.1 

[zimulala]

@darren
Sorry, I misunderstand this issue. We supported this behavior once. But we don't support it after #4683.
We will support it when we support utf8_general_ci collation.

[darren]

Is there any update on this?

[shenli]

@darren Case insensitive collation is on our roadmap but we will not work on in this year. Maybe in the early days next year.

[nickbaba2012]

Any updates? Is it included in the latest release version v3.0.2?

[alivxxx]

@nickbaba2012 No, it has not started yet.

[bb7133]

Hi @nickbaba2012 @dawxy @darren , now we have a plan for CI collations, I will land an RFC firstly.

[qw4990]

This issue can still be reproduced on Master.

[xiongjiwei]

utf8mb4 charset in TiDB default collation is utf8mb4_bin
see https://docs.pingcap.com/tidb/stable/mysql-compatibility#default-differences

[ichn-hu]

/assign @ichn-hu

[ichn-hu]

It is confirmed that this bug still persists in the current master branch, even if we explicitly use the utf8mb4 collation.

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;
insert into t values("a");
SELECT * FROM t WHERE c LIKE 'A';

[ichn-hu]

/assign @bb7133

[ichn-hu]

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.

[seiya-annie]

/info