STRCMP() result error

[jingshanglu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int, p1 VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin,p2 CHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci , p3 CHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,p4 CHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,n1 CHAR(255) CHARACTER SET utf8 COLLATE utf8_bin,n2 CHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci , n3 CHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,n4 CHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci );
insert into t1 (a) values (1);
insert into t1 (a,p1,p2,p3,p4,n1,n2,n3,n4) values (2,'0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ');
insert into t1 (a,p1,p2,p3,p4,n1,n2,n3,n4) values (3,'0aA1!测试テストמבחן','0aA1!测试テストמבחן','0aA1!测试テストמבחן','0aA1!测试テストמבחן','0Aa1!测试テストמבחן','0Aa1!测试テストמבחן','0Aa1!测试テストמבחן','0Aa1!测试テストמבחן');
insert into t1 (a,p1,p2,p3,p4,n1,n2,n3,n4) values (4,'0aA1!测试テストמבחן','0aA1!测试テストמבחן','0aA1!测试テストמבחן','0aA1!测试テストמבחן','0Aa1!测试テストמבחן  ','0Aa1!测试テストמבחן  ','0Aa1!测试テストמבחן  ','0Aa1!测试テストמבחן  ');
insert into t1 (a,p1,p2,p3,p4,n1,n2,n3,n4) values (5,'0aA1!测试テストמבחן0aA1!测试','0aA1!测试テストמבחן0aA1!测试','0aA1!测试テストמבחן0aA1!测试','0aA1!测试テストמבחן0aA1!测试','0Aa1!测试','0Aa1!测试','0Aa1!测试','0Aa1!测试');
insert into t1 (a,p1,p2,p3,p4,n1,n2,n3,n4) values (6,'0aA1!测试テストמבחן0aA1!测试','0aA1!测试テストמבחן0aA1!测试','0aA1!测试テストמבחן0aA1!测试','0aA1!测试テストמבחן0aA1!测试','0aA1!测试','0aA1!测试','0aA1!测试','0aA1!测试');
insert into t1 (a,p1,p2,p3,p4,n1,n2,n3,n4) values (7,'0aA1!测试テストמבחן  ','0aA1!测试テストמבחן         ','0aA1!测试テストמבחן         ','0aA1!测试テストמבחן         ','0aA1!测试テストמבחן','0aA1!测试テストמבחן','0aA1!测试テストמבחן','0aA1!测试テストמבחן');
insert into t1 (a,p1,p2,p3,p4,n1,n2,n3,n4) values (8,'0aA1!测试テストמבחן','0aA1!测试テストמבחן','0aA1!测试テストמבחן','0aA1!测试テストמבחן','0aA1!测试テストמבחן  ','0aA1!测试テストמבחן  ','0aA1!测试テストמבחן  ','0aA1!测试テストמבחן  ');
select STRCMP(p1 , n1) from t1;

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

mysql> select STRCMP(p1 , n1) from t1;\
+-----------------+
| STRCMP(p1 , n1) |
+-----------------+
|            NULL |
|               0 |
|               1 |
|               1 |
|               1 |
|               1 |
|               1 |
|               0 |
+-----------------+

3. What did you see instead (Required)

mysql> select STRCMP(p1 , n1) from t1;\
+-----------------+
| STRCMP(p1 , n1) |
+-----------------+
|            NULL |
|               1 |
|               1 |
|               1 |
|               1 |
|               1 |
|               1 |
|               0 |
+-----------------+

4. What is your TiDB version? (Required)

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.3.0-alpha-210-ga55ec7b44
Edition: Community
Git Commit Hash: a55ec7b448bea73b037d0340442359680d27981c
Git Branch: master
UTC Build Time: 2021-09-26 08:42:46
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[jingshanglu]

1. Minimal reproduce step

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int, p1 VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin,p2 CHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci , p3 CHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,p4 CHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,n1 CHAR(255) CHARACTER SET utf8 COLLATE utf8_bin,n2 CHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci , n3 CHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,n4 CHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci );
insert into t1 (a,p1,p2,p3,p4,n1,n2,n3,n4) values (2,'0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ','0aA1!测试テストמבחן     ');
select STRCMP(p1 , n1) from t1;

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

mysql> select STRCMP(p1 , n1) from t1;
+-----------------+
| STRCMP(p1 , n1) |
+-----------------+
|               0 |
+-----------------+

3. What did you see instead (Required)

mysql> select STRCMP(p1 , n1) from t1;
+-----------------+
| STRCMP(p1 , n1) |
+-----------------+
|               1 |
+-----------------+

[bb7133]

Interesting... I think this is a bug of MySQL, as you can see from the manual:

STRCMP() returns 0 if the strings are the same.
...
STRCMP() performs the comparison using the collation of the arguments.

However, if you run the following in MySQL, the result is confusing:

mysql> select STRCMP(p1,n1), p1=n1 from t1;
+---------------+-------+
| STRCMP(p1,n1) | p1=n1 |
+---------------+-------+
|             0 |     1 |
+---------------+-------+
1 row in set (0.00 sec)

[bb7133]

I just found bug #57104 has been reported to MySQL, however, it is marked as 'not a bug'(I don't agree with it).

So I think TiDB is the one that followed the manual and it is not a bug of TiDB.

[xiongjiwei]

I just found bug #57104 has been reported to MySQL, however, it is marked as 'not a bug'(I don't agree with it).

So I think TiDB is the one that followed the manual and it is not a bug of TiDB.

a return value of 0 from STRCMP() is means equality, the same as a return value of 1 from the = operator.

[xiongjiwei]

at least in master, it returns 0

what version do you use? @jingshanglu

[jingshanglu]

at least in master, it returns 0

what version do you use? @jingshanglu

master git commit: 74b3294

[bb7133]

@xiongjiwei I see, thanks for correcting me.

[xiongjiwei]

it a bug introduced by #27789 (me), I will fix it in #27863

[xiongjiwei]

@jebter it only affects master, I will remove the lables

[xiongjiwei]

I'd like to change the severity to major since it only affects the master bransh

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.