Support ORDER BY BINARY in MySQL

[Paul Kenjora <pkenjora@…>]

The Problem:

MySQL supports both ORDER BY and ORDER BY BINARY.

ORDER BY does NOT match the string sort used by Python but ORDER BY BINARY DOES. Lots of issues arise when returned list from DB needs to be in proper order.

The problem is described well at: ​http://blog.awarelabs.com/?p=18 (article by the reporter of this ticket)

The Proposed Fix:

Add a __binary tag to the end of any order_by('column') caluse, like so order_by('column__binary').
Strip the __binary tag before any processing and ensure 'BINARY ' is inserted into generated SQL.
The above solution ensures existing behavior is not altered. It does carry the side effect of making __binary a reserved word which come to think of it is reserved in SQL so no issue there.

[Malcolm Tredinnick]

This doesn't feel like the right solution. We just do the Right Thing, rather than introduce extra options. If doing case-sensitive ordering is felt to be the right thing, then let's just do that always. I don't really see the need to be able to flip back and forth. Also, the fact that it removes "binary" as a potential field name counts against this patch (the fact that it's reserved in MySQL has nothing to do with it; we quote field names for that very reason). We should try to avoid doing stuff like that since it's difficult to tell who it will affect.

All told, I'm -1 on this approach to the problem (introducing a new option). Instead something that only added the "binary" extra bit for ordering when using MySQL as the engine (via a connection.ops feature, not via a test for MySQL in the Query class) would stand a better chance. You probably want to start a discussion on django-dev first, however, to see what the consensus might be as far as the natural ordering to use. Let's move discussion to the mailing list for now.

(Fixed wiki formatting problems in the description. The "preview" button is your friend.)