| Bug #15888 | Wrong order in latin2_czech_cs collation | ||
|---|---|---|---|
| Submitted: | 20 Dec 2005 12:59 | Modified: | 6 Jan 2006 14:45 |
| Reporter: | Dusan Pavlica | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | Linux (linux) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[20 Dec 2005 13:47]
Aleksey Kishkin
verified on linux slackware against 4.1.16 and 5.0.16
mysql> DROP TABLE IF EXISTS `cz_test`;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> CREATE TABLE `cz_test` (
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `txt_utf8` char(10) character set utf8 collate utf8_czech_ci NOT NULL default
-> '',
-> `txt_cp1250` char(10) collate cp1250_czech_cs NOT NULL default '',
-> `txt_latin2` char(10) character set latin2 collate latin2_czech_cs NOT NULL
-> default '',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs;
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> INSERT INTO `cz_test` VALUES
-> (1,'0','0','0'),(2,'A','A','A'),(3,'1','1','1'),(4,'B','B','B');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM `cz_test` ORDER BY `txt_utf8`;
+----+----------+------------+------------+
| id | txt_utf8 | txt_cp1250 | txt_latin2 |
+----+----------+------------+------------+
| 1 | 0 | 0 | 0 |
| 3 | 1 | 1 | 1 |
| 2 | A | A | A |
| 4 | B | B | B |
+----+----------+------------+------------+
4 rows in set (0.03 sec)
mysql> SELECT * FROM `cz_test` ORDER BY `txt_cp1250`;
+----+----------+------------+------------+
| id | txt_utf8 | txt_cp1250 | txt_latin2 |
+----+----------+------------+------------+
| 1 | 0 | 0 | 0 |
| 3 | 1 | 1 | 1 |
| 2 | A | A | A |
| 4 | B | B | B |
+----+----------+------------+------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM `cz_test` ORDER BY `txt_latin2`;
+----+----------+------------+------------+
| id | txt_utf8 | txt_cp1250 | txt_latin2 |
+----+----------+------------+------------+
| 2 | A | A | A |
| 4 | B | B | B |
| 1 | 0 | 0 | 0 |
| 3 | 1 | 1 | 1 |
+----+----------+------------+------------+
4 rows in set (0.00 sec)
[6 Jan 2006 14:45]
Alexander Barkov
This is not a bug. This collation was designed to work this way. If you don't like this sort order, consider using another latin2 collation, or using cp1250 or utf8 or ucs2 instead.
Description: Latin2_czech_cs collation has different (wrong) order than cp1250_czech_cs or utf8_czech_ci. It sorts numbers ('0', '1', ...) after letters ('a', ... ,'z', 'A', ... , 'Z') How to repeat: DROP TABLE IF EXISTS `cz_test`; CREATE TABLE `cz_test` ( `id` int(10) unsigned NOT NULL auto_increment, `txt_utf8` char(10) character set utf8 collate utf8_czech_ci NOT NULL default '', `txt_cp1250` char(10) collate cp1250_czech_cs NOT NULL default '', `txt_latin2` char(10) character set latin2 collate latin2_czech_cs NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs; INSERT INTO `cz_test` VALUES (1,'0','0','0'),(2,'A','A','A'),(3,'1','1','1'),(4,'B','B','B'); SELECT * FROM `cz_test` ORDER BY `txt_utf8`; SELECT * FROM `cz_test` ORDER BY `txt_cp1250`; SELECT * FROM `cz_test` ORDER BY `txt_latin2`;