| Bug #108269 | Have the same character set and collation, but different results | ||
|---|---|---|---|
| Submitted: | 24 Aug 2022 14:49 | Modified: | 25 Aug 2022 12:07 |
| Reporter: | qing yang | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.7.38 8.0.29 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[25 Aug 2022 10:56]
Bernt Marius Johnsen
In 5.7.39 I get
mysql> create table t_group2 (id int auto_increment primary key,stu_name varchar(10),course varchar(10),score int) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0,04 sec)
mysql> insert into t_group2(stu_name,course,score) values ('张三','语文',80),('李四','数学',90),('李四','语文',100),('王五','数学',80),('王五','语文',90),('张三','数学',100);
Query OK, 6 rows affected (0,00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t_group2 order by course,score desc;
+----+----------+--------+-------+
| id | stu_name | course | score |
+----+----------+--------+-------+
| 6 | 张三 | 数学 | 100 |
| 2 | 李四 | 数学 | 90 |
| 4 | 王五 | 数学 | 80 |
| 3 | 李四 | 语文 | 100 |
| 5 | 王五 | 语文 | 90 |
| 1 | 张三 | 语文 | 80 |
+----+----------+--------+-------+
6 rows in set (0,00 sec)
Which the same as in 8.0
[25 Aug 2022 12:07]
MySQL Verification Team
Thank you, Mr. Johnsen, I have got the same results with latest releases of 5.7 and 8.0. Hence, this test case can not be repeated.
Description: When creating the same table in different versions, tables have the same character set and collation, but different results with chinese or other How to repeat: -- create table and insert create table t_group2 (id int auto_increment primary key,stu_name varchar(10),course varchar(10),score int) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; insert into t_group2(stu_name,course,score) values ('张三','语文',80),('李四','数学',90),('李四','语文',100),('王五','数学',80),('王五','语文',90),('张三','数学',100); -- 8.0.29 root@8.0.29 [employees]> select * from t_group2 order by course,score desc; +----+----------+--------+-------+ | id | stu_name | course | score | +----+----------+--------+-------+ | 6 | 张三 | 数学 | 100 | ------ CHECK 数学 | 2 | 李四 | 数学 | 90 | | 4 | 王五 | 数学 | 80 | | 3 | 李四 | 语文 | 100 | | 5 | 王五 | 语文 | 90 | | 1 | 张三 | 语文 | 80 | +----+----------+--------+-------+ 6 rows in set (0.00 sec) -- 5.7.38 root@5.7.38 [employees]> select * from t_group2 order by course,score desc; +----+----------+--------+-------+ | id | stu_name | course | score | +----+----------+--------+-------+ | 3 | 李四 | 语文 | 100 | ------ CHECK 语文 | 5 | 王五 | 语文 | 90 | | 1 | 张三 | 语文 | 80 | | 6 | 张三 | 数学 | 100 | | 2 | 李四 | 数学 | 90 | | 4 | 王五 | 数学 | 80 | +----+----------+--------+-------+ 6 rows in set (0.00 sec)