Support ORDER BY BINARY in MySQL
|Reported by:||Paul Kenjora <pkenjora@…>||Owned by:||anonymous|
|Component:||Database layer (models, ORM)||Version:||master|
|Severity:||Normal||Keywords:||mysql, binary, order by|
|Cc:||mmitar@…||Triage Stage:||Design decision needed|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
Description (last modified by mtredinnick)
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.
Change History (11)
comment:1 Changed 7 years ago by Paul Kenjora <pkenjora@…>
- Owner changed from pkenjora to anonymous
- Status changed from new to assigned
Changed 7 years ago by Paul Kenjora <pkenjora@…>
comment:2 Changed 7 years ago by Paul Kenjora <pkenjora@…>
- Triage Stage changed from Unreviewed to Ready for checkin
comment:3 Changed 7 years ago by Alex
- milestone 1.0 alpha deleted
- Triage Stage changed from Ready for checkin to Design decision needed
comment:9 Changed 3 years ago by Alex
- Easy pickings unset
- Resolution set to duplicate
- Status changed from assigned to closed
- UI/UX unset