What is the default order of a list returned from a Django filter call?

Question

Short Question
What is the default order of a list returned from a Django filter call when connected to a PostgreSQL database?

Background
By my own admission, I had made a poor assumption at the application layer in that the order in which a list is returned will be constant, that is without using 'order_by'. The list of items I was querying is not in alphabetic order or any other deliberate order. It was thought to remain in the same order as which they were added to the database.

This assumption held true for hundreds of queries, but a failure was reported by my application when the order changed unknowingly. To my knowledge, none of these records were touched during this time as I am the only person who maintains the DB. To add to the confusion, when running the Django app on Mac OS X, it still worked as expected, but on Win XP, it changed the order. (Note that the mentioned hundreds of queries was on Win XP).

Any insight to this would be helpful as I could not find anything in the Django or PostgreSQL documentation that explained the differences in operating systems.

Example Call

required_tests = Card_Test.objects.using(get_database()).filter(name__icontains=key)

EDIT
After speaking with some colleague's of mine today, I had come up with the same answer as Björn Lindqvist.

Looking back, I definitely understand why this is done wrong so often. One of the benefits to using an ORM Django, sqlalchemy, or whatever is that you can write commands without having to know or understand (in detail) the database it's connected to. Admittedly I happen to have been one of these users. However on the flip-side of this is that without knowing the database in detail debugging errors like this are quite troublesome and potentially catastrophic.

Solution 1

There is NO DEFAULT ORDER, a point that can not be emphasized enough because everyone does it wrong.

A table in a database is not an ordinary html table, it is an unordered set of tuples. It often surprises programmers only used to MySQL because in that particular database the order of the rows are often predictable due to it not taking advantage of some advanced optimization techniques. For example, it is not possible to know which rows will be returned, or their order in any of the following queries:

select * from table limit 10
select * from table limit 10 offset 10
select * from table order by x limit 10

In the last query, the order is only predictable if all values in column x are unique. The RDBMS is free to returns any rows in any order it pleases as long as it satisfies the conditions of the select statement.

Though you may add a default ordering on the Django level, which causes it to add an order by clause to every non-ordered query:

class Table(models.Model):
    ...
    class Meta:
        ordering = ['name']

Note that it may be a performance drag, if for some reason you don't need ordered rows.

Solution 2

If you want to have them returned in the order they were inserted:

Add the following to your model:

created = models.DateTimeField(auto_now_add=True, db_index=True)
# last_modified = models.DateTimeField(auto_now=True, db_index=True)

class Meta:
    ordering = ['created',]
    # ordering = ['-last_modified']  # sort last modified first

Comments

[aggieNick02]

Rather than modifying your model to add a new field, why not just order by the auto-incrementing id that most models have? It is not subject to precision issues (you have to worry about how granular the DateTimeField is) or being tricked by system time changes.

[Risadinha]

That only works if you are really sure that inserts are always using the auto-incr. sequence and are never re-using unused IDs, dumps from other DBs or tables that set a created time that would not be reflected in the order of the primary key. It's not transparent to use the ID when you really want to sort by date - and often you really want to sort by last_modified - and the ID won't help in that case.

[aggieNick02]

I'll agree there are some corner-cases where you may want to do something custom, but by default, every model gets an auto-incrementing id and there is no unused id reuse. Further, with DateTimeField, you've got a problem if two models end up with the same value in their DateTimeField. This could happen depending on the precision due to django version/database backend.

[Paolo]

Django docs: "If a query doesn’t have an ordering specified, results are returned from the database in an unspecified order. A particular ordering is guaranteed only when ordering by a set of fields that uniquely identify each object in the results. For example, if a name field isn’t unique, ordering by it won’t guarantee objects with the same name always appear in the same order."

[mirek]

If I understand it well, I should always add class Meta: ordering and if the listed fields are not guarantied to be unique, I should always add `[...., 'id'] as the last one item.