Illegal mix of collations for operation 'UNION'

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Illegal mix of collations for operation ‘UNION’

| username: TiDBer_puCBPHsR

[Test Environment] TiDB
[TiDB Version] v6.2.0
[Issue] The following SQL reports an error: 1271 - Illegal mix of collations for operation ‘UNION’

select distinct a.ti_jgywlb check_value from tcsjsjg a 
union all  
select 'TGZX' check_value from dual

[Configuration Information]

  1. Database server character set: export LANG=“zh_CN.gbk”
  2. Create database character set: CREATE DATABASE fm CHARACTER SET gbk COLLATE gbk_bin;
  3. Table character set:
create table tcsjsjg
(
 ...
) default charset=gbk collate gbk_bin;

What could be the reason for this issue?

2
| username: tidb菜鸟一只 | Original post link

Let’s try it first.

3
| username: 小于同学 | Original post link

Waiting for an answer.

4
| username: yytest | Original post link

Take a look at the execution plan.

5
| username: zhaokede | Original post link

His issue is a character set mismatch, which probably won’t be reflected in the execution plan.

6
| username: zhaokede | Original post link

Have you checked the character set and collation of dual;
Or directly use the second sentence SELECT ‘TGZX’ check_value
to check the collation of the corresponding columns in each SELECT query involving UNION and ensure they are the same.

7
| username: TiDBer_H5NdJb5Q | Original post link

Correct, select specifies the character set.