Opened 13 years ago
Closed 13 years ago
#17273 closed Bug (invalid)
Wrong slice on QuerySet when using Oracle database
Reported by: | 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)
Change History (2)
by , 13 years ago
Attachment: | oracle_compiler.diff added |
---|
comment:1 by , 13 years ago
Resolution: | → invalid |
---|---|
Status: | new → 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')
.
Changes into the Oracle backend