Bug #69005 Order by lower(column) produces out-of-order results
Submitted: 19 Apr 2013 5:01 Modified: 11 Dec 2013 11:53
Reporter: Bryan Turner Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.11 OS:Linux (Ubuntu 12.04 LTS)
Assigned to: CPU Architecture:Any
Tags: regression

[19 Apr 2013 5:01] Bryan Turner 
Description:
Given a table with a column "name", running a query with "order by lower(name) asc" sometimes produces incorrectly-ordered results. When it does, it is _consistent_; the same query executed multiple times always produces incorrect results. However, the same order by clause on other tables will produce correctly-ordered results.

I first noticed this with Hibernate queries returning unexpected results, but I was able to replicate the issue using the mysql command line tool directly.

How to repeat:
The following is an excerpt of a dump taken from a schema which exhibits this problem:

-- MySQL dump 10.13  Distrib 5.6.11, for debian6.0 (x86_64)
--
-- Host: localhost    Database: oops
-- ------------------------------------------------------
-- Server version       5.6.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `project`
--

DROP TABLE IF EXISTS `project`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project` (
  `id` int(11) NOT NULL,
  `name` varchar(64) COLLATE utf8_bin NOT NULL,
  `project_key` varchar(64) COLLATE utf8_bin NOT NULL,
  `description` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `project_type` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_project_key` (`project_key`),
  UNIQUE KEY `uk_project_name` (`name`),
  KEY `idx_project_type` (`project_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `project`
--

LOCK TABLES `project` WRITE;
/*!40000 ALTER TABLE `project` DISABLE KEYS */;
INSERT INTO `project` VALUES (1,'TEST1','TEST1','Test Project 1',0),(2,'TEST2','TEST2','Test Project 2',0),(3,'TEST3','TEST3','Test Project 3',0),(4,'TEST4','TEST4','Test Project 4',0);
/*!40000 ALTER TABLE `project` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `repository`
--

DROP TABLE IF EXISTS `repository`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `repository` (
  `id` int(11) NOT NULL,
  `slug` varchar(128) COLLATE utf8_bin NOT NULL,
  `name` varchar(128) COLLATE utf8_bin NOT NULL,
  `state` int(11) NOT NULL,
  `origin_id` int(11) DEFAULT NULL,
  `project_id` int(11) NOT NULL,
  `scm_id` varchar(255) COLLATE utf8_bin NOT NULL,
  `hierarchy_id` varchar(20) COLLATE utf8_bin NOT NULL,
  `is_forkable` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_slug_project_id` (`slug`,`project_id`),
  KEY `idx_repository_hierarchy_id` (`hierarchy_id`),
  KEY `idx_repository_origin_id` (`origin_id`),
  KEY `idx_repository_project_id` (`project_id`),
  KEY `idx_repository_state` (`state`),
  CONSTRAINT `fk_repository_origin` FOREIGN KEY (`origin_id`) REFERENCES `repository` (`id`),
  CONSTRAINT `fk_repository_project` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `repository`
--

LOCK TABLES `repository` WRITE;
/*!40000 ALTER TABLE `repository` DISABLE KEYS */;
INSERT INTO `repository` VALUES (1,'test_repository_1','Test Repository 1',1,NULL,1,'git','a',1),(2,'test_repository_2','Test Repository 2',1,NULL,1,'git','b',1),(3,'cloned_repository','Cloned Repository',1,1,2,'git','a',0),(4,'no_prs_repository','Repository with no pull requests',1,NULL,2,'git','c',1),(5,'restricted_repo','Restricted Repository',1,NULL,4,'git','d',0);
/*!40000 ALTER TABLE `repository` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2013-04-18 21:42:49

I created a fresh, empty database using the following:

 create database oops character set utf8 collate utf8_bin;

I imported this excerpt back again using this command:

  mysql oops < tables.dmp

With that data imported, the following queries demonstrate the issue:

1. The query with order by lower(name):

mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_, this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_, this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_ from repository this_ order by lower(this_.name) asc limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)

Notice that "Restricted Repository" is returned before "Repository with no pull requests".

2. The query with the lower(name) removed:

mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_, this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_, this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_ from repository this_ order by this_.name asc limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)

Notice that "Repository with no pull requests" is now returned before "Restricted Repository".

(My apologies for the ugly queries; they're generated by Hibernate)
[19 Apr 2013 5:02] Bryan Turner 
Table dump as a standalone file

Attachment: tables.dmp (application/octet-stream, text), 3.94 KiB.
[19 Apr 2013 7:11] Bryan Turner 
In my testing, this same query returns results in the expected order under MySQL 5.6.10, as well as 5.5.29.
[19 Apr 2013 9:23] MySQL Verification Team 
Hello Bryan,

Thank you for the report.
Verified as described.

Regards,
Umesh
[19 Apr 2013 9:23] MySQL Verification Team 
### Affected

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.11-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_, this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_, this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_ from repository this_ order by lower(this_.name) asc limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)

mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_, this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_, this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_ from repository this_ order by this_.name asc limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)
[19 Apr 2013 9:24] MySQL Verification Team 
### Affected

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.6.10-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_, this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_, this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_ from repository this_ order by lower(this_.name) asc limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.01 sec)

mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_, this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_, this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_ from repository this_ order by this_.name asc limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)
[19 Apr 2013 9:25] MySQL Verification Team 
#### Not affected

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.5.31-debug |
+--------------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_, this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_, this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_ from repository this_ order by lower(this_.name) asc limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.02 sec)

mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_, this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_, this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_ from repository this_ order by this_.name asc limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)
[4 Jun 2013 17:45] MySQL Verification Team 
Testcase:
-----------
drop table if exists `t1`;
create table `t1`(`p` int,`n` char(3) charset utf8 collate utf8_bin)engine=myisam;
insert into `t1` values (1,'a a'),(2,'a b');
select * from `t1` order by lower(`n`) asc;
select * from `t1` order by lower(`n`) desc;
----------

Affects all versions of 5.6.x and 5.7.x
[16 Oct 2013 18:39] Ben Stillman 
Verified in 5.6.13 Enterprise on SmartOS (Solaris 11).

uname -a
SunOS 5.11 joyent_20130719T220136Z i86pc i386 i86pc Solaris

mysql> select version ();
+-------------------------------------------+
| version ()                                |
+-------------------------------------------+
| 5.6.13-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_,
    -> this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_,
    -> this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_
    -> from repository this_
    -> order by lower(this_.name) asc
    -> limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)

mysql>  select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_,
    -> this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_,
    -> this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_
    -> from repository this_
    -> order by this_.name asc
    -> limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)
[16 Oct 2013 19:08] Ben Stillman 
It's the utf8_bin collation. A random sampling of other collations returns the correct order.

mysql> ALTER TABLE repository CHANGE COLUMN name name VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL ;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_,
    -> this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_,
    -> this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_
    -> from repository this_
    -> order by lower(this_.name) asc
    -> limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)
[16 Oct 2013 19:26] Ben Stillman 
utf16_bin works as well:

mysql> ALTER TABLE repository CHANGE COLUMN name name VARCHAR(128) CHARACTER SET 'utf16' COLLATE 'utf16_bin' NOT NULL ;
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_,
    -> this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_,
    -> this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_
    -> from repository this_
    -> order by lower(this_.name) asc
    -> limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)
[7 Nov 2013 15:51] Paul DuBois 
Noted in 5.5.36, 5.6.15, 5.7.4 changelogs.

For the utf8_bin collation, ORDER BY LOWER(col_name) could produce
incorrect ordering.
[6 Dec 2013 19:19] Bryan Turner 
Now that 5.6.15 is available for download I have installed it clean VMs for both Ubuntu and Windows and this appears to _not_ be fixed. The same reproduce case I described when I created this issue still fails:

mysql> select this_.id as id10_0_, this_.is_forkable as is2_10_0_, this_.hierarchy_id as hierarchy3_10_0_, this_.name as name10_0_, this_.origin_id as origin8_10_0_, this_.project_id as project9_10_0_, this_.scm_id as scm5_10_0_, this_.slug as slug10_0_, this_.state as state10_0_ from repository this_ order by lower(this_.name) asc limit 4;
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
| id10_0_ | is2_10_0_ | hierarchy3_10_0_ | name10_0_                        | origin8_10_0_ | project9_10_0_ | scm5_10_0_ | slug10_0_         | state10_0_ |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
|       3 |         0 | a                | Cloned Repository                |             1 |              2 | git        | cloned_repository |          1 |
|       5 |         0 | d                | Restricted Repository            |          NULL |              4 | git        | restricted_repo   |          1 |
|       4 |         1 | c                | Repository with no pull requests |          NULL |              2 | git        | no_prs_repository |          1 |
|       1 |         1 | a                | Test Repository 1                |          NULL |              1 | git        | test_repository_1 |          1 |
+---------+-----------+------------------+----------------------------------+---------------+----------------+------------+-------------------+------------+
4 rows in set (0.00 sec)

mysql> status;
--------------
./bin/mysql  Ver 14.14 Distrib 5.6.15, for debian6.0 (x86_64) using  EditLine wrapper

Connection id:		1
Current database:	oops
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.6.15 MySQL Community Server (GPL)
...

Can someone please reconfirm this issue? Perhaps it is encoding specific. As I noted in my initial reproduce case, "oops" is:

mysql> create database oops character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)

Thanks!
Bryan Turner
[6 Dec 2013 19:26] MySQL Verification Team 
changelog entry could be wrong. it is fixed in bzr source, but not in 5.6.15.
mysql> select * from `t1` order by lower(`n`) asc;
+------+------+
| p    | n    |
+------+------+
|    1 | a a  |
|    2 | a b  |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from `t1` order by lower(`n`) desc;
+------+------+
| p    | n    |
+------+------+
|    2 | a b  |
|    1 | a a  |
+------+------+
2 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.6.16-debug |
+--------------+
1 row in set (0.00 sec)
[6 Dec 2013 19:51] Daniel Price 
Correction:

This bug is addressed in *5.6.16*, not 5.6.15 as previously stated.

Noted in 5.5.36, *5.6.16*, 5.7.4 changelogs.

For the utf8_bin collation, ORDER BY LOWER(col_name) could produce
incorrect ordering.
[11 Dec 2013 11:53] Erlend Dahl 
Re-closing.
[2 Feb 2014 17:23] Laurynas Biveinis 
5.5$ bzr log -r 4541
------------------------------------------------------------
revno: 4541
committer: Neeraj Bisht <neeraj.x.bisht@oracle.com>
branch nick: 5.5
timestamp: Thu 2013-11-07 16:46:24 +0530
message:
  Bug#16691598 - ORDER BY LOWER(COLUMN) PRODUCES OUT-OF-ORDER RESULTS
  
  Problem:-
  We have created a table with UTF8_BIN collation.
  In case, when in our query we have ORDER BY clause over a function 
  call we are getting result in incorrect order.
  Note:the bug is not there in 5.5.
  
  Analysis:
  In 5.5, for UTF16_BIN, we have min and max multi-byte length is 2 and 4 
  respectively.In make_sortkey(),for 2 byte character character we are 
  assuming that the resultant length will be 2 byte/character. But when we 
  use my_strnxfrm_unicode_full_bin(), we store sorting weights using 3 bytes 
  per character.This result in truncated result.
  
  Same thing happen for UTF8MB4, where we have 1 byte min multi-byte and 
  4 byte max multi-byte.We will accsume resultant data as 1 byte/character, 
  which result in truncated result.
  
  Solution:-
  use strnxfrm(means use of MY_CS_STRNXFRM macro) is used for sort, in 
  which the resultant length is not dependent on source length.
[24 Apr 2014 23:35] Jeremy Cole 
This actually affects more than just LOWER(). It also at least affects ORDER BY c COLLATE utf8_bin amongst other things.