Bug #9402 Don`t work convertation from latin1 to cp1251 or incorrect collation cp1251_bin

• Submitted: 25 Mar 2005 10:28
• Modified: 20 May 2005 9:46
• Reporter: Alex Popov
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: MySQL 4.1.3
• OS: Windows (Windows XP SP1)
• Assigned to: Vasily Kishkin
• CPU Architecture: Any

[25 Mar 2005 10:28] Alex Popov

Description:
Incorrect sort next constracion:
SELECT *  FROM cities ORDER BY convert ( _latin1 'cityname' USING cp1251 ) COLLATE cp1251_bin
SELECT *  FROM cities ORDER BY CAST(_latin1 'cityname' AS CHAR CHARACTER SET cp1251) COLLATE cp1251_bin

This constraction is correct sort:
SELECT * FROM `cities` ORDER BY cityname COLLATE latin1_bin

How to repeat:

show variables;
+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| character_set_client            | latin1                   |
| character_set_connection        | latin1                   |
| character_set_database          | latin1                   |
| character_set_results           | latin1                   |
| character_set_server            | latin1                   |
| character_set_system            | utf8                     |
| character_sets_dir              | c:\mysql\share\charsets/ |
| collation_connection            | latin1_swedish_ci        |
| collation_database              | latin1_swedish_ci        |
| collation_server                | latin1_swedish_ci        |
+---------------------------------+--------------------------+

CREATE TABLE `cities` (
  `cityid` int(10) unsigned NOT NULL auto_increment,
  `cityname` char(20) NOT NULL default '',
  `countryid` char(100) NOT NULL default '',
  PRIMARY KEY  (`cityid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Fill table russian words and do sort.

[25 Mar 2005 10:30] Alex Popov

none

[25 Mar 2005 14:06] Alex Popov

--
-- Table structure for table `cities`
--
DROP TABLE IF EXISTS `cities`;
CREATE TABLE `cities` (
  `cityid` int(10) unsigned NOT NULL auto_increment,
  `cityname` char(20) NOT NULL default '',
  `countryid` char(100) NOT NULL default '',
  PRIMARY KEY  (`cityid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `cities`
--

/*!40000 ALTER TABLE `cities` DISABLE KEYS */;
LOCK TABLES `cities` WRITE;
INSERT INTO `cities` VALUES (1,'Çàïîðîæüå','1'),(2,'Äíåïðîïåòðîâñê','1'),(3,'Êèåâ','1'),(4,'Âèííèöà','1'),(5,'Äîíåöê','1'),(6,'Æèòîìèð','1'),(7,'Èâàíî-Ôðàíêîâñê','1'),(8,'Êèðîâîãðàä','1'),(9,'Ëóãàíñê','1'),(10,'Ëóöê','1'),(11,'Ëüâîâ','1'),(12,'Íèêîëàåâ','1'),(13,'Îäåññà','1'),(14,'Ïîëòàâà','1'),(15,'Ðîâíî','1'),(16,'Ñåâàñòîïîëü','1'),(17,'Ñèìôåðîïîëü','1'),(18,'Ñóìû','1'),(19,'Òåðíîïîëü','1'),(20,'Óæãîðîä','1'),(21,'Õàðüêîâ','1'),(22,'Õåðñîí','1'),(23,'Õìåëüíèöêèé','1'),(24,'×åðêàññû','1'),(25,'×åðíîâöû','1'),(26,'×åðíèãîâ','1'),(27,'Ìîñêâà','2');
UNLOCK TABLES;

[20 May 2005 6:29] Vasily Kishkin

Tested on Win 2000 Sp4, MySQL server 4.1.11

[20 May 2005 9:46] Vasily Kishkin

Sorry... but you made a mistake in your queries.
When you send the query:
SELECT *  FROM cities ORDER BY convert ( _latin1 'cityname' USING cp1251 )
COLLATE cp1251_bin
The _latin1 'cityname'   is constant.
When you create a table and want to store russian values. You need to set right 
default charset for the table.