Bug #5558 CONCAT(string_field,numeric_field) results in wrong charset
Submitted: 14 Sep 2004 6:59 Modified: 25 Oct 2004 13:04
Reporter: Marat Latypov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.4 OS:Microsoft Windows (WinXP)
Assigned to: Alexander Barkov CPU Architecture:Any

[14 Sep 2004 6:59] Marat Latypov
Description:
CONCAT(string_field,numeric_field)  results in wrong charset.

How to repeat:
Sorry.
I used MyODBC client instead of standart mysql client.
I just can't  grab correct russian chars from ms-dos output.
But all of this is true there too.

>select version();
4.1.4-gamma-nt                      

>show variables like "chara%";

character_set_client	cp1251
character_set_connection	koi8r
character_set_database	koi8r
character_set_results	cp1251
character_set_server	koi8r
character_set_system	utf8
character_sets_dir	C:\mysql\share\charsets/

>show create table city;
                                          
CREATE TABLE `city` (
  `id` int(11) NOT NULL auto_increment,	
  `parentid` smallint(5) unsigned NOT NULL default '0',	
  `dir` tinyint(11) default NULL,	
  `old` tinyint(11) default NULL,	
  `city` varchar(20) NOT NULL default '',	
  `ip` varchar(6) NOT NULL default '',	
  `wp` int(11) default NULL,	
  PRIMARY KEY  (`id`),	
  KEY `dir` (`dir`),	
  KEY `old` (`old`),	
  KEY `parentid` (`parentid`),	
  KEY `wp` (`wp`),	
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`wp`) REFERENCES `wp` (`id`)	
) ENGINE=InnoDB DEFAULT CHARSET=koi8r	
           
>select concat(city,' region:',2)  from city   where parentid=2 ;        
Уфа region:2
Салават region:2
Стерлитамак region:2
Ишимбай region:2
Белебей region:2
Агидель region:2
Нефтекамск region:2
Туймазы region:2
Белорецк region:2
Октябрьский region:2

#  !!! This is normal expected russian charset result

>select concat(city,' region:',parentid)  from city   where parentid=2;                                                                                                 
хЖБ region:2
уБМБЧБФ region:2
уФЕТМЙФБНБЛ region:2
йЫЙНВБК region:2
вЕМЕВЕК region:2
бЗЙДЕМШ region:2
оЕЖФЕЛБНУЛ region:2
фХКНБЪЩ region:2
вЕМПТЕГЛ region:2
пЛФСВТШУЛЙК region:2
                                                                                                                #  !!! This is INCORRECT 
#  russian charset 
     
>select convert(concat(city,' region:',parentid) using koi8r)  from city   where parentid=2;        

Уфа region:2
Салават region:2
Стерлитамак region:2
Ишимбай region:2
Белебей region:2
Агидель region:2
Нефтекамск region:2
Туймазы region:2
Белорецк region:2
Октябрьский region:2

#  !!! This is normal expected russian charset result again
[14 Sep 2004 7:14] Marat Latypov
Screenshot with correct cyrillic output

Attachment: screen.gif (image/gif, text), 22.60 KiB.

[15 Sep 2004 12:22] Marat Latypov
There are attached screenshot with natural cyrillic result.
[23 Sep 2004 9:45] Marat Latypov
Ver4.1.5 also have this bug
[25 Oct 2004 13:04] Alexander Barkov
This is not a bug. All numbers are treated as binary strings
when they appear in string context. Concatenation of a character
string and a binary sting is binary string, and it is not converted
when sent to the client. You should use CAST to CHAR to make
it work as you expect it:

CONCAT(string_field, CAST(numeric_field AS CHAR))