Description:
The SELECT results that contain the special character " ︠e " is different to/from the following two query statements :
select t3.c0 from t3 where not ((t3.c0) >=('︠e'));
select t3.c0, not ((t3.c0) >=('︠e')) from t3;
Execution process:
mysql> drop database if exists database001;
Query OK, 1 row affected (0.01 sec)
mysql> create database database001;
Query OK, 1 row affected (0.00 sec)
mysql> use database001;
Database changed
mysql> CREATE TABLE `t3` (
-> `c0` varchar(500) DEFAULT NULL,
-> KEY `i0` (`c0`(1))
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t3 values (1202049718);
Query OK, 1 row affected (0.00 sec)
mysql> select t3.c0 from t3 where not ((t3.c0) >=('︠e'));
Empty set (0.00 sec)
mysql> select t3.c0, not ((t3.c0) >=('︠e')) from t3;
+------------+--------------------------+
| c0 | not ((t3.c0) >=('︠e')) |
+------------+--------------------------+
| 1202049718 | 1 |
+------------+--------------------------+
1 row in set (0.00 sec)
The result of the first query statement is empty , but the second is not empty .
How to repeat:
drop database if exists database001;
create database database001;
use database001;
CREATE TABLE `t3` (
`c0` varchar(500) DEFAULT NULL,
KEY `i0` (`c0`(1))
);
insert into t3 values (1202049718);
select t3.c0 from t3 where not ((t3.c0) >=('︠e'));
select t3.c0, not ((t3.c0) >=('︠e')) from t3;
The collation used above is utf8mb4_0900_ai_ci.
However, if the sorting rule utf8mb4_general_ci is used, the query result is the same.
mysql> drop database if exists database001;
Query OK, 1 row affected (0.01 sec)
mysql> create database database001;
Query OK, 1 row affected (0.00 sec)
mysql> alter database database001 character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use database001;
Database changed
mysql> CREATE TABLE `t3` (
-> `c0` varchar(500) DEFAULT NULL,
-> KEY `i0` (`c0`(1))
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t3 values (1202049718);
Query OK, 1 row affected (0.00 sec)
mysql> select t3.c0 from t3 where not ((t3.c0) >=('︠e'));
+------------+
| c0 |
+------------+
| 1202049718 |
+------------+
1 row in set (0.00 sec)
mysql> select t3.c0, not ((t3.c0) >=('︠e')) from t3;
+------------+--------------------------+
| c0 | not ((t3.c0) >=('︠e')) |
+------------+--------------------------+
| 1202049718 | 1 |
+------------+--------------------------+
1 row in set (0.00 sec)