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
2473	[patch] 'in' QuerySet operator generates invalid SQL for empty list	adurdin@…	Malcolm Tredinnick	"If an empty iterable is passed to the 'in' operator in a QuerySet method or Q() call, the SQL generated is invalid:  'WHERE ... IN ()'; this results in a ProgrammingError exception:

For example:
{{{
>>> Book.objects.filter(id__in=[])
Traceback (most recent call last):
  File ""<console>"", line 1, in ?
  File ""/usr/lib/python2.4/site-packages/django/db/models/query.py"", line 97, in __repr__
    return repr(self._get_data())
  File ""/usr/lib/python2.4/site-packages/django/db/models/query.py"", line 430, in _get_data
    self._result_cache = list(self.iterator())
  File ""/usr/lib/python2.4/site-packages/django/db/models/query.py"", line 172, in iterator
    cursor.execute(""SELECT "" + (self._distinct and ""DISTINCT "" or """") + "","".join(select) + sql, params)
  File ""/usr/lib/python2.4/site-packages/django/db/backends/util.py"", line 12, in execute
    return self.cursor.execute(sql, params)
  File ""/usr/lib/python2.4/site-packages/django/db/backends/mysql/base.py"", line 35, in execute
    return self.cursor.execute(sql, params)
  File ""/usr/lib/python2.4/site-packages/MySQLdb/cursors.py"", line 163, in execute
    self.errorhandler(self, exc, value)
  File ""/usr/lib/python2.4/site-packages/MySQLdb/connections.py"", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1064, ""You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1"")
}}}

The SQL generated in this case was:

{{{
SELECT `test_book`.`id`,`test_book`.`title`,`test_book`.`author_id` FROM `test_book` WHERE (`test_book`.`id` IN ())
}}}

The attached patch will return '0' instead of '... IN ()' if list(iterable) is empty; i.e. for SQL like this:

{{{
SELECT `test_book`.`id`,`test_book`.`title`,`test_book`.`author_id` FROM `test_book` WHERE (0)
}}}
"	defect	closed	Database layer (models, ORM)		normal	fixed		mir@… gary.wilson@… tom@…	Unreviewed	1	0	0	0	0	0