Bug #102656 SELECT results containing the special character " ︠e " is different
Submitted: 19 Feb 2021 3:52 Modified: 19 Feb 2021 6:48
Reporter: CM M Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.22, 8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[19 Feb 2021 3:52] CM M
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)
[19 Feb 2021 6:48] MySQL Verification Team
Hello!

Thank you for the report and test case.

regards,
Umesh