Opened 7 years ago

Closed 5 years ago

Last modified 5 years ago

#15786 closed Bug (fixed)

Wrong SQL for OneToOneField with exclude() using F() parameter

Reported by: gerdemb Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


class U(models.Model):

class P(models.Model):
    c = models.CharField(max_length=256)
    u = models.OneToOneField(U)

class L(models.Model):
    u1 = models.ForeignKey(U, related_name='u1')
    u2 = models.ForeignKey(U, related_name='u2')

# This query works
q0 = models.L.objects.filter(u1__p=F('u2__p'))
print q0.query
print "----"
# This query generates broken SQL
q1 = models.L.objects.exclude(u1__p=F('u2__p'))
print q1.query


SELECT "library_l"."id", "library_l"."u1_id", "library_l"."u2_id" FROM "library_l" INNER JOIN "library_u" ON ("library_l"."u2_id" = "library_u"."id") INNER JOIN "library_p" ON ("library_u"."id" = "library_p"."u_id") INNER JOIN "library_u" T4 ON ("library_l"."u1_id" = T4."id") INNER JOIN "library_p" T5 ON (T4."id" = T5."u_id") WHERE T5."id" =  "library_p"."id"
SELECT "library_l"."id", "library_l"."u1_id", "library_l"."u2_id" FROM "library_l" INNER JOIN "library_u" ON ("library_l"."u2_id" = "library_u"."id") INNER JOIN "library_p" ON ("library_u"."id" = "library_p"."u_id") WHERE NOT (("library_l"."u1_id" IN (SELECT U4."u_id" FROM "library_l" U0 INNER JOIN "library_u" U1 ON (U0."u2_id" = U1."id") INNER JOIN "library_p" U2 ON (U1."id" = U2."u_id") INNER JOIN "library_p" U4 ON (U3."id" = U4."u_id") WHERE U4."id" =  U2."id") AND "library_l"."u1_id" IS NOT NULL))
Traceback (most recent call last):
  File "./", line 11, in <module>
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/core/management/", line 438, in execute_manager
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/core/management/", line 379, in execute
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/core/management/", line 191, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/core/management/", line 220, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/core/management/", line 351, in handle
    return self.handle_noargs(**options)
  File "/home/gerdemb/Desktop/vagabooks/vagabooks/vagabooks/library/management/commands/", line 17, in handle_noargs
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/db/models/", line 83, in __len__
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/db/models/", line 269, in iterator
    for row in compiler.results_iter():
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/db/models/sql/", line 672, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/db/models/sql/", line 727, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/db/backends/", line 15, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/db/backends/postgresql_psycopg2/", line 44, in execute
    return self.cursor.execute(query, args)
django.db.utils.DatabaseError: missing FROM-clause entry for table "u3"
LINE 1: ...1."id" = U2."u_id") INNER JOIN "library_p" U4 ON (U3."id" = ...

Attachments (1)

0001-Fixed-15786-Added-a-regression-test-for-o2o-excludes.patch (2.7 KB) - added by Simon Charette 5 years ago.

Download all attachments as: .zip

Change History (5)

comment:1 Changed 7 years ago by Jonas H.

Easy pickings: unset
Triage Stage: UnreviewedAccepted

comment:2 Changed 6 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:3 Changed 5 years ago by Simon Charette

Resolution: fixed
Status: newclosed

This is fixed in master. Attaching a testcase that fails in 1.3.x but pass on master.

comment:4 Changed 5 years ago by Simon Charette <charette.s@…>

In c4fdd859ecba0b8e6dac6eca06429e66925722bd:

Fixed #15786 -- Added a regression test for o2o excludes using F().

The issue was reported against 1.3.x but has been fixed since.

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