Filtering __in a sliced queryset with a 0 limit raises an error

[chkwok@…]

I've noticed that after upgrading to Django 1.4, __in queries really don't like empty sets. Simple queries still work, like User.objects.filter(groups__in=[]), but most failures I've seen are with Paginators. I think this is the minimum set to cause a DatabaseError, create any app, add a models.py with:

from django.db import models

class Author(models.Model):
    pass

class Book(models.Model):
    author = models.ForeignKey(Author)

def crash():
    from django.core.paginator import Paginator
    
    pages = Paginator(Author.objects.all(), 25)
    page = pages.page(1)
    
    books = Book.objects.filter(author__in=page.object_list)
    print books

calling crash() will cause this stack trace:

C:\Workspace\someproject\src\someproject\test.py in <module>()
      6
      7 books = Book.objects.filter(author__in=page.object_list)
----> 8 print books
      9

C:\Dev\Python27\lib\site-packages\django\db\models\query.pyc in __repr__(self)
     70
     71     def __repr__(self):
---> 72         data = list(self[:REPR_OUTPUT_SIZE + 1])
     73         if len(data) > REPR_OUTPUT_SIZE:
     74             data[-1] = "...(remaining elements truncated)..."

C:\Dev\Python27\lib\site-packages\django\db\models\query.pyc in __len__(self)
     85                 self._result_cache = list(self.iterator())
     86         elif self._iter:
---> 87             self._result_cache.extend(self._iter)
     88         if self._prefetch_related_lookups and not self._prefetch_done:
     89             self._prefetch_related_objects()

C:\Dev\Python27\lib\site-packages\django\db\models\query.pyc in iterator(self)
    289             klass_info = get_klass_info(model, max_depth=max_depth,
    290                                         requested=requested, only_load=only_load)
