Report Illegal mix of collations error when character_set_server/database is gbk

[zimulala]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

This case is obtained from #29199.

set names utf8mb4;
SET character_set_server= 'gbk';
CREATE DATABASE d1;
USE d1;
create table t1 (
name varchar(10),
level smallint unsigned);
show create table t1;
insert into t1 values ('string',1);
select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;

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

+---------------------------+---------------------------------+
| concat(name,space(level)) | concat(name, repeat(' ',level)) |
+---------------------------+---------------------------------+
| string                    | string                          |
+---------------------------+---------------------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

ERROR 1267 (HY000): Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8mb4_bin,COERCIBLE) for operation 'concat'

4. What is your TiDB version? (Required)

master

Release Version: v5.3.0-alpha-1230-gb7ed87de7-dirty
Edition: Community
Git Commit Hash: b7ed87de7c17ba7bc4d931ce71e68d791cb24de7

[mjonss]

/assign @xiongjiwei

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.