[patch] 'in' QuerySet operator generates invalid SQL for empty list

[adurdin@…]

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)

[Malcolm Tredinnick]

This stuff is undergoing a rewrite at the moment, but I'll make sure to include something like your patch in the process. Thanks.

[Gary Wilson <gary.wilson@…>]

The patch doesn't work on postgres. It seems to work, though, with SQL like:

SELECT "auth_user"."id","auth_user"."username" FROM "auth_user" WHERE ("auth_user"."username" IN (0));

which would work with this simple patch:

=== modified file 'django/db/models/query.py'
--- django/db/models/query.py   2006-12-19 04:35:09 +0000
+++ django/db/models/query.py   2006-12-19 05:53:41 +0000
@@ -641,7 +641,7 @@
     except KeyError:
         pass
     if lookup_type == 'in':
-        return '%s%s IN (%s)' % (table_prefix, field_name, ','.join(['%s' for v in value]))
+        return '%s%s IN (%s)' % (table_prefix, field_name, ','.join(['%s' for id in value]) or 0)
     elif lookup_type == 'range':
         return '%s%s BETWEEN %%s AND %%s' % (table_prefix, field_name)
     elif lookup_type in ('year', 'month', 'day'):

Can anyone test if this patch also works on MySQL?

[Gary Wilson <gary.wilson@…>]

added some tests

[Gary Wilson <gary.wilson@…>]

My little patch above is incorrect. Would WHERE (false) possibly work cross backend?

[Gary Wilson <gary.wilson@…>]

using WHERE (false) when empty, with the tests

[Gary Wilson <gary.wilson@…>]

used where 0=1 instead of where false

[Russell Keith-Magee]

(In [4283]) Fixed #2473 -- Added special case for 'in=[]' (empty set) queries, because 'WHERE attr IN ()' is invalid SQL on many backends. Thanks, Gary Wilson.

[anonymous]

​Buy Discount Coach Sunglasses
​Cheap Authentic Coach Sunglasses sale