Wrong slice on QuerySet when using Oracle database

[rodolfo.3+django@…]

I have a model like this:

class MyTest(models.Model):
    afield = models.CharField(max_length=20, null=True, blank=True)

and populate it with this code:

>>> for i in xrange(10):
...     MyTest(afield=None).save()
...     MyTest(afield=str(i)).save()

And I use the field to query, sorting by the field:

>>> [i.pk for i in MyTest.objects.all().order_by('afield')]
[2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 13, 15, 17, 19, 11, 9, 1, 5, 3, 7]

But, if I slice in the queryset, the results will be wrong:

>>> [i.pk for i in MyTest.objects.all().order_by('afield')[10:15]]
[1, 15, 13, 11, 9]
>>> [i.pk for i in MyTest.objects.all().order_by('afield')[15:20]]
[11, 9, 7, 5, 3]

Please, note the objects with pk = 11 and 9 are repeated into the 2 different queries. This happen because Oracle first do a "where" and then sort the items. I solve it changing 2 lines into the backend (I'll attach a diff file). With this patch applied, the results are ok:

>>> [i.pk for i in MyTest.objects.all().order_by('afield')]
[2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 13, 15, 17, 19, 11, 9, 1, 5, 3, 7]
>>> [i.pk for i in MyTest.objects.all().order_by('afield')[10:15]]
[13, 15, 17, 19, 11]
>>> [i.pk for i in MyTest.objects.all().order_by('afield')[15:20]]
[9, 1, 5, 3, 7]

[rodolfo.3+django@…]

Changes into the Oracle backend

[Luke Plant]

This is not a bug. Having some of the same items appear in both [10:15] and [15:20] is correct, because those items have a NULL value for the field you are sorting on. These items therefore don't have strict total order defined for them, so when you ask [n:n+k], the database backend is free to choose and 'k' of them that it pleases.

For comparison, other database backends have the same behaviour if the ordering you specify does not provide a strict total order.

For the behaviour you want, add a secondary sorting field that will fully define the order e.g. order_by('afield', 'pk').