Bug #119367 query function coalesce occur error 'Cannot convert string 'xxx from binary to utf8mb4'

• Submitted: 12 Nov 6:18
• Reporter: david silva
• Status: Open
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 9.1.0
• OS: Any
• CPU Architecture: Any
• Tags: coalesce Cannot convert string

[12 Nov 6:18] david silva

Description:
query function coalesce occur error 'Cannot convert string 'xxx from binary to utf8mb4'

mysql> create table t1(c1 int, c2 char(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 values(1, '1');
Query OK, 1 row affected (0.01 sec)

mysql> select coalesce(char(18599510.67), c2) from t1;
ERROR 3854 (HY000): Cannot convert string '\x01\x1B\xCEW' from binary to utf8mb4

How to repeat:
create table t1(c1 int, c2 char(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into t1 values(1, '1');
select coalesce(char(18599510.67), c2) from t1;

Suggested fix:
run no error.