id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
9188	Postgresql 'missing FROM-clause entry in subquery for table' error on lookup that spans relationships	naitsirhc	Malcolm Tredinnick	"This is a bug discussed in the Django users Google Group: 
http://groups.google.com/group/django-users/browse_thread/thread/3940c77215fa9fa7?hl=en#

I'm encountering an error when performing a lookup that spans
relationships.  The query is as follows:

{{{
myitems = MyItem.objects_all.exclude(user__somemodel__created__gte=(datetime.now()-timedelta(days=3)))
}}}

With the following (stripped) models:

{{{
class MyItem(models.Model):
    user = models.ForeignKey(user, unique=True)

class SomeModel(models.Model):
    created = models.DateTimeField('Date created', default=datetime.now)
    user = models.ForeignKey(User)
}}}

The error I get is:

{{{
Traceback (most recent call last):
  ....
  File ""/usr/lib/python2.5/site-packages/django/db/models/query.py"",
line 179, in _result_iter
    self._fill_cache()
  File ""/usr/lib/python2.5/site-packages/django/db/models/query.py"",
line 612, in _fill_cache
    self._result_cache.append(self._iter.next())
  File ""/usr/lib/python2.5/site-packages/django/db/models/query.py"",
line 269, in iterator
    for row in self.query.results_iter():
  File ""/usr/lib/python2.5/site-packages/django/db/models/sql/
query.py"", line 206, in results_iter
    for rows in self.execute_sql(MULTI):
  File ""/usr/lib/python2.5/site-packages/django/db/models/sql/
query.py"", line 1723, in execute_sql
    cursor.execute(sql, params)
  File ""/usr/lib/python2.5/site-packages/django/db/backends/util.py"",
line 19, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: missing FROM-clause entry in subquery for
table ""u1""
LINE 1: ..._myitem"" U0 INNER JOIN ""notes_note"" U2 ON (U1.""id"" = ...
                                                      ^
}}}

To debug the issue, I looked at the SQL generated by the 'myitems'
queryset above.  The generated SQL is:

{{{
SELECT ""users_myitem"".""id"", ""users_myitem"".""user_id""
FROM ""users_myitem""
WHERE NOT (
  ""users_myitem"".""user_id"" IN (
    SELECT U2.""user_id""
    FROM ""users_myitem"" U0
    INNER JOIN ""myapp_somemodel"" U2 ON (U1.""id"" = U2.""user_id"")
    WHERE U2.""created"" >= 2008-09-19 19:57:43.111687
  )
)
}}

It looks like the table ""users_myitem"" is being improperly labeled as
'U0', and then is referred to as 'U1' on the next line.  If I correct
this issue and run the SQL command manually, I get the expected
records returned.  The corrected SQL is as follows:

{{{
SELECT ""users_myitem"".""id"", ""users_myitem"".""user_id""
FROM ""users_myitem""
WHERE NOT (
  ""users_myitem"".""user_id"" IN (
    SELECT U2.""user_id""
    FROM ""users_myitem"" U1
    INNER JOIN ""myapp_somemodel"" U2 ON (U1.""id"" = U2.""user_id"")
    WHERE U2.""created"" >= '2008-09-19 19:51:43.151089'
  )
)
}}}"		closed	Database layer (models, ORM)	1.0		fixed	Postgresql,join,relationships	brent.hagany@… erik@…	Unreviewed	0	0	0	0	0	0