Bug #72426 LOWER() influences utf8_bin collation

• Submitted: 22 Apr 2014 21:23
• Modified: 26 Apr 2014 18:13
• Reporter: Wojciech Scigala
• Status: Duplicate
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 5.6.14
• OS: FreeBSD
• CPU Architecture: Any
• Tags: collation, LOWER, utf8_bin

[22 Apr 2014 21:23] Wojciech Scigala

Description:
Using LOWER() in ORDER BY sentence causes utf8_bin collation to place single-letter values first.
Reported on MySQL forums at http://forums.mysql.com/read.php?103,612862,612862

How to repeat:
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');

SELECT a FROM t ORDER BY a;  -- this gives expected result
SELECT a FROM t ORDER BY LOWER(a); -- this gives result with "m" value first

[22 Apr 2014 21:24] Wojciech Scigala

corrected tags

[22 Apr 2014 21:45] MySQL Verification Team

Please try latest released version. Thanks.

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> SET NAMES utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t (a varchar(4)) DEFAULT CHARACTER SET 'UTF8' COLLATE 'utf8_bin';
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO t VALUES ('zzz');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t VALUES ('tt');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t VALUES ('m');
Query OK, 1 row affected (0.11 sec)

mysql> INSERT INTO t VALUES ('kkk');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO t VALUES ('bb');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t VALUES ('a');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> SELECT a FROM t ORDER BY a;  -- this gives expected result
+------+
| a    |
+------+
| a    |
| bb   |
| kkk  |
| m    |
| tt   |
| zzz  |
+------+
6 rows in set (0.00 sec)

mysql> SELECT a FROM t ORDER BY LOWER(a); -- this gives result with "m" value first
+------+
| a    |
+------+
| a    |
| bb   |
| kkk  |
| m    |
| tt   |
| zzz  |
+------+
6 rows in set (0.00 sec)

mysql>

[23 Apr 2014 17:15] Rick James

Looks like a dup of bug #69005, which was fixed in 5.5.36, 5.6.16, 5.7.4.

[23 Apr 2014 17:46] MySQL Verification Team

Thank you Rick James to point the duplicate case.

[26 Apr 2014 18:13] Wojciech Scigala

I do confirm, bug fixed in 5.6.17.