Opened 8 years ago

Closed 8 years ago

#8384 closed (invalid)

Wrong alphabetical ordering with accented letters (sqlite issue?)

Reported by: florent@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.0-beta
Severity: Keywords: ordering, alphabetical, sqlite
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


(I hope this is not a duplicate. Even though this is a simple problem, I ran searches on
“ordering”, “sorting”, “alphabetical”, etc., and found nothing close to this. Also, I put
this under the “Database wrapper” component but this may be off the mark.)

While running simple tests with the Django admin, I noticed that alphabetical ordering
was pretty bad for non-ascii or mixed-case characters.

I have a simple model class:

class Livre(models.Model):
    titre = models.CharField('titre du livre', max_length=100)
    # ...

    def __unicode__(self):
        return self.titre

    class Meta:
        ordering = ('titre',)

Through the Django admin, I created several entries, which end up displayed like this:

000 zéro zéro zéro
A la claire fontaine, la suite
Tu t’es vu quand t’as bu?
a la claire fontaine, sans majuscule
etoile du matin
À la claire fontaine
Étoile du matin
à la claire fontaine, sans majuscule
étoile du matin

This is not very satisfying. A decent alphabetical ordering would be like this:

000 zéro zéro zéro
À la claire fontaine
A la claire fontaine, la suite
a la claire fontaine, sans majuscule
à la claire fontaine, sans majuscule
etoile du matin
étoile du matin
Étoile du matin
Tu t’es vu quand t’as bu?

(I'm not sure about the exact order for the very similar ones, though.)

It seems that entries are sorted according to the unicode values of letters, or something
like that. Capitals come before lowercase, ASCII capitals and lowercase come before accented
characters, accented capitals come before accented lowercase letters.

Useful information:

  • I'm using sqlite3. I haven't tested the same models and content with another database (MySQL or Postgresql).
  • I get the exact same (wrong) ordering when calling Livre.objects.all() or Livre.objects.order_by('titre'), which means it's not related to the admin app.

Change History (1)

comment:1 Changed 8 years ago by Malcolm Tredinnick

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Resolution: invalid
Status: newclosed

The collation order returned is whatever the database returns for the ordering. Django does not try to change that. The ordering is a database thing. So it looks like this is the natural SQLite behaviour. If you want things to collate differently, resort them in Python -- sorting in Python is very fast and you can do whatever you like there. But this isn't a bug.

Note: See TracTickets for help on using tickets.
Back to Top