Postgresql 'missing FROM-clause entry in subquery for table' error on lookup that spans relationships

[naitsirhc]

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'
  )
)
}}}

[anonymous]

The same error occurs with sqlite as the backend db.

[Malcolm Tredinnick]

As I mentioned in the original thread, this description doesn't contain all the information needed to replicate the problem. It refers to a "user" model that isn't described anywhere and uses a custom manager (object_all). I can't repeat the bug using a normal manager and two models pointing to a third common model.

So I'll either need a complete set of models that is self-contained and replicates the problem or a patch against django/regressiontests/queries/models.py that fails with the same problem. At the moment, I'm a bit stuck as to how to reveal the problem and it's not caused by what I thought might have been the issue.

[Brent Hagany]

Replying to mtredinnick:

As I mentioned in the original thread, this description doesn't contain all the information needed to replicate the problem. It refers to a "user" model that isn't described anywhere and uses a custom manager (object_all). I can't repeat the bug using a normal manager and two models pointing to a third common model.

So I'll either need a complete set of models that is self-contained and replicates the problem or a patch against django/regressiontests/queries/models.py that fails with the same problem. At the moment, I'm a bit stuck as to how to reveal the problem and it's not caused by what I thought might have been the issue.

This is the attachment I put on the ticket I opened about this (#9192), which includes three very simple models that replicate this issue. I haven't tried it against a recent revision, but I don't think any major changes have been made that would affect this behavior.

[Brent Hagany]

messed the original upload up somehow

[Malcolm Tredinnick]

Excellent. Thanks, Brent. Sorry for forgetting about the example on the duplicate ticket. I don't know why my almost identical case didn't trigger this, but you win the prize for "simplest possible example." Fails reliably now (only in debugging is this considered a good thing).

[Brent Hagany]

Replying to mtredinnick:

Heh, I don't think anybody expects you to remember every duplicate ticket :). If you need anything else to fail all the time, just let me know. I seem to have a knack.

[Malcolm Tredinnick]

My problem turned out to be much more fundamental: my test case fails as expected when I remember to run it against trunk. I was accidentally running it against my local branch of SQL-exclude fixes where I have already fixed this problem just from code inspection.

[Brent Hagany]

Excellent news! I eagerly await the commit. Thanks, Malcolm.

[Malcolm Tredinnick]

(In [9588]) Fixed #9188 -- Fixed a case where we were generating syntactically invalid SQL in some exclude() queries.

[Malcolm Tredinnick]

(In [9589]) [1.0.X] Fixed #9188 -- Fixed a case where we were generating syntactically invalid SQL in some exclude() queries.

Backport of r9588 from trunk.