Collation Problem with Function

Mariadb/MySQL collation problem when returning a value from a function.

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

Very important !!!

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
Last update: Tue, 13 Sep 2022 14:32:15