#12328: subqueries in django 1.1 behave oddly
------------------------------------------+---------------------------------
Reporter: wdoekes | Owner: nobody
Status: new | Milestone:
Component: Database layer (models, ORM) | Version: 1.1
Keywords: | Stage: Unreviewed
Has_patch: 0 |
------------------------------------------+---------------------------------
Hi, I'm not sure what is wrong exactly. But it looks like subqueries in
filters (introduced in django 1.1?) do not always work as intended.
A basic model:
{{{
from django.db import models
class M(models.Model):
def __unicode__(self):
return 'M(id=%d)' % self.id
}}}
The setup:
{{{
$ ./manage shell
Python 2.5.2 (r252:60911, Jan 4 2009, 21:59:32)
[GCC 4.3.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from osso.test.models import M
>>> M.objects.create(id=1)
<M: M(id=1)>
>>> M.objects.create(id=2)
<M: M(id=2)>
>>> M.objects.create(id=3)
<M: M(id=3)>
>>> M.objects.all()
[<M: M(id=1)>, <M: M(id=2)>, <M: M(id=3)>]
>>> M.objects.order_by('id')[:1]
[<M: M(id=1)>]
>>> M.objects.order_by('id')[:1].delete()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/opt/django11/django/db/models/query.py", line 379, in delete
"Cannot use 'limit' or 'offset' with delete."
AssertionError: Cannot use 'limit' or 'offset' with delete.
}}}
Bear with me. You have noticed my goal is to delete old records.
Watch django1.0:
{{{
>>> M.objects.filter(id__in=M.objects.order_by('id')[0:1])
<snip>
TypeError: int() argument must be a string or a number, not 'M'
>>>
M.objects.filter(id__in=M.objects.order_by('id')[0:1].values_list('id',
flat=True))
[<M: M(id=1)>]
>>>
M.objects.filter(id__in=M.objects.order_by('id')[0:1].values_list('id',
flat=True)).delete()
>>> M.objects.all()
[<M: M(id=2)>, <M: M(id=3)>]
}}}
That works as intended.
And now django 1.1:
{{{
>>> M.objects.filter(id__in=M.objects.order_by('id')[:1])
[<M: M(id=1)>]
>>> M.objects.filter(id__in=M.objects.order_by('id')[:1]).delete()
>>> M.objects.all()
[]
}}}
(The same happens if I use the values_list() notation as used above.)
What happens is that the del_query in QuerySet.delete() refers to a newer
object every time, until all M objects have been deleted.
The following could be related, or not :)
After deleting an M object and re-adding it, I notice that ordering in
subqueries does not work as intended either:
{{{
>>> M.objects.order_by('id')
[<M: M(id=1)>, <M: M(id=2)>, <M: M(id=3)>]
>>> M.objects.order_by('id')[0:1]
[<M: M(id=1)>]
>>> M.objects.filter(id__in=M.objects.order_by('id')[0:1])
[<M: M(id=2)>]
>>>
M.objects.filter(id__in=M.objects.order_by('id')[0:1].values_list('id',
flat=True))
[<M: M(id=2)>]
}}}
This was tested with postgresql_psycopg2. With MySQL I cannot reproduce
it, as it disallows LIMIT in subqueries, sending me an early error instead
of behaving errantly.
My current workaround is to wrap "M.objects.order_by('id')[:1]" in a
list() so I do not get a dynamic object as id\_\_in value. Having to force
early evaluation is a bit frightening though.
I assume one should be able to reproduce this easily. If not, I'd be happy
to share more information about my setup.
Regards,[[br]]
Walter Doekes
--
Ticket URL: <http://code.djangoproject.com/ticket/12328>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-upda...@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
