Join between blob type with matching returns incorrect result.

[wengsy150943]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t2(a blob);
create table t3(a blob);
insert into t2 values(0xC2A0);
insert into t3 values(0xC2);
select * from t2,t3 where t2.a like concat("%",t3.a,"%");

2. What did you expect to see? (Required)

Returns one row, as MySQL(8.0.33) does.

mysql> select * from t2,t3 where t2.a like concat("%",t3.a,"%");
+------------+------------+
| a          | a          |
+------------+------------+
| 0xC2A0     | 0xC2       |
+------------+------------+
1 row in set (0.01 sec)

3. What did you see instead (Required)

Returns empty set.

> select * from t2,t3 where t2.a like concat("%",t3.a,"%");
Empty set (0.00 sec)

It looks weird since in the case below, TiDB works well.

create table t2(a blob);
create table t3(a blob);
insert into t2 values(0xC2A020);
insert into t3 values(0xC2A0);
select * from t2,t3 where t2.a like concat("%",t3.a,"%");

In this case, TiDB returns one row:

> select * from t2,t3 where t2.a like concat("%",t3.a,"%");
+------+------+
| a    | a    |
+------+------+
|      |      |
+------+------+
1 row in set (0.00 sec)

which is similar with MySQL.(The difference in display is reported in #50392).

mysql> select * from t2,t3 where t2.a like concat("%",t3.a,"%");
+------------+------------+
| a          | a          |
+------------+------------+
| 0xC2A020   | 0xC2A0     |
+------------+------------+
1 row in set (0.02 sec)

The charsets of TiDB and MySQL are the same(utf8mb4).

4. What is your TiDB version? (Required)

>  SELECT tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()

       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.4.0
Edition: Community
Git Commit Hash: 38cb4f3312be9199a983c0ef282d2ea2e28a7824
Git Branch: heads/refs/tags/v7.4.0
UTC Build Time: 2023-10-10 14:18:50
GoVersion: go1.21.1
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[yibin87]

/assign @yibin87

[yibin87]

Reproduced it using latest version.