Description:
version is 8.0.22 and code commit id is ee4455a.
In a testcase query result failure caused by character set.
query result is correct when charset and collate is
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci.
query resut is wrong when charset and collate is
CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci.
How to repeat:
create database test;
use test;
DROP TABLE IF EXISTS t3;
#create table COLLATE is utf8mb4_general_ci
CREATE TABLE `t3` (
`c0` text,
KEY `i0` (`c0`(1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
insert into t3 select '-1415158225';
insert into t3 select '1415158225';
#the following two query's result both has two rows, it is corret.
SELECT t3.c0, ('᳙4MCcdmz') > (t3.c0) from t3;
+-------------+--------------------------+
| c0 | ('᳙4MCcdmz') > (t3.c0) |
+-------------+--------------------------+
| -1415158225 | 1 |
| 1415158225 | 1 |
+-------------+--------------------------+
2 rows in set (0.01 sec)
SELECT t3.c0 FROM t3 WHERE ('᳙4MCcdmz') > (t3.c0);
+-------------+
| c0 |
+-------------+
| -1415158225 |
| 1415158225 |
+-------------+
2 rows in set (0.00 sec)
DROP TABLE IF EXISTS t2;
CREATE TABLE `t2` (
`c0` text,
KEY `i0` (`c0`(1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into t2 select '-1415158225';
insert into t2 select '1415158225';
#the following two query's result has different rows, it is corret.
SELECT t2.c0, ('᳙4MCcdmz') > (t2.c0) from t2;
+-------------+--------------------------+
| c0 | ('᳙4MCcdmz') > (t2.c0) |
+-------------+--------------------------+
| -1415158225 | 1 |
| 1415158225 | 1 |
+-------------+--------------------------+
SELECT t2.c0 FROM t2 WHERE ('᳙4MCcdmz') > (t2.c0);
Empty set (0.00 sec)
Description: version is 8.0.22 and code commit id is ee4455a. In a testcase query result failure caused by character set. query result is correct when charset and collate is DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci. query resut is wrong when charset and collate is CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci. How to repeat: create database test; use test; DROP TABLE IF EXISTS t3; #create table COLLATE is utf8mb4_general_ci CREATE TABLE `t3` ( `c0` text, KEY `i0` (`c0`(1)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; insert into t3 select '-1415158225'; insert into t3 select '1415158225'; #the following two query's result both has two rows, it is corret. SELECT t3.c0, ('᳙4MCcdmz') > (t3.c0) from t3; +-------------+--------------------------+ | c0 | ('᳙4MCcdmz') > (t3.c0) | +-------------+--------------------------+ | -1415158225 | 1 | | 1415158225 | 1 | +-------------+--------------------------+ 2 rows in set (0.01 sec) SELECT t3.c0 FROM t3 WHERE ('᳙4MCcdmz') > (t3.c0); +-------------+ | c0 | +-------------+ | -1415158225 | | 1415158225 | +-------------+ 2 rows in set (0.00 sec) DROP TABLE IF EXISTS t2; CREATE TABLE `t2` ( `c0` text, KEY `i0` (`c0`(1)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; insert into t2 select '-1415158225'; insert into t2 select '1415158225'; #the following two query's result has different rows, it is corret. SELECT t2.c0, ('᳙4MCcdmz') > (t2.c0) from t2; +-------------+--------------------------+ | c0 | ('᳙4MCcdmz') > (t2.c0) | +-------------+--------------------------+ | -1415158225 | 1 | | 1415158225 | 1 | +-------------+--------------------------+ SELECT t2.c0 FROM t2 WHERE ('᳙4MCcdmz') > (t2.c0); Empty set (0.00 sec)