id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
12328	subqueries in django 1.1 behave oddly	Walter Doekes	Marcos Moyano	"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"		closed	Database layer (models, ORM)	1.1		fixed		galli.87@…	Accepted	1	0	0	0	0	0