Bug #31555 IFNULL(some_field, 123) doesn't convert charset from UTF8
Submitted: 12 Oct 2007 8:07 Modified: 21 Jun 2010 7:48
Reporter: Vladimir Turov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.0.27, 4.1, 5.0, 5.1, 5.2 BK OS:Any
Assigned to: Alexander Barkov CPU Architecture:Any
Tags: ifnull, utf8

[12 Oct 2007 8:07] Vladimir Turov 
Description:
MySQL doesn't convert the string from UTF8 to neeeded charset if IFNULL have a non-string second parameter.

How to repeat:
-- prepearing
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test_table (id, name) VALUES (1, "ИМЯ ЗАПИСИ НА РУССКОМ");

-- in client programm
set names cp866;
SELECT IFNULL(t.name, t.id), t.name from test_table t;
-- result[0] will be in UTF8, result[1] will be in cp866
[15 Oct 2007 11:37] Sveta Smirnova 
Thank you for the report.

Verified as described.
[17 Jan 2008 11:20] Vladimir Turov 
The same we have if trying to use CONCAT() when some fields to cancat have (at least) bigint type.
This will return value in UTF8 with no look at the connection's charset:

SELECT CONCAT(varchar_field_1, varchar_field_2, ..., bigint_field, ...) FROM some_table;
[30 Jan 2008 20:01] Omer Barnir 
Workaround: Use CONVERT in the select statement e.g. CONVERT(expr USING cp866)
[4 Dec 2008 11:31] Alexander Barkov 
Another workaround:

Use CAST in the select statement e.g. CAST(expr AS CHAR)
[21 Jun 2010 7:48] Alexander Barkov 
Fixed in MySQL-5.5.

For details see:
http://forge.mysql.com/worklog/task.php?id=2649