Bug #17615 invalid handling of function results in UPDATE...SET statement
Submitted: 21 Feb 2006 14:39 Modified: 4 Mar 2006 9:21
Reporter: Andrey Kazachkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.18-nt/5.0 BK OS:Windows (Windows/Suse Linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[21 Feb 2006 14:39] Andrey Kazachkov 
Description:
Updating the table column of VARCHAR(256) UNICODE type with the value returned by a function, results to storing in the column characters from ansi charset which are interpreted as ucs2 nevertheless.

How to repeat:
Run following script and you'll see that instead of storing 'foo string' string some invalid characters have been stored. 
-------------------------------------------------------
delimiter GO

DROP DATABASE IF EXISTS ak_test_db
GO

CREATE DATABASE ak_test_db  DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`
GO

USE ak_test_db
GO

CREATE TABLE t_test_1(
	nId int AUTO_INCREMENT PRIMARY KEY,
	wstrName VARCHAR(256) UNICODE 
)ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`
GO

INSERT t_test_1(wstrName) VALUES(NULL)
GO

CREATE FUNCTION `loc_getstring`() RETURNS varchar(256) UNICODE
BEGIN
    
    DECLARE `__vtmp_result` VARCHAR(256) UNICODE;

    SET `__vtmp_result` = 'foo string';
    RETURN (`__vtmp_result` );
END
GO

UPDATE `t_test_1` SET `wstrName`= `loc_getstring`() WHERE `nId` = 1
GO

SELECT `t_test_1`.`wstrName`, `loc_getstring`() "must be" FROM `t_test_1`
GO

-------------------------------------------------------

The above script results in 

+-----------------+------------+
| wstrName        | must be    |
+-----------------+------------+
| цЩпц╝ачН┤чЙйц╣з      | foo string |
+-----------------+------------+
[21 Feb 2006 16:32] MySQL Verification Team 
Thank you for the bug report. Verified also on Linux.
[22 Feb 2006 23:37] Calvin Sun 
The engine type is InnoDB.
[2 Mar 2006 11:09] 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/3384
[3 Mar 2006 5:10] Alexander Barkov 
The patch looks ok to push.
[3 Mar 2006 11:13] Ramil Kalimullin 
fixed in 5.0.20
[4 Mar 2006 9:21] 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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.20 changelog; closed.