MySQL Bugs: #16209: Stored Function: Character Set of Parameter not converted

Bug #16209	Stored Function: Character Set of Parameter not converted
Submitted:	5 Jan 2006 1:30	Modified:	28 Jul 2006 16:47
Reporter:	Roland Volkmann	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.19-BK, 5.0.18-nt	OS:	Linux (Linux, Windows)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
If Database has default-character-set=utf8 and default-collation=utf8_unicode_ci
then you cannot use Stored Functions with String-Parameter from Client, when Parameter contains non-ASCII-Characters (e.g. german umlauts). The Problem doesn't occure on Stored Procedures.

You get error "ERROR 1406 (22001): Data too long for column 'param1' at row 1"

With best regards,

Roland.

How to repeat:
mysql> set names cp850;
Query OK, 0 rows affected (0.00 sec)

mysql> create database test character set utf8 collate utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql> connect test;
Connection id:    10
Current database: test

mysql> delimiter //
mysql> CREATE FUNCTION `f_test`(param1 VARCHAR(30))
    ->     RETURNS varchar(30)
    -> begin
    ->   return param1;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `p_test`(param1 VARCHAR(30))
    -> BEGIN
    ->   select param1 as result;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select f_test('Hallo');
+-----------------+
| f_test('Hallo') |
+-----------------+
| Hallo           |
+-----------------+
1 row in set (0.00 sec)

mysql> select f_test('Vögel');
ERROR 1406 (22001): Data too long for column 'param1' at row 1

/******  and now using Procedure instead of Function  *****/

mysql> call p_test('Vögel');
+--------+
| result |
+--------+
| Vögel  |
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

/*****  it doesn't matter which Client Encoding is used  *****/

Thank you for a bug report. Verified just as described on 5.0.19-BK on Linux:

mysql> set names cp850;
Query OK, 0 rows affected (0.00 sec)
 
mysql>  select f_test('Vögel');
+------------------+
| f_test('Vögel') |
+------------------+
| V��gel           |
+------------------+
1 row in set (0.00 sec)
 
mysql> call p_test('Vögel');
+--------+
| result |
+--------+
| V?�gel |
+--------+
1 row in set (0.01 sec)
 
Query OK, 0 rows affected, 1 warning (0.05 sec)
 
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'param1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.07 sec)
 
mysql> select 'Vögel';
+--------+
| Vögel |
+--------+
| Vögel |
+--------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.07 sec)
 
The results are different, but that warning for procedure and no warnings for function, as well as the results are surely a problem to be solved.

mysql> set names cp850;
Query OK, 0 rows affected (0.02 sec)

mysql> create database db1 character set utf8 collate utf8_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use db1
Database changed

mysql> create function f1(p varchar(30)) returns varchar(30) return p;
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure p1(p varchar(30)) select p;
Query OK, 0 rows affected (0.00 sec)

mysql> select f1('Vögel');
+--------------+
| f1('Vögel') |
+--------------+
| Vögel       |
+--------------+
1 row in set (0.00 sec)

mysql> call p1('Vögel');
+--------+
| p      |
+--------+
| Vögel |
+--------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.25-debug |
+--------------+
1 row in set (0.00 sec)