Wrong alphabetical ordering with accented letters (sqlite issue?)

[florent@…]

(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.

[Malcolm Tredinnick]

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.

[yesyves]

Hi

I'm not sure about this.

I have a table in which the objects are already sorted in the right order.

for p in Programme.objects.all()

will output names beginning with a unicode character (É in this instance) at the end of the list, while

for p in Programme.objects.all().order_by('created')

will output them in the right order.

I get the same behaviour with sqlite3 and PostgreSQL.

Django 2.2 / Python 3.7.4

Thanks

[yesyves]

OK, my error, I have a default sort in my model. Sorry !