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
23713	SQlite3: too many SQL variables, Foreign Keys & Delete	Tanbouz	nobody	"Using:
{{{
Python 3.4.2 (default, Oct 23 2014, 12:36:46) 
[GCC 4.9.1] on linux
Type ""help"", ""copyright"", ""credits"" or ""license"" for more information.
>>> import django
>>> django.VERSION
(1, 7, 1, 'final', 0)
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.7'
}}}

Database Structure

{{{
# Attempt to delete a row from the Categories table

Table-1 Categories
Table-2 Groups (FK: Groups -> Categories)
Table-3 Items (FK: Items -> Groups)
Table-R Features <-> Items (Many to Many) Error happens on a query to delete rows from this table!
Table-4 Features
}}}



Test code:
{{{
from django.db import connection as dbconnection

# Pass a model to have its data deleted
def clean(db_model):
    while db_model.objects.count():
        row = db_model.objects.all()[:1]
        # Can't use delete on a slice
        row = db_model.objects.get(pk = row[0].pk)
        try:
            row.delete()
        except django.db.utils.OperationalError:
            print(dbconnection.queries)
}}}

dbconnection.queries prints several small SELECT queries but the '''last''' query is a DELETE query with more than 1000 parameters

{{{
{'time': '0.001', 'sql': 'QUERY =
\'DELETE FROM ""myapp_foreignmodel"" WHERE ""myapp_foreignmodel"".""foreignkey_id""
IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s.... 
And so on (1180 total in my case)
}}}


Errors:
{{{
django.db.utils.OperationalError: too many SQL variables
sqlite3.OperationalError: too many SQL variables
}}}

[https://code.djangoproject.com/ticket/16426 Related ticket #16426]

The above ticket solves the problem by splitting delete queries into batches. It doesn't seem to include cascaded delete queries aimed at a foreign table.

''
[https://docs.djangoproject.com/en/1.7/topics/db/queries/#deleting-objects When Django deletes an object, by default it emulates the behavior of the SQL constraint ON DELETE CASCADE – in other words, any objects which had foreign keys pointing at the object to be deleted will be deleted along with it.]''"	Bug	closed	Database layer (models, ORM)	1.7	Normal	duplicate	sqlite cascade delete foreign		Unreviewed	0	0	0	0	0	0