Opened 3 years ago

Closed 3 years ago

#17273 closed Bug (invalid)

Wrong slice on QuerySet when using Oracle database

Reported by: rodolfo.3+django@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
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 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]

Attachments (1)

oracle_compiler.diff (848 bytes) - added by rodolfo.3+django@… 3 years ago.
Changes into the Oracle backend

Download all attachments as: .zip

Change History (2)

Changed 3 years ago by rodolfo.3+django@…

Changes into the Oracle backend

comment:1 Changed 3 years ago by lukeplant

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

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').

Last edited 3 years ago by lukeplant (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top