Opened 16 years ago

Closed 16 years ago

Last modified 5 years ago

#8384 closed Uncategorized (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: Normal Keywords: ordering, alphabetical, sqlite
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

(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 (3)

comment:1 by Malcolm Tredinnick, 16 years ago

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.

comment:2 by yesyves, 5 years ago

Easy pickings: unset
Severity: Normal
Type: Uncategorized
UI/UX: unset

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

comment:3 by yesyves, 5 years ago

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

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