This only occurs when your database collation is not equal to the table collations.
This has been tested with Mariadb version 10
Assumption:
When defining a function, there is a big difference between this:
CREATE DEFINER=`secret` FUNCTION `getSomeName`()
RETURNS text CHARSET utf8mb4
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Testing the collation of the name'
BEGIN
declare supportuser char(100);
select concat(users.surname,' ', users.name)
into supportuser
from users
where users.id = (
select user
from seminars_support
where seminar = parm_seminar
and active = 1
);
RETURN supportuser;
END
This returns:
Isfahan Bo?azl?yan
and:
CREATE DEFINER=`secret` FUNCTION `getSomeName`()
RETURNS text CHARSET utf8mb4
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Testing the collation of the name'
BEGIN
set @supportuser = '';
select concat(users.surname,' ', users.name)
into @supportuser
from users
where users.id = (
select user
from seminars_support
where seminar = parm_seminar
and active = 1
);
return @supportuser;
END
returns:
Isfahan Boğazlıyan
You have to initialize the variable at the start of the routine. Otherwise the previous value will be the result of the field in case the result of the following query is NULL.
The results will also be wrong when the CHARSET is left out. Then it will use the default latin1
, which shows the same garbled result.
RETURNS text CHARSET utf8mb4