I was faced with strange server behavior MySQL 5.1.50. It sorts records incorrectly.
For example I have created a table test
:
CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `title`) VALUES
(1, 'record1'),
(2, 'record2'),
(3, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),
(4, 'ABCDEFGHIJKLMNOPQRSTUVWXYY');
and make an query:
mysql> set names utf8; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test order by title asc;
+----+----------------------------+
| id | title |
+----+----------------------------+
| 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ |
| 4 | ABCDEFGHIJKLMNOPQRSTUVWXYY |
| 1 | record1 |
| 2 | record2 |
+----+----------------------------+
4 rows in set (0.00 sec)
mysql> select * from test order by title desc;
+----+----------------------------+
| id | title |
+----+----------------------------+
| 2 | record2 |
| 1 | record1 |
| 3 | ABCDEFGHIJKLMNOPQRSTUVWXYZ |
| 4 | ABCDEFGHIJKLMNOPQRSTUVWXYY |
+----+----------------------------+
4 rows in set (0.00 sec)
As you see records 3 and 4 don't change places
I did such query and some letters does not change an order, for example, A and a.
mysql> SELECT * FROM test ORDER BY title COLLATE utf8_unicode_ci ASC;
+----+---------+
| id | title |
+----+---------+
| 1 | A |
| 27 | a |
| 28 | b |
| 2 | B |
| 29 | c |
| 3 | C |
| 4 | D |
| 30 | d |
| 31 | e |
| 5 | E |
| 6 | F |
| 32 | f |
| 33 | g |
| 7 | G |
| 34 | h |
| 8 | H |
| 35 | i |
| 9 | I |
| 36 | j |
| 10 | J |
| 11 | K |
| 37 | k |
| 12 | L |
| 38 | l |
| 39 | m |
| 13 | M |
| 40 | n |
| 14 | N |
| 41 | o |
| 15 | O |
| 42 | p |
| 16 | P |
| 17 | Q |
| 43 | q |
| 44 | r |
| 18 | R |
| 19 | S |
| 45 | s |
| 20 | T |
| 46 | t |
| 21 | U |
| 47 | u |
| 48 | v |
| 22 | V |
| 49 | w |
| 23 | W |
| 50 | x |
| 24 | X |
| 25 | Y |
| 51 | y |
| 26 | Z |
| 52 | z |
+----+---------+
mysql> SELECT * FROM test ORDER BY title COLLATE utf8_unicode_ci DESC;
+----+---------+
| id | title |
+----+---------+
| 52 | z |
| 26 | Z |
| 25 | Y |
| 51 | y |
| 50 | x |
| 24 | X |
| 49 | w |
| 23 | W |
| 48 | v |
| 22 | V |
| 47 | u |
| 21 | U |
| 20 | T |
| 46 | t |
| 45 | s |
| 19 | S |
| 18 | R |
| 44 | r |
| 17 | Q |
| 43 | q |
| 16 | P |
| 42 | p |
| 41 | o |
| 15 | O |
| 40 | n |
| 14 | N |
| 39 | m |
| 13 | M |
| 12 | L |
| 38 | l |
| 11 | K |
| 37 | k |
| 10 | J |
| 36 | j |
| 9 | I |
| 35 | i |
| 8 | H |
| 34 | h |
| 7 | G |
| 33 | g |
| 32 | f |
| 6 | F |
| 5 | E |
| 31 | e |
| 4 | D |
| 30 | d |
| 29 | c |
| 3 | C |
| 2 | B |
| 28 | b |
| 1 | A |
| 27 | a |
+----+---------+
I think that it is the bug with collation.
May be someone ran into such conduct of server?