MySQL Bugs: #16211: Stored function return type for strings is ignored

Bug #16211	Stored function return type for strings is ignored
Submitted:	5 Jan 2006 6:25	Modified:	15 Aug 2006 12:46
Reporter:	R T
Status:	Closed
Category:	Server: SP	Severity:	S2 (Serious)
Version:	5.0.20	OS:	Microsoft Windows (Windows XP SP2/Linux)
Assigned to:	Alexander Nozdrin	Target Version:

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.

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)

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>

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.

BUG#16330 has been marked as a duplicate of this bug.

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

Approved with several important notes sent over email.

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

A new patch: http://lists.mysql.com/commits/9488

Approved by email with a few minor comments.

The final patch:
http://lists.mysql.com/commits/9665

Pushed into 5.0.25

Merged into 5.1.12

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