django values_list unexpected behaviour

Question

I have a model which has 2 fields

class MyModel: 
    name = charField()
    code = charField(unique=True)

Thats all. no other fields or meta class, nothing.. it's all here.

and in Mysql table I have 8 entries/rows

ID name              code
-------------------------
8  Aborted           ABT
7  Returned          RTC
6  Delivery-Failed   DLF
5  Delivered         DLV
4  Out-for-Delivery  OFD
3  Ready-to-Deliver  RTD
2  Order-Placed      OPD
1  in transit        INT

thats means, the very first entry I did was ("in transit", "INT") then OPD..and so on.

so when I use values_list on this table it returns

MyModel.objects.values_list('code', 'name')

[(u'INT', u'in transit'), (u'OPD', u'Order-Placed'), (u'RTD', u'Ready-to-Deliver'), (u'OFD', u'Out-for-Delivery'), (u'DLV', u'Delivered'), (u'DLF', u'Delivery-Failed'), (u'RTC', u'Returned'), (u'ABT', u'Aborted')]

which is in expected order, i mean first entry first and last one last.

now when I do MyModel.objects.values_list('code') it returns

[(u'ABT',), (u'DLF',), (u'DLV',), (u'INT',), (u'OFD',), (u'OPD',), (u'RTC',), (u'RTD',)]

State.objects.values_list('code', flat=True) returns same result as well.

which seems to be random at first but it seems it's alphabetically sorted.

This is not all, I even tried to pass slicing list in filter

MyMode.objects.filter(code__in=['DLF', 'RTC', 'ABT'][1:]).values_list('code', 'name')

and it again returned some unordered data [(u'ABT', u'Aborted'), (u'RTC', u'Returned')]

now I want to understand why this is happening. if it's in the code, why django developers did this sorting by default?

PS: I didn't find anything helpful from th doc https://docs.djangoproject.com/en/1.9/ref/models/querysets/#values-list

the code doesn't say anything too https://github.com/django/django/blob/master/django/db/models/query.py#L146

PS: I'm using django 1.9.6 and Mysql Ver 14.14 Distrib 5.5.47

Answer 1

The most likely reason for this is the default ordering returned by the database. I can not be more certain than I am only because I do not know if full model description has been posted. As far as I can tell, there's no DJANGO magic involved here. To guarantee the expected ordering, Django relies on the default ordering specified by the model's author.

This seems like the most relevant/related SO QA to the current one. Please have a look at the OP's edit and the accepted answer.

EDIT: Okay, it appears mysql's default order does depend on either insert order or primary key. Databases are free to implement a default order of their own, and it is a good thing because they can choose to give the best performance for those cases. This is precisely the reason why DJANGO models and other ORMs specifically have a way to provide default ordering on models.

AGAIN, to repeat (and this can't be iterated enough!) - quoting from an answer in this post

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause

Be especially vary of using slicing on top of the queryset if order is not specified, because SQL's TOP is applied on the ordered results, and without explicit order to sort RDBMS implementations are free to choose their own and possibly use one at random across versions or even execution to execution (because it depends on the query plan implementation of the RDBMS in question).