Getting Queryset item by index with PostgreSQL behaves randomly

[Paweł Adamczak]

I originally 'reported' it on ​StackOverflow, but this is probably where it should be.

This code on SQLite works as intended and gets two random items from queryset:

random.seed()
index1, index2 = random.sample(range(0, qs.count()), 2)

all_objects = qs.all()
object1 = all_objects[index1]
object2 = all_objects[index2]

but when run on PostgreSQL, getting items from Queryset by index seems to behave randomly.

Here are index1, index2 and object1, object2 pairs when that code is run 5 times:

9 12
ID: 2754 ID: 2365
15 11
ID: 1626147 ID: 200811
12 1
ID: 2365 ID: 203112
1 12
ID: 203112 ID: 2365
1 3
ID: 203112 ID: 203112

The last one is especially important, because what it means is qs.all()[1] == qs.all()[3] which just can't be (all ID's are unique). And this occurred quite regularly.

Converting queryset to list fixed the issue (all_objects = list(qs.all())), so I am guessing it has something to do with Queryset laziness in PostgreSQL implementation. I tried to look it up in the source code but with no luck.

[Tim Graham]

Does the queryset have an order_by()? If a query doesn’t have an ordering specified, results are returned from the database in an unspecified order.

[Paweł Adamczak]

Yes, both the model and queryset has order_by set.

But even if it's in unspecified order, could the order change between accessing it's items? Because from my understanding, event if it's random, qs.all()[1] == qs.all()[3] should never be true.

[Tim Graham]

I believe all_objects[index1] and all_objects[index2] are running two separate queries. When you first cast to a list, you only have one query. Please verify by checking ​connection.queries.

[Tim Graham]

Please reopen with a test for Django's test suite that demonstrates the problem.

[Paweł Adamczak]

You were right with the order_by, but why setting it on model level isn't taking care of that?
Shouldn't that be enough?

class Meta:
        ordering = ['-field1, '-field2', 'field3']

[Tim Graham]

Again, I'd ask if you could please provide more details such as a test for Django's test suite (ideally) or a sample project to reproduce the issue. Although I suspect a bug in your code rather than in Django, there aren't enough details here currently to investigate the issue. Ideally, "is it a bug?" questions would first be directed to our support channels (TicketClosingReasons/UseSupportChannels) and reported here only if someone else confirms the problem as a probable bug. Thanks!

[Paweł Adamczak]

OK, sorry for the inconvenience.