Pushdown TopN into TiKV with cast function meets incorrect result

[Defined2014]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `update_user` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into test values(1,'张三');
insert into test values(2,'李四');
insert into test values(3,'张三');
insert into test values(4,'李四');
insert into test values(5,'张三');
insert into test values(6,'李四');
insert into test values(7,'张三');
insert into test values(8,'李四');
insert into test values(9,'张三');
insert into test values(10,'李四');
insert into test values(11,'张三');
insert into test values(12,'李四');
insert into test values(13,'张三');
insert into test values(14,'李四'); 
select * from test order by cast(update_user as char character set gbk) desc , id limit 10; 

Another test case

CREATE TABLE `test` (
  `id` bigint NOT NULL,
  `update_user` varchar(32) CHARACTER SET gbk COLLATE gbk_chinese_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into test values(1,'张三');
insert into test values(2,'李四');
insert into test values(3,'张三');
insert into test values(4,'李四');
insert into test values(5,'张三');
insert into test values(6,'李四');
insert into test values(7,'张三');
insert into test values(8,'李四');
insert into test values(9,'张三');
insert into test values(10,'李四');
insert into test values(11,'张三');
insert into test values(12,'李四');
insert into test values(13,'张三');
insert into test values(14,'李四'); 
select * from test order by cast(update_user as char) desc , id limit 10; 

2. What did you expect to see? (Required)

MySQL [test]>  select * from test order by cast(update_user as char character set gbk) desc , id limit 10;   
+----+-------------+
| id | update_user |
+----+-------------+
|  1 | 张三        |
|  3 | 张三        |
|  5 | 张三        |
|  7 | 张三        |
|  9 | 张三        |
| 11 | 张三        |
| 13 | 张三        |
|  2 | 李四        |
|  4 | 李四        |
|  6 | 李四        |
+----+-------------+
10 rows in set (0.00 sec)

Second case

mysql> select * from test order by cast(update_user as char) desc , id limit 10;
+----+-------------+
| id | update_user |
+----+-------------+
|  2 | 李四        |
|  4 | 李四        |
|  6 | 李四        |
|  8 | 李四        |
| 10 | 李四        |
| 12 | 李四        |
| 14 | 李四        |
|  1 | 张三        |
|  3 | 张三        |
|  5 | 张三        |
+----+-------------+
10 rows in set (0.00 sec)

3. What did you see instead (Required)

MySQL [test]>  select * from test order by cast(update_user as char character set gbk) desc, id limit 10; 
+----+-------------+
| id | update_user |
+----+-------------+
|  1 | 张三        |
|  3 | 张三        |
|  5 | 张三        |
|  2 | 李四        |
|  4 | 李四        |
|  6 | 李四        |
|  8 | 李四        |
| 10 | 李四        |
| 12 | 李四        |
| 14 | 李四        |
+----+-------------+
10 rows in set (0.00 sec)

Second case

mysql> select * from test order by cast(update_user as char) desc , id limit 10;
+----+-------------+
| id | update_user |
+----+-------------+
|  2 | 李四        |
|  4 | 李四        |
|  6 | 李四        |
|  1 | 张三        |
|  3 | 张三        |
|  5 | 张三        |
|  7 | 张三        |
|  9 | 张三        |
| 11 | 张三        |
| 13 | 张三        |
+----+-------------+
10 rows in set (25.76 sec)

4. What is your TiDB version? (Required)

v7.5.1, nightly(v8.3.0+) and more

[Defined2014]

The typeFiled of cast function is

{
    charset: "gbk",
    collation: "utf8bm4_bin"
}

It's set by

tidb/pkg/expression/expr_to_pb.go

Line 291 in 74404a4

tp.SetCollate(str1)