Opened 2 years ago

Last modified 22 months ago

#19263 assigned Bug

DatabaseError on using empty Page .object_list in __in clause in a query

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

Description (last modified by aaugustin)

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 (17)

comment:1 Changed 2 years ago by chkwok

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 2 years ago by aaugustin

  • Component changed from Uncategorized to Core (Other)
  • Description modified (diff)
  • Triage Stage changed from Unreviewed to Accepted
  • Type changed from Uncategorized to Bug

I could reproduce the failure under PostgreSQL.

comment:3 Changed 2 years ago by biern

  • Owner changed from nobody to biern
  • Status changed from new to assigned

comment:4 Changed 2 years ago by biern

  • 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 2 years ago by biern (previous) (diff)

comment:5 Changed 2 years ago by biern

  • Component changed from Core (Other) to Database layer (models, ORM)

comment:6 Changed 2 years ago by gszczepanczyk

  • Triage Stage changed from Accepted to Ready for checkin

seems ok

comment:7 Changed 2 years ago by gszczepanczyk

  • Triage Stage changed from Accepted to Ready for checkin

seems ok

comment:8 Changed 2 years ago by biern

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

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

  • Resolution set to fixed
  • Status changed from assigned to closed

comment:10 Changed 2 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 2 years ago by aaugustin

  • Resolution fixed deleted
  • Status changed from closed to new
  • Triage Stage changed from Ready for checkin to Accepted

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 2 years ago by aaugustin (previous) (diff)

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

  • Resolution set to fixed
  • Status changed from new to closed

In cbb9f629b88d97dd9a3f8d425fd56c8b80d7cddf:

Revert "fixes #19263" - Fails if not SQLite

This reverts commit 2b76f19f2b89ac96bae2a169d71b23553c8101c7.

comment:13 Changed 2 years ago by Honza_Kral

  • Resolution fixed deleted
  • Status changed from closed to new

Reverted in cbb9f629b88d97dd9a3f8d425fd56c8b80d7cddf, working on it.

comment:14 Changed 2 years ago by anonymous

  • Owner changed from biern to anonymous
  • Status changed from new to assigned

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

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

comment:15 Changed 2 years ago by biern

  • Owner changed from anonymous to biern

comment:16 Changed 2 years ago by gnosek

  • Triage Stage changed from Accepted to Ready for checkin

comment:17 Changed 22 months ago by timo

  • Cc timograham@… added
  • Patch needs improvement set
  • Triage Stage changed from Ready for checkin to Accepted

Patch no longer applies cleanly after 97774429aeb54df4c09895c07cd1b09e70201f7d

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