Bug #9041 collation utf8_swedish_ci sorts Ö,Ä,Å instead of Å,Ä,Ö

• Submitted: 8 Mar 2005 8:17
• Modified: 26 Jun 2005 8:29
• Reporter: Pontus Svefors
• Status: Can't repeat
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 4.1.9
• OS: Windows (WINDOWS)
• CPU Architecture: Any

[8 Mar 2005 8:17] Pontus Svefors

Description:
I hope I do the right ting reporting here. Have found nothing on the web and really tried hard to find any error in my setup.

I have tables in utf8 using mysql 4.1.9 with php5 and apache2. Everything works fine except for the order_by statment that inverse the order of the 3 last characters of the swedish alfabet.

Ive started a dos window and performed the query from mysql. I copy the result below. 

Thanks for a fantastic database.

Pontus

select city_name from city order by city_name collate utf8_swedish_ci;

It returns,

| Ötvidaberg       |
| Överkalix        |
| Övertorneå      |
| Åmål            |
| Ånge             |
| Åre              |
| Årjäng          |
| Åsele            |
| Åstorp           |
| Älmhult          |
| Älvdalen         |
| Älvkarleby       |
| Älvsbyn          |
| Ängelholm        |
| Ale               |
| Alingsås         |
| Alvesta           |
| Aneby             |
| Arboga            |
| Arjeplog          |
| Arvidsjaur        |
| Arvika            |
| Askersund         |
| Avesta            |
| Båstad           |
| Bengtsfors        |
| Berg              |
| Bjurholm          |
| Bjuv              |
| Blekinge          |
| Boden             |
| Bollebygd         |
| Bollnäs          |
| Borås            |
| Borgholm          |
| Borlänge         |
| Botkyrka          |
| Boxholm           |
| Bräcke           |
| Brom├Âlla         |
| Burl├Âv           |
| Dalarna           |
| Dals-Ed           |
| Danderyd          |
| Degerfors         |
| Dorotea           |
| Eda               |
| Eker├            |
| Eksj├            |
| Emmaboda          |
| Enk├Âping         |
| Eskilstuna        |
| Esl├Âv            |
| Essunga           |
| Färgelanda       |
| Fagersta          |
| Falk├Âping        |
| Falkenberg        |
| Falun             |
| Filipstad         |
| Finspång         |
| Flen              |
| Forshaga          |
| G├Âteborg         |
| G├Âtene           |
| Gällivare        |
| Gävle            |
| Gävleborg        |
| Gagnef            |
| Gislaved          |
| Gnesta            |
| Gnosj├           |
| Gotland           |
| Grästorp         |
| Grums             |
| Gullspång        |
| H├Â├Âr            |
| H├Âgan├ñs         |
| H├Âgsby           |
| H├Ârby            |
| Hällefors        |
| Härjedalen       |
| H├ñrn├Âsand       |
| Härryda          |
| Hässleholm       |
| Håbo             |
| Habo              |
| Hagfors           |
| Halland           |
| Hallsberg         |
| Hallstahammar     |
| Halmstad          |
| Hammar├          |
| Haninge           |
| Haparanda         |
| Heby              |
| Hedemora          |
| Helsingborg       |
| Herrljunga        |
| Hjo               |
| Hofors            |
| Huddinge          |
| Hudiksvall        |
| Hultsfred         |
| Hylte             |
| J├Ânk├Âping       |
| Jämtland         |
| Järfälla        |
| Jokkmokk          |
| K├Âping           |
| Kävlinge         |
| Kalix             |
| Kalmar            |
| Karlsborg         |
| Karlshamn         |
| Karlskoga         |
| Karlskrona        |
| Karlstad          |
| Katrineholm       |
| Kil               |
| Kinda             |
| Kiruna            |
| Klippan           |
| Kramfors          |
| Kristianstad      |
| Kristinehamn      |
| Krokom            |
| Kronoberg         |
| Kumla             |
| Kungälv          |
| Kungs├Âr          |
| Kungsbacka        |
| Länsnamn         |
| Laholm            |
| Landskrona        |
| Laxå             |
| Lekeberg          |
| Leksand           |
| Lerum             |
| Lessebo           |
| Liding├          |
| Lidk├Âping        |
| Lilla Edet        |
| Lindesberg        |
| Link├Âping        |
| Ljungby           |
| Ljusdal           |
| Ljusnarsberg      |
| Lomma             |
| Ludvika           |
| Luleå            |
| Lund              |
| Lycksele          |
| Lysekil           |
| M├Âlndal          |
| M├Ânster├Ñs       |
| M├Ârbyl├Ñnga      |
| Malå             |
| Malm├            |
| Malung            |
| Mariestad         |
| Mark              |
| Markaryd          |
| Mellerud          |
| Mj├Âlby           |
| Mora              |
| Motala            |
| Mullsj├          |
| Munkedal          |
| Munkfors          |
| N├ñssj├          |
| Nacka             |
| Nora              |
| Norberg           |
| Nordanstig        |
| Nordmaling        |
| Norrbotten        |
| Norrk├Âping       |
| Norrtälje        |
| Norsj├           |
| Nybro             |
| Nyk├Âping         |
| Nykvarn           |
| Nynäshamn        |
| Ockelbo           |
| Olofstr├Âm        |
| Orsa              |
| Orust             |
| Osby              |
| Oskarshamn        |
| Ovanåker         |
| Oxel├Âsund        |
| Pajala            |
| Partille          |
| Perstorp          |
| Piteå            |
| Rättvik          |
| Ragunda           |
| Riket totalt      |
| Robertsfors       |
| Ronneby           |
| S├Âderhamn        |
| S├Âderk├Âping     |
| S├Âdermanland     |
| S├Âdert├ñlje      |
| S├Âlvesborg       |
| Säffle           |
| Säter            |
| S├ñvsj├          |
| Sala              |
| Salem             |
| Sandviken         |
| Sigtuna           |
| Simrishamn        |
| Sj├Âbo            |
| Sk├Âvde           |
| Skåne            |
| Skara             |
| Skellefteå       |
| Skinnskatteberg   |
| Skurup            |
| Smedjebacken      |
| Sollefteå        |
| Sollentuna        |
| Solna             |
| Sorsele           |
| Sotenäs          |
| Staffanstorp      |
| Stenungsund       |
| Stockholm         |
| Storfors          |
| Storuman          |
| Str├Âmstad        |
| Str├Âmsund        |
| Strängnäs       |
| Sundbyberg        |
| Sundsvall         |
| Sunne             |
| Surahammar        |
| Sval├Âv           |
| Svedala           |
| Svenljunga        |
| T├Âreboda         |
| Täby             |
| Tanum             |
| Tibro             |
| Tidaholm          |
| Tierp             |
| Timrå            |
| Tingsryd          |
| Tj├Ârn            |
| Tomelilla         |
| Torsås           |
| Torsby            |
| Tranås           |
| Tranemo           |
| Trelleborg        |
| Trollhättan      |
| Trosa             |
| Tyres├           |
| Uddevalla         |
| Ulricehamn        |
| Umeå             |
| Upplands-Bro      |
| Upplands-Väsby   |
| Uppsala           |
| Uppvidinge        |
| Vänersborg       |
| Vännäs          |
| V├ñrmd├          |
| Värmland         |
| Värnamo          |
| Västerås        |
| Västerbotten     |
| Västernorrland   |
| Västervik        |
| Västmanland      |
| V├ñstra G├Âtaland |
| V├ñxj├           |
| Vårgårda        |
| Vadstena          |
| Vaggeryd          |
| Valdemarsvik      |
| Vallentuna        |
| Vansbro           |
| Vara              |
| Varberg           |
| Vaxholm           |
| Vellinge          |
| Vetlanda          |
| Vilhelmina        |
| Vimmerby          |
| Vindeln           |
| Vingåker         |
| Ydre              |
| Ystad             |
+-------------------+

