Code

Opened 18 months ago

Last modified 11 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

Attachments (0)

Change History (17)

comment:1 Changed 17 months 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 17 months 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 14 months ago by biern

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

comment:4 Changed 14 months 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 14 months ago by biern (previous) (diff)

comment:5 Changed 14 months ago by biern

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

comment:6 Changed 14 months ago by gszczepanczyk

  • Triage Stage changed from Accepted to Ready for checkin

seems ok

comment:7 Changed 14 months ago by gszczepanczyk

  • Triage Stage changed from Accepted to Ready for checkin

seems ok

comment:8 Changed 14 months ago by biern

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

comment:9 Changed 14 months ago by Marcin Biernat <mb@…>

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

comment:10 Changed 14 months 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 14 months 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 14 months ago by aaugustin (previous) (diff)

comment:12 Changed 14 months 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 14 months ago by Honza_Kral

  • Resolution fixed deleted
  • Status changed from closed to new

Reverted in cbb9f629b88d97dd9a3f8d425fd56c8b80d7cddf, working on it.

comment:14 Changed 14 months 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 14 months ago by biern

  • Owner changed from anonymous to biern

comment:16 Changed 14 months ago by gnosek

  • Triage Stage changed from Accepted to Ready for checkin

comment:17 Changed 11 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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as assigned
The owner will be changed from biern to anonymous. Next status will be 'assigned'
The ticket will be disowned. Next status will be 'new'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.