Bug #103424 Query error in hexadecimal
Submitted: 22 Apr 2021 8:58 Modified: 22 Apr 2021 9:17
Reporter: Guo Wenxuan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:8.0.23, 8.0.24 OS:Any
Assigned to: CPU Architecture:Any

[22 Apr 2021 8:58] Guo Wenxuan
Description:
correct´╝Ü
mysql> select * from t1 where col1 = "mysql" and col1 = "mysql";
+-------+
| COL1  |
+-------+
| mysql |
+-------+

incorrect:
mysql> select * from t1 where col1 = 0x6d7973716c and col1 = 0x6d7973716c;
Empty set

How to repeat:
CREATE TABLE `t1`  (
  `COL1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

insert into t1 values("mysql");
[22 Apr 2021 9:13] MySQL Verification Team
Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.24 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> CREATE TABLE `t1`  (
    ->   `COL1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
    -> ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> insert into t1 values("mysql");
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1 where col1 = "mysql" and col1 = "mysql";
+-------+
| COL1  |
+-------+
| mysql |
+-------+
1 row in set (0.01 sec)

mysql>
[22 Apr 2021 9:15] Guo Wenxuan
Why not a bug
[22 Apr 2021 9:16] MySQL Verification Team
d:\dbs>d:\dbs\8.0\bin\mysql -uroot --port=3580 -p --local-infile=1 --prompt="mysql 8.0 > " --default-character-set=latin1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25 Source distribution BUILT: 2021-APR-22

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > use test
Database changed
mysql 8.0 > CREATE TABLE `t1`  (
    ->   `COL1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
    -> ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.03 sec)

mysql 8.0 >
mysql 8.0 > insert into t1 values("mysql");
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > select * from t1 where col1 = "mysql" and col1 = "mysql";
+-------+
| COL1  |
+-------+
| mysql |
+-------+
1 row in set (0.00 sec)

mysql 8.0 >
[22 Apr 2021 9:17] MySQL Verification Team
Sorry I didn't noticed the correct query.