Django

Code

Ticket #2473 (closed: fixed)

Opened 2 years ago

Last modified 2 years ago

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

Reported by: adurdin@gmail.com Assigned to: mtredinnick
Milestone: Component: Database layer (models, ORM)
Version: Keywords:
Cc: mir@noris.de, gary.wilson@gmail.com, tom@eggdrop.ch Triage Stage: Unreviewed
Has patch: 1 Needs documentation: 0
Needs tests: 0 Patch needs improvement: 0

Description

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)

Attachments

in_empty_list.diff (0.7 kB) - added by adurdin@gmail.com on 08/03/06 03:29:03.
tests.diff (1.0 kB) - added by Gary Wilson <gary.wilson@gmail.com> on 12/19/06 13:43:31.
added some tests
where_false.diff (1.7 kB) - added by Gary Wilson <gary.wilson@gmail.com> on 12/19/06 21:41:02.
using WHERE (false) when empty, with the tests
where_0=1.diff (2.0 kB) - added by Gary Wilson <gary.wilson@gmail.com> on 01/03/07 17:51:14.
used where 0=1 instead of where false

Change History

08/03/06 03:29:03 changed by adurdin@gmail.com

  • attachment in_empty_list.diff added.

08/03/06 03:47:32 changed by mtredinnick

  • owner changed from adrian to mtredinnick.

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

08/11/06 12:02:34 changed by mir@noris.de

  • cc set to mir@noris.de.

12/11/06 17:03:47 changed by Gary Wilson <gary.wilson@gmail.com>

  • cc changed from mir@noris.de to mir@noris.de, gary.wilson@gmail.com.

12/19/06 00:05:33 changed by Gary Wilson <gary.wilson@gmail.com>

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?

12/19/06 13:43:31 changed by Gary Wilson <gary.wilson@gmail.com>

  • attachment tests.diff added.

added some tests

12/19/06 21:38:53 changed by Gary Wilson <gary.wilson@gmail.com>

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

12/19/06 21:41:02 changed by Gary Wilson <gary.wilson@gmail.com>

  • attachment where_false.diff added.

using WHERE (false) when empty, with the tests

12/27/06 04:21:28 changed by Thomas Steinacher <tom@eggdrop.ch>

  • cc changed from mir@noris.de, gary.wilson@gmail.com to mir@noris.de, gary.wilson@gmail.com, tom@eggdrop.ch.

01/03/07 17:51:14 changed by Gary Wilson <gary.wilson@gmail.com>

  • attachment where_0=1.diff added.

used where 0=1 instead of where false

01/03/07 22:00:16 changed by russellm

  • status changed from new to closed.
  • resolution set to fixed.

(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.

Add/Change #2473 ([patch] 'in' QuerySet operator generates invalid SQL for empty list)




Change Properties
Action