How to repeat:
select city_name from city order by city_name collate utf8_swedish_ci;

[21 Jun 2005 16:50] Aleksey Kishkin

Hi. tested on linux and get:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 4.1.9-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select city_name from city order by city_name collate utf8_swedish_ci;
+-----------+
| city_name |
+-----------+
| Å         |
| Ä         |
| Ö         |
+-----------+
3 rows in set (0.00 sec)

mysql> select city_name from city order by city_name;
+-----------+
| city_name |
+-----------+
| Ä         |
| Å         |
| Ö         |
+-----------+
3 rows in set (0.00 sec)

I plan to check it on windows. What windows do you use?

[26 Jun 2005 8:29] Aleksey Kishkin

tested on windows, screenshoot attached, sorry, was not able to reproduce.

[26 Jun 2005 8:29] Aleksey Kishkin

screenshoot of result set with utf8 swedish collate

Attachment: bug9041.JPG (image/jpeg, text), 72.82 KiB.

[24 Jan 2006 15:02] Björn Lindqvist

I can reproduce this bug:

mysql> create table t1(
    -> a varchar(4) character set utf8 collate utf8_swedish_ci);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 values('a'), ('b'), ('å'), ('ä'), ('ö');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t1 order by a;
+------+
| a    |
+------+
| a    |
| ö   |
| ä   |
| å   |
| b    |
+------+
5 rows in set (0.01 sec)

MySQL version 5.0.18 on Debian from the MySQL deb-package.

[21 Apr 2010 17:46] Petteri Aimonen

I think the bug is caused by wrong connection character set. I had the same problem but after running these commands it works:
SET character_set_connection = utf8;
SET character_set_results = utf8;
SET character_set_client = utf8;