--> 291         for row in compiler.results_iter():
    292             if fill_cache:
    293                 obj, _ = get_cached_row(row, index_start, db, klass_info,

C:\Dev\Python27\lib\site-packages\django\db\models\sql\compiler.pyc in results_iter(self)
    761         if self.query.select_for_update and transaction.is_managed(self.using):
    762             transaction.set_dirty(self.using)
--> 763         for rows in self.execute_sql(MULTI):
    764             for row in rows:
    765                 if resolve_columns:

C:\Dev\Python27\lib\site-packages\django\db\models\sql\compiler.pyc in execute_sql(self, result_type)
    816
    817         cursor = self.connection.cursor()
--> 818         cursor.execute(sql, params)
    819
    820         if not result_type:

C:\Dev\Python27\lib\site-packages\django\db\backends\util.pyc in execute(self, sql, params)
     38         start = time()
     39         try:
---> 40             return self.cursor.execute(sql, params)
     41         finally:
     42             stop = time()

C:\Dev\Python27\lib\site-packages\django\db\backends\postgresql_psycopg2\base.pyc in execute(self, query, args)
     50     def execute(self, query, args=None):
     51         try:
---> 52             return self.cursor.execute(query, args)
     53         except Database.IntegrityError, e:
     54             raise utils.IntegrityError, utils.IntegrityError(*tuple(e)), sys.exc_info()[2]

DatabaseError: syntax error at or near ")"
LINE 1: ...ugtest_book" WHERE "bugtest_book"."author_id" IN () LIMIT 21

The SQL statement created is:

SELECT "bugtest_book"."id", "bugtest_book"."author_id" FROM "bugtest_book" WHERE "bugtest_book"."author_id" IN () LIMIT 21

[chkwok]

I've traced the change back, but it doesn't seem to be introduced at 1.4. On 1.2, this works:

C:\Dev\django\bugs>manage.py shell
Python 2.7.3 (default, Apr 10 2012, 23:31:26) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from bugtest.models import *
>>> crash()
[]
>>> import django
>>> django.VERSION
(1, 2, 0, 'final', 0)
>>>

On 1.3.4:

C:\Dev\django\bugs>manage.py shell
Python 2.7.3 (default, Apr 10 2012, 23:31:26) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from bugtest.models import *
>>> crash()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "C:\Dev\django\bugs\bugtest\models.py", line 16, in crash
    print books
  File "C:\Dev\Python27\lib\site-packages\django-1.3.4-py2.7.egg\django\db\models\query.py", line 69, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "C:\Dev\Python27\lib\site-packages\django-1.3.4-py2.7.egg\django\db\models\query.py", line 84, in __len__
    self._result_cache.extend(self._iter)
  File "C:\Dev\Python27\lib\site-packages\django-1.3.4-py2.7.egg\django\db\models\query.py", line 273, in iterator
    for row in compiler.results_iter():
  File "C:\Dev\Python27\lib\site-packages\django-1.3.4-py2.7.egg\django\db\models\sql\compiler.py", line 680, in results
_iter
    for rows in self.execute_sql(MULTI):
  File "C:\Dev\Python27\lib\site-packages\django-1.3.4-py2.7.egg\django\db\models\sql\compiler.py", line 735, in execute
_sql
    cursor.execute(sql, params)
  File "C:\Dev\Python27\lib\site-packages\django-1.3.4-py2.7.egg\django\db\backends\util.py", line 34, in execute
    return self.cursor.execute(sql, params)
  File "C:\Dev\Python27\lib\site-packages\django-1.3.4-py2.7.egg\django\db\backends\postgresql_psycopg2\base.py", line 4
4, in execute
    return self.cursor.execute(query, args)
DatabaseError: syntax error at or near ")"
LINE 1: ...ugtest_book" WHERE "bugtest_book"."author_id" IN () LIMIT 21
                                                             ^
>>> import django; django.VERSION
(1, 3, 4, 'final', 0) 

It's also database dependent, I couldn't reproduce the issue with SQLite.

[Aymeric Augustin]

I could reproduce the failure under PostgreSQL.

[Marcin Biernat]

This was a bug related to improper behaviour of querysets sliced with equal indices, which generated empty sql that caused errors when used in subqueries.

This query failed:

Book.objects.filter(author__in=Author.objects.empty()[0:0])

while this succeded:

Book.objects.filter(author__in=Author.objects.empty())

Instead of later returning empty sql string, a QuerySet with equal indices is now immediately converted to QuerySet.none().

​https://github.com/django/django/pull/808

[Grzegorz Szczepańczyk]

seems ok

[Grzegorz Szczepańczyk]

seems ok

[Marcin Biernat]

Fixed by checking for empty sql params
​https://github.com/django/django/pull/813

[Marcin Biernat <mb@…>]

In 2b76f19f2b89ac96bae2a169d71b23553c8101c7:

fixes #19263

[Honza Král <Honza.Kral@…>]

In fc38d6a92be8baec1d02fbdf95e56c4204b9425b:

Merge pull request #813 from HiddenData/ticket-19263

fixes #19263 - EmptyResultSet in subquery causes incorrect SQL

[Aymeric Augustin]

This commit introduced test failures under all databases except SQLite.

If it cannot be fixed quickly, it should be reverted to avoid getting in the way of the sprints today.

[Honza Kral <honza.kral@…>]

In cbb9f629b88d97dd9a3f8d425fd56c8b80d7cddf:

Revert "fixes #19263" - Fails if not SQLite

This reverts commit 2b76f19f2b89ac96bae2a169d71b23553c8101c7.

[Honza Král]

Reverted in cbb9f629b88d97dd9a3f8d425fd56c8b80d7cddf, working on it.

[anonymous]

fixed and tested on sqlite, pgsql, oracle and mysql:

​https://github.com/django/django/pull/830

[Tim Graham]

Patch no longer applies cleanly after 97774429aeb54df4c09895c07cd1b09e70201f7d

[Tim Graham]

​PR

[Tim Graham <timograham@…>]

In 233b46f9:

Fixed #19263 -- Fixed crash when filtering using in and an empty QuerySet.

Thanks Marcin Biernat for the initial patch and tests.