Code

Opened 3 years ago

Closed 7 months ago

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

Description

class U(models.Model):
    pass

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
list(q0)
print "----"
# This query generates broken SQL
q1 = models.L.objects.exclude(u1__p=F('u2__p'))
print q1.query
list(q1)

Output:

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 "./manage.py", line 11, in <module>
    execute_manager(settings)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/core/management/__init__.py", line 438, in execute_manager
    utility.execute()
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/core/management/__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/core/management/base.py", 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/base.py", 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/base.py", line 351, in handle
    return self.handle_noargs(**options)
  File "/home/gerdemb/Desktop/vagabooks/vagabooks/vagabooks/library/management/commands/foo.py", line 17, in handle_noargs
    list(q1)
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/db/models/query.py", line 83, in __len__
    self._result_cache.extend(list(self._iter))
  File "/usr/local/lib/python2.6/dist-packages/Django-1.2.3-py2.6.egg/django/db/models/query.py", 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/compiler.py", 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/compiler.py", 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/util.py", 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/base.py", 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 charettes 7 months ago.

Download all attachments as: .zip

Change History (5)

comment:1 Changed 3 years ago by jonash

  • Easy pickings unset
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:3 Changed 7 months ago by charettes

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

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

comment:4 Changed 7 months 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


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

 
Note: See TracTickets for help on using tickets.