Bug #115355 SQLs in procedure make replication throw error

Description:
When using the master-slave synchronization mode with binlog_format=statement, the SQL statement "UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = id" in the stored procedure will be rewritten by the subst_spvars(in sql/sp_instr.cc) as "UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = NAME_CONST('id',_utf8mb3't1' COLLATE 'utf8mb3_general_ci')"" and executed on the slave database. The error "Illegal mix of collations (utf8mb3_tolower_ci,EXPLICIT) and (utf8mb3_general_ci,EXPLICIT) for operation '='' on query" is reported.

The problem can be reproduced using the latest version 8.4 

How to repeat:
Build one master and one slave, use 'binlog_format=statement'

1、my.cnf example
[mysqld]
log_bin=binlog          # Enable binlog
binlog_format=statement # Use statement format

2、execute sql on master
CREATE TABLE dd_table (name VARCHAR(64) COLLATE utf8mb3_tolower_ci, UNIQUE KEY(name));
INSERT INTO dd_table VALUES('t1'), ('t2');
delimiter //
CREATE PROCEDURE sub(id CHAR(10) CHARACTER SET utf8)
BEGIN
UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci = id;
END;//
delimiter ;
call sub('t1');

3、The replication on slave has already failed.
select * from performance_schema.replication_applier_status_by_worker where last_error_number > 0;

The error message on slave replication is like : Worker 1 failed executing transaction '01204e7c-2c6b-11ef-8e95-5254004b04d4:34' at source log binlog.000005, end_log_pos 2143; Error 'Illegal mix of collations (utf8mb3_tolower_ci,EXPLICIT) and (utf8mb3_general_ci,EXPLICIT) for operation '='' on query. Default database: 'test'. Query: 'UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci =  NAME_CONST('id',_utf8mb3't1' COLLATE 'utf8mb3_general_ci')'

Suggested fix:
Some solutions, I'm not sure is there some corner cases may be error.

1. Remove append 'collate' in sp_get_item_value（see sql/sp.cc)

sql with 'UPDATE dd_table set name = 't3' where name COLLATE utf8_tolower_ci =  NAME_CONST('id',_utf8mb3't1')' is ok

2. Throw warning warning instead of error for 'Illegal mix of collations'

Hello Malt Chen,

Thank you for the report and test case.
Issue can be seen even on 8.0.37.

regards,
Umesh