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" = ...
Change UI/UX from NULL to False.