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
16426	sqlite: Cannot delete more than 999 things if there is a relation pointing to them	Karen Tracey	nobody	"Given these models:

{{{
#!python
class Thing(models.Model):
    name = models.CharField(max_length=32)

    def __unicode__(self):
        return self.name


class RelatedThing(models.Model):
    thing = models.ForeignKey(Thing)

    def __unicode__(self):
        return u'object related to %s.' % self.thing
}}}

using sqlite as the DB, attempting to delete more than 999 Things in one go fails:

{{{
--> python manage.py shell
/home/kmtracey/django/hg-django/django/conf/__init__.py:75: DeprecationWarning: The ADMIN_MEDIA_PREFIX setting has been removed; use STATIC_URL instead.
  ""use STATIC_URL instead."", DeprecationWarning)
Python 2.6.5 (r265:79063, Apr 16 2010, 13:57:41) 
[GCC 4.4.3] on linux2
Type ""help"", ""copyright"", ""credits"" or ""license"" for more information.
(InteractiveConsole)
>>> from ttt.models import Thing
>>> Thing.objects.count()
0
>>> for i in range(1000):
...     Thing.objects.create(name='Thing %d' % i)
... 
<Thing: Thing 0>
<Thing: Thing 1>
<Thing: Thing 2>
[...output snipped...]
<Thing: Thing 996>
<Thing: Thing 997>
<Thing: Thing 998>
<Thing: Thing 999>
>>> Thing.objects.all().delete()
Traceback (most recent call last):
  File ""<console>"", line 1, in <module>
  File ""/home/kmtracey/django/hg-django/django/db/models/query.py"", line 444, in delete
    collector.collect(del_query)
  File ""/home/kmtracey/django/hg-django/django/db/models/deletion.py"", line 167, in collect
    if not sub_objs:
  File ""/home/kmtracey/django/hg-django/django/db/models/query.py"", line 113, in __nonzero__
    iter(self).next()
  File ""/home/kmtracey/django/hg-django/django/db/models/query.py"", line 107, in _result_iter
    self._fill_cache()
  File ""/home/kmtracey/django/hg-django/django/db/models/query.py"", line 784, in _fill_cache
    self._result_cache.append(self._iter.next())
  File ""/home/kmtracey/django/hg-django/django/db/models/query.py"", line 273, in iterator
    for row in compiler.results_iter():
  File ""/home/kmtracey/django/hg-django/django/db/models/sql/compiler.py"", line 699, in results_iter
    for rows in self.execute_sql(MULTI):
  File ""/home/kmtracey/django/hg-django/django/db/models/sql/compiler.py"", line 754, in execute_sql
    cursor.execute(sql, params)
  File ""/home/kmtracey/django/hg-django/django/db/backends/util.py"", line 34, in execute
    return self.cursor.execute(sql, params)
  File ""/home/kmtracey/django/hg-django/django/db/backends/sqlite3/base.py"", line 226, in execute
    return Database.Cursor.execute(self, query, params)
DatabaseError: too many SQL variables
>>> Thing.objects.count()
1000
>>> Thing.objects.filter(pk__gte=1000).delete()
>>> Thing.objects.count()
999
>>> Thing.objects.all().delete()
>>> Thing.objects.count()
0
}}}

The problem is occurring when trying to collect the !RelatedThings that might need to be deleted along with the Things being deleted. The SQL it is trying to execute is of the form:

{{{
#!sql
SELECT [list_of_columns] FROM [related_table] WHERE [related_table].""thing_id"" IN ([list of 1000 pks of Things being deleted])
}}}

I'm guessing this is due to item #9 in http://www.sqlite.org/limits.html

I tried the same thing on 1.2.X branch level and did not see the failure, it is new with 1.3 (I did also try 1.3 from around 1.3 beta in addition to the above which is with current trunk...it failed with the pre-release 1.3 as well)."	Bug	closed	Database layer (models, ORM)	1.3	Normal	fixed		will@… Akos Ladanyi	Accepted	1	0	0	0	0	0