Opened 13 years ago

Closed 13 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: dev
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@… 13 years ago.
Changes into the Oracle backend

Download all attachments as: .zip

Change History (2)

by rodolfo.3+django@…, 13 years ago

Attachment: oracle_compiler.diff added

Changes into the Oracle backend

comment:1 by Luke Plant, 13 years ago

Resolution: invalid
Status: newclosed

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 13 years ago by Luke Plant (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top