LOWER() influences utf8_bin collation?
I've encountered a strange behaviour of LOWER() function, which in some way influences sorting behaviour. The database and table are all UTF8, with collation set to utf8_bin. Mysql 5.6.14 on FreeBSD.
Example code:
SET NAMES utf8 COLLATE utf8_bin;
CREATE TABLE t (a varchar(4)) DEFAULT CHARACTER SET 'UTF8' COLLATE 'utf8_bin';
INSERT INTO t VALUES ('zzz');
INSERT INTO t VALUES ('tt');
INSERT INTO t VALUES ('m');
INSERT INTO t VALUES ('kkk');
INSERT INTO t VALUES ('bb');
INSERT INTO t VALUES ('a');
mysql> SELECT a FROM t ORDER BY a;
+------+
| a |
+------+
| a |
| bb |
| kkk |
| m |
| tt |
| zzz |
+------+
mysql> SELECT a FROM t ORDER BY LOWER(a);
+------+
| a |
+------+
| m |
| a |
| bb |
| kkk |
| tt |
| zzz |
+------+
6 rows in set (0.00 sec)
While both SELECTs should return exactly same values, they do not. LOWERed results have single-letter values ordered first, I have no idea why. Can somebody shed some light?
PS. I know I can use utf8_general_ci for case-insensitive ordering. I'm not asking for a walkaround solution ;)
Subject
Views
Written By
Posted
LOWER() influences utf8_bin collation?
3890
April 19, 2014 04:03AM
1811
April 21, 2014 11:43PM
1515
April 22, 2014 03:25PM
1431
April 23, 2014 11:13AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.