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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.1.4
• OS: 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))