Bug #16211 Stored function return type for strings is ignored
Submitted: 5 Jan 2006 5:25 Modified: 15 Aug 2006 10:46
Reporter: Ryan T Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.20 OS:Windows (Windows XP SP2/Linux)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[5 Jan 2006 5:25] Ryan T
Description:
The return values of stored functions which return string values are always of charset binary.  This would seem to make it not possible to use the return value in a case-insensitive search.

How to repeat:
create a function like this one:
create function GetCustomerName (customer_id INT) returns varchar(100) charset latin1
begin
  return 'John Smith';
end

now if you check the charset of the return value it's binary even though the the function is declared to return latin1

select charset(GetCustomerName(1));

so if the return value is used with a LIKE operator like so:
select 'test' FROM mysql.db WHERE GetCustomerName(2) LIKE 'j%' LIMIT 1

it will return no rows because John does not match 'j%' :(

Suggested fix:
I would like to know why the return value is charset binary.  Should it not be latin1?  Is there a workaround for this possible problem?

I suppose this would work:

... WHERE CONVERT(GetCustomerName(2) USING latin1) LIKE 'j%' ....

but it's ugly.
[5 Jan 2006 15:53] MySQL Verification Team
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> create function GetCustomerName (customer_id INT) returns varchar(100) charset
    -> latin1
    -> begin
    ->   return 'John Smith';
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> select charset(GetCustomerName(1));
+-----------------------------+
| charset(GetCustomerName(1)) |
+-----------------------------+
| binary                      |
+-----------------------------+
1 row in set (0.01 sec)
[12 Apr 2006 16:00] MySQL Verification Team
In a recent bk version on windows 2000:

Your MySQL connection id is 1 to server version: 5.0.21

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> create function GetCustomerName (customer_id INT) returns varchar(100) charset
    -> latin1
    -> begin
    ->   return 'John Smith';
    -> end//
Query OK, 0 rows affected (0.11 sec)

mysql> delimiter ;
mysql> select charset(GetCustomerName(1));
+-----------------------------+
| charset(GetCustomerName(1)) |
+-----------------------------+
| latin1                      |
+-----------------------------+
1 row in set (0.00 sec)

mysql>
[13 Apr 2006 8:39] Valeriy Kravchuk
In 5.0.20 return type is not always binary - it is the default charset for server. So, in stored functions clauses like:

... returns varchar(100) character set utf8

are still ignored! I had changed the synopsis accordingly.
[22 Jun 2006 15:09] Alexander Nozdrin
BUG#16330 has been marked as a duplicate of this bug.
[28 Jun 2006 12:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8408
[30 Jun 2006 13:02] Konstantin Osipov
Approved with several important notes sent over email.
[18 Jul 2006 16:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9286
[24 Jul 2006 11:44] Alexander Nozdrin
A new patch: http://lists.mysql.com/commits/9488
[26 Jul 2006 19:59] Konstantin Osipov
Approved by email with a few minor comments.
[27 Jul 2006 14:01] Alexander Nozdrin
The final patch:
http://lists.mysql.com/commits/9665
[2 Aug 2006 15:50] Konstantin Osipov
Pushed into 5.0.25
[14 Aug 2006 20:45] Konstantin Osipov
Merged into 5.1.12
[15 Aug 2006 10:46] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25 and 5.1.12 changelogs