Bug #93256 CREATE PROCEDURE/FUNCTION should store the collation for its arguments
Submitted: 20 Nov 2018 10:29
Reporter: Georgi Kodinov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any

[20 Nov 2018 10:29] Georgi Kodinov 
Description:
Stored programs, unlike table definitions for example, do not store the collation of the arguments and the return values if not explicitly specified. 
This causes error,as in for example bug#28099926.

How to repeat:
drop table if exists foo;
drop procedure if exists p;
set session default_collation_for_utf8mb4=utf8mb4_0900_ai_ci;
create table foo (v varchar(10));
delimiter $$
create procedure p(arg varchar(10))
deterministic
BEGIN
    select v from foo where v = arg;
END$$
delimiter ;
set session default_collation_for_utf8mb4=utf8mb4_general_ci;
call p('x');

Suggested fix:
Make stored programs store the collation if not specified explicitly.
[20 Nov 2018 10:33] Georgi Kodinov 
Posted by developer:
 
Please do not forget to revert the fix for bug #28099926 when fixing this bug !
[10 May 2019 7:43] Erlend Dahl 
Bug#91072 default_collation_for_utf8mb4 leads ERROR 1267 (HY000): Illegal mix of collation

was marked as a duplicate.