The query results of the LIKE operation are incorrect in TiDB version 5.4

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb5.4版本like查询数据结果不对

| username: luqiuhua

TiDB version 5.4.0 / 5.4.2

SQL query using LIKE with one character ‘阿%’ returns no results, but using LIKE with two characters ‘阿莫%’ displays results normally.

Image
Image596×549 18 KB
[Image]
Image [Image]

2
| username: luqiuhua | Original post link

Is this a bug?

3
| username: Billmay表妹 | Original post link

Does it mean that the result from 5.4.0 is correct, and the result from 5.4.2 is incorrect?

4
| username: luqiuhua | Original post link

No, it’s not. None of the 5.4.X versions are working properly.

5
| username: luqiuhua | Original post link

To add, I tried searching with ‘阿%’ and ‘阿莫%’ and the former also couldn’t retrieve results.

6
| username: 啦啦啦啦啦 | Original post link

See if the situation is similar to this post.

7
| username: ShawnYan | Original post link

Could you show the table structure definition (including collation)?

8
| username: TiDBer_wTKU9jv6 | Original post link

Version 6.1 tested successfully

image
image690×808 106 KB

9
| username: songxuecheng | Original post link
  1. show create table xxx; Please share the table structure.
  2. show global variables like ‘%coll%’; Please check.
10
| username: cs58_dba | Original post link

In version 5.7.25-TiDB-v5.4.0, setting the table character set to utf8 and collation to utf8_general_ci did not result in any anomalies.

11
| username: ealam_小羽 | Original post link

It is most likely due to character set issues. You can check out this document:

12
| username: xiaohetao | Original post link

:+1::+1::+1:

13
| username: cs58_dba | Original post link

Tested in version 5.4.0, it is indeed the case. Marking it down.

14
| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.