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:
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
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))
    select v from foo where v = arg;
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.