Opened 4 years ago

Closed 15 months ago

#19263 closed Bug (fixed)

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

Reported by: chkwok@… Owned by: Marcin Biernat
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords:
Cc: timograham@… Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Aymeric Augustin)

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

Change History (20)

comment:1 Changed 4 years ago by 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.

comment:2 Changed 4 years ago by Aymeric Augustin

Component: UncategorizedCore (Other)
Description: modified (diff)
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

I could reproduce the failure under PostgreSQL.

comment:3 Changed 4 years ago by Marcin Biernat

Owner: changed from nobody to Marcin Biernat
Status: newassigned

comment:4 Changed 4 years ago by Marcin Biernat

Has patch: set

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

Last edited 4 years ago by Marcin Biernat (previous) (diff)

comment:5 Changed 4 years ago by Marcin Biernat

Component: Core (Other)Database layer (models, ORM)

comment:6 Changed 4 years ago by Grzegorz Szczepańczyk

Triage Stage: AcceptedReady for checkin

seems ok

comment:7 Changed 4 years ago by Grzegorz Szczepańczyk

Triage Stage: AcceptedReady for checkin

seems ok

comment:8 Changed 4 years ago by Marcin Biernat

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

comment:9 Changed 4 years ago by Marcin Biernat <mb@…>

Resolution: fixed
Status: assignedclosed

comment:10 Changed 4 years ago by Honza Král <Honza.Kral@…>

In fc38d6a92be8baec1d02fbdf95e56c4204b9425b:

Merge pull request #813 from HiddenData/ticket-19263

fixes #19263 - EmptyResultSet in subquery causes incorrect SQL

comment:11 Changed 4 years ago by Aymeric Augustin

Resolution: fixed
Status: closednew
Triage Stage: Ready for checkinAccepted

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.

http://ci.djangoproject.com/job/Django/2353/testReport/

Last edited 4 years ago by Aymeric Augustin (previous) (diff)

comment:12 Changed 4 years ago by Honza Kral <honza.kral@…>

Resolution: fixed
Status: newclosed

In cbb9f629b88d97dd9a3f8d425fd56c8b80d7cddf:

Revert "fixes #19263" - Fails if not SQLite

This reverts commit 2b76f19f2b89ac96bae2a169d71b23553c8101c7.

comment:13 Changed 4 years ago by Honza Král

Resolution: fixed
Status: closednew

Reverted in cbb9f629b88d97dd9a3f8d425fd56c8b80d7cddf, working on it.

comment:14 Changed 4 years ago by anonymous

Owner: changed from Marcin Biernat to anonymous
Status: newassigned

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

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

comment:15 Changed 4 years ago by Marcin Biernat

Owner: changed from anonymous to Marcin Biernat

comment:16 Changed 4 years ago by Grzegorz Nosek

Triage Stage: AcceptedReady for checkin

comment:17 Changed 4 years ago by Tim Graham

Cc: timograham@… added
Patch needs improvement: set
Triage Stage: Ready for checkinAccepted

Patch no longer applies cleanly after 97774429aeb54df4c09895c07cd1b09e70201f7d

comment:18 Changed 16 months ago by Tim Graham

Patch needs improvement: unset
Summary: DatabaseError on using empty Page .object_list in __in clause in a queryFiltering __in a sliced queryset with a 0 limit raises an error

comment:19 Changed 15 months ago by Josh Smeaton

Triage Stage: AcceptedReady for checkin

comment:20 Changed 15 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 233b46f9:

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

Thanks Marcin Biernat for the initial patch and tests.

Note: See TracTickets for help on using tickets.
Back to